如何将DataFrame中分离的日期与时间行合并为datetime列？

我收集的时间数据中，日期与时间分别位于不同行，希望将二者合并为包含完整日期和时间的datetime列。数据未包含年份，默认所有日期为2023年。

示例输入数据 #

import pandas as pd
import numpy as np

sampletest = pd.DataFrame(columns=['daytime','datetime'])

sampletest = sampletest.append({'daytime':'Sunday, January 1'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'01:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'13:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'17:30'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'19:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'Monday, January 2'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'08:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'09:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'10:30'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'11:30'}, ignore_index=True)

期望输出结果 #

（注：修正原示例中笔误的时间值）

ConvertTime = pd.DataFrame(columns=['daytime','datetime'])
ConvertTime = ConvertTime.append({'daytime':'Sunday, January 1','datetime': np.NaN}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'01:00','datetime': '2023-01-01 01:00:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'13:00','datetime': '2023-01-01 13:00:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'17:30','datetime': '2023-01-01 17:30:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'19:00','datetime': '2023-01-01 19:00:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'Monday, January 2','datetime': np.NaN}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'08:00','datetime': '2023-01-02 08:00:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'09:00','datetime': '2023-01-02 09:00:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'10:30','datetime': '2023-01-02 10:30:00'}, ignore_index=True)
ConvertTime = ConvertTime.append({'daytime':'11:30','datetime': '2023-01-02 11:30:00'}, ignore_index=True)

解决方案 #

通过以下步骤实现需求：

1. 识别所有日期行（包含星期名称的行）
2. 为日期行补充2023年，生成完整日期字符串
3. 向前填充最近的日期值到后续时间行
4. 合并日期与时间字符串，转换为标准datetime格式
5. 将原始日期行的datetime列设为NaN

完整代码如下：

import pandas as pd
import numpy as np

# 构建示例数据
sampletest = pd.DataFrame(columns=['daytime','datetime'])
sampletest = sampletest.append({'daytime':'Sunday, January 1'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'01:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'13:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'17:30'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'19:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'Monday, January 2'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'08:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'09:00'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'10:30'}, ignore_index=True)
sampletest = sampletest.append({'daytime':'11:30'}, ignore_index=True)

# 标记日期行：匹配包含星期名称的行
sampletest['is_date'] = sampletest['daytime'].str.contains(r'(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday),', regex=True)

# 为日期行补充2023年，生成完整日期字符串
sampletest['date_str'] = np.where(
    sampletest['is_date'],
    sampletest['daytime'] + ', 2023',
    np.nan
)

# 向前填充最近的日期到所有后续时间行
sampletest['date_str'] = sampletest['date_str'].ffill()

# 合并日期与时间，转换为datetime格式；日期行的datetime设为NaN
sampletest['datetime'] = np.where(
    ~sampletest['is_date'],
    pd.to_datetime(sampletest['date_str'] + ' ' + sampletest['daytime']),
    np.nan
)

# 筛选目标列，得到最终结果
ConvertTime = sampletest[['daytime', 'datetime']].copy()

# 可选：将datetime格式转为字符串（与示例输出格式完全一致）
ConvertTime['datetime'] = ConvertTime['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S').where(~pd.isna(ConvertTime['datetime']), np.nan)

print(ConvertTime)

代码说明 #

• is_date列通过正则精准识别日期行，避免误判
• ffill()方法确保每个时间行都能获取到最近的日期值
• 使用pd.to_datetime()转换为标准datetime类型，如需字符串格式可通过dt.strftime()调整
• 最终仅保留需求中的daytime和datetime列，得到目标结果

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