In [47]:
#导入15年数据
import os
import pandas as pd
import glob
import numpy as np
#导入15年的CHARLS调查数据
# 获取当前工作目录下的所有dta文件的路径
dta_files = glob.glob('D:\\论文\\数据\\2015年全国追踪调查\\*.dta')
# 创建一个空字典，用于存放dataframe对象和名称
dataframes = {}
# 遍历每个dta文件，读取数据并存入字典
for file in dta_files:
# 去掉文件名后缀，作为dataframe的名称，并在后面加上15，表示这是15年的
name = os.path.basename(file.split('.')[0])+ '15'
# 读取dta文件，返回一个dataframe对象
df = pd.read_stata(file)
# 将dataframe对象和名称存入字典
dataframes[name] = df
# 使用globals()函数将字典中的键和值分别赋值给全局变量和dataframe对象
globals().update(dataframes)
In [48]:
#导入18年数据
import os
import pandas as pd
import glob
import numpy as np#用同样的方法导入18年的CHARLS数据
# 获取当前工作目录下的所有dta文件的路径
dta_filess = glob.glob('D:\\论文\\数据\\2018年全国追踪调查\\*.dta')
# 创建一个空字典，用于存放dataframe对象和名称
dataframess = {}
# 遍历每个dta文件，读取数据并存入字典
for file in dta_filess:
# 去掉文件名后缀，作为dataframe的名称，并在后面加上15，表示这是15年的
name = os.path.basename(file.split('.')[0])+ '18'
# 读取dta文件，返回一个dataframe对象
dff = pd.read_stata(file)
# 将dataframe对象和名称存入字典
dataframess[name] = dff
# 使用globals()函数将字典中的键和值分别赋值给全局变量和dataframe对象
globals().update(dataframess)
In [53]:
city_gender_age_premium_ratio_district15 = pd.read_csv(r'D:\论文\最后一波一鼓作气\数据\city_gender_age_premium_ratio_district15.csv')
city_gender_age_premium_ratio_district18 = pd.read_csv(r'D:\论文\最后一波一鼓作气\数据\city_gender_age_premium_ratio_district18.csv')
In [51]:
import pandas as pd
# ========== A) 工具函数：规范列名 + 统一键名 ==========
def normalize_columns(df):
# 去前后空格、转小写、把连续空白折叠成一个下划线
df.columns = (
df.columns.astype(str)
.str.strip()
.str.replace(r'\s+', '_', regex=True)
.str.lower()
)
return df
def coerce_key(df, candidates, target):
"""
在 df 中查找 candidates 里的任一列名，若找到则重命名为 target；
支持大小写/空格差异（因 normalize_columns 已处理）。
"""
for c in candidates:
if c in df.columns:
if c != target:
df.rename(columns={c: target}, inplace=True)
return True
return False
def norm_id_series(s):
# 统一为字符串ID，并去掉可能的 '.0'
return (
s.astype(str).str.strip()
.str.replace(r'\.0$', '', regex=True)
)
# ========== B) 先规范列名 ==========
Household_Income15 = normalize_columns(Household_Income15)
Household_Income18 = normalize_columns(Household_Income18)
city_gender_age_premium_ratio_district15 = normalize_columns(city_gender_age_premium_ratio_district15)
city_gender_age_premium_ratio_district18 = normalize_columns(city_gender_age_premium_ratio_district18)
# ========== C) 统一键名 ==========
# 2015 用 ID
ok15_L = coerce_key(Household_Income15, candidates=['id','ID','Id'], target='id')
ok15_R = coerce_key(city_gender_age_premium_ratio_district15, candidates=['id','ID','Id'], target='id')
# 2018 用 householdID（统一为小写 'householdid'）
ok18_L = coerce_key(Household_Income18, candidates=['householdid','household_id','id'], target='householdid')
ok18_R = coerce_key(city_gender_age_premium_ratio_district18, candidates=['householdid','household_id','id'], target='householdid')
# 若找不到对应键，抛出更友好的错误提示
if not ok15_L or not ok15_R:
raise KeyError("2015合并键未找到：请确认左/右表含有 'ID' 列。")
if not ok18_L or not ok18_R:
raise KeyError("2018合并键未找到：请确认左/右表含有 'householdID'（或 'ID'）列。")
# ========== D) 统一键值格式 ==========
Household_Income15['id'] = norm_id_series(Household_Income15['id'])
city_gender_age_premium_ratio_district15['id'] = norm_id_series(city_gender_age_premium_ratio_district15['id'])
Household_Income18['householdid'] = norm_id_series(Household_Income18['householdid'])
city_gender_age_premium_ratio_district18['householdid'] = norm_id_series(city_gender_age_premium_ratio_district18['householdid'])
# ========== E) 右表去重并校验==========
city_gender_age_premium_ratio_district15 = city_gender_age_premium_ratio_district15.drop_duplicates('id', keep='first')
city_gender_age_premium_ratio_district18 = city_gender_age_premium_ratio_district18.drop_duplicates('householdid', keep='first')
# ========== F) 合并 + 去掉城市样本 +只留下2015未改革2018已改革的样本 ==========
Household_Income15 = pd.merge(
Household_Income15,
city_gender_age_premium_ratio_district15,
how='left',
on='id',
validate='many_to_one'
)
Household_Income15 = Household_Income15[Household_Income15['urban_nbs'].ne('urban')]
Household_Income15 = Household_Income15[(Household_Income15['policyintergration2015'] == 0) &
(Household_Income15['policyintergration2018'] == 1)]
Household_Income18 = pd.merge(
Household_Income18,
city_gender_age_premium_ratio_district18,
how='left',
on='householdid',
validate='many_to_one'
)
Household_Income18 = Household_Income18[Household_Income18['urban_nbs'].ne('urban')]
Household_Income18 = Household_Income18[(Household_Income18['policyintergration2015'] == 0) &
(Household_Income18['policyintergration2018'] == 1)]
# ========== G) 下面接你的原统计代码即可 ==========
cols15 = ['ge006','ge008','ge009_2','ge009_3','ge009_6','ge009_7','ge010_6']
cols18 = ['ge006_w4','ge008','ge009_2','ge009_3','ge009_6','ge009_7','ge010_6']
for c in cols15:
if c in Household_Income15.columns:
Household_Income15[c] = pd.to_numeric(Household_Income15[c], errors='coerce')
for c in cols18:
if c in Household_Income18.columns:
Household_Income18[c] = pd.to_numeric(Household_Income18[c], errors='coerce')
food15 = Household_Income15.get('ge006', 0).fillna(0) + Household_Income15.get('ge008', 0).fillna(0)
utilities15 = Household_Income15.get('ge009_2', 0).fillna(0) + Household_Income15.get('ge009_3', 0).fillna(0)
goodsent15 = Household_Income15.get('ge009_6', 0).fillna(0) + Household_Income15.get('ge009_7', 0).fillna(0)
medical15 = Household_Income15.get('ge010_6', 0).fillna(0)
food18 = Household_Income18.get('ge006_w4', 0).fillna(0) + Household_Income18.get('ge008', 0).fillna(0)
utilities18 = Household_Income18.get('ge009_2', 0).fillna(0) + Household_Income18.get('ge009_3', 0).fillna(0)
goodsent18 = Household_Income18.get('ge009_6', 0).fillna(0) + Household_Income18.get('ge009_7', 0).fillna(0)
medical18 = Household_Income18.get('ge010_6', 0).fillna(0)
def stats_nonzero(s):
s = pd.to_numeric(s, errors='coerce')
s = s[(s != 0) & s.notna()]
return float(s.mean()), float(s.std(ddof=1))
def stats_include_zero(s):
s = pd.to_numeric(s, errors='coerce').fillna(0)
return float(s.mean()), float(s.std(ddof=1))
def three_stats_nonzero(s15, s18):
m15, sd15 = stats_nonzero(s15)
m18, sd18 = stats_nonzero(s18)
mall, sdall = stats_nonzero(pd.concat([s15, s18], ignore_index=True))
return m15, sd15, m18, sd18, mall, sdall
def three_stats_zero(s15, s18):
m15, sd15 = stats_include_zero(s15)
m18, sd18 = stats_include_zero(s18)
mall, sdall = stats_include_zero(pd.concat([s15, s18], ignore_index=True))
return m15, sd15, m18, sd18, mall, sdall
food_stats = three_stats_nonzero(food15, food18)
utilities_stats = three_stats_nonzero(utilities15, utilities18)
goodsent_stats = three_stats_nonzero(goodsent15, goodsent18)
medical_stats = three_stats_zero(medical15, medical18)
index = ['食品、就餐及烟酒','水费及燃料费','日用品及文娱','医疗消费']
cols = ['2015均值','2015标准差','2018均值','2018标准差','全样本均值','全样本标准差']
result = pd.DataFrame([food_stats, utilities_stats, goodsent_stats, medical_stats], index=index, columns=cols)
result
Out[51]:
| 2015均值 | 2015标准差 | 2018均值 | 2018标准差 | 全样本均值 | 全样本标准差 | |
|---|---|---|---|---|---|---|
| 食品、就餐及烟酒 | 273.586103 | 340.242541 | 331.045380 | 616.601501 | 302.301066 | 498.706759 |
| 水费及燃料费 | 253.214344 | 948.897081 | 253.108017 | 467.982422 | 253.161197 | 748.173569 |
| 日用品及文娱 | 197.609609 | 1002.553956 | 124.608330 | 311.756714 | 161.270680 | 744.587117 |
| 医疗消费 | 4396.273210 | 20516.852859 | 6591.151855 | 28213.359375 | 5468.398406 | 24602.340388 |
In [54]:
import pandas as pd
# ========== 0) 工具：规范 ID 值 ==========
def norm_id_series(s):
return (
s.astype(str).str.strip()
.str.replace(r'\.0$', '', regex=True) # 处理 Excel 读入的 12345.0
)
def require_col(df, colname, dfname):
if colname not in df.columns:
raise KeyError(f"{dfname} 缺少列：{colname}")
# ========== 1) 合并：两年都用 ID ==========
# 1.1 检查并规范 ID
for _df, _name in [
(Health_Status_and_Functioning15, 'Health_Status_and_Functioning15'),
(Health_Status_and_Functioning18, 'Health_Status_and_Functioning18'),
(city_gender_age_premium_ratio_district15, 'city_gender_age_premium_ratio_district15'),
(city_gender_age_premium_ratio_district18, 'city_gender_age_premium_ratio_district18')
]:
require_col(_df, 'ID', _name)
_df['ID'] = norm_id_series(_df['ID'])
# 1.2 右表按 ID 去重，避免一对多放大
city_gender_age_premium_ratio_district15 = city_gender_age_premium_ratio_district15.drop_duplicates('ID', keep='first')
city_gender_age_premium_ratio_district18 = city_gender_age_premium_ratio_district18.drop_duplicates('ID', keep='first')
# 1.3 左连接并剔除城市样本并只留下2015未改革2018已改革的样本
Health_Status_and_Functioning15 = pd.merge(
Health_Status_and_Functioning15,
city_gender_age_premium_ratio_district15,
how='left',
on='ID',
validate='many_to_one'
)
Health_Status_and_Functioning15 = Health_Status_and_Functioning15[Health_Status_and_Functioning15['urban_nbs'] != 'Urban']
Health_Status_and_Functioning15 = Health_Status_and_Functioning15[(Health_Status_and_Functioning15['policyintergration2015'] == 0) &
(Health_Status_and_Functioning15['policyintergration2018'] == 1)]
Health_Status_and_Functioning18 = pd.merge(
Health_Status_and_Functioning18,
city_gender_age_premium_ratio_district18,
how='left',
on='ID',
validate='many_to_one'
)
Health_Status_and_Functioning18 = Health_Status_and_Functioning18[Health_Status_and_Functioning18['urban_nbs'] != 'Urban']
Health_Status_and_Functioning18 = Health_Status_and_Functioning18[(Health_Status_and_Functioning18['policyintergration2015'] == 0) &
(Health_Status_and_Functioning18['policyintergration2018'] == 1)]
# ========== 2) 变量映射 ==========
mapping = {
'高血压': ('da007_w2_2_1_', 'da007_1_'),
'癌症': ('da007_w2_2_4_', 'da007_4_'),
'心脏病': ('da007_w2_2_7_', 'da007_7_'),
'情感及精神问题': ('da007_w2_2_11_', 'da007_11_')
}
# ========== 3) 将 1/2 或 “1 Yes/2 No/空值” → 二元 {Yes=1, No/空=0} ==========
def to_binary(s):
s_str = s.astype(str).str.strip().str.lower()
yes = (
s_str.eq('1') |
s_str.str.startswith('1') |
s_str.eq('yes') |
s_str.str.contains(r'\byes\b', na=False)
)
return yes.fillna(False).astype(int)
# ========== 4) 统计函数（样本标准差；样本量<=1 时 sd=0） ==========
def mean_std(x):
m = x.mean()
n = x.count()
sd = x.std(ddof=1) if n > 1 else 0.0
return float(m), float(sd)
# ========== 5) 逐项计算：2015 / 2018 / 合并 ==========
rows = []
for label, (c15, c18) in mapping.items():
# 若列不存在则补 0，避免 KeyError
s15_raw = Health_Status_and_Functioning15[c15] if c15 in Health_Status_and_Functioning15.columns else pd.Series([None]*len(Health_Status_and_Functioning15))
s18_raw = Health_Status_and_Functioning18[c18] if c18 in Health_Status_and_Functioning18.columns else pd.Series([None]*len(Health_Status_and_Functioning18))
s15 = to_binary(s15_raw)
s18 = to_binary(s18_raw)
m15, sd15 = mean_std(s15)
m18, sd18 = mean_std(s18)
mall, sdall = mean_std(pd.concat([s15, s18], ignore_index=True))
rows.append([m15, sd15, m18, sd18, mall, sdall])
# ========== 6) 输出表格 ==========
cols = ['2015均值','2015标准差','2018均值','2018标准差','全样本均值','全样本标准差']
result = pd.DataFrame(rows, index=list(mapping.keys()), columns=cols)
# 可选：显示为百分比
# result = (result * 100).round(2)
result
Out[54]:
| 2015均值 | 2015标准差 | 2018均值 | 2018标准差 | 全样本均值 | 全样本标准差 | |
|---|---|---|---|---|---|---|
| 高血压 | 0.061224 | 0.239769 | 0.108872 | 0.311517 | 0.084357 | 0.277940 |
| 癌症 | 0.002783 | 0.052686 | 0.011059 | 0.104593 | 0.006801 | 0.082193 |
| 心脏病 | 0.027829 | 0.164503 | 0.063160 | 0.243281 | 0.044983 | 0.207279 |
| 情感及精神问题 | 0.003711 | 0.060808 | 0.013517 | 0.115488 | 0.008472 | 0.091656 |
In [55]:
import pandas as pd
import numpy as np
# —— 小工具 ——
def drop_urban(df):
return df[df['urban_nbs'] != 'Urban'] if 'urban_nbs' in df.columns else df
def mean_std(x):
# 只做数值化以便计算；不做任何重编码
x = pd.to_numeric(x, errors='coerce')
n = x.count()
m = float(x.mean())
sd = float(x.std(ddof=1)) if n > 1 else 0.0
return m, sd
def calc_3stats(s15, s18):
m15, sd15 = mean_std(s15)
m18, sd18 = mean_std(s18)
mall, sdall = mean_std(pd.concat([s15, s18], ignore_index=True))
return [m15, sd15, m18, sd18, mall, sdall]
# —— 1) 取样本：剔除城市样本（只删 Urban，不改任何值） +只留下2015未改革2018已改革的样本——
df15 = drop_urban(city_gender_age_premium_ratio_district15).copy()
df15 = df15[(df15['policyintergration2015']==0.0) & (df15['policyintergration2018']==1.0)]
df18 = drop_urban(city_gender_age_premium_ratio_district18).copy()
df18 = df18[(df18['policyintergration2015']==0.0) & (df18['policyintergration2018']==1.0)]
# —— 2) educationrevised → 教育终止年龄（唯一需要新生成的列）——
edu_end_age_map = {
1: 6, # 无正式教育
2: 9, # 未读完小学
3: 11, # 私塾≈小学
4: 11, # 小学毕业
5: 14, # 初中毕业
6: 17, # 高中毕业
7: 17, # 中专/职高
8: 20, # 大专
9: 22, # 本科
10: 24, # 硕士
11: 28, # 博士
-1: np.nan # 不知道→忽略
}
def edu_end_age(series):
x = pd.to_numeric(series, errors='coerce')
return x.map(edu_end_age_map)
df15['edu_end_age'] = edu_end_age(df15['educationrevised']) if 'educationrevised' in df15.columns else pd.Series(dtype='float')
df18['edu_end_age'] = edu_end_age(df18['educationrevised']) if 'educationrevised' in df18.columns else pd.Series(dtype='float')
# —— 3) 直接用原始列做统计（不做任何重编码/映射）——
# 年龄
s15_age = df15['age'] if 'age' in df15.columns else pd.Series(dtype='float')
s18_age = df18['age'] if 'age' in df18.columns else pd.Series(dtype='float')
# 性别（原始值直接统计）
s15_gender = df15['gender'] if 'gender' in df15.columns else pd.Series(dtype='float')
s18_gender = df18['gender'] if 'gender' in df18.columns else pd.Series(dtype='float')
# 有无配偶（原始 marriage 直接统计）
s15_marriage = df15['marriage'] if 'marriage' in df15.columns else pd.Series(dtype='float')
s18_marriage = df18['marriage'] if 'marriage' in df18.columns else pd.Series(dtype='float')
# 子女数量：2015 用 kids15，2018 用 kids18（原始值）
s15_kids = df15['kids15'] if 'kids15' in df15.columns else pd.Series(dtype='float')
s18_kids = df18['kids18'] if 'kids18' in df18.columns else pd.Series(dtype='float')
# 收入：2015 用 ic15，2018 用 ic18（原始值）
s15_ic = df15['ic15'] if 'ic15' in df15.columns else pd.Series(dtype='float')
s18_ic = df18['ic18'] if 'ic18' in df18.columns else pd.Series(dtype='float')
# —— 4) 组装结果表 ——
rows = []
rows.append( calc_3stats(s15_age, s18_age) ) # 年龄
rows.append( calc_3stats(s15_gender, s18_gender) ) # 性别
rows.append( calc_3stats(df15['edu_end_age'], df18['edu_end_age']) ) # 教育终止年龄（新列）
rows.append( calc_3stats(s15_marriage, s18_marriage) ) # 有无配偶（原始 marriage）
rows.append( calc_3stats(s15_kids, s18_kids) ) # 子女数量
rows.append( calc_3stats(s15_ic, s18_ic) ) # 收入
index = ['年龄','性别','教育终止年龄','有无配偶','子女数量','收入']
cols = ['2015均值','2015标准差','2018均值','2018标准差','全样本均值','全样本标准差']
result = pd.DataFrame(rows, index=index, columns=cols)
# result = result.round(3) # 可选
result
Out[55]:
| 2015均值 | 2015标准差 | 2018均值 | 2018标准差 | 全样本均值 | 全样本标准差 | |
|---|---|---|---|---|---|---|
| 年龄 | 59.482375 | 10.552695 | 61.822347 | 10.093041 | 60.624588 | 10.396535 |
| 性别 | 0.481261 | 0.499681 | 0.477336 | 0.499521 | 0.479360 | 0.499591 |
| 教育终止年龄 | 10.671276 | 3.445694 | 10.115119 | 3.405575 | 10.395880 | 3.437037 |
| 有无配偶 | 0.868110 | 0.338393 | 0.851023 | 0.356091 | 0.859838 | 0.347167 |
| 子女数量 | 0.982283 | 0.131936 | 0.979216 | 0.142678 | 0.980789 | 0.137273 |
| 收入 | 10609.812609 | 32715.144767 | 15113.054399 | 124985.216845 | 12793.568982 | 90172.041212 |