如何合并含naive/UTC感知日期字符串的Pandas DataFrame？

问题背景 #

需要合并两个Pandas DataFrame，匹配规则为：

• df1的home_team匹配df2的home
• df1的away_team匹配df2的away
• df1的date（朴素日期字符串）匹配df2的date（带UTC标识的时间字符串，转换后可能对应前一天的日期）

df1数据及字段类型 #

df1.tail(10)
Out[8]: 
             date            home_team            away_team
65070  2022-11-29  Philadelphia Flyers   New York Islanders
65071  2022-11-29  Nashville Predators        Anaheim Ducks
65072  2022-11-29        Winnipeg Jets   Colorado Avalanche
65073  2022-11-29       Calgary Flames     Florida Panthers
65074  2022-11-29    Vancouver Canucks  Washington Capitals
65075  2022-11-29    Los Angeles Kings       Seattle Kraken
65076  2022-11-30  Toronto Maple Leafs      San Jose Sharks
65077  2022-11-30      Ottawa Senators     New York Rangers
65078  2022-11-30    Detroit Red Wings       Buffalo Sabres
65079  2022-11-30   Chicago Blackhawks      Edmonton Oilers

df1.dtypes
Out[10]: 
date         object
home_team    object
away_team    object
dtype: object

df2数据 #

df2.head(5)
Out[11]: 
                            game_id            home               away  
0  5852b18718643aa437344891e62dea6e  Buffalo Sabres  Detroit Red Wings   
1  5852b18718643aa437344891e62dea6e  Buffalo Sabres  Detroit Red Wings   
2  5852b18718643aa437344891e62dea6e  Buffalo Sabres  Detroit Red Wings   
3  5852b18718643aa437344891e62dea6e  Buffalo Sabres  Detroit Red Wings   
4  5852b18718643aa437344891e62dea6e  Buffalo Sabres  Detroit Red Wings   

                   date  
0  2022-10-31T23:00:00Z  
1  2022-10-31T23:00:00Z  
2  2022-10-31T23:00:00Z  
3  2022-10-31T23:00:00Z  
4  2022-10-31T23:00:00Z

df2.tail(5)
Out[15]: 
                                game_id                home             away  
45930  3cfc76eb156254d9fa5584be3442636e  Chicago Blackhawks  Edmonton Oilers   
45931  3cfc76eb156254d9fa5584be3442636e  Chicago Blackhawks  Edmonton Oilers   
45932  3cfc76eb156254d9fa5584be3442636e  Chicago Blackhawks  Edmonton Oilers   
45933  3cfc76eb156254d9fa5584be3442636e  Chicago Blackhawks  Edmonton Oilers   
45934  3cfc76eb156254d9fa5584be3442636e  Chicago Blackhawks  Edmonton Oilers   

                       date  
45930  2022-12-01T02:30:00Z  
45931  2022-12-01T02:30:00Z  
45932  2022-12-01T02:30:00Z  
45933  2022-12-01T02:30:00Z  
45934  2022-12-01T02:30:00Z 

核心问题 #

df2的UTC时间（如2022-12-01T02:30:00Z）转换为比赛当地时区后，日期会变为前一天（2022-11-30），导致直接按原始日期字符串或未调整的日期对象合并时，无法匹配df1的日期。

解决方案 #

关键是将df2的UTC时间转换为与df1对齐的比赛当地日期，再执行合并。

1. 统一日期处理 #

• 转换df1的date为日期对象：

df1['date'] = pd.to_datetime(df1['date']).dt.date

• 处理df2的UTC时间：优先用时区转换匹配实际比赛日期（以北美东部时区为例，NHL赛事常用该时区，可根据实际场景替换）：

# 转换为UTC时区的datetime对象
df2['utc_date'] = pd.to_datetime(df2['date'], utc=True)
# 转换为比赛当地时区并提取日期
df2['local_date'] = df2['utc_date'].dt.tz_convert('America/New_York').dt.date

如果不确定具体时区，可针对UTC凌晨时间直接调整日期（适用于多数跨UTC日期的赛事场景）：

# 备选方案：直接调整UTC日期到前一天
df2['adjusted_date'] = pd.to_datetime(df2['date']).dt.date - pd.Timedelta(days=1)

2. 执行合并操作 #

使用merge方法按主客场球队和调整后的日期匹配：

merged_df = pd.merge(
    df1,
    df2,
    left_on=['home_team', 'away_team', 'date'],
    right_on=['home', 'away', 'local_date'],  # 若用备选方案则替换为'adjusted_date'
    how='left'  # 根据需求选择left/inner/right合并方式
)

3. 清理冗余字段（可选） #

合并后删除中间生成的冗余字段并整理列名：

merged_df.drop(columns=['utc_date', 'local_date', 'date_y'], inplace=True)
merged_df.rename(columns={'date_x': 'date'}, inplace=True)

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