[原创]市级常用面板数据整合过程

这里只有作者精心编写的研究经历!
回复
头像
hellohappy
网站管理员
网站管理员
帖子: 329
注册时间: 2018年11月18日, 14:27
Has thanked: 1 time
Been thanked: 6 time

#1 [原创]市级常用面板数据整合过程

未读文章 hellohappy » 2019年1月14日, 03:00

简介:
    这里仅仅记录了当时多合并两个变量数据进来的过程,原来三个大数据库合并的过程我没做记录。可以略作参考,记录的可能有点乱。然后如果只是要数据,请直接访问这里

步骤和过程:

导入数据到数据库和以前数据合并的步骤
1.复制一个最相似的文件夹,用该文件夹下的程序重复,这次要导入的数据是省级的年度中国宏观经济数据库,和之前导入的城市级别的年度数据的很像,其中有两个数据库都是采集于eps,所以复制了区域经济统计年鉴的程序模版。

2.下载数据,按照标准名字命名,这样,导入sql时候都不需要重写程序。

3.粘贴到文件夹,执行 1单个excel的所有表纵向复制到当前excel.vbs 程序,生成new文件夹和相应的excel。

4.到sql生成新的数据库,名字为THEECONOMICSDatabase4 ,日志记得修改为简单。

5.去sql 执行 CREATE TABLE ECONOMICS。

6.数据库的访问读写权限付给登录名 uid=mysqlname

7.修改程序 全市or市辖区_指标名_年份_区域名_单位_录入数据库.vbs
    修改database名字,修改sql语句插入的表 ,由于和区域经济统计年鉴太像,基本上不用改什么。

8.执行 全市or市辖区_指标名_年份_区域名_单位_录入数据库.vbs 插入数据库。
    出于安全起见,你应该先关掉用户名的对其他数据库的权限,免得插错数据库。
    出错请关闭进程 wscript.exe 重新删表(因为id会自己加,如果只是情况表的话。)
    该表的数据从第二行开始。年份为1949到2017 。

9.检查结果没什么问题,打开trueregionname文件夹,修改数据,注意吉林省和吉林市之前的匹配方式。

10.计算地区名字和地区对应的城市数目,命令如下:

Code: 全选

 select REGIONNAME,count(REGIONNAME) as COUNTREGIONNAME from ECONOMICS group by REGIONNAME order by REGIONNAME;

10.复制计算结果,并修改 匹配区域名字的表格修正.xls 其中吉林改为吉林省。

11.根据 7.0区域名字的修正录入表TRUEREGIONNAME.vbs
    新建表 CREATE TABLE TRUEREGIONNAME 。
    修改 TRUEREGIONNAME.vbs (注意数据库,辛亏前面删了权限,不然又凉凉)并拖动Excel执行。

12.打开regionbelong 文件夹 分地区城市情况用于检查是否有遗漏.xls 原来是用于检查是否有不在原先城市名字列表的城市,由于这个是省级数据,显然不需要检查 。

13.进入新建文件夹 根据 7.5根据调整表录入REGIONBELONG.vbs 执行sql语句 CREATE TABLE REGIONBELONG。这个数据表其实对全部数据库都是一样的,建一个只是为了数据的完整性和完整性检查而已。

14.修改 根据 7.5根据调整表录入REGIONBELONG.vbs 并拖动执行。

15.给表和表之间建立外键,以检查是否有录错。关系如图:
修改以后的sql关系如图.png
修改以后的sql关系如图.png (30.57 KiB) 查看 152 次
修改以后的sql关系如图.png
修改以后的sql关系如图.png (30.57 KiB) 查看 152 次
ECONOMICS.png
ECONOMICS.png (16.85 KiB) 查看 152 次
ECONOMICS.png
ECONOMICS.png (16.85 KiB) 查看 152 次
REGIONBELONG.png
REGIONBELONG.png (24.45 KiB) 查看 152 次
REGIONBELONG.png
REGIONBELONG.png (24.45 KiB) 查看 152 次
TRUEREGIONNAME.png
TRUEREGIONNAME.png (10.21 KiB) 查看 152 次
TRUEREGIONNAME.png
TRUEREGIONNAME.png (10.21 KiB) 查看 152 次

16.打开最外层的文件夹,查看文件 数据提取.sql 修改一下数据库的名字,其他的变化不大。 在sql server 执行命令。
数据提取.zip
(5.53 KiB)
数据提取.zip
(5.53 KiB) 尚未被下载

17.然后就完成了这个数据集合的数据整理,要用这个数据直接查看视图或者直接查看用视图拼接的数据表 RESULTVIEW,下面是要与其他数据进行合并数据。

18.由于我们的目的是与别的数据进行合并,而之前已经合并过了,所以只需要根据数据库 ECONOMICSDatabase 联合查询一下,生成一个有相同序列顺序的数据结果就行,最后只要复制过去就好。

19.找一个参考程序 合并中国城市统计年鉴和区域经济统计年鉴和国家统计局.sql
    由于我们的目的不是再次合并,所以不需要再生成总表。(事实上,由于我们新数据有1949年到2017年,数据应该会多出几年,新表应该时间维度会变长,但是这里的目的不是这个,所以不需要执行这个sql文件的前面部分。)运行这段sql程序显然应该在有操作所有表的权限的用户上面进行。查询代码如下:

Code: 全选

--根据InformationOtherThanVariableNames这个总表联合中国城市统计年鉴和区域经济统计年鉴的表生成要用的表视图
select * from 
    InformationOtherThanVariableNames as p
    LEFT JOIN THEECONOMICSDatabase.dbo.RESULTVIEW as p1
    on p.[year] = p1.ISSUEYEAR and p.[地区名] = p1.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase.dbo.RESULTVIEW2 as p2
    ON p.[year] = p2.ISSUEYEAR and p.[地区名] = p2.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW as p3
    ON p.[year] = p3.ISSUEYEAR and p.[地区名] = p3.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW2 as p4
    ON p.[year] = p4.ISSUEYEAR and p.[地区名] = p4.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW3 as p5
    ON p.[year] = p5.ISSUEYEAR and p.[地区名] = p5.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW4 as p6
    ON p.[year] = p6.ISSUEYEAR and p.[地区名] = p6.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW5 as p7
    ON p.[year] = p7.ISSUEYEAR and p.[地区名] = p7.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW6 as p8
    ON p.[year] = p8.ISSUEYEAR and p.[地区名] = p8.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase3.dbo.RESULTVIEW as p9
    ON p.[year] = p9.ISSUEYEAR and p.[地区名] = p9.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase3.dbo.RESULTVIEW2 as p10
    ON p.[year] = p10.ISSUEYEAR and p.[地区名] = p10.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase4.dbo.RESULTVIEW as p11
    ON p.[year] = p11.ISSUEYEAR and p.[地区名] = p11.THETRUEREGIONNAME
    order by p.按地区时间排序号

--上面这个完成了他成表的任务,但是有很多完全是空的城市,完全可以删掉以简化表格,所以加个where
select *  from 
    InformationOtherThanVariableNames as p
    LEFT JOIN THEECONOMICSDatabase.dbo.RESULTVIEW as p1
    on p.[year] = p1.ISSUEYEAR and p.[地区名] = p1.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase.dbo.RESULTVIEW2 as p2
    ON p.[year] = p2.ISSUEYEAR and p.[地区名] = p2.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW as p3
    ON p.[year] = p3.ISSUEYEAR and p.[地区名] = p3.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW2 as p4
    ON p.[year] = p4.ISSUEYEAR and p.[地区名] = p4.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW3 as p5
    ON p.[year] = p5.ISSUEYEAR and p.[地区名] = p5.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW4 as p6
    ON p.[year] = p6.ISSUEYEAR and p.[地区名] = p6.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW5 as p7
    ON p.[year] = p7.ISSUEYEAR and p.[地区名] = p7.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase2.dbo.RESULTVIEW6 as p8
    ON p.[year] = p8.ISSUEYEAR and p.[地区名] = p8.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase3.dbo.RESULTVIEW as p9
    ON p.[year] = p9.ISSUEYEAR and p.[地区名] = p9.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase3.dbo.RESULTVIEW2 as p10
    ON p.[year] = p10.ISSUEYEAR and p.[地区名] = p10.THETRUEREGIONNAME
    LEFT JOIN THEECONOMICSDatabase4.dbo.RESULTVIEW as p11
    ON p.[year] = p11.ISSUEYEAR and p.[地区名] = p11.THETRUEREGIONNAME
    where p1.Region_ID <> '' or p3.Region_ID <>'' or p9.Region_ID<>''
    or p.[地区名] in
    (select distinct THETRUEREGIONNAME from THEECONOMICSDatabase.dbo.RESULTVIEW
        UNION
        select distinct THETRUEREGIONNAME from THEECONOMICSDatabase2.dbo.RESULTVIEW
        UNION
        select distinct THETRUEREGIONNAME from THEECONOMICSDatabase3.dbo.RESULTVIEW
        )
    order by p.按地区时间排序号
20.查询出来的结果根据全表唯一id进行匹配,那个是唯一不会出错的id。
    数据表如下:
国家统计局.xls
(33 KiB) 尚未被下载
    其中,收集中央和省的一般公共预算收入和支出,并根据city2.dta进行数据匹配
        中央数据来源于国家统计局,省数据来源于中国宏观经济统计年鉴
        由于没有中央的一般公共预算收入和支出这个指标,用 中央财政收入(亿元)和中央财政支出(亿元) 替代,个体名字为全国

21.stata 导入 合并以后用于匹配的数据.xlsx 执行(其中为了防止重名导致匹配错误,提前改好名字):

Code: 全选

drop if year < 1999
keep year 所属省名 所属省拼音 地区名 地区的级别 是否直辖市 城市名称 城市唯一ID 六大经济协作区 热点地区 东部西部地区 东中西地区12910 八大经济区域 全表唯一ID 一般预算收入亿元 一般预算支出亿元
save "C:\Users\Administrator\Desktop\20190115匹配数据\city2_use2merge.dta"
rename 一般预算收入亿元 _一般预算收入亿元
rename 一般预算支出亿元 _一般预算支出亿元

22.打开city2.dta,并执行下面命令以合并数据。

Code: 全选

merge 1:1 全表唯一ID using"C:\Users\Administrator\Desktop\20190115匹配数据\city2_use2merge.dta"

结果可以看此链接

Link:
Hide post links
Show post links


回复