CSV to Specific JSON Structure

Question

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!

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community
– Community Bot, Commented Oct 4, 2021 at 14:01

user2668284 · Accepted Answer · 2021-09-26 09:30:30Z

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

answered Sep 26, 2021 at 9:30

user2668284

Sign up to request clarification or add additional context in comments.

1 Comment

Fonti Over a year ago

Thank you so much for your help!

Collectives™ on Stack Overflow

CSV to Specific JSON Structure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related