如何按Style、Gender、Region分组数据并向下填充值（Pandas）

我尝试在DataFrame中基于Style、Gender和Region对列表值进行分组，并实现值的向下填充。目前用groupby得到的结果里Style和Region没有完成向下填充，不确定当前方法是否合适，或者直接操作原始列表lst会不会更好。

相关代码：

import pandas as pd

lst = [
        ['Tee','Boy','East','12','11.04'],
        ['Golf','Boy','East','12','13'],
        ['Fancy','Boy','East','12','11.96'],
        ['Tee','Girl','East','10','11.27'],
        ['Golf','Girl','East','10','12.12'],
        ['Fancy','Girl','East','10','13.74'],
        ['Tee','Boy','West','11','11.44'],
        ['Golf','Boy','West','11','12.63'],
        ['Fancy','Boy','West','11','12.06'],
        ['Tee','Girl','West','15','13.42'],
        ['Golf','Girl','West','15','11.48']
    ]


df1 = pd.DataFrame(lst, columns = ['Style','Gender','Region','Units','Price'])

df2 = df1.groupby(['Style','Region','Gender']).count()

问题分析 #

你当前用groupby(...).count()的思路不对：这个操作是统计每个分组的行数，而你的原始数据中每个(Style,Region,Gender)组合本来就只有一行，所以结果里的数值都是1，完全不符合你想要的向下填充展示需求。

解决方案 #

方案1：实现类似Excel合并单元格的显示效果（推荐） #

如果只是想要显示时让重复的Style、Region值隐藏（看起来像向下填充），不需要修改原始数据，用Pandas的Styler功能即可：

import pandas as pd

lst = [
        ['Tee','Boy','East','12','11.04'],
        ['Golf','Boy','East','12','13'],
        ['Fancy','Boy','East','12','11.96'],
        ['Tee','Girl','East','10','11.27'],
        ['Golf','Girl','East','10','12.12'],
        ['Fancy','Girl','East','10','13.74'],
        ['Tee','Boy','West','11','11.44'],
        ['Golf','Boy','West','11','12.63'],
        ['Fancy','Boy','West','11','12.06'],
        ['Tee','Girl','West','15','13.42'],
        ['Golf','Girl','West','15','11.48']
    ]

df1 = pd.DataFrame(lst, columns=['Style','Gender','Region','Units','Price'])

# 构建Style、Region、Gender的多级索引
df2 = df1.set_index(['Style', 'Region', 'Gender'])

# 隐藏重复的上层索引值，实现向下填充的视觉效果
styled_df = df2.style.hide(axis='index', level=[0, 1])

# 在Jupyter等环境中执行可渲染成带合并效果的表格
display(styled_df)

方案2：在数据中实际填充重复值 #

如果需要修改数据本身，让重复的Style、Region值真的填充到对应行，可以用ffill()（向前填充）：

import pandas as pd

lst = [
        ['Tee','Boy','East','12','11.04'],
        ['Golf','Boy','East','12','13'],
        ['Fancy','Boy','East','12','11.96'],
        ['Tee','Girl','East','10','11.27'],
        ['Golf','Girl','East','10','12.12'],
        ['Fancy','Girl','East','10','13.74'],
        ['Tee','Boy','West','11','11.44'],
        ['Golf','Boy','West','11','12.63'],
        ['Fancy','Boy','West','11','12.06'],
        ['Tee','Girl','West','15','13.42'],
        ['Golf','Girl','West','15','11.48']
    ]

df1 = pd.DataFrame(lst, columns=['Style','Gender','Region','Units','Price'])

# 先按Style、Region、Gender排序，确保填充顺序正确
df3 = df1.sort_values(['Style', 'Region', 'Gender'])

# 对Style和Region列执行向前填充
df3[['Style', 'Region']] = df3[['Style', 'Region']].ffill()

print(df3)

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