如何使用两份pandas DataFrame生成含邮编人口数据的多级索引DataFrame

实现方案 #

步骤1：统一两份DataFrame的结构

我们最终采用一级索引为邮编、二级索引为统计项的多级索引结构，完美适配后续多邮编扩展需求，具体实现代码如下：

import pandas as pd

# 原始df1、df2定义
df1 = pd.DataFrame([['Enrolled In Public School',2000 ], ['Enrolled In Private School', 100], ['Not Enrolled In School', 1]], columns = ['enrollment type', 'count'], index = ['10001', '10001','10001'])
df2 =  pd.DataFrame([12000], columns = ['population'], index = ['10001'])

# 处理df1：将入学类型设为二级索引
df1_processed = df1.set_index('enrollment type', append=True)
df1_processed.columns = ['value']

# 处理df2：将人口指标转为二级索引格式，和df1结构对齐
df2_processed = df2.stack().reset_index(level=1, name='value').rename(columns={'level_1':'enrollment type'}).set_index('enrollment type', append=True)

# 合并得到最终多级索引DataFrame
final_df = pd.concat([df1_processed, df2_processed])
# 给索引命名方便后续查询
final_df.index = final_df.index.set_names(['zip_code', 'stat_item'])

最终结构预览

value
zip_code stat_item           
10001    Enrolled In Public School   2000
         Enrolled In Private School   100
         Not Enrolled In School       1
         population                12000

后续扩展多邮编的方法

新邮编的统计数据只要按照上述逻辑处理成同样的二级索引结构，直接拼接即可：

# 示例：添加邮编10002的统计数据
new_df1 = pd.DataFrame([['Enrolled In Public School',3000 ], ['Enrolled In Private School', 200], ['Not Enrolled In School', 2]], columns = ['enrollment type', 'count'], index = ['10002', '10002','10002'])
new_df2 = pd.DataFrame([15000], columns = ['population'], index = ['10002'])

# 按上述逻辑处理新数据后拼接
new_df1_processed = new_df1.set_index('enrollment type', append=True)
new_df1_processed.columns = ['value']
new_df2_processed = new_df2.stack().reset_index(level=1, name='value').rename(columns={'level_1':'enrollment type'}).set_index('enrollment type', append=True)
new_final = pd.concat([final_df, new_df1_processed, new_df2_processed])

常用查询示例

• 查指定邮编的所有统计项：final_df.loc['10001']
• 查所有邮编的公立学校入学人数：final_df.xs('Enrolled In Public School', level='stat_item')

内容的提问来源于stack exchange，提问作者Saeed