Python中CSV日期时间转DateTime对象及比对问题

需求与问题 #

要编写Python函数处理CSV：将每行的日期时间，与CSV前两行（第一行的日期+时间字段）拼接成的基准DateTime比对，筛选不符合条件的行。目前遇到两个问题：

• ValueError异常：当前代码中date_time获取的是整行内容的字符串，无法匹配%m/%d/%Y %H:%M:%S格式，导致转换DateTime失败
• 动态拼接基准时间：不确定如何正确提取第一行的日期和时间字段，拼接成可比对的基准DateTime，再与后续行的合并日期时间做比对

当前测试代码 #

from datetime import datetime
import csv

def CheckDates(f):
    with open(f, newline='', encoding='utf-8') as g:
        r = csv.reader(g)
        date_time = str(next(r))
        for line in r:
            if datetime.strptime(date_time, '%m/%d/%Y %H:%M:%S') >= datetime.strptime('01/11/2022 13:19:00', '%m/%d/%Y %H:%M:%S'):
                # Dates pass
                pass
            else:
                # Dates fail
                pass

示例CSV内容 #

TD,08/24/2021,14:14:08,21012,223,0,1098,0,031,810,12,01,092,048,0008,02
Date/Time,G120010,M129000,G110100,M119030,G112070,G112080,G111030,G127020,G127030,G120020,G120030,G121020,G111040,G112010,P102000,G112020,G112040,G112090,G110050,G110060,G110070,T111100
06/27/2022 00:00:01,40,133.2,0,0,7.284853,0,0.6030464,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:03,40,133.2,0,0,7.284853,0,0.5898247,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:05,40,133.2,0,0,7.284853,0,0.6135368,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:07,40,133.2,0,0,7.284853,0,0.6087456,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:09,40,133.2,0,0,7.284853,0,0.5903625,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:11,40,133.2,0,0,7.284853,0,0.5799789,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:13,40,133.2,0,0,7.284853,0,0.5821953,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:15,40,133.2,0,0,7.284853,0,0.6024017,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5
06/27/2022 00:00:17,40,133.2,0,0,7.284853,0,0.5984001,0,0,1,0,5,11,5,0,0,414,344,0,154,0,5

修复后的代码 #

from datetime import datetime
import csv

def CheckDates(f):
    invalid_rows = []
    date_format = '%m/%d/%Y %H:%M:%S'
    
    with open(f, newline='', encoding='utf-8') as g:
        r = csv.reader(g)
        
        # 读取第一行，提取日期和时间字段
        header_row1 = next(r)
        # 第一行的第2个元素是日期，第3个是时间（索引从0开始）
        base_date_str = header_row1[1]
        base_time_str = header_row1[2]
        # 拼接成完整的日期时间字符串并转换为datetime对象
        try:
            base_datetime = datetime.strptime(f"{base_date_str} {base_time_str}", date_format)
        except ValueError as e:
            print(f"基准时间转换失败: {e}")
            return invalid_rows
        
        # 跳过第二行表头（不需要处理表头内容）
        next(r)
        
        # 遍历后续数据行
        for line_num, line in enumerate(r, start=3):  # 行号从3开始，对应CSV的第三行
            if not line:
                continue
            row_datetime_str = line[0]
            try:
                row_datetime = datetime.strptime(row_datetime_str, date_format)
                # 比对：如果行时间早于基准时间，标记为无效行
                if row_datetime < base_datetime:
                    invalid_rows.append((line_num, line))
            except ValueError as e:
                print(f"第{line_num}行日期时间转换失败: {e}")
                invalid_rows.append((line_num, line))
    
    return invalid_rows

# 调用示例
# invalid = CheckDates("your_file.csv")
# for line_num, line in invalid:
#     print(f"无效行 {line_num}: {line}")

关键说明 #

1. 解决ValueError：不再将整行转为字符串，而是直接提取第一行的日期（header_row1[1]）和时间（header_row1[2]）字段，拼接后转换为datetime对象
2. 动态基准时间拼接：通过提取第一行指定位置的字段，灵活拼接成基准时间，无需硬编码
3. 异常处理：添加try-except捕获转换失败的情况，避免程序崩溃，同时记录错误行
4. 筛选逻辑：将不符合条件（行时间早于基准时间）的行记录下来，返回给调用方处理

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