Hello I have been exploring Python to do a task in which I have the following csv file:
My goal was to get a json with the following structure:
[ { "1":[ { "Day":"1", "Result":"4", "Accuracy":"80" }, { "Day":"2", "Result":"4", "Accuracy":"80" }, { "Day":"3", "Result":"5", "Accuracy":"100" } ], "2":[ { "Day":"1", "Result":"3", "Accuracy":"60" }, { "Day":"2", "Result":"4", "Accuracy":"80" }, { "Day":"3", "Result":"5", "Accuracy":"100" } ], "3":[ { "Day":"1", "Result":"2", "Accuracy":"40" }, { "Day":"2", "Result":"2", "Accuracy":"40" }, { "Day":"3", "Result":"3", "Accuracy":"60" } ] } ]
I was looking at a suggestion on how to convert csv files to json using the following code
import csv import json with open('test.csv', 'r', encoding='utf8') as csvfile: with open('test.json', 'w', encoding='utf8') as jsonfile: reader = csv.DictReader(csvfile, delimiter=';') json.dump(list(reader), jsonfile)
However the reader is not identifying each item in the csv as an element and instead is only giving an output with a single element like this:
[ { "Day":"1", "Result":"4", "Accuracy":"80" }, { "Day":"2", "Result":"4", "Accuracy":"80" }, { "Day":"3", "Result":"5", "Accuracy":"100" }, { "Day":"", "Result":"", "Accuracy":"" }, { "Day":"Day", "Result":"Result", "Accuracy":"Accuracy" }, { "Day":"1", "Result":"3", "Accuracy":"60" }, { "Day":"2", "Result":"4", "Accuracy":"80" }, { "Day":"3", "Result":"5", "Accuracy":"100" }, { "Day":"", "Result":"", "Accuracy":"" }, { "Day":"Day", "Result":"Result", "Accuracy":"Accuracy" }, { "Day":"1", "Result":"2", "Accuracy":"40" }, { "Day":"2", "Result":"2", "Accuracy":"40" }, { "Day":"3", "Result":"3", "Accuracy":"60" } ]
How should I modify my csv file/ python code in order to obtain a list of elements with each item from the csv?
Advertisement
Answer
This is a quick solution, there is scope for improvement.
Check if this code works for you, let me know in case of any issues/confusion:
import json import pandas as pd import numpy as np data = pd.read_csv('test.csv') df_list = np.split(data, data[data.isnull().all(1)].index) output_data = {} for idx, df in enumerate(df_list): output_data[idx+1] = eval(df.dropna(how='all')[df['Day'] != 'Day'].to_json(orient='records')) with open('test.json', 'w', encoding='utf-8-sig') as jsonfile: json.dump([output_data], jsonfile, ensure_ascii=False)
Output:
[{“1”: [{“Day”: “1”, “Result”: “4”, “Accuracy”: “80”}, {“Day”: “2”, “Result”: “4”, “Accuracy”: “80”}, {“Day”: “3”, “Result”: “5”, “Accuracy”: “100”}], “2”: [{“Day”: “1”, “Result”: “3”, “Accuracy”: “60”}, {“Day”: “2”, “Result”: “4”, “Accuracy”: “80”}, {“Day”: “3”, “Result”: “5”, “Accuracy”: “100”}], “3”: [{“Day”: “1”, “Result”: “2”, “Accuracy”: “40”}, {“Day”: “2”, “Result”: “2”, “Accuracy”: “40”}, {“Day”: “3”, “Result”: “3”, “Accuracy”: “60”}]}]