Stata数据处理:清洗中国城市建设统计年鉴

发布时间:2022-03-17 阅读 2056

Stata连享会   主页 || 视频 || 推文 || 知乎 || Bilibili 站

温馨提示: 定期 清理浏览器缓存,可以获得最佳浏览体验。

New! lianxh 命令发布了:
随时搜索推文、Stata 资源。安装:
. ssc install lianxh
详情参见帮助文件 (有惊喜):
. help lianxh
连享会新命令:cnssc, ihelp, rdbalance, gitee, installpkg

课程详情 https://gitee.com/lianxh/Course

课程主页 https://gitee.com/lianxh/Course

⛳ Stata 系列推文:

PDF下载 - 推文合集

作者:初虹 (微信公众号-虹鹄山庄)
邮箱ch2099058972@163.com


目录


统计年鉴的数据不仅逐年下载麻烦,而且相较于 CSMAR、CEIC 等其他商业数据库的处理难度也更大一些。前段时间要用到《中国城市建设统计年鉴》的数据,也费了一些精力,于是便想整理出来、相互交流。

本文以《中国城市建设统计年鉴》里的「按行业全国城市市政公用设施建设固定资产投资」数据表为例。该表涉及的变量较多、页面较复杂,在数据清洗方面可能具有一定代表性。

1. 建立文件夹体系

我的习惯是为每个项目建立一个文件夹,在父文件夹下可以建立多个子文件夹。原始文件、临时文件和最终文件都有安放之所。

. * 在当前工作目录下创建 lianxh_test 文件夹
. cap mkdir lianxh_test 
. global path1 "./lianxh_test"

. * 在 "$path1" 路径下,生成 syb_cons 子文件夹,以及 Raw、Temp、Final 三个孙文件夹
. cap efolder syb_cons, cd("$path1") sub(Raw Temp Final) noc
. global DR    "$path1/syb_cons/Raw"   // 原始文件
. global DT    "$path1/syb_cons/Temp"  // 临时文件
. global DF    "$path1/syb_cons/Final" // 最终文件

2. 下载相关数据

下载数据很无聊,不过也不要掉以轻心。关于年份信息,需要引起警惕。统计年鉴的数据在下载时,通常会以时间戳自动为文件命名。

不过,只有部分年鉴在数据表里才会有年份信息,很多年鉴表里不含有年份。如果数据表里没有年份信息,我们还是在文件下载的时候就标注好,则将会为下面进行多年份对接带来很大的便利。若含有年份信息,下载 Excel 文件的时候,便就不必额外重命名文件了,可以在 Stata 里充分利用给出的年份信息,生成年份变量。

3. 取消密码保护

统计年鉴数据的下载常会出现密码保护问题,在 Excel 里无法编辑,也无法导入 Stata。可以使用 Stata 外部命令 dxlstxls 进行转换。

. * 使用 dxls、txls 取消 Excel 文件的密码保护
. net install dxls.pkg, 
. net install txls.pkg, 
. * 转换 "$DR" 目录下所有的文件,将新生成的文件存放在 "$DT/"
. txls "$DR/", todir("$DT/")

4. 数据清洗

这里先放一张效果图。数据清洗之后,我们便可以把杂乱无章的多个 Excel 文件合并在一个 DTA 文件中,并批量使用该变量的英文描述重命名。

4.1 循环导入单个 Excel 文件

本文选取的原始 Excel 文件导入 Stata 后的结果非常杂乱 (下图 1)。如果需要用到的年份比较多,逐年清洗的工程量不算太小。可以尝试在 Stata 中批量转换。

首先,增加变量标签,第五六行的信息可以作为标签。于是利用 labone, nrow(5 6) 达到下图 2 的效果。我们想要构建面板数据,而导入的单个表中没有年份信息,因此必须自己生成。我们能够发现 A 列第一行 (A[1]) 中含有年份信息,于是便可使用 substr() 函数进行字符串的截取 (下图 3)。最后再把不需要的数据删除即可,比如全国的数据、空行、无用信息等 (下图 4)。

达到上图 4 的效果仍然不太满意,主要是以 A、B、C、D 命名的变量名称感觉不很直观。

下面的操作是先利用正则表达式的 \s 匹配出标签中的空白字符 (包含空格、制表符、换行符、换页符等) 并去掉 (下图 2)。然后使用外部命令 renvarlab 把标签转为变量名 (下图 3)。最后再使用正则把变量名中的中文字符去掉,就能实现使用英文命名的效果了 (下图 4)。

该部分完整的代码如下:

* Excel 批量导入 Stata
cd "$DT/"
fs "*.xlsx"
foreach xlsx in `r(files)' {
  * Excel 文件导入 Stata,并删除无用信息
  import excel "`xlsx'", clear
  labone, nrow(5 6)
  local year = substr(A[1], 7, 4)
  gen year = `year'
  drop if inlist(A, "", "全国", "全 国")
  drop if (B == "") & (C == "") & (D == "")
  drop if ustrregexm(A, "城市.*")
  * 去掉标签中的空白字符,然后以标签作为变量名
  foreach v of varlist _all {
    local lbl: var label `v'   
    local u = ustrregexra("`lbl'", "\s", "", .)
    label var `v' `"`u'"'   
    cap renvarlab `v', label  
  }
  * 去掉变量名称中的中文字符
  foreach i of varlist _all {
    local j = ustrregexra("`i'", ".[\u4E00-\u9FA5]+", "", .)
    rename `i' `j'
  }
  save "syb_cons_`year'", replace 
}

4.2 合并多个 DTA 文件

通过 fs 获取上一步导入的 DTA 文件列表,然后使用 foreach 循环 append。因为 2015 年初始 use 时已经使用过一次了,因此循环 append 会导致 2015 年重复,于是便需要 duplicates drop。接着再将字符型变量 destring 为数值型变量,最后把行政区划名称中的空格删掉,便可以与行政区划代码数据相匹配了。

到这里,我们就得到了《中国城市建设统计年鉴》 2015-2019 共五年的「按行业全国城市市政公用设施建设固定资产投资」数据表的面板数据。不过,这里的行政区划包含了省级层面、地市层面和部分区县层面。

. fs "syb_cons*.dta"
. use "syb_cons_2015.dta", clear
. foreach dta in `r(files)' {
  2.     append using "`dta'", force
  3. }
. drop Q R
. duplicates drop 
. order NameofCities year
. destring CompletedInves-UtilityTunnel, replace force 
. * 去除行政区划名称里的空格,便于与行政区划代码匹配
. replace NameofCities = ustrregexra(NameofCities, "\ ", "", .)
. save "syb_cons.dta", replace
. tab year

       year |      Freq.     Percent        Cum.
------------+-----------------------------------
       2015 |        677       19.89       19.89
       2016 |        681       20.01       39.91
       2017 |        682       20.04       59.95
       2018 |        679       19.95       79.90
       2019 |        684       20.10      100.00
------------+-----------------------------------
      Total |      3,403      100.00

一般来说,我们做研究还需要与其他数据对接,以构建省级/地市级/区县级面板。下面以与行政区划代码对接为例,展示如何与不同级别的数据进行匹配对接。

5. 与行政区划代码数据进行对接

5.1 Stata 爬取行政区划代码数据

这里使用的行政区划代码数据来自于「民政部:2020 年 12 月中华人民共和国县以上行政区划代码」。页面数据十分规整,如果仅是手工复制,也可以一两分钟清洗成想要的样子。不过我们也可以趁机会进行 Stata 爬虫、练练手。

首先网页爬取和数据导入。使用 copy 命令,可以在当前目录生成 district_code.txt 文件,这里存储的是网页源码。接着使用 infix 将 TXT 文件导入 Stata。

. copy "http://www.mca.gov.cn/article/sj/xzqh/2020/20201201.html" "district_code.txt"
. infix strL v 1-20000 using "district_code.txt", clear

从导入的结果 (上图右侧) 来看,我们想要的区划名称和区划代码的存储位置都很有规律,可以利用正则表达式进行提取。提取方式有很多种,笔者的方法一定不是最简洁的,仅提供思路、抛砖引玉。

源码中杂乱的信息很多,我们想要的数据均存储在 <td> 标签内,可以通过 keep if ustrregexm(v, "<td.*") 筛选 (见下图 2)。又发现有好多 <td></td> 标签内没有数据,于是使用 drop if ustrregexm(v, "<td.*></td>") 完全删除 (下图 3)。头部和尾部还有几行也不是我们想要的内容,其共同点是都含有 width 的字样,于是 dorp if ustrregexm(v, ".*width.*") 删除。到这里爬虫的清洗工作就过半了。

接着,我们使用 ustrregexra() 函数保留 <td> 标签内的数据,删掉无用信息,就能把乱糟糟的网页源码清洗成我们想要的数据了 (下图 4-5)。

当然,到这里还不够。我们希望分别将区划代码和区划名称伸展成两个变量 (效果见下图 6)。我这里是先克隆 (clonevar) 一个完全相同的变量,然后一个变量保留区划代码 (destring,下图 3),另一个变量保留区划名称 (下图 4)。接着利用 carryforward 向下填充区划代码 (下图 5)。最后保留区划名称非空值、给变量重命名即可完成目标 (下图 6)。

该部分完整的代码如下:

* 爬取并清洗民政部行政区划代码
copy "http://www.mca.gov.cn/article/sj/xzqh/2020/20201201.html" "district_code.txt"
infix strL v 1-20000 using "district_code.txt", clear
keep if ustrregexm(v, "<td.*")
drop if ustrregexm(v, "<td.*></td>")
drop if ustrregexm(v, ".*width.*")
replace v = ustrregexra(v, "</td>|</span>|\s", "", .)
replace v = ustrregexra(v, ".*>", "", .)
clonevar v1 = v
destring v,replace force
replace v1 = "" if (v != .)
carryforward v, replace
drop if v1 == ""
rename (v v1) (district_code district_name)
save "district_code.dta", replace 

行政区划代码有六位数,省级代码为前两位数非空,后四位数为零;地市级别代码为前四位非空,后两位为零;区县代码六位均非空。根据这个特点,我们可以通过 mod() 取余函数,分别提取出不同级别的代码和名称。再结合 preserve...restore 的保存快照功能,便可生成三份新数据:省级代码、地市级代码和区县级代码。

5.2 构建省级面板

构建省级面板,首先需要对接省级区划代码。最简单的方式,就是使用省份的前两个字符进行 merge。我国有 34 个省级行政区,统计年鉴的数据一般不含台湾省、香港特别行政区和澳门特别行政区,而本文选取的是 2015-2019 五年的数据,所以应该对接上 31×5=155 个样本,但 merge 的结果却是 161。

推敲一下很容易发现,统计年鉴里将「吉林省」和「吉林市」统称为吉林。所以使用在省份名称的前两个字与区划代码数据进行对接时,吉林省或者吉林市就被无差别对待 merge 成了吉林省的区划代码。除此之外,统计年鉴还有「新疆建设兵团」的数据,所以也会对接成功,是因为使用了新疆维吾尔自治区的区划代码。

duplicates drop 可以解决这个问题,但是需要注意,该命令保留首次出现的数据,删除剩下的数据。为了保证正确 drop,需要先降序排序 gsort,保证吉林省的数据在吉林市数据的前面、新疆维吾尔自治区的数据在新疆建设兵团数据的前面。既然是省份数据,所有变量指标的数值大小均要比地市数据更大,因此可以本年完成投资额为准:gsort name year -ComplitedInves


* 构建省级面板
use "syb_cons.dta", clear 
gen name = substr(NameofCities, 1, 6)
preserve
  use "prov_code.dta", clear 
  cap gen name = substr(prov_name, 1, 6)
  save, replace 
restore
merge m:1 name using "prov_code.dta", keep(3) nogen // obs: 161
gsort name year -CompletedInves
duplicates drop name year, force
drop name NameofCities
order prov_* year 
* des
save "syb_cons_prov.dta", replace   

5.3 构建地市级别面板

地市级别数据构建面板数据时,使用地市前两个字符进行匹配的方式也比较容易。不过张家口市、张家界市前两个字符相同,所以可以使用 replace 命令保证前两个字符为「张家」的数据,生成的 name 与原来的 city_name 保持一致。又因为统计年鉴里还有部分区县的数据,此方法下,乌兰察布和乌兰尔、阿拉山口和阿拉善也会混淆结果,也应该一并处理。

还有一个小细节,不同的数据地市名称的规范也不同,有些数据带有「市」、有些没有,所以通过 replace 保留的原始名称就可能有所不同。一般我会统一将「市」删掉 replace name = subinstr(name , "市", "", .),基本上 merge 时就不会有太大问题了。当然,吉林省和吉林市的问题依然存在,这次我们需要保留吉林市的数据,所以使用 sort 升序排序即可。

* 构建地市级别面板数据
use "syb_cons.dta", clear
gen name = substr(NameofCities, 1, 6)
preserve
  use "city_code.dta", clear
  cap gen name = substr(city_name, 1, 6)
  replace name = city_name if name == "张家"
  replace name = city_name if name == "乌兰"
  replace name = city_name if name == "阿拉"
  replace name = subinstr(name, "市", "", .)
  save, replace
restore
merge m:1 name using "city_code.dta", keep(3) nogen 
sort name year CompletedInves
duplicates drop name year, force 
drop name NameofCities
order city_* year 
save "syb_cons_city.dta", replace 

6. 相关推文

Note:产生如下推文列表的 Stata 命令为:
lianxh global efolder 爬虫, m
安装最新版 lianxh 命令:
ssc install lianxh, replace

相关课程

免费公开课

最新课程-直播课

专题 嘉宾 直播/回看视频
最新专题 文本分析、机器学习、效率专题、生存分析等
研究设计 连玉君 我的特斯拉-实证研究设计-幻灯片-
面板模型 连玉君 动态面板模型-幻灯片-
面板模型 连玉君 直击面板数据模型 [免费公开课,2小时]
  • Note: 部分课程的资料,PPT 等可以前往 连享会-直播课 主页查看,下载。

课程主页

课程主页

关于我们

  • Stata连享会 由中山大学连玉君老师团队创办,定期分享实证分析经验。
  • 连享会-主页知乎专栏,700+ 推文,实证分析不再抓狂。直播间 有很多视频课程,可以随时观看。
  • 公众号关键词搜索/回复 功能已经上线。大家可以在公众号左下角点击键盘图标,输入简要关键词,以便快速呈现历史推文,获取工具软件和数据下载。常见关键词:课程, 直播, 视频, 客服, 模型设定, 研究设计, stata, plus, 绘图, 编程, 面板, 论文重现, 可视化, RDD, DID, PSM, 合成控制法

连享会小程序:扫一扫,看推文,看视频……

扫码加入连享会微信群,提问交流更方便

✏ 连享会-常见问题解答:
https://gitee.com/lianxh/Course/wikis

New! lianxhsongbl 命令发布了:
随时搜索连享会推文、Stata 资源,安装命令如下:
. ssc install lianxh
使用详情参见帮助文件 (有惊喜):
. help lianxh