Converting csv with several items to a json with several elements in Python-CodePudding

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?

CodePudding user response：

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"}]}]