Pandas技术问询：如何将age_group行转换为列并映射registered_patients列对应值

Got it, let's walk through exactly how to pull off this pivot operation with Pandas—it's a super common task and totally straightforward once you know the right tools!

1. 先看示例数据集 #

First, let's set up a sample dataset that matches your scenario (swap this out with your actual open-source data):

import pandas as pd

# 示例数据：包含机构ID、年龄组、注册患者数
data = {
    'clinic_id': [101, 101, 102, 102, 103],
    'age_group': ['18-25', '26-35', '18-25', '36-45', '26-35'],
    'registered_patients': [145, 210, 98, 167, 189]
}
df = pd.DataFrame(data)

2. 使用pivot()方法（无重复分组时首选） #

If your data has unique combinations of your grouping column(s) + age_group, the pivot() method is your best bet. It directly maps every unique age_group value to a new column, then fills in the corresponding registered_patients values:

# 执行行转列：以clinic_id为行标识，age_group转为新列，填充registered_patients值
pivoted_df = df.pivot(
    index='clinic_id',  # 保持为行的字段（如果不需要分组，可临时用df.index）
    columns='age_group',  # 要转为新列的目标字段
    values='registered_patients'  # 填充新列的数值字段
).reset_index()  # 将索引转为普通列，让结果更易用

# 移除列名的层级标签（可选，让输出更整洁）
pivoted_df.columns.name = None

The output will look like this:

3. 使用pivot_table()处理重复分组 #

If you have duplicate entries for the same index + age_group combination (e.g., multiple rows for clinic 101 and age group 18-25), use pivot_table() to aggregate those values:

# 聚合重复值（这里用sum，你也可以根据需求换成mean/max/min等）
pivoted_df = df.pivot_table(
    index='clinic_id',
    columns='age_group',
    values='registered_patients',
    aggfunc='sum',  # 选择合适的聚合函数
    fill_value=0  # 可选：将空值NaN填充为0或其他默认值
).reset_index()
pivoted_df.columns.name = None

4. 无额外分组字段的情况 #

If your dataset only has age_group and registered_patients (no other grouping columns), you can use a temporary default index:

# 临时用默认索引完成转列，再清理结果
pivoted_df = df.pivot(
    index=df.index,
    columns='age_group',
    values='registered_patients'
).dropna(axis=1, how='all')  # 移除全为空的列
pivoted_df = pivoted_df.reset_index(drop=True)
pivoted_df.columns.name = None

That's all! Just adjust the index parameter to match your actual data structure, and tweak the aggregation function if you need to handle duplicate entries.

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