使用Pandas将同ID NUMBER的行数据转换为列数据的技术求助

需求说明 #

现有Excel数据如下：

需要将相同ID NUMBER的行合并为一行，将重复的FUNCTION和NAME字段转为多列，最终结构如下：

实现代码及步骤 #

1. 导入依赖并读取数据
   先导入Pandas，读取目标Excel文件（以下示例用构造的测试数据代替，实际使用时替换为你的文件路径）：

   import pandas as pd
   
   # 构造测试数据（替换为 pd.read_excel("your_excel_file.xlsx") 读取实际文件）
   data = {
       "COMPANY": ["A", "A", "A", "B", "B"],
       "ID NUMBER": [123, 123, 123, 456, 456],
       "FUNCTION": ["director", "partner", "secretary", "partner", "manager"],
       "NAME": ["Smith", "Jones", "Evans", "Brown", "Wilson"]
   }
   df = pd.DataFrame(data)
   

2. 为同ID组内的行添加序号
   通过groupby和cumcount为每个ID NUMBER分组内的行生成递增序号，用于后续列名区分：

   # 生成组内序号（从1开始）
   df["seq"] = df.groupby("ID NUMBER").cumcount() + 1
   

3. 将FUNCTION和NAME分别转成宽格式
   使用pivot方法将分组内的多行转为多列，并自定义列名格式：

   # 处理FUNCTION列，转宽格式并重命名列
   func_wide = df.pivot(
       index=["COMPANY", "ID NUMBER"],
       columns="seq",
       values="FUNCTION"
   )
   func_wide.columns = [f"FUNCTION{' ' + str(col) if col > 1 else ''}" for col in func_wide.columns]
   
   # 处理NAME列，转宽格式并重命名列
   name_wide = df.pivot(
       index=["COMPANY", "ID NUMBER"],
       columns="seq",
       values="NAME"
   )
   name_wide.columns = [f"NAME{' ' + str(col) if col > 1 else ''}" for col in name_wide.columns]
   

4. 合并结果并处理空值
   合并两个宽格式数据框，重置索引，并将空值替换为空白字符串：

   # 合并数据
   result = pd.concat([func_wide, name_wide], axis=1).reset_index()
   # 将NaN替换为空字符串，匹配示例格式
   result = result.fillna("")
   
   # 查看最终结果
   print(result)
   

最终输出 #

运行上述代码后，输出结果与需求中的目标结构完全一致。

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