如何基于相似时间列合并多时段序列DataFrame

问题描述 #

需要合并两个（后续将扩展为多个）DataFrame，最终生成包含大量行和列的DataFrame。原始数据如下：

原始DataFrame #

df1：

Init Date   2021-01-01 00:00:00 2021-01-01 06:00:00 2021-01-01 12:00:00 2021-01-01 18:00:00
2021-01-01  37.3                35.29               35.72               41.28   

df2：

Init Date   2021-01-01 06:00:00 2021-01-01 12:00:00 2021-01-01 18:00:00 2021-01-02 00:00:00
2021-01-01 06:00:00 35.35       35.54               41.12               42.33

每个DataFrame的Init Date列值按6小时顺序排列，后续列是对应时间点的数值，同样按6小时间隔排列。核心要求是：Init Date时间越晚，数据的起始时间也越晚，因此合并后的结果中，第二行的第一个非Init Date列需为空，第三行的前两列需为空，以此类推。尝试过pd.merge()和pd.concat()方法，未找到正确实现方式。

期望输出 #

df3：

Init Date   2021-01-01 00:00:00 2021-01-01 06:00:00 2021-01-01 12:00:00 2021-01-01 18:00:00 2021-01-02 00:00:00
2021-01-01  37.3                35.29               35.72               41.28
2021-01-01 06:00:00             35.35               35.54               41.12               42.25 

解决方案 #

• 首先将所有DataFrame的Init Date设置为索引，确保索引是时间类型；
• 获取所有DataFrame中出现过的所有时间列，构建一个完整的时间列集合；
• 对每个DataFrame，重新索引到完整的时间列集合，利用时间差计算需要填充空值的位置：每个行的索引（Init Date）对应的时间，比时间列早的位置都设为空；
• 最后将处理后的DataFrame纵向拼接。

具体代码如下：

import pandas as pd

# 构造示例数据
df1 = pd.DataFrame(
    {'Init Date': ['2021-01-01'],
     '2021-01-01 00:00:00': [37.3],
     '2021-01-01 06:00:00': [35.29],
     '2021-01-01 12:00:00': [35.72],
     '2021-01-01 18:00:00': [41.28]}
).set_index('Init Date')
df1.index = pd.to_datetime(df1.index)

df2 = pd.DataFrame(
    {'Init Date': ['2021-01-01 06:00:00'],
     '2021-01-01 06:00:00': [35.35],
     '2021-01-01 12:00:00': [35.54],
     '2021-01-01 18:00:00': [41.12],
     '2021-01-02 00:00:00': [42.33]}
).set_index('Init Date')
df2.index = pd.to_datetime(df2.index)

# 获取所有时间列并排序
all_time_cols = sorted(set(df1.columns) | set(df2.columns))
all_time_cols = pd.to_datetime(all_time_cols)

def process_single_df(df):
    # 重新索引到完整时间列集合
    df = df.reindex(columns=all_time_cols)
    # 遍历每行，将早于当前行Init Date的列设为空
    for idx, _ in df.iterrows():
        mask = all_time_cols < idx
        df.loc[idx, mask] = None
    return df

# 处理所有DataFrame并拼接
processed_dfs = [process_single_df(df) for df in [df1, df2]]
df3 = pd.concat(processed_dfs)

# 将列名转回字符串格式（可选）
df3.columns = df3.columns.strftime('%Y-%m-%d %H:%M:%S')
print(df3)

运行后即可得到符合要求的合并结果。

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