@fanxy
2016-10-09T16:30:42.000000Z
字数 9815
阅读 1648
樊潇彦
复旦大学经济学院
中级宏观
library(readxl) # 读取excel数据
library(stringr) # 字符串处理
library(corrplot)
library(igraph)
library(forecast)
library(stats) # 基础包,不用安装直接调用
library(dplyr)
library(tidyr)
library(data.table)
library(foreign)
library(readstata13)
library(haven)
library(ggplot2)
library(ggrepel)
library(dygraphs)
library(plotrix)
library(lubridate)
library(zoo)
library(mFilter)
setwd("D:\\") # 设定工作目录
cz=read_excel("Ch05_Data.xlsx",sheet="财政部")
colnames(cz)=c("unit","var",seq(1995,2014))
# 规模
g_scale=cz%>%
select(-unit)%>%
filter(var %in% c("汇编企业户数","汇编企业户数:中央企业","汇编企业户数:地方企业",
"所有者权益总额","所有者权益总额:中央","所有者权益总额:地方"))%>%
mutate(level=ifelse(regexpr("中央",var)>0,"中央",ifelse(regexpr("地方",var)>0,"地方","全部")))%>%
mutate(var=sub(":.*","",var))%>%
mutate(var=ifelse(var=="所有者权益总额","所有者权益总额(万亿元)","汇编企业户数(万户)"))%>%
gather(year,value,-var,-level)%>%
mutate(year=as.numeric(year))%>%
mutate(value=ifelse(var=="所有者权益总额(万亿元)",value/10000,value))%>%
filter(!is.na(value))%>%
arrange(var,level,year)
ggplot(g_scale,aes(year,value,color=level))+geom_line(size=1)+facet_wrap(~var)+
labs(title="全国国有企业:规模",x="",y="")+
scale_x_continuous(breaks = c(1997, 2003, 2008, 2014))+
guides(color=guide_legend(title=NULL))+theme_bw()+theme(legend.position="bottom")
# 盈利
g_prof=cz%>%
select(-unit)%>%
filter(var %in% c("总资产报酬率","净资产利润率","销售利润率"))%>%
gather(year,value,-var)%>%
mutate(year=as.numeric(year))%>%
filter(!is.na(value))%>%
arrange(var,year)
ggplot(g_prof,aes(year,value,color=var))+geom_line(size=1)+
labs(title="全国国有企业:盈利",x="",y="")+
scale_x_continuous(breaks = c(1997, 2003, 2008, 2014))+
guides(color=guide_legend(title=NULL))+theme_bw()+theme(legend.position="bottom")
# 负债
g_risk=cz%>%
select(-unit)%>%
filter(var %in% c("资产总额","资产总额:中央企业","资产总额:地方企业",
"负债总额","负债总额:中央","负债总额:地方",
"所有者权益总额","所有者权益总额:中央","所有者权益总额:地方"))%>%
gather(year,value,-var)%>%
mutate(year=as.numeric(year))%>%
filter(!is.na(value))%>%
mutate(level=ifelse(regexpr("中央",var)>0,"中央",ifelse(regexpr("地方",var)>0,"地方","全部")))%>%
mutate(var=sub(":.*","",var))%>%
spread(var,value)%>%
mutate(`杠杆率`=`负债总额`/`所有者权益总额`)%>%
mutate(`资产负债率`=`负债总额`/`资产总额`)%>%
select(year,level,`杠杆率`,`资产负债率`)%>%
gather(var,value,-year,-level)
ggplot(g_risk,aes(year,value,color=level))+geom_line(size=1)+facet_free(~var)+
labs(title="全国国有企业:债务",x="",y="")+
scale_x_continuous(breaks = c(1997, 2003, 2008, 2014))+
guides(color=guide_legend(title=NULL))+theme_bw()+theme(legend.position="bottom")
gz=read_excel("Ch05_Data.xlsx",sheet="国资委")
colnames(gz)=c("unit","var",seq(2005,2014))
gz=gz%>%
select(-unit)%>%
mutate(group=sub(".*:","",var))%>%
mutate(var=sub(":.*","",var))%>%
gather(year,value,-var,-group)%>%
mutate(year=as.numeric(year))%>%
filter(!is.na(value))%>%
spread(var,value)%>%
mutate(`杠杆率`=`资产负债率`/(100-`资产负债率`))%>%
mutate(gtype=ifelse(group %in% c("大型企业","中型企业","小型企业"),"规模",""))%>%
mutate(gtype=ifelse(group %in% c("国资委系统监管企业","政府部门管理企业","地方企业"),"央地",gtype))%>%
mutate(gtype=ifelse(group %in% c("东部沿海地区","中部内陆地区","西部边远地区"),"区域",gtype))%>%
mutate(gtype=ifelse(group %in% c("盈利企业","亏损企业"),"盈亏",gtype))%>%
filter(group!="中央企业" & !(year==2014 & gtype=="央地"))%>%
group_by(year,gtype)%>%
mutate(`净资产占比`=`所有者权益(净资产)`/sum(`所有者权益(净资产)`))%>%
mutate(color=ifelse(group %in% c("大型企业","国资委系统监管企业","东部沿海地区","盈利企业"),"1","2"))%>%
mutate(color=ifelse(group %in% c("小型企业","地方企业","西部边远地区"),"3", color))%>%
mutate(label=ifelse(year==2009,group,""))
ggplot(gz,aes(year,`净资产占比`,color=color))+geom_line(size=1)+facet_wrap(~gtype)+
scale_x_continuous(breaks = c(2005, 2008, 2014))+
labs(title="不同组别净资产占比",x="",y="")+geom_text(aes(label=label))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")
ggplot(gz,aes(year,`杠杆率`,color=color))+geom_line(size=1)+facet_wrap(~gtype)+
scale_x_continuous(breaks = c(2005, 2008, 2014))+
labs(title="不同组别杠杆率比较",x="",y="")+geom_text(aes(label=label))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")
# 行业及代码
ind=data.frame(indno=1:13,ind=c("农林牧渔业","工业","建筑业","地质勘查及水利业","交通运输仓储业",
"邮电通信业","批发和零售、餐饮业","房地产业","信息技术服务业","社会服务业",
"卫生体育福利业","教育文化广播业","科学研究和技术服务业"))
ind$indno=factor(ind$ind,ordered=is.ordered(ind$indno))
ind$ind=as.character(ind$ind)
ind_m=cz%>%
select(var)%>%
filter(substring(var,nchar(var)-1)=="工业")%>%
mutate(var=sub(".*工业:","",var))%>%
mutate(var=sub(".*:工业|汽车工业",NA,var))%>%
filter(!is.na(var))
ind_m=data.frame(indno=201:215,ind=unique(ind_m$var))
ind_m$indno=factor(ind_m$ind,ordered=is.ordered(ind_m$indno))
ind_m$ind=as.character(ind_m$ind)
ind_cz=cz%>%
select(-unit)%>%
filter(substring(var,nchar(var))=="业" & substring(var,nchar(var)-1)!="企业") %>%
mutate(ind=sub(".*:","",var))%>%
mutate(var=sub(":.*","",var))%>%
gather(year,value,-var,-ind)%>%
mutate(year=as.numeric(year))%>%
filter(!is.na(value)) %>%
spread(var,value)
# 行业规模占比
g_ind_scale=merge(ind_cz,ind,by="ind")
g_ind_scale=g_ind_scale%>%
group_by(year)%>%
mutate(`职工人数占比`=`职工人数`/sum(`职工人数`))%>%
mutate(`所有者权益占比`=`所有者权益总额`/sum(`所有者权益总额`))%>%
select(year,indno,`职工人数占比`,`所有者权益占比`)%>%
gather(var,value,-year,-indno)%>%
group_by(year,var)%>%
mutate(rk=n()+1-min_rank(value))%>%
filter(year %in% c(1995,2003,2014))%>%
arrange(year,indno)
ggplot(g_ind_scale[g_ind_scale$rk<=5,],aes(rk,value,fill=indno))+facet_grid(var~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国企规模:按行业分",x="",y="")+geom_text(aes(label=indno))+
guides(fill=guide_legend(title=NULL))+theme_bw()+theme(legend.position="non")
# 工业规模占比
g_m_scale=merge(ind_cz,ind_m,by="ind")
g_m_scale=g_m_scale%>%
group_by(year)%>%
mutate(`职工人数占比`=`职工人数`/sum(`职工人数`))%>%
mutate(`所有者权益占比`=`所有者权益总额`/sum(`所有者权益总额`))%>%
select(year,indno,`职工人数占比`,`所有者权益占比`)%>%
gather(var,value,-year,-indno)%>%
group_by(year,var)%>%
mutate(rk=n()+1-min_rank(value))%>%
filter(year %in% c(1995,2003,2014))%>%
arrange(year,indno)
ggplot(g_m_scale[g_m_scale$rk<=5,],aes(rk,value,fill=indno))+facet_grid(var~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国企规模:按工业行业分",x="",y="")+geom_text(aes(label=indno))+
guides(fill=guide_legend(title=NULL))+theme_bw()+theme(legend.position="non")
# 行业财务状况
g_ind_prof=merge(ind_cz,ind,by="ind")
g_ind_prof=g_ind_prof%>%
mutate(`利税/总资产`=(`利润总额`+`上交税金总额`)/`资产总额`)%>%
mutate(`利税/所有者权益`=(`利润总额`+`上交税金总额`)/`所有者权益总额`)%>%
mutate(`人均利税`=(`利润总额`+`上交税金总额`)/`职工人数`)%>%
mutate(`杠杆率`=`负债合计`/`所有者权益总额`)%>%
mutate(`资产负债率`=`负债合计`/`资产总额`)%>%
select(year,indno,`利税/总资产`:`资产负债率`) %>%
gather(var,value,-year,-indno)%>%
filter(!is.na(value))%>%
group_by(year,var)%>%
mutate(rk=n()+1-min_rank(value))%>%
filter(year %in% c(2003,2014))
ggplot(g_ind_prof[g_ind_prof$var %in% c("利税/总资产","利税/所有者权益","人均利税") &
g_ind_prof$rk<=5,],aes(rk,value,fill=indno))+facet_free(var~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国企盈利能力:按行业分",x="",y="")+
geom_text(aes(label=indno,vjust = 1.2, angle = 10))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")
ggplot(g_ind_prof[g_ind_prof$var %in% c("杠杆率","资产负债率") &
g_ind_prof$rk<=5,],aes(rk,value,fill=indno))+facet_free(var~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国企债务负担:按行业分",x="",y="")+
geom_text(aes(label=indno,vjust = 1.2, angle = 10))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")
# 工业财务状况
g_m_prof=merge(ind_cz,ind_m,by="ind")
g_m_prof=g_m_prof%>%
mutate(`利税/总资产`=(`利润总额`+`上交税金总额`)/`资产总额`)%>%
mutate(`利税/所有者权益`=(`利润总额`+`上交税金总额`)/`所有者权益总额`)%>%
mutate(`人均利税`=(`利润总额`+`上交税金总额`)/`职工人数`)%>%
mutate(`杠杆率`=`负债合计`/`所有者权益总额`)%>%
mutate(`资产负债率`=`负债合计`/`资产总额`)%>%
select(year,indno,`利税/总资产`:`资产负债率`) %>%
gather(var,value,-year,-indno)%>%
filter(!is.na(value))%>%
group_by(year,var)%>%
mutate(rk=n()+1-min_rank(value))%>%
filter(year %in% c(2003,2014))
ggplot(g_m_prof[g_m_prof$var %in% c("利税/总资产","利税/所有者权益","人均利税") &
g_m_prof$rk<=5,],aes(rk,value,fill=indno))+facet_free(var~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国企盈利能力:按工业行业分",x="",y="")+
geom_text(aes(label=indno))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")
ggplot(g_m_prof[g_m_prof$var %in% c("杠杆率","资产负债率") &
g_m_prof$rk<=5,],aes(rk,value,fill=indno))+facet_free(var~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国企债务负担:按工业行业分",x="",y="")+
geom_text(aes(label=indno))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")
inc=read_excel("Ch05_Data.xlsx",sheet="国有资本经营收入")
colnames(inc)=c("unit","var",seq(2012,2015))
inc=inc%>%
select(-unit)%>%
mutate(level=substr(var,1,2))%>%
mutate(var=sub(".*经营收入:","",var))%>%
mutate(ind=ifelse(regexpr("利润收入:",var)>0,sub("利润收入:","",var),NA))%>%
mutate(var=ifelse(regexpr(":",var)<0,var,NA))
total=inc%>%
select(-ind,-level)%>%
filter(var %in% c("地方国有资本经营收入","中央国有资本经营收入"))%>%
mutate(var=substr(var,1,2))%>%
gather(year,value,-var)%>%
mutate(year=as.numeric(year))%>%
spread(var,value)%>%
mutate(`全国`=`地方`+`中央`)%>%
gather(level,value,-year)
ggplot(total,aes(year,value,color=level))+geom_line(size=1)+
labs(title="国有资本经营收入:数额",x="",y="")+
guides(color=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="bottom")
share=inc%>%
select(-ind)%>%
filter(!is.na(var))%>%
gather(year,value,-var,-level)%>%
filter(!is.na(value) & !var %in% c("上年结转收入","地方国有资本经营收入","中央国有资本经营收入"))%>%
group_by(year,level)%>%
mutate(share=value/sum(value))%>%
select(-value)
ggplot(share,aes(year,share,fill=var))+geom_bar(stat="identity")+facet_wrap(~level,ncol=3)+
labs(title="国有资本经营收入:来源",x="",y="")+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="bottom")
prof=inc%>%
select(-var)%>%
filter(!is.na(ind))%>%
mutate(ind=sub("企业","",ind))%>%
mutate(ind=ifelse(ind=="其他国有资本经营预算","其他经营",ind))%>%
gather(year,value,-ind,-level)%>%
filter(!is.na(value))%>%
group_by(year,level)%>%
mutate(share=value/sum(value))%>%
mutate(rk=n()+1-min_rank(share))%>%
select(-value)%>%
filter(year %in% c(2012,2015) & rk<=5)
ggplot(prof,aes(rk,share,fill=ind))+facet_free(level~year)+
geom_bar(stat="identity", position="dodge")+
labs(title="国有资本经营收入:行业",x="",y="")+
geom_text(aes(label=ind,vjust = 1.2))+
guides(fill=guide_legend(title=NULL))+theme_bw()+ theme(legend.position="non")