如何将CSV中的分组表头提取为DataFrame的独立列

我正在将CSV文件读取为DataFrame，该CSV包含不同赛事的分组表头行。我希望将这些子表头行提取为独立列，后续用该列数据分析不同赛事区域的结果差异，请问如何实现？

原始数据 #

期望转换后的数据 #

初始代码 #

import pandas as pd
import numpy as np

data = {
    "Date": ['USA Hamersely M45', '22nd Jul 2023,\n04:00 EDT', '22nd Jul 2023,\n05:00 EDT', 'Canada Valley Y67', '22nd Jul 2023,\n04:00 EDT', '22nd Jul 2023,\n05:00 EDT', '22nd Jul 2023,\n22:00 EDT'],
    "p1": [np.nan, 1.75, 1.85, np.nan, 2.37, 1.97, 1.75],
    "p2": [np.nan, '56%', '76%', np.nan, '26%', '86%', '96%']
}

# 加载数据到DataFrame
df = pd.DataFrame(data)
df.head()

解决方案 #

可以通过以下步骤实现需求：

1. 提取并填充赛事名称 #

判断p1和p2列均为NaN的行，这些行的Date值即为赛事名称。用ffill()方法向下填充，为后续数据行匹配对应赛事：

# 创建Tournament列并填充赛事名称
df['Tournament'] = df.loc[df[['p1', 'p2']].isna().all(axis=1), 'Date']
df['Tournament'] = df['Tournament'].ffill()

2. 过滤赛事名称行 #

保留p1或p2不为NaN的数据行，移除作为表头的赛事名称行：

# 过滤掉赛事标题行
df = df[~df[['p1', 'p2']].isna().all(axis=1)]

3. 格式化日期列 #

将原始日期字符串提取出日期部分，转换为YYYY/MM/DD格式：

# 处理日期格式，提取日期部分并转换
df['Date'] = pd.to_datetime(df['Date'].str.split(',').str[0]).dt.strftime('%Y/%m/%d')

4. 调整列结构（可选） #

将Tournament列移至最前方，并对齐列名：

# 调整列顺序并重命名列名
df = df[['Tournament', 'Date', 'p1', 'p2']].rename(columns={'p1': 'P1', 'p2': 'P2'})

完整代码 #

import pandas as pd
import numpy as np

data = {
    "Date": ['USA Hamersely M45', '22nd Jul 2023,\n04:00 EDT', '22nd Jul 2023,\n05:00 EDT', 'Canada Valley Y67', '22nd Jul 2023,\n04:00 EDT', '22nd Jul 2023,\n05:00 EDT', '22nd Jul 2023,\n22:00 EDT'],
    "p1": [np.nan, 1.75, 1.85, np.nan, 2.37, 1.97, 1.75],
    "p2": [np.nan, '56%', '76%', np.nan, '26%', '86%', '96%']
}

df = pd.DataFrame(data)

# 提取并填充赛事名称
df['Tournament'] = df.loc[df[['p1', 'p2']].isna().all(axis=1), 'Date']
df['Tournament'] = df['Tournament'].ffill()

# 过滤赛事标题行
df = df[~df[['p1', 'p2']].isna().all(axis=1)]

# 格式化日期
df['Date'] = pd.to_datetime(df['Date'].str.split(',').str[0]).dt.strftime('%Y/%m/%d')

# 调整列顺序和名称
df = df[['Tournament', 'Date', 'p1', 'p2']].rename(columns={'p1': 'P1', 'p2': 'P2'})

print(df)

执行后输出的结果将与期望结构完全一致。

内容的提问来源于Stack Exchange，提问作者newoptionz