stata-data-cleaning

Stata 数据清理

用途

这项技能可帮助经济学家在 Stata 中清理、转换和准备数据集，以供分析使用。它强调可复现性、规范的文档记录，以及处理经济学研究中常见的数据质量问题。

适用场景

• 清理原始调查数据或行政数据
• 合并多个数据源
• 处理缺失值、重复项和离群值
• 创建可用于分析的面板数据集
• 记录数据转换过程，以便复现

指令

步骤 1：了解数据

生成代码前，询问用户：

1. 数据来源是什么？（调查、行政数据、API 等）
2. 观测单位是什么？
3. 分析需要哪些关键变量？
4. 是否存在需要处理的已知数据质量问题？

步骤 2：生成清理流程

创建一个 Stata do 文件，其中：

1. 包含清晰的文件头，写明项目信息和日期
2. 设置运行环境（clear all、set memory、log）
3. 加载并检查原始数据
4. 用注释记录每项转换
5. 为最终数据集创建 codebook

步骤 3：遵循最佳实践

• 使用 assert 语句验证数据完整性
• 使用 label variable 创建带标签的变量
• 为分类变量使用值标签
• 生成日志文件，以便复现
• 适当保存中间文件

输出示例

/*==============================================================================
    Project:    Economic Analysis Data Cleaning
    Author:     [Your Name]
    Date:       [Date]
    Purpose:    Clean raw survey data for regression analysis
    Input:      raw_survey_data.dta
    Output:     cleaned_analysis_data.dta
==============================================================================*/

* ============================================
* 1. SETUP
* ============================================

clear all
set more off
cap log close
log using "logs/data_cleaning_`c(current_date)'.log", replace

* Set working directory
cd "/path/to/project"

* Define globals for paths
global raw_data "data/raw"
global clean_data "data/clean"
global output "output"

* ============================================
* 2. LOAD AND INSPECT RAW DATA
* ============================================

use "${raw_data}/raw_survey_data.dta", clear

* Basic inspection
describe
summarize
codebook, compact

* Check for duplicates
duplicates report id_var
duplicates list id_var if _dup > 0

* ============================================
* 3. VARIABLE CLEANING
* ============================================

* --- Rename variables for clarity ---
rename q1 age
rename q2 income_reported
rename q3 education_level

* --- Clean numeric variables ---
* Replace missing value codes with .
mvdecode age income_reported, mv(-99 -88 -77)

* Cap outliers at 99th percentile
qui sum income_reported, detail
replace income_reported = r(p99) if income_reported > r(p99) & !mi(income_reported)

* --- Clean string variables ---
* Standardize state names
replace state = upper(trim(state))
replace state = "NEW YORK" if inlist(state, "NY", "N.Y.", "N Y")

* --- Create categorical variables ---
gen education_cat = .
replace education_cat = 1 if education_level < 12
replace education_cat = 2 if education_level == 12
replace education_cat = 3 if education_level > 12 & education_level <= 16
replace education_cat = 4 if education_level > 16 & !mi(education_level)

label define edu_lbl 1 "Less than HS" 2 "High School" 3 "College" 4 "Graduate"
label values education_cat edu_lbl

* ============================================
* 4. HANDLE MISSING DATA
* ============================================

* Create missing indicator variables
gen mi_income = mi(income_reported)

* Document missingness
tab mi_income

* Count complete cases
egen complete_case = rownonmiss(age income_reported education_cat)
tab complete_case

* ============================================
* 5. CREATE DERIVED VARIABLES
* ============================================

* Age groups
gen age_group = .
replace age_group = 1 if age >= 18 & age < 30
replace age_group = 2 if age >= 30 & age < 50
replace age_group = 3 if age >= 50 & age < 65
replace age_group = 4 if age >= 65 & !mi(age)

label define age_lbl 1 "18-29" 2 "30-49" 3 "50-64" 4 "65+"
label values age_group age_lbl

* Log income
gen log_income = ln(income_reported + 1)

* ============================================
* 6. DATA VALIDATION
* ============================================

* Assert expected ranges
assert age >= 18 & age <= 120 if !mi(age)
assert income_reported >= 0 if !mi(income_reported)

* Check variable types
assert !mi(id_var)
isid id_var  // Verify unique identifier

* ============================================
* 7. LABEL VARIABLES
* ============================================

label variable age "Age in years"
label variable income_reported "Annual income (USD)"
label variable education_cat "Education category"
label variable log_income "Log of annual income"
label variable mi_income "Missing income indicator"

* ============================================
* 8. FINAL CHECKS AND SAVE
* ============================================

* Keep relevant variables
keep id_var age age_group income_reported log_income ///
     education_cat mi_income state year

* Order variables logically
order id_var year state age age_group income_reported ///
      log_income education_cat mi_income

* Compress to minimize file size
compress

* Save cleaned data
save "${clean_data}/cleaned_analysis_data.dta", replace

* Create codebook
codebook, compact

* Close log
log close

* ============================================
* END OF FILE
* ============================================

要求

软件

• Stata 15+（部分命令需要较新版本）

推荐的用户编写命令

ssc install unique     // For unique value checking
ssc install mdesc      // For missing data patterns
ssc install labutil    // For label manipulation

最佳实践

1. 始终从 clear all 开始，确保环境干净
2. 使用日志文件记录所有转换
3. 添加详尽注释——说明原因，而不只是说明做了什么
4. 使用 assert 语句尽早捕获数据错误
5. 在清理后的数据旁创建数据字典
6. 对 do 文件和数据集进行版本管理

常见陷阱

• ❌ 合并前未检查重复项
• ❌ 忘记处理缺失值编码（-99、-88 等）
• ❌ 未标注变量和值
• ❌ 覆盖原始数据文件
• ❌ 未记录数据转换过程

参考资料

• Stata 数据管理手册
• Gentzkow 和 Shapiro（2014）《社会科学的代码与数据》
• DIME Analytics 数据手册

更新日志

v1.0.0

• 首次发布，包含完整的清理模板