Python：从文件名提取日期并与Excel时间合并为DataFrame日期时间列问题

我有大量Excel文件需要提取数据到pandas DataFrame中，这些文件包含多列数据，其中一列是格式为“16:30”的时间字符串；文件名格式示例为“Monday 21st September 2020.xlsx”。

我尝试遍历文件，添加一个包含文件名中的日期与Excel文件内时间列的日期时间列，单文件测试时发现生成的Date列出现错误（显示为01-$m-2022），测试代码及运行输出如下：

测试代码 #

import pandas as pd
import datetime
import dateutil
import glob
import pathlib

folder = r"C:\temp\Friday 1st April 2022 (SB).xlsx"

for file in glob.glob(folder, recursive=False):
    #read in the excel file
    df = pd.read_excel(file, sheet_name="SB", usecols="B,I,J")
    
    #workout the date from the file name
    filedate = dateutil.parser.parse(pathlib.Path(file).stem.replace(" (SB)",""))
    
    #print filedate because it doesnt end up in the df correctly!
    print(type(filedate))
    print(filedate)
    df.insert(0,'Date', racedate.strftime('%d-$m-%Y'))
  
    print(df)

运行输出 #

C:\temp\Friday 1st April 2022 (SB).xlsx
<class 'datetime.datetime'>
2022-04-01 00:00:00
           Date   Time  R1  R2
0    01-$m-2022  16:30   9   5
1    01-$m-2022  16:30   5   5
2    01-$m-2022  16:30   6   5
3    01-$m-2022  16:30   3   6
4    01-$m-2022  16:30   3   3
..          ...    ...  ..  ..
446  01-$m-2022  16:15   3   4
447  01-$m-2022  16:15   3   3
448  01-$m-2022  16:15   3   3
449  01-$m-2022  16:15   5   3
450  01-$m-2022  16:15   5   4

[451 rows x 4 columns]

此外，我还需要将Date和Time两列合并为单个datetime对象。

错误原因 #

1. 变量名不一致：代码中解析得到的日期变量是filedate，但插入DataFrame时误用了未定义的racedate，不过从输出看filedate解析正确，核心问题是第二个点。
2. strftime格式符错误：%d-$m-%Y中的$m是错误写法，月份对应的格式符应为%m，多余的$被直接当作字符串输出，导致日期格式错误。

修正后的代码 #

以下代码同时修正格式错误，并实现日期与时间的合并：

import pandas as pd
import dateutil
import glob
import pathlib

# 若要遍历文件夹所有Excel文件，可改为r"C:\temp\*.xlsx"
folder = r"C:\temp\Friday 1st April 2022 (SB).xlsx"

for file in glob.glob(folder, recursive=False):
    # 读取指定工作表和列
    df = pd.read_excel(file, sheet_name="SB", usecols="B,I,J")
    
    # 从文件名提取并解析日期
    file_stem = pathlib.Path(file).stem.replace(" (SB)", "")
    filedate = dateutil.parser.parse(file_stem)
    
    # 插入正确格式的日期列
    df.insert(0, 'Date', filedate.strftime('%d-%m-%Y'))
    
    # 合并Date与Time列为datetime对象
    df['Datetime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'])
    
    print(df)

关键修改说明 #

• 统一变量名：将racedate改为filedate，避免未定义变量报错
• 修正日期格式符：把$m改为%m，确保月份正确解析
• 合并日期时间：使用pd.to_datetime()将日期字符串与时间字符串拼接后转为标准datetime对象，生成新的Datetime列

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