@fanxy
2016-10-09T16:30:42.000000Z
字数 9815
阅读 1653
樊潇彦 复旦大学经济学院 中级宏观
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")