如何使用pd.to_datetime调整DataFrame中时间列的日期？

问题分析 #

你之前的尝试失败是因为pd.to_datetime(test['Date'].dt.date)和pd.to_datetime(test['WakeUp'].dt.time)都是datetime对象，pandas不支持直接相加两个datetime。正确的做法是将时间转为时间差（timedelta）后与日期相加，或者直接拼接日期和时间字符串再转datetime，同时处理跨天的时间（如00:10这类属于次日的时间）。

方法一：时间差（Timedelta）合并法 #

这种方法先将时间转为timedelta，再与日期列相加，同时判断跨天时间并调整日期：

import pandas as pd

# 加载你的数据（示例）
df = pd.DataFrame({
    'Unnamed: 1': ['2022-09-06', '2022-09-07', '2022-09-08', '2022-09-09'],
    'Unnamed: 2': ['08:03:00', '07:30:00', '08:30:00', '08:56:00'],
    'Unnamed: 3': ['12:09:00', '12:20:00', '12:15:00', '11:00:00'],
    'Unnamed: 4': ['20:19:00', '20:35:00', '21:30:00', '23:00:00'],
    'Unnamed: 5': ['22:35:00', '00:10:00', '00:33:00', '02:00:00']
})

# 重命名列，方便操作
df.columns = ['Date', 'WakeUp', '1stMeal', '2ndMeal', 'Sleep']

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

# 定义合并日期和时间的函数，自动处理跨天情况
def merge_date_time(date_col, time_str):
    # 将时间字符串转为timedelta
    time_delta = pd.to_timedelta(time_str)
    # 初始合并日期与时间
    combined_dt = date_col + time_delta
    # 判断：如果时间小于6小时（属于次日作息，比如凌晨睡觉），则日期加1天
    # 可根据你的实际需求调整这个阈值（比如改为3小时）
    cross_day_mask = time_delta < pd.Timedelta('6 hours')
    combined_dt.loc[cross_day_mask] += pd.Timedelta(days=1)
    return combined_dt

# 批量处理所有时间列
df['WakeUp'] = merge_date_time(df['Date'], df['WakeUp'])
df['1stMeal'] = merge_date_time(df['Date'], df['1stMeal'])
df['2ndMeal'] = merge_date_time(df['Date'], df['2ndMeal'])
df['Sleep'] = merge_date_time(df['Date'], df['Sleep'])

方法二：字符串拼接法 #

直接将日期字符串和时间字符串拼接，转成datetime后再调整跨天日期：

import pandas as pd

# 加载并预处理数据
df = pd.DataFrame({
    'Unnamed: 1': ['2022-09-06', '2022-09-07', '2022-09-08', '2022-09-09'],
    'Unnamed: 2': ['08:03:00', '07:30:00', '08:30:00', '08:56:00'],
    'Unnamed: 3': ['12:09:00', '12:20:00', '12:15:00', '11:00:00'],
    'Unnamed: 4': ['20:19:00', '20:35:00', '21:30:00', '23:00:00'],
    'Unnamed: 5': ['22:35:00', '00:10:00', '00:33:00', '02:00:00']
})

df.columns = ['Date', 'WakeUp', '1stMeal', '2ndMeal', 'Sleep']
df['Date'] = pd.to_datetime(df['Date'])

# 拼接日期和时间字符串，转成datetime
df['WakeUp'] = pd.to_datetime(df['Date'].astype(str) + ' ' + df['WakeUp'])
df['1stMeal'] = pd.to_datetime(df['Date'].astype(str) + ' ' + df['1stMeal'])
df['2ndMeal'] = pd.to_datetime(df['Date'].astype(str) + ' ' + df['2ndMeal'])
df['Sleep'] = pd.to_datetime(df['Date'].astype(str) + ' ' + df['Sleep'])

# 调整跨天的睡眠时间：如果小时数小于6，日期加1天
sleep_cross_day_mask = df['Sleep'].dt.hour < 6
df.loc[sleep_cross_day_mask, 'Sleep'] += pd.Timedelta(days=1)

效果验证 #

处理后的数据会变成：

Date               WakeUp              1stMeal              2ndMeal                Sleep
0 2022-09-06 2022-09-06 08:03:00 2022-09-06 12:09:00 2022-09-06 20:19:00 2022-09-06 22:35:00
1 2022-09-07 2022-09-07 07:30:00 2022-09-07 12:20:00 2022-09-07 20:35:00 2022-09-08 00:10:00
2 2022-09-08 2022-09-08 08:30:00 2022-09-08 12:15:00 2022-09-08 21:30:00 2022-09-09 00:33:00
3 2022-09-09 2022-09-09 08:56:00 2022-09-09 11:00:00 2022-09-09 23:00:00 2022-09-10 02:00:00

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