如何将Pandas DataFrame中特定列的行值转换为聚合列

需求说明 #

现有包含Month、Group、channel、viewership、rating列的DataFrame，需要移除channel列，生成以channel行值（cbs、fox）为前缀，结合viewership、rating的聚合列：cbs_viewership、fox_viewership、cbs_rating、fox_rating，对应填充原数据中匹配的行值。

原数据代码 #

import pandas as pd

data = {
    'Month': ['2020-12-01', '2020-12-01', '2021-01-01', '2021-01-01'],
    'Group': ['a', 'b', 'a', 'b'],
    'channel': ['cbs', 'fox', 'cbs', 'fox'],
    'viewership': ['10k', '15k', '12k', '6k'],
    'rating': ['1', '3.2', '1.2', '2.1']
}

df = pd.DataFrame(data)

解决方案 #

方法1：使用pivot（推荐，扩展性强） #

通过pivot将channel转为列维度，再合并多级列名得到目标格式：

# 按Month、Group分组，将channel转为列，展开viewership和rating
pivoted = df.pivot(index=['Month', 'Group'], columns='channel', values=['viewership', 'rating'])

# 合并多级列名，格式为"channel_metric"
pivoted.columns = [f'{col[1]}_{col[0]}' for col in pivoted.columns]

# 重置索引，恢复Month和Group为普通列
result_df = pivoted.reset_index()

# 可选：将空值替换为空字符串
# result_df = result_df.fillna('')

print(result_df)

输出结果：

Month Group cbs_viewership fox_viewership cbs_rating fox_rating
0  2020-12-01     a            10k            NaN          1        NaN
1  2020-12-01     b            NaN            15k        NaN        3.2
2  2021-01-01     a            12k            NaN        1.2        NaN
3  2021-01-01     b            NaN             6k        NaN        2.1

方法2：手动创建列（适合少量channel值场景） #

通过apply判断channel值，直接生成目标列：

# 逐个生成聚合列，匹配channel值时填充对应metrics，否则留空
df['cbs_viewership'] = df.apply(lambda x: x['viewership'] if x['channel'] == 'cbs' else '', axis=1)
df['fox_viewership'] = df.apply(lambda x: x['viewership'] if x['channel'] == 'fox' else '', axis=1)
df['cbs_rating'] = df.apply(lambda x: x['rating'] if x['channel'] == 'cbs' else '', axis=1)
df['fox_rating'] = df.apply(lambda x: x['rating'] if x['channel'] == 'fox' else '', axis=1)

# 移除原channel列
result_df = df.drop('channel', axis=1)

print(result_df)

输出结果：

Month Group viewership rating cbs_viewership fox_viewership cbs_rating fox_rating
0  2020-12-01     a        10k      1            10k                            1          
1  2020-12-01     b        15k    3.2                                      15k            3.2
2  2021-01-01     a        12k    1.2            12k                          1.2          
3  2021-01-01     b         6k    2.1                                       6k            2.1

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