pythonpython-3.x

Nested JSON files in python to CSV file


i have used my script to pull API Data (thanks to those who helped) next stage of my problem... the JSON file came back like below

[
        {
            "team": {
                "division": "divison1",
                "rank": 1,
                "team_name": "team name 1",
                "team_id": "82f9a58f",
                "value": 1450,
                "score": 2417,
                "w/d/l": "35/10/6"
            },
            "coach": {
                "coach_name": "coach a",
                "coach_id": "4364b994",
                "lang": "english",
                "country": null
            }
        },

[in a csv the data looks like this] (https://i.sstatic.net/AEmwua8J.png)

i now need to get this into .csv with the formatting below.

division,rank,team_name,team_id,value,score,w/d/l,coach_name,coach_id,lang,country division1,1,team name 1,82f9a58f,1450,2417,35/10/6,coach a,4364b994,english,null

anyone know how i can do this? have had a search around and cant find anything that has worked so far

searched around but to no avail


Solution

  • To convert your JSON data to a CSV file with the desired format, you can use Python. The pandas library is particularly useful for this kind of data manipulation. Here’s a script that accomplishes this task:

    import json
    import pandas as pd
    
    # Sample JSON data
    json_data = '''
    [
        {
            "team": {
                "division": "division1",
                "rank": 1,
                "team_name": "team name 1",
                "team_id": "82f9a58f",
                "value": 1450,
                "score": 2417,
                "w/d/l": "35/10/6"
            },
            "coach": {
                "coach_name": "coach a",
                "coach_id": "4364b994",
                "lang": "english",
                "country": null
            }
        }
    ]
    '''
    
    # Load JSON data
    data = json.loads(json_data)
    
    # Normalize the JSON data into a flat table
    team_data = pd.json_normalize(data)
    
    # Create a DataFrame with the desired columns
    df = pd.DataFrame({
        'division': team_data['team.division'],
        'rank': team_data['team.rank'],
        'team_name': team_data['team.team_name'],
        'team_id': team_data['team.team_id'],
        'value': team_data['team.value'],
        'score': team_data['team.score'],
        'w/d/l': team_data['team.w/d/l'],
        'coach_name': team_data['coach.coach_name'],
        'coach_id': team_data['coach.coach_id'],
        'lang': team_data['coach.lang'],
        'country': team_data['coach.country']
    })
    
    # Save the DataFrame to a CSV file
    df.to_csv('output.csv', index=False)
    
    print(df)