如何在Pandas中将多行表头值及行内客户名称转换为列值

如何将表格中作为行的客户名称转换为列值？

示例数据 #

期望结果 #

原始数据字典 #

{'Invoice No': {'Ketan patel': nan,
'03/25/2022': 175264.0,
'03/24/2022': 175034.0,
'03/22/2022': 174548.0,
'Almenda sarah': nan,
'03/31/2022': 176323.0,
'03/29/2022': 175934.0,
'Hassan ': nan,
'Lara  Brian ': nan,
'03/28/2022': 175668.0,
'03/23/2022': 174854.0},
'Sales Amount': {'Ketan patel': nan,
'03/25/2022': 477600.0,
'03/24/2022': 16800.0,
'03/22/2022': 315000.0,
'Almenda sarah': nan,
'03/31/2022': 350200.0,
'03/29/2022': 263400.0,
'Hassan ': nan,
'Lara  Brian ': nan,
'03/28/2022': 232700.0,
'03/23/2022': 319600.0},
'Delivery Charges': {'Ketan patel': nan,
'03/25/2022': 0.0,
'03/24/2022': 0.0,
'03/22/2022': 0.0,
'Almenda sarah': nan,
'03/31/2022': 0.0,
'03/29/2022': 0.0,
'Hassan ': nan,
'Lara  Brian ': nan,
'03/28/2022': 0.0,
'03/23/2022': 0.0},
'Total Sales': {'Ketan patel': nan,
'03/25/2022': 477600.0,
'03/24/2022': 16800.0,
'03/22/2022': 315000.0,
'Almenda sarah': nan,
'03/31/2022': 350200.0,
'03/29/2022': 263400.0,
'Hassan ': nan,
'Lara  Brian ': nan,
'03/28/2022': 232700.0,
'03/23/2022': 319600.0}}

使用Pandas完成数据转换，步骤如下：

1. 将数据字典转为DataFrame并重置索引：

import pandas as pd
import numpy as np

# 导入原始数据字典
raw_data = {'Invoice No': {'Ketan patel': np.nan,
'03/25/2022': 175264.0,
'03/24/2022': 175034.0,
'03/22/2022': 174548.0,
'Almenda sarah': np.nan,
'03/31/2022': 176323.0,
'03/29/2022': 175934.0,
'Hassan ': np.nan,
'Lara  Brian ': np.nan,
'03/28/2022': 175668.0,
'03/23/2022': 174854.0},
'Sales Amount': {'Ketan patel': np.nan,
'03/25/2022': 477600.0,
'03/24/2022': 16800.0,
'03/22/2022': 315000.0,
'Almenda sarah': np.nan,
'03/31/2022': 350200.0,
'03/29/2022': 263400.0,
'Hassan ': np.nan,
'Lara  Brian ': np.nan,
'03/28/2022': 232700.0,
'03/23/2022': 319600.0},
'Delivery Charges': {'Ketan patel': np.nan,
'03/25/2022': 0.0,
'03/24/2022': 0.0,
'03/22/2022': 0.0,
'Almenda sarah': np.nan,
'03/31/2022': 0.0,
'03/29/2022': 0.0,
'Hassan ': np.nan,
'Lara  Brian ': np.nan,
'03/28/2022': 0.0,
'03/23/2022': 0.0},
'Total Sales': {'Ketan patel': np.nan,
'03/25/2022': 477600.0,
'03/24/2022': 16800.0,
'03/22/2022': 315000.0,
'Almenda sarah': np.nan,
'03/31/2022': 350200.0,
'03/29/2022': 263400.0,
'Hassan ': np.nan,
'Lara  Brian ': np.nan,
'03/28/2022': 232700.0,
'03/23/2022': 319600.0}}

df = pd.DataFrame(raw_data).reset_index(names='index_col')

2. 标记并填充客户名称：

# 识别所有数值列为空的行（即客户名称行）
df['Customer'] = np.where(df[['Invoice No', 'Sales Amount', 'Delivery Charges', 'Total Sales']].isna().all(axis=1), df['index_col'], np.nan)
# 向前填充客户名称到后续交易行
df['Customer'] = df['Customer'].ffill()

3. 过滤无效行并调整格式：

# 过滤掉原始的客户名称行
result_df = df[~df[['Invoice No', 'Sales Amount', 'Delivery Charges', 'Total Sales']].isna().all(axis=1)]
# 重命名日期列
result_df = result_df.rename(columns={'index_col': 'Date'})
# 调整列顺序
result_df = result_df[['Customer', 'Date', 'Invoice No', 'Sales Amount', 'Delivery Charges', 'Total Sales']]

执行后即可得到期望的表格格式。

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