Extracting information from multiple JSON files to single CSV file in python

Question

I have a JSON file with multiple dictionaries:

{"team1participants": 
[ {
        "stats": {
            "item1": 3153, 
            "totalScore": 0, 
            ...
        }
   },
   {
        "stats": {
            "item1": 2123, 
            "totalScore": 5, 
            ...
        }
   },
   {
        "stats": {
            "item1": 1253, 
            "totalScore": 1, 
            ...
        }
   }
],
"team2participants": 
[ {
        "stats": {
            "item1": 1853, 
            "totalScore": 2, 
            ...
        }
   },
   {
        "stats": {
            "item1": 21523, 
            "totalScore": 5, 
            ...
        }
   },
   {
        "stats": {
            "item1": 12503, 
            "totalScore": 1, 
            ...
        }
   }
]
}

In other words, the JSON has multiple keys. Each key has a list containing statistics of individual participants.

I have many such JSON files, and I want to extract it to a single CSV file. I can of course do this manually, but this is very tedious. I know of DictWriter, but it seems to work only for single dictionaries. I also know that dictionaries can be concatenated, but it will be problematic because all dictionaries have the same keys.

How can I efficiently extract this to a CSV file?

Alexander · Accepted Answer · 2016-08-22 19:14:59Z

3

You can make your data tidy so that each row is a unique observation.

teams = []
items = []
scores = []
for team in d:
    for item in d[team]:
        teams.append(team)
        items.append(item['stats']['item1'])
        scores.append(item['stats']['totalScore'])


# Using Pandas.
import pandas as pd

df = pd.DataFrame({'team': teams, 'item': items, 'score': scores})
>>> df
    item   score               team
0   1853       2  team2participants
1  21523       5  team2participants
2  12503       1  team2participants
3   3153       0  team1participants
4   2123       5  team1participants
5   1253       1  team1participants

You could also use a list comprehension instead of a loop.

results = [[team, item['stats']['item1'], item['stats']['totalScore']] 
           for team in d for item in d[team]]
df = pd.DataFrame(results, columns=['team', 'item', 'score'])

You can then do a pivot table, for example:

>>> df.pivot_table(values='score ', index='team ', columns='item', aggfunc='sum').fillna(0)
item               1253   1853   2123   3153   12503  21523
team                                                       
team1participants      1      0      5      0      0      0
team2participants      0      2      0      0      1      5

Also, now that it is a dataframe, it is easy to save it as a CSV.

df.to_csv(my_file_name.csv)

edited Aug 22, 2016 at 19:14

answered Aug 22, 2016 at 18:57

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

5 Comments

juanpa.arrivillaga Over a year ago

You should probably clarify that you are using the pandas library.

wwl Over a year ago

Thanks. If I want to make the four rows into one, should I repeatedly pivot?

Alexander Over a year ago

@wwl What would you like the result to look like?

wwl Over a year ago

the columns should be: team1player1item1, team1player1totalscore, ..., team1player2item1, team1player2totalscore, ..., team2player1item1, team2player1totalscore, ...

Alexander Over a year ago

I belive you could just transpose the dataframe via df.T

Collectives™ on Stack Overflow

Extracting information from multiple JSON files to single CSV file in python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related