如何在Pandas中清洗混合日期与变量的列并转换为指定结构数据表

核心逻辑 #

你所遇到的是「分类头（日期）与明细行（州数据）混合在同一列」的典型表格结构，核心处理思路是先将日期值关联到其下属的所有州数据行，再通过透视操作转换为目标宽表。

实现步骤 #

• 第一步：识别日期并关联到对应州行
  用正则匹配etiqueta列中的年月值，新建fecha列存储匹配到的日期，未匹配到的空值通过向下填充的方式，让每个州行都能关联到上方最近的对应日期。
• 第二步：过滤纯州名数据行
  剔除etiqueta列为日期的行，剩余行的etiqueta值均为墨西哥州名。
• 第三步：透视生成宽表
  以fecha为索引、州名为列、suma为值做透视，无匹配的单元格统一填充为0，重置索引后即为目标结果。

完整代码示例 #

import pandas as pd
import numpy as np

# 替换为你自己的数据源读取代码，例如pd.read_excel、pd.read_csv
df = pd.DataFrame({
    'etiqueta': ['2023-01', 'Ciudad de México', 'Jalisco', 'Nuevo León', '2023-02', 'Ciudad de México', 'Puebla'],
    'suma': [0, 120, 98, 76, 0, 142, 85]
})

# 可根据你实际的年月格式调整正则规则，当前规则匹配YYYY-MM、YYYY/MM格式
date_regex = r'^\d{4}[-/]\d{2}$'
# 生成日期列并向下填充
df['fecha'] = np.where(df['etiqueta'].str.match(date_regex), df['etiqueta'], np.nan)
df['fecha'] = df['fecha'].ffill()

# 过滤仅保留州名数据行
df_state = df[~df['etiqueta'].str.match(date_regex)].copy()

# 透视转宽表，缺失值填充0
result = df_state.pivot_table(
    index='fecha',
    columns='etiqueta',
    values='suma',
    fill_value=0
).reset_index()

# 可选：清除列名的额外索引标记
result.columns.name = None

如果你的日期是西班牙文格式（如Ene 2023、2023-enero），只需要调整date_regex的匹配规则即可，整体处理逻辑无需修改。

内容的提问来源于stack exchange，提问作者Jay Ballesteros C.