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

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 系列推文：

• 全部 | Stata入门 | Stata教程 | Stata资源 | Stata命令
• 计量专题 | 论文写作 | 数据分享 | 专题课程
• 结果输出 | Stata绘图 | 数据处理 | Stata程序
• 回归分析 | 面板数据 | 交乘项-调节 | IV-GMM
• 内生性-因果推断 | 倍分法DID | 断点回归RDD | PSM-Matching | 合成控制法
• Probit-Logit | 时间序列 | 空间计量 | 分位数回归 | 生存分析 | SFA-DEA
• 文本分析-爬虫 | Python-R-Matlab | 机器学习
• Markdown | 工具软件 | 其它

☝ 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 外部命令 dxls 和 txls 进行转换。

. * 使用 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

• 专题：专题课程

  • ⏩ 专题课：文本分析-爬虫-机器学习-2022年4月
  • 助教招聘：文本分析-爬虫-机器学习
  • 助教入选结果 - 连享会 文本分析与爬虫直播课
• 专题：论文写作

  • Stata：如何组织实证分析文档-global-rangestat
• 专题：数据处理

  • Stata数据处理：用-efolder-快速生成文件夹和子文件夹
• 专题：Stata程序

  • Stata程序：暂元-(local)-和全局暂元-(global)
• 专题：文本分析-爬虫

  • Stata爬虫：爬取地区宏观数据
  • Stata爬虫：爬取Ａ股公司基本信息
  • Stata爬虫-正则表达式：爬取必胜客
  • Python爬虫: 《经济研究》研究热点和主题分析
• 专题：Python-R-Matlab

  • Python爬虫：爬取华尔街日报的全部历史文章并翻译
  • Python爬虫：从SEC-EDGAR爬取股东治理数据-Shareholder-Activism

相关课程

免费公开课

• 直击面板数据模型 - 连玉君，时长：1小时40分钟，课程主页，Bilibili 站版
• Stata 33 讲 - 连玉君, 每讲 15 分钟. 课程主页，课件，Bilibili 站版
• Stata 小白的取经之路 - 龙志能，时长：2 小时，课程主页
• 部分直播课 课程资料下载 (PPT，dofiles等)

最新课程-直播课

• Note: 部分课程的资料，PPT 等可以前往 连享会-直播课 主页查看，下载。

⛳ 课程主页

⛳ 课程主页

关于我们

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

连享会小程序：扫一扫，看推文，看视频……

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

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

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