基于日期合并指定ID的DataFrame值并修正列序与缺失值

问题描述 #

需要将Pandas DataFrame按ID分组，把指定日期（2019-01-01、2019-01-02、2019-01-03）对应的所有val列合并到单行。当前使用pivot方法存在两个问题：

• 列顺序混乱（如val1、val10、val11...而非val1、val2...的自然顺序）
• 缺失日期的数据未用前后日期值填充

原DataFrame代码 #

import pandas as pd

df = pd.DataFrame()
df['id'] = [1, 1, 2,2, 3, 3, 3]
df['date'] = ['2019-01-01', '2019-01-03', '2019-01-01','2019-01-02', '2019-01-01',   '2019-01-02','2019-01-03']
df['val1']  = [10, 100, 20, 30, 40, 50, 60]
df['val2']  = [30, 30, -20, -30, -40,-50, -60 ]
df['val3']  = [50, 10, 120, 300, 140, 150, 160]

df['val4']  = [10, 100, 20, 30, 40, 50, 60]
df['val5']  = [30, 30, -20, -30, -40,-50, -60 ]
df['val6']  = [50, 10, 120, 300, 140, 150, 160]

df['val7']  = [10, 100, 20, 30, 40, 50, 60]
df['val8']  = [30, 30, -20, -30, -40,-50, -60 ]
df['val9']  = [50, 10, 120, 300, 140, 150, 160]
df['val10']  = [10, 100, 20, 30, 40, 50, 60]
df['val11']  = [30, 30, -20, -30, -40,-50, -60 ]
df['val12']  = [50, 10, 120, 300, 140, 150, 160]

当前使用的代码（存在问题） #

val_cols = df.filter(like='val').columns

df_s = (df.pivot('id', 'date', val_cols).groupby(level=0, axis=1).apply(lambda x:x.ffill(axis=1).bfill(axis=1)).sort_index(axis=1, level=1))

解决方案代码 #

import pandas as pd

# 1. 定义目标日期列表，确保每个ID都覆盖这些日期
target_dates = ['2019-01-01', '2019-01-02', '2019-01-03']

# 2. 生成ID+目标日期的完整索引，补充缺失的日期行
full_index = pd.MultiIndex.from_product([df['id'].unique(), target_dates], names=['id', 'date'])
df_full = df.set_index(['id', 'date']).reindex(full_index).reset_index()

# 3. 对val列按数字排序，解决字符串排序导致的顺序混乱
val_cols_sorted = sorted(df.filter(like='val').columns, key=lambda x: int(x[3:]))

# 4. 按ID分组，用前后日期的值填充缺失数据
df_filled = df_full.groupby('id').apply(lambda x: x.set_index('date')[val_cols_sorted].ffill().bfill()).reset_index()

# 5. 转成宽表并调整列顺序，确保日期和val列都按预期排列
df_result = df_filled.pivot(index='id', columns='date', values=val_cols_sorted)
df_result = df_result.reindex(columns=target_dates, level=1)
df_result = df_result.reindex(columns=val_cols_sorted, level=0)

# 可选：合并多级列名为单级，方便查看
df_result.columns = [f"{date}_{col}" for col, date in df_result.columns]
df_result = df_result.reset_index()

print(df_result)

关键说明 #

• 先通过reindex补充每个ID的所有目标日期行，为填充缺失值打基础
• 用key=lambda x: int(x[3:])提取val列的数字后缀排序，避免val1、val10的错位
• 分组后用ffill()和bfill()双向填充，保证缺失日期的val值有合理数据
• 最后通过reindex双重调整列顺序，确保日期和val列都按自然顺序排列

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