-1

I need help in converting a CSV file to a specifically-formatted JSON file in Python.

My CSV file looks like the following:

Player Name Team R1 Position R1 Price R1 Score R2 Position R2 Price R2 Score
Player A Team A DEF 100000 10 DEF/MID 110000 11
Player B Team B RUC 200000 20 RUC/FWD 210000 21

The JSON file's code that is in the correct formatting is as follows:

{
    "Player A": {
        "Team": "Team A",
        "Position": {
            "1": "DEF",
            "2": "DEF/MID"
        },
        "Price": {
            "1": 100000,
            "2": 110000
        },
        "Score": {
            "1": 10,
            "2": 11
        }
    },
    "Player B": {
        "Team": "Team B",
        "Position": {
            "1": "RUC",
            "2": "RUC/FWD"
        },
        "Price": {
            "1": 200000,
            "2": 210000
        },
        "Score": {
            "1": 20,
            "2": 21
        }
    }
}

So far, my current Python code consists of the following below, but I am stuck to where to go to from here. I understand I need to make the first row of my CSV the header, and that I need to somehow group the columns by Position, Price and Score.

import pandas as pd
df = pd.read_csv(r"file_name.csv")
df = df.fillna(0)
df = df.T
df.columns = df.iloc[0]
df = df[1:]
df.to_json(r"file_name.json", orient='columns')

I have tried viewing and reproducing many past Stack Overflow questions and solutions, such as the following: Convert csv to JSON tree structure?

Thank you for taking the time to help me! I really appreciate it!

Fonti
  • 133
  • 1
  • 9
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Oct 04 '21 at 14:01

1 Answers1

0

Here are a couple of examples. Firstly without and secondly with pandas:-

import json
import pandas as pd
D = {}
FILE = 'players.csv'
with open(FILE) as csv:
    for i, line in enumerate(csv.readlines()):
        if i > 0:
            t = line.strip().split(',')
            k = t[0]
            D[k] = {}
            D[k]['Team'] = t[1].strip()
            D[k]['Position'] = {'1': t[2].strip(), '2': t[5].strip()}
            D[k]['Price'] = {'1': int(t[3]), '2': int(t[6])}
            D[k]['Score'] = {'1': int(t[4]), '2': int(t[7])}
print(json.dumps(D, indent=2))

df = pd.read_csv(FILE)
D = {}
for _, r in df.iterrows():
    k = r['Player Name']
    D[k] = {}
    D[k]['Team'] = r['Team'].strip()
    D[k]['Position'] = {
        '1': r['R1 Position'].strip(), '2': r['R2 Position'].strip()}
    D[k]['Price'] = {'1': r['R1 Price'], '2': r['R2 Price']}
    D[k]['Score'] = {'1': r['R1 Score'], '2': r['R2 Score']}
print(json.dumps(D, indent=2))