使用Pandas外连接与分组填充缺失日期遇列重复问题求助

原问题场景 #

现有如下门店小时销售数据：

import pandas as pd

# 创建示例数据
data = {'store name': ['Store A', 'Store A', 'Store B', 'Store B', 'Store C', 'Store C'],
        'time': ['2023-04-25 00:00:00', '2023-04-25 01:00:00', '2023-04-25 00:00:00', '2023-04-25 01:00:00', '2023-04-25 00:00:00', '2023-04-25 01:00:00'],
        'sales': [100, 200, 150, 250, 300, 350]}

df = pd.DataFrame(data)

需要补全2023年4月25日所有门店的24小时销售数据（缺失时段的sales可留空或填充默认值），但原代码执行时出现'store name'列已存在的报错，使用reset_index(drop=True)则会丢失门店名称。

解决方案1：改进GroupBy Apply逻辑 #

先将time列转换为datetime类型（确保时间匹配），然后在分组处理时给缺失时间行填充对应门店名称：

import pandas as pd
import datetime

# 转换time列为datetime类型
df['time'] = pd.to_datetime(df['time'])

# 生成4月25日完整小时范围（不含26日0点）
start = datetime.datetime(2023, 4, 25)
end = datetime.datetime(2023, 4, 26)
full_range = pd.date_range(start, end, freq='H', inclusive='left')
time_df = pd.DataFrame(full_range, columns=['time'])

# 分组合并并填充门店名称
df_full = df.groupby('store name').apply(
    lambda x: pd.merge(x, time_df, on='time', how='outer').assign(**{'store name': x.name})
).reset_index(drop=True)

# 可选：将缺失的sales填充为0或其他值
# df_full['sales'] = df_full['sales'].fillna(0)

逻辑说明 #

• x.name获取当前分组的门店名称，用assign将其赋值给合并后的所有行（包括新增的缺失时间行）
• reset_index(drop=True)直接丢弃分组产生的临时索引，不会丢失门店名称

解决方案2：笛卡尔积+左连接（更高效） #

对于大数据量场景，直接构建「所有门店×所有时间」的笛卡尔积，再左连接原数据，效率更高：

import pandas as pd
import datetime

# 转换time列为datetime类型
df['time'] = pd.to_datetime(df['time'])

# 获取所有门店列表和完整时间范围
stores = df['store name'].unique()
start = datetime.datetime(2023, 4, 25)
end = datetime.datetime(2023, 4, 26)
full_range = pd.date_range(start, end, freq='H', inclusive='left')

# 构建门店与时间的笛卡尔积
full_index = pd.MultiIndex.from_product([stores, full_range], names=['store name', 'time'])
df_full = pd.DataFrame(index=full_index).reset_index()

# 左连接原数据，保留所有时段
df_full = df_full.merge(df, on=['store name', 'time'], how='left')

逻辑说明 #

• from_product直接生成所有门店和时间的组合，确保无遗漏
• 左连接后，原数据中不存在的时段会自动留空sales，后续可按需填充

原代码问题分析 #

原代码执行报错的原因是：

1. groupby('store name').apply()后，每个分组的合并结果中，新增的缺失时间行的store name列为空
2. 调用reset_index()时，分组的门店名称会被添加为新的列，与原有的store name列重复，导致报错
3. 使用reset_index(drop=True)会同时丢弃分组索引（门店名称），导致新增行丢失门店信息

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