Python pandas如何将三列合并为分类列与数值列

我是一名数据科学实习生，当前在Python中有如下结构的DataFrame：

import pandas as pd
import numpy as np

df = pd.DataFrame({'Utility': ["Akron", 'Akron', 'Akron', 'Akron'],
                   'Area': ['other', 'other', 'other', 'other'],
                   'Category': ['Digital', 'Digital', 'Digital', 'Digital'],
                   'Subcategory': ['Plans', 'Services', 'Asset Management', 'Billing'],
                   'Unit':['USD','USD','USD','USD'],
                   'Value':[0,0,0,0],
                   "Population Served": [280000,280000,280000,280000]})
print(df)

输出结果为：

Utility   Area Category       Subcategory Unit  Value  Population Served
0   Akron  other  Digital             Plans  USD      0             280000
1   Akron  other  Digital          Services  USD      0             280000
2   Akron  other  Digital  Asset Management  USD      0             280000
3   Akron  other  Digital           Billing  USD      0             280000

需求说明 #

主管要求实现以下逻辑：可通过过滤Unit列同时查询原Value和Population Served列的数据，即Unit列需包含USD和Population Served两类值，Value列对应存储对应单位的支出或服务人口数值；且服务人口对应的行中Area、Category、Subcategory等分类列需置空。最终目标DataFrame格式如下：

df = pd.DataFrame({'Utility': ["Akron", 'Akron', 'Akron', 'Akron', "Akron", 'Akron', 'Akron', 'Akron'],
                   'Area': ['other', 'other', 'other', 'other', np.nan, np.nan, np.nan, np.nan],
                   'Category': ['Digital', 'Digital', 'Digital', 'Digital', np.nan, np.nan, np.nan, np.nan],
                   'Subcategory': ['Plans', 'Services', 'Asset Management', 'Billing', np.nan,np.nan,np.nan,np.nan],
                   'Unit':['USD','USD','USD','USD', 'Pop Served', 'Pop Served', 'Pop Served', 'Pop Served'],
                   'Value':[0,0,0,0,280000,280000,280000,280000]})

print(df)输出结果为：

Utility   Area Category       Subcategory        Unit   Value
0   Akron  other  Digital             Plans         USD       0
1   Akron  other  Digital          Services         USD       0
2   Akron  other  Digital  Asset Management         USD       0
3   Akron  other  Digital           Billing         USD       0
4   Akron    NaN      NaN               NaN  Pop Served  280000
5   Akron    NaN      NaN               NaN  Pop Served  280000
6   Akron    NaN      NaN               NaN  Pop Served  280000
7   Akron    NaN      NaN               NaN  Pop Served  280000

已尝试方案&额外诉求 #

我尝试使用pd.melt实现但未成功，也考虑过使用for循环，但担心数据量大时运行效率低、插入行索引易出错。另外我个人认为该方案会无意义地将文件体积翻倍，若有方案可直接在Excel中实现主管需要的视图、无需修改CSV文件也可作为答案。

Python实现方案 #

直接用pd.concat拼接原数据和新增的人口数据行即可，全程向量运算无循环，百万级数据也能快速处理：

# 复制原数据生成人口对应的行结构
pop_df = df.copy()
# 分类字段统一置空
pop_df[['Area', 'Category', 'Subcategory']] = np.nan
# 替换Unit和Value的取值
pop_df['Unit'] = 'Pop Served'
pop_df['Value'] = pop_df['Population Served']
# 拼接两个表后删除多余的Population Served列
final_df = pd.concat([df, pop_df], ignore_index=True).drop(columns='Population Served')

执行后即可直接得到符合要求的目标DataFrame。

Excel视图实现方案 #

如果不想修改原CSV文件，可以用Power Query实现动态视图，后续原文件更新时只需刷新即可同步结果：

1. 选中原数据区域，点击「数据」选项卡下的「从表格/范围」，导入Power Query编辑器
2. 按住Ctrl选中Unit、Value两列，右键点击「逆透视其他列」
3. 筛选新生成的「属性」列，仅保留Population Served选项
4. 将「属性」列值批量替换为Pop Served，「值」列的内容覆盖到Value列
5. 筛选Unit列的Pop Served选项，将对应Area、Category、Subcategory三列的值置空
6. 点击「关闭并上载」即可在新Sheet生成目标视图，不会修改原CSV文件。

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