Print a json following a specific pattern-CodePudding

I have a file .csv like that:

ID;Name;Sports
1;Mark;["Football", "Volleyball"]
2;Luuk;["Fencing"]
3;Carl;["Swimming", "Basketball"]

My goal is to print on a file .json the following:

[
{
"ID": 1,
"Name": "Mark",
"Sports": ["Football", "Volleyball"]
}
,
{
"ID": 2,
"Name": "Luuk",
"Sports": ["Fencing"]
}
,
{
"ID": 3,
"Name": "Carl",
"Sports": ["Swimming", "Basketball"]
}
]

My code to produce the list:

from ast import literal_eval
import pandas as pd

df = pd.read_csv("test.csv", sep=";", converters={'Sports': literal_eval})

columns = list(df.columns)

json = []
for i in range(len(df)):
    line = {}
    for j in range(len(columns)):
        line[columns[j]] = df.iloc[i, j]
    json.append(line)

How can I smartly print my list in the same format written above?

CodePudding user response：

import json

data = {"data" : []}

for index, row in df.iterrows():
    data.append(row.to_dict())

with open('data.json', 'w') as fp:
    json.dump(data, fp)

CodePudding user response：

Use DataFrame.to_json:

from ast import literal_eval

df = pd.read_csv("test.csv", sep=";", converters={'Sports': literal_eval})


json = df.to_json(orient='records')
print (json)
[{"ID":1,"Name":"Mark","Sports":["Football","Volleyball"]},
 {"ID":2,"Name":"Luuk","Sports":["Fencing"]},
 {"ID":3,"Name":"Carl","Sports":["Swimming","Basketball"]}]

EDIT:

from ast import literal_eval

df = pd.read_csv("test.csv", sep=";", converters={'Sports': literal_eval})

d = df.to_json(orient='records')
print (d)
[{"ID":1,"Name":"Mark","Sports":["Football","Volleyball"]},
 {"ID":2,"Name":"Luuk","Sports":["Fencing"]},
 {"ID":3,"Name":"Carl","Sports":["Swimming","Basketball"]}]

import json
with open('result.json', 'w') as fp:
    json.dump(d, fp)