Python格式化JSON并生成指定格式扁平TXT文件技术求助

我是Python新手，需要将给定的JSON数据与几个硬编码值结合，生成指定格式的扁平.txt文件，自己尝试编写的代码未成功，现提供相关信息请求协助。

源数据 #

import datetime

json_data = [
    {"studentid": 1, "name": "ABC", "subjects": ["Python", "Data Structures"]},
    {"studentid": 2, "name": "PQR", "subjects": ["Java", "Operating System"]}
]

Hardcoded_Val1 = 10
Hardcoded_Val2 = 20
Hardcoded_Val3 = str(datetime.datetime.now().date())  # 可调整为仅保留日期，与示例格式匹配

目标文件格式 #

ID,DEPT,"studentid|name|subjects",execution_dt
10,20,"1|ABC|Python,Data Structures",2023-06-01
10,20,"2|PQR|Java,Operating System",2023-06-01

当前尝试的代码 #

import datetime
import pandas as pd
import json

json_data = [{"studentid": 1, "name": "ABC", "subjects": ["Python", "Data Structures"]}, {"studentid": 2, "name": "PQR", "subjects": ["Java", "Operating System"]}]

Hardcoded_Val1 = 10
Hardcoded_Val2 = 20
Hardcoded_Val3 = str(datetime.datetime.now())

profile = str(Hardcoded_Val1) + ',' + str(Hardcoded_Val2) + ',"' + str(json_data) + '",' + Hardcoded_Val3
        
print(profile)
#data = json.dumps(profile, indent=True)
#print(data)
data_list = []
for data_info in profile:
   data_list.append(data_info.replace(", '", '|'))
data_df = pd.DataFrame(data=data_list)
data_df.to_csv(r'E:\DataLake\api_fetched_sample_output.txt', sep='|', index=False, encoding='utf-8')

解决方案 #

你的代码问题在于直接把整个JSON数组转成字符串拼接，没有逐个处理学生数据，后续循环逻辑也偏离需求。以下是两种可行实现方式：

方式1：直接写入文件（无需pandas） #

import datetime

json_data = [
    {"studentid": 1, "name": "ABC", "subjects": ["Python", "Data Structures"]},
    {"studentid": 2, "name": "PQR", "subjects": ["Java", "Operating System"]}
]

Hardcoded_Val1 = 10
Hardcoded_Val2 = 20
# 格式化日期为目标格式
Hardcoded_Val3 = datetime.datetime.now().strftime("%Y-%m-%d")

output_path = r'E:\DataLake\api_fetched_sample_output.txt'

# 写入文件
with open(output_path, 'w', encoding='utf-8') as f:
    # 写入表头
    f.write('ID,DEPT,"studentid|name|subjects",execution_dt\n')
    # 遍历每个学生生成行数据
    for student in json_data:
        subjects_str = ','.join(student['subjects'])
        student_info = f"{student['studentid']}|{student['name']}|{subjects_str}"
        line = f"{Hardcoded_Val1},{Hardcoded_Val2},\"{student_info}\",{Hardcoded_Val3}\n"
        f.write(line)

print(f"文件已生成：{output_path}")

方式2：使用pandas实现 #

import datetime
import pandas as pd

json_data = [
    {"studentid": 1, "name": "ABC", "subjects": ["Python", "Data Structures"]},
    {"studentid": 2, "name": "PQR", "subjects": ["Java", "Operating System"]}
]

Hardcoded_Val1 = 10
Hardcoded_Val2 = 20
Hardcoded_Val3 = datetime.datetime.now().strftime("%Y-%m-%d")

# 构造DataFrame数据
data_rows = []
for student in json_data:
    subjects_str = ','.join(student['subjects'])
    student_info = f"{student['studentid']}|{student['name']}|{subjects_str}"
    data_rows.append({
        'ID': Hardcoded_Val1,
        'DEPT': Hardcoded_Val2,
        'studentid|name|subjects': student_info,
        'execution_dt': Hardcoded_Val3
    })

df = pd.DataFrame(data_rows)
output_path = r'E:\DataLake\api_fetched_sample_output.txt'
# 设置quoting参数确保第三列被双引号包裹
df.to_csv(output_path, index=False, encoding='utf-8', quoting=1, quotechar='"')

print(f"文件已生成：{output_path}")

关键说明 #

1. 处理subjects：将列表转为逗号分隔的字符串，匹配目标格式
2. 拼接学生信息：用|连接studentid、name和处理后的subjects字符串
3. 硬编码值复用：每行学生数据都使用相同的固定值和执行日期
4. 格式匹配：确保第三列被双引号包裹，表头与目标完全一致

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