Python converting nested JSON to CSV

Question

I'm trying to convert a JSON file to CSV as structured below:

I'm using the json and csv modules and my current solution reads the json file, spits out the unique headers (a, b, c, d, ...) Matches a slot to a header But does not write the csv yet.

My main issue is trying to convert something like: inventory,a,b,... slot1 a,b,... to 0 and 1 values if the entity contains it

There's also a lot of for loops involved and I'm not sure that is best practice: Nested for loop for getting all the headers, Looping through the json file, Writing csv file

tl;dr problem is: translate list of "inventory items" from JSON per slot, to 0s and 1s in a CSV I'd also like it to work for growing inventories (maybe e,f,g,h,...) more than 4 slots, etc So I'm trying to avoid any hard-coded rows and columns

First time using stackoverflow, thank you!

equip_inventory.json

{
    "slot1": ["a", "b"],
    "slot2": ["c", "d"],
    "slot3": ["a", "b", "c"]
    "slot4": ["d"]
}

Intended output CSV

inventory,a,b,c,d, ...
slot1,1,1,0,0,...
slot2,0,0,1,1,...
slot3,1,1,1,0,...
slot4,0,0,0,1,..

jpp · Accepted Answer · 2018-05-17 15:23:16Z

1

If you are comfortable using a 3rd party library, can use pandas.get_dummies for this:

import pandas as pd

d = {"slot1": ["a", "b"],
     "slot2": ["c", "d"],
     "slot3": ["a", "b", "c"],
     "slot4": ["d"]}

df = pd.DataFrame([[d[i]] for i in d], index=d.keys())

dummies = pd.get_dummies(df[0].apply(pd.Series).stack()).sum(level=0)

df = df.join(dummies)\
       .drop(0, axis=1)\
       .rename_axis('inventory')\
       .reset_index()

df.to_csv('file.csv', index=False)

Result:

print(df)

  inventory  a  b  c  d
0     slot1  1  1  0  0
1     slot2  0  0  1  1
2     slot3  1  1  1  0
3     slot4  0  0  0  1

answered May 17, 2018 at 15:23

jpp

166k37 gold badges301 silver badges362 bronze badges

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

Comments

Praveen Kumar Myakala · Accepted Answer · 2018-05-17 15:32:14Z

1

We can do like this without pandas

import json
import csv

with open('output.csv', 'w') as csvfile, open('sample.json') as jsonfile:
    data = json.load(jsonfile)
    for key, value in data.items():
            writer = csv.writer(csvfile, delimiter=',')
            writer.writerow([
            key,
            1 if 'a' in value else 0,
            1 if 'b' in value else 0,
            1 if 'c' in value else 0,
            1 if 'd' in value else 0]
            )

answered May 17, 2018 at 15:32

Praveen Kumar Myakala

1335 bronze badges

2 Comments

n01 Over a year ago

What happens if there are e, f, g,... etc, I would have to manually add each value correct?

qwr Over a year ago

use a for loop and append to list

Collectives™ on Stack Overflow

Python converting nested JSON to CSV

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related