How can I write JSON output (Fantasy Football data) to CSV using Python?

Question

I have managed to create a JSON file, parse it and view in Jupyter Notebook. What I can't figure out is how to export the datasets to .csv

import pandas as pd
import json
import requests
from pandas.io.json import json_normalize
import seaborn as sns
from matplotlib import pyplot as plt

# Define a function to get info from the FPL API and save to the specified file_path
# It might be a good idea to navigate to the link in a browser to get an idea of what the data looks like
def get_json(file_path):
    r = requests.get('https://fantasy.premierleague.com/api/bootstrap-static')
    jsonResponse = r.json()
    with open(file_path, 'w') as outfile:
        json.dump(jsonResponse, outfile)

# Run the function and choose where to save the json file CHANGE TO YOUR LOCATION
get_json('C:/Ste Files/Python/fantasy/fpl.json')

# Open the json file and print a list of the keys CHANGE TO YOUR LOCATION
with open('C:/Ste Files/Python/fantasy/fpl.json') as json_data:
    d = json.load(json_data)
    print(list(d.keys()))

['events', 'game_settings', 'phases', 'teams', 'total_players', 'elements', 'element_stats', 'element_types']

df = json_normalize(d['elements'])
print('Columns:\n', list(df), '\n')
print('Dataframe Head:\n', df.head())

Columns:

['assists', 'bonus', 'bps', 'chance_of_playing_next_round', 'chance_of_playing_this_round', 'clean_sheets', 'code', 'cost_change_event', 'cost_change_event_fall', 'cost_change_start', 'cost_change_start_fall', 'creativity', 'dreamteam_count', 'element_type', 'ep_next', 'ep_this', 'event_points', 'first_name', 'form', 'goals_conceded', 'goals_scored', 'ict_index', 'id', 'in_dreamteam', 'influence', 'minutes', 'news', 'news_added', 'now_cost', 'own_goals', 'penalties_missed', 'penalties_saved', 'photo', 'points_per_game', 'red_cards', 'saves', 'second_name', 'selected_by_percent', 'special', 'squad_number', 'status', 'team', 'team_code', 'threat', 'total_points', 'transfers_in', 'transfers_in_event', 'transfers_out', 'transfers_out_event', 'value_form', 'value_season', 'web_name', 'yellow_cards']

Dataframe Head:

    assists  bonus  bps  chance_of_playing_next_round  \
0        0      5  475                           NaN   
1        5      5  304                           0.0   
2        7     10  382                           NaN   
3        1      3  152                           NaN   
4        2      4  402                           NaN   

  chance_of_playing_this_round  clean_sheets    code  cost_change_event  \
0                         None             6   69140                  0   
1                         None             4   98745                  0   
2                         None             5  111457                  0   
3                         None             2  154043                  0   
4                         None             5   39476                  0   

   cost_change_event_fall  cost_change_start  ...  threat total_points  \
0                       0                  0  ...   252.0           80   
1                       0                  0  ...   280.0           60   
2                       0                  0  ...   242.0           81   
3                       0                  0  ...   126.0           34   
4                       0                  0  ...   120.0           64   

   transfers_in  transfers_in_event transfers_out transfers_out_event  \
0             0                   0             0                   0   
1             0                   0             0                   0   
2             0                   0             0                   0   
3             0                   0             0                   0   
4             0                   0             0                   0   

   value_form value_season        web_name  yellow_cards  
0         0.0          0.0         Mustafi             9  
1         0.0          0.0        Bellerín             3  
2         0.0          0.0       Kolasinac             5  
3         0.0          0.0  Maitland-Niles             1  
4         0.0          0.0        Sokratis            12  

[5 rows x 53 columns]

I am trying to get a .csv file with the above data tables fully populated

Possible duplicate of [How can I convert JSON to CSV?](https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv) — Markus, Aug 08 '19 at 09:25
@Markus I can't see how I can use that - unless I'm missing something — Ste B, Aug 08 '19 at 09:31
You data structure is no longer a JSON object after you packed it in a DataFrame. You can write a DataFrame to a CSV with `df.to_csv(file_name)`. — BoboDarph, Aug 08 '19 at 09:38

score 0 · Answer 1 · answered Aug 08 '19 at 09:40

Something like this:

import json
import csv

data_parsed = json.loads('your_json_data')
root_data = data_parsed['your_root_key']

with open('your_file.csv', 'w') as f:
    csvwriter = csv.writer(f)
    count = 0
    for d in root_data:
          if count == 0:
                 header = d.keys()
                 csvwriter.writerow(header)
                 count += 1
          csvwriter.writerow(d.values())
    f.close()

How can I write JSON output (Fantasy Football data) to CSV using Python?

1 Answers1