使用Python将含日期分组的CSV文件转换为JSON格式

需求说明 #

将按日期分组的CSV数据转换为以日期为键、对应记录列表为值的JSON结构（注：你提供的JSON示例中每个日期仅展示单条记录，但CSV中每个日期包含多条，以下代码按实际多条记录处理，若需单条可自行调整）

实现代码 #

import csv
import json
from datetime import datetime

def csv_to_json(csv_file_path, json_file_path):
    result = {}
    current_date = None
    headers = []
    
    with open(csv_file_path, 'r', encoding='utf-8') as csv_file:
        # 逐行读取，跳过空行
        for line in csv_file:
            line = line.strip()
            if not line:
                continue
            
            # 分割字段并清理空值
            parts = line.split(',')
            parts = [p.strip() for p in parts if p.strip()]
            
            # 识别日期行：验证格式是否为%d %b %Y
            if len(parts) == 1:
                try:
                    datetime.strptime(parts[0], '%d %b %Y')
                    current_date = parts[0]
                    result[current_date] = []
                    # 读取下一行的表头
                    header_line = next(csv_file).strip()
                    headers = [h.strip() for h in header_line.split(',') if h.strip()]
                except ValueError:
                    continue
            elif current_date and headers:
                # 将数据行与表头映射为字典，加入当前日期的记录列表
                record = dict(zip(headers, parts))
                result[current_date].append(record)
    
    # 移除无记录的日期条目
    result = {date: records for date, records in result.items() if records}
    
    # 写入格式化后的JSON文件
    with open(json_file_path, 'w', encoding='utf-8') as json_file:
        json.dump(result, json_file, indent=4, ensure_ascii=False)

# 使用示例：替换为你的输入输出文件路径
csv_to_json('input.csv', 'output.json')

代码说明 #

1. 日期识别：通过datetime.strptime验证格式，确保仅识别符合日 月 年格式的行作为分组日期
2. 数据分组：遇到日期行后自动读取下一行的表头，后续数据行按表头生成字典，存入对应日期的列表中
3. 无效数据清理：自动跳过空行，移除没有任何记录的日期条目
4. JSON格式化：生成带缩进的可读性JSON文件，支持非ASCII字符

输出示例（对应你提供的CSV） #

{
    "31 Jan 2023": [
        {
            "Type": "Talk",
            "Time": "12:32",
            "Number": "61407228499",
            "Duration": "00:00:14",
            "Quantity": "N/A",
            "Cost": "$0.00"
        },
        {
            "Type": "Talk",
            "Time": "11:50",
            "Number": "61425380005",
            "Duration": "00:41:17",
            "Quantity": "N/A",
            "Cost": "$0.00"
        },
        {
            "Type": "Talk",
            "Time": "08:28",
            "Number": "61414464256",
            "Duration": "00:20:22",
            "Quantity": "N/A",
            "Cost": "$0.00"
        }
    ],
    "30 Jan 2023": [
        {
            "Type": "Text",
            "Time": "12:24",
            "Number": "61431655867",
            "Duration": "N/A",
            "Quantity": "N/A",
            "Cost": "$0.00"
        },
        {
            "Type": "Talk",
            "Time": "11:15",
            "Number": "61431655867",
            "Duration": "00:00:21",
            "Quantity": "N/A",
            "Cost": "$0.00"
        },
        {
            "Type": "Talk",
            "Time": "09:00",
            "Number": "131318",
            "Duration": "00:11:53",
            "Quantity": "N/A",
            "Cost": "$0.00"
        }
    ],
    "28 Jan 2023": [
        {
            "Type": "Talk",
            "Time": "12:45",
            "Number": "61414464256",
            "Duration": "00:00:03",
            "Quantity": "N/A",
            "Cost": "$0.00"
        }
    ]
}

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