如何对齐合并后的供应与销售时间序列数据集？

原始数据集 #

供应数据集 #

Year      Month   Day  Hour       Item
    0    2023       05     17    10         8 
    1    2023       06     01    12         8 
    2    2023       06     10    16         3
    3    2023       06     17    10         8 
    4    2023       07     01    10         8 
    5    2023       08     17    10         8 

销售数据集 #

Year      Month   Day  Hour       Sale
    0    2023       05     17    16         3 
    1    2023       05     18    12         3 
    2    2023       05     24    16         3 
    3    2023       05     27    10         1 
    4    2023       06     02    10         2 
    5    2023       06     03    10         3 

当前合并问题 #

直接按行对齐合并的结果完全不符合需求，得到的错误输出如下：

Year      Month   Day  Hour       Item    Year      Month   Day  Hour       Sale  
    0    2023       05     17    10         8      2023       05     17    16         3
    1    2023       06     01    12         8      2023       05     18    12         3
    2    2023       06     10    16         3      2023       05     24    16         3
    3    2023       06     17    10         8      2023       05     27    10         1
    4    2023       07     01    10         8      2023       06     02    10         2
    5    2023       08     17    10         8      2023       06     03    10         3 

期望合并结果 #

要求每个销售记录对应最近且不晚于该销售时间的供应记录，规则如下：

• 若销售日期无匹配的供应记录，供应侧日期字段填NaN，Item先填0
• 若供应记录的日期早于销售记录的日期，将Item为0的字段替换为NaN

Year      Month   Day  Hour       Item    Year      Month   Day  Hour       Sale  
    0    2023       05     17    10         8      2023       05     17    16         3
    1     NaN       NaN    NaN    NaN        0      2023       05     18    12         3
    2     NaN       NaN    NaN    NaN        0      2023       05     24    16         3
    3     NaN       NaN    NaN    NaN        0      2023       05     27    10         1
    4    2023       06     01    12         8      2023       06     02    10         2
    5     NaN       NaN    NaN    NaN        0      2023       06     03    10         3 

实现方案（Python Pandas） #

代码实现 #

import pandas as pd

# 初始化供应数据集
supply_df = pd.DataFrame({
    'Year': [2023, 2023, 2023, 2023, 2023, 2023],
    'Month': [5, 6, 6, 6, 7, 8],
    'Day': [17, 1, 10, 17, 1, 17],
    'Hour': [10, 12, 16, 10, 10, 10],
    'Item': [8, 8, 3, 8, 8, 8]
})

# 初始化销售数据集
sales_df = pd.DataFrame({
    'Year': [2023, 2023, 2023, 2023, 2023, 2023],
    'Month': [5, 5, 5, 5, 6, 6],
    'Day': [17, 18, 24, 27, 2, 3],
    'Hour': [16, 12, 16, 10, 10, 10],
    'Sale': [3, 3, 3, 1, 2, 3]
})

# 1. 生成时间戳列，用于时间比较
supply_df['timestamp'] = pd.to_datetime(supply_df[['Year', 'Month', 'Day', 'Hour']])
sales_df['timestamp'] = pd.to_datetime(sales_df[['Year', 'Month', 'Day', 'Hour']])

# 2. 时间序列最近邻合并：只匹配不晚于销售时间的最近供应记录
merged_df = pd.merge_asof(
    sales_df.sort_values('timestamp'),
    supply_df.sort_values('timestamp'),
    on='timestamp',
    direction='backward'
)

# 3. 填充缺失值：未匹配的供应日期字段设为NaN，Item先填0
supply_date_cols = ['Year_y', 'Month_y', 'Day_y', 'Hour_y']
merged_df[supply_date_cols] = merged_df[supply_date_cols].where(merged_df['Item'].notna(), pd.NA)
merged_df['Item'] = merged_df['Item'].fillna(0)

# 4. 处理供应日期早于销售日期的情况：将Item为0的替换为NaN
mask = (merged_df['timestamp_y'] < merged_df['timestamp_x']) & (merged_df['Item'] == 0)
merged_df.loc[mask, 'Item'] = pd.NA

# 调整列顺序与名称，匹配期望格式
final_df = merged_df[['Year_y', 'Month_y', 'Day_y', 'Hour_y', 'Item', 'Year_x', 'Month_x', 'Day_x', 'Hour_x', 'Sale']]
final_df.columns = ['Year', 'Month', 'Day', 'Hour', 'Item', 'Year', 'Month', 'Day', 'Hour', 'Sale']

print(final_df)

关键逻辑说明 #

• pd.merge_asof：专为时间序列设计的合并方法，direction='backward'确保只匹配不晚于当前销售时间的最近供应记录
• 缺失值处理：未匹配到供应记录的行，日期字段置为NaN，Item先填充0，再根据时间比较结果替换为NaN
• 列调整：最后重新排列列顺序并修改名称，完全匹配期望的输出格式

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