一份经济微观数据库的数据清洗快速指南

1
2
3
4
5

clear
cd "C:\Users\Desktop\转码"    // 新建一个转码文件夹，然后修改自己的路径
unicode encoding set gb18030
unicode analyze 流动人口数据个人问卷2018.dta   // 文件名根据自己的数据进行调整
unicode translate 流动人口数据个人问卷2018.dta, invalid   // 文件名根据自己的数据进行调整

1
2
3
4

cd "D:\household_and_community_questionnaire_data"  //设置文件储存地点
unicode analyze PSU.dta //PSU.dta换成要编译的文件
unicode encoding set "GB18030"
unicode retranslate psu.dta, transutf8

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

//master_data.dta就是基底数据库，using_data.dta就是我们要匹配上去的数据库
//m:n id 意思就是第一个数据的m个id可以对应n个第二个数据库的样本id
//简而言之，m:n就是多对一，n:n就是1对1，m:m就是多对多
// 所以我们也可以添加多个匹配条件，例如merge 1:1 id year treat ......
************横向匹配**************************
use "master_data.dta"
merge m:n id using "using_data.dta"
drop _merge
save "new_data.dta"
************纵向匹配**************************
use "data1.dta"
append using "data2.dta"
save "new_data.dta"

1
2
3

xtset id  year
by id: ipolate YY year,gen(YYY)  //内插，中间空缺值，填完后命名为新变量YYY
by id: ipolate XX year,gen(XXX) epolate //外插，边缘空缺值，填完后命名为新变量XXX

1
2
3
4
5
6
7
8

global var var0 var1 var2 var3 genshin
*打包所有需要插值的变量
foreach i in $var {
by id: ipolate `i' year,gen(varx`i') 
by id: ipolate varx`i' year,gen(varn`i') epolate
replace `i' =  varn`i' 
drop  varx`i'   varn`i' 
  } 

1
2
3
4
5
6
7
8

global var  var0 var1 var2 var3 genshin 
*打包所有需要插值的变量
foreach i in $var {
drop if `i' == -88
drop if `i' == -99
drop if `i' ==.
drop if `i' <=0
  } 

1

destring ID householdID communityID , replace force *加上force，正义执行！

1
2
3
4

clear
import excel "E:\桌面\笔记本.xlsx", sheet("Sheet1") firstrow
generate date=date(year,"YMD") //识别日期
format date %td //进一步转化

1
2

generate year=date(date, "YMD") //识别日期
format year %td //进一步转化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

generate month = month(year) //对已经处理后的year日期进行月份切片
//更详细解释可以参考：https://stata-club.github.io/stata_article/2017-10-29.html
gen spring = 1 if month == 3 |month == 4 | month == 5
replace spring = 0 if spring != 1
gen summer = 1 if month == 6 |month == 7 | month == 8
replace summer = 0 if summer != 1
gen fall = 1 if month == 9 |month == 10 | month == 11
replace fall = 0 if fall != 1
gen winter = 1 if month == 12 |month == 1 | month == 2
replace winter = 0 if winter != 1

1
2

histogram var,  ylabel(, angle(0)) xtitle("横坐标名字") name(图片文件名, replace) \\频数图绘制下分布看看
winsor2 var, cut(0 97.5) trim replace  \\去掉变量var右侧2.5%的极端数据

1
2
3
4
5
6

bysort 国界: egen 乡村振兴边境 = mean(乡村振兴)
bysort 国界: egen 产业兴旺边境 = mean(产业兴旺)
bysort 国界: egen 生态宜居边境 = mean(生态宜居)
bysort 国界: egen 治理有效边境 = mean(治理有效)
bysort 国界: egen 乡风文明边境 = mean(乡风文明)
bysort 国界: egen 生活富裕边境 = mean(生活富裕)

1

sort year age income //首先按照年份排序，相同年份的一列中按照年龄排序，同年同年龄就按收入排序

1

duplicates drop var，force

1

gen x=substr(var,1,4)

1

graph combine "a.gph" "b.gph", col(1) xcommon ycommon

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

sysuse auto,clear
set scheme white_tableau //设置主题，小伙伴可以根据自己的喜好自行设置
twoway ///
(scatter mpg weight [w= price] if foreign == 0 & price <= 4220, msymbol(Oh) mcolor("220 235 136") msize(small)) ///
(scatter mpg weight [w= price] if foreign == 1 & price <= 4220, msymbol(o) mcolor("220 235 136") msize(small)) ///
(scatter mpg weight [w= price] if foreign == 0 & (price > 4220 & price <= 6332), msymbol(Oh) mcolor("180 232 172") msize(medium)) ///
(scatter mpg weight [w= price] if foreign == 1 & (price > 4220 & price <= 6332), msymbol(o) mcolor("180 232 172") msize(medium)) ///
(scatter mpg weight [w= price] if foreign == 0 & price > 6332, msymbol(Oh) mcolor("152 210 196") msize(large)) ///
(scatter mpg weight [w= price] if foreign == 1 & price > 6332, msymbol(o) mcolor("152 210 196") msize(large)), ///
xlabel(2000(1000) 5000) ylabel(10(10)50) legend(label(1 " ") label(2 " ") label(3 " ") label(4 " ") label(5 " ") label(6 " ") order(2 4 6 1 3 5) row(2) ring(0) size(10) position(2) bmargin(medlarge)) text(45.3 3800 "Foreign", size(*0.7)) text(39.5 3800 "Domestic", size(*0.7)) text(49.5 4100 "Low Price", size(*0.7)) text(49.5 4450 "Med Price", size(*0.7)) text(49.5 4800 "High Price", size(*0.7)) 
//mycolor中为RGB颜色编码，可以自己根据喜好定义；
//xlabel和ylabel分别设置横纵轴的标签；legend设置图例；text设置文字的具体位置，具体格式text（y坐标 x坐标 "标签文本"）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

twoway ///
	(line var2 date if group==1) ///
	(line var2 date if group==2) ///
	(line var2 date if group==3) ///
	(line var2 date if group==4) ///
	(line var2 date if group==5) ///
	(line var2 date if group==6) ///
	(line var2 date if group==7) ///
	(line var2 date if group==8) ///
	(line var2 date if group==9) ///
	(line var2 date if group==10) ///
	(line var2 date if group==11) ///
	(line var2 date if group==12) ///
	, ///
		legend(order(1 "group1" 2 "group2" 3 "group3"  4 "group4"  5 "group5" 6 "group6" 7 "group7" 8 "group8" 9 "group9" 10 "group10" 11 "group11" 12 "group12")) ///
		title("Line plot") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5
6

graph pie var2 if group <= 10, ///
	over(group) plabel(_all percent, format(%9.2f)) ///
	line(lcolor(black) lwidth(vvthin)) 	///                  
	// outline colors have to be manually added
	title("Pie plot") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5

graph box ///
	var* ///
		, ///
		title("Box plot") ///
		note("The Stata Guide", size(vsmall))

1
2
3

histogram var4, percent ///
	title("Histogram") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4

histogram var4, ///
    ylabel(, angle(0)) ///
    xtitle("税收") ///
    kdensity

1
2
3
4
5
6

graph bar ///
	var* ///
		, ///
		blabel(bar, format(%9.2f)) ///
		title("Bar graph") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5
6
7
8

graph hbar (mean) ///
	var* ///
	if group <= 6, ///
		over(group) ///
		percentages stack	///
		legend(order(1 "Var 1" 2 "Var 2" 3 "Var 3"  4 "Var 4"  5 "Var 5" 6 "Var 6")) ///
		title("Bar graph") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5
6
7

twoway ///
	(lpolyci var1 var9, fcolor(%80)) ///
	(lpolyci var2 var9, fcolor(%80)) ///
	(lpolyci var3 var9, fcolor(%80)) ///
		, ///
		title("Confidence Interval") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5
6

twoway ///
	(rcapsym var2 var3 date if group==1, sort) ///
	(rcapsym var2 var3 date if group==2, sort) ///
		, ///
		title("Range plots") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5
6

twoway ///
	(area den1d den1x, fcolor(%50)) ///
	(area gen2d gen2x, fcolor(%50)) ///
	(area gen3d gen3x, fcolor(%50)), ///
			title("Density plots") ///
			note("The Stata Guide", size(vsmall))

1
2
3
4

twoway ///
	(scatter var2 var1, mlabel(group)) ///
		if date==22320///,///title("Confidence Interval") ///
		note("The Stata Guide", size(vsmall))

1
2
3
4
5

twoway ///
	(scatter var2 var1) ///
		if group <= 12, ///
		by(group, yrescale xrescale)	///
		by(, title("By graphs") note("The Stata Guide", size(vsmall)))

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

ssc install palettes, replace
ssc install colrspace, replace
ssc install schemepack, replace
* 更新
ado update, update
clear
input strL 属性 A B C D E F
力量 2 2 6 4 5 6
敏捷 1 1 3 4 3 6
智力 3 2 2 4 5 6
感知 2 5 1 6 2 5
魅力 4 6 4 5 6 5
幸运 5 3 3 5 4 4
end
compress
spider A-F, over(属性)


sysuse auto,clear   
radar make weight if foreign

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

ssc install schemepack, replace  
* set scheme white_tableau 
* set scheme black_tableau 
* set scheme gg_tableau 
* set scheme white_tableau
* set scheme Rainbow  

汇总风格如下——
Series:
    Tableau - white_tableau black_tableau gg_tableau
    Cividis - white_cividis black_cividis gg_cividis
    Viridis - white_viridis black_viridis gg_viridis
    Hue     - white_hue     black_hue     gg_hue
    BrBg    - white_brbg    black_brbg    gg_brbg
    PiYg    - white_piyg    black_piyg    gg_piyg
    pTol    - white_ptol    black_ptol    gg_ptol
    Jet     - white_jet     black_jet     gg_jet
    w3d     - white_w3d     black_w3d     gg_w3d
Standalones:
    Rainbow            - rainbow
    Neon               - neon
    Taylor Swift's Red - swift_red  
    Ukraine            - ukraine
Colorblind-friendly schemes:
    Tab1               - tab1
    Tab2               - tab2
    Tab3               - tab3  
    cblind1            - cblind1   

1

export excel using "data.xlsx", firstrow(variables)

1
2
3
4
5
6
7
8
9

global $xlist x1 x2 x3 x4 x5 

foreach i in $xlist {
	egen b = mean(`i')
	gen a = 1*(b < `i')+0*(b>= `i')
	egen sum_`i' = sum(a)	
	drop a
	drop b
  }