Code to format JSON data and append hardcoded data to create a flat .txt file

Source Data::

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())

Need to create a flat .txt file with the below data.

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

I am very new in python. Have already tried to figure it out to achieve it but couldn't. Your help will be much appreciated.

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')

I would bypass using pandas for this and just build the string manually primarily using a list comprehension and join().

import datetime
import csv

Hardcoded_Val1 = 10
Hardcoded_Val2 = 20
Hardcoded_Val3 = str(datetime.date.today())
json_data = [
    {"studentid": 1, "name": "ABC", "subjects": ["Python", "Data Structures"]},
    {"studentid": 2, "name": "PQR", "subjects": ["Java", "Operating System"]}
]

csv_data = []
for row in json_data:
    keys = "|".join(row.keys())
    values = "|".join([
        ",".join(value) if isinstance(value, list) else str(value)
        for value in row.values()
    ])
    csv_data.append(dict([
        ("ID", Hardcoded_Val1),
        ("DEPT", Hardcoded_Val2),
        (keys, values),
        ("execution_dt", Hardcoded_Val3)
    ]))

with open("out.csv", "w", encoding="utf-8", newline="") as file_out:
    writer = csv.DictWriter(file_out, fieldnames=list(csv_data[0].keys()))
    writer.writeheader()
    writer.writerows(csv_data)

This will produce a file with the following contents:

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

I think this will help.

import pandas as pd

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

# Convert JSON to DataFrame
df = pd.json_normalize(json_data, "subjects", ["studentid", "name"])

# Rename columns
df.columns = ["subject", "studentid", "name"]

# Save DataFrame to CSV
df.to_csv("output.csv", index=False)