Nested JSON file flattened and stored as CSV

Question

I'm trying to get a json file into csv format, here is a snippet of the json file(sample3.json) :

{
  "x" : {
    "-tst1" : {
      "da" : "8C",
      "d" : "df4",
      "h" : 0,
      "i" : 1,
      "s" : false,
      "t" : 1501394756245
    },
    "-tst2" : {
      "da" : "8C",
      "d" : "\\df&*",
      "h" : 0,
      "i" : 0,
      "s" : true,
      "t" : 1501394946296
    }
  }   
}

These are some solutions that I've tried but I'm unabe to get any of them working: Convert list into a pandas data frame DataFrame from list of list Convert Nested JSON to Excel using Python

How can I get a table like the one below that I can export to a csv?

Table

I've tried several different ways but I'm not getting anywhere...the furthest I've gotten is getting the values into a list.

It seems like it would be simple but I'm more of a sql guy not python.

I appreciate any help.

import json
import ast
import pandas as pd
from pprint import pprint
from pandas.io.json import json_normalize
import itertools
from openpyxl import load_workbook
import openpyxl
from collections import Counter


test = open('sample3.json').read()
data = json.loads(test)
vals = data['x']

for key in vals.keys():
    v = vals.values
    t = list(vals.values())
    #pd.DataFrame(t)
#print(type(t)) 
#print('Separator')
#print(type(v))  




df = pd.DataFrame.from_items(t)  #error:  Not enough values to unpack...expected 2, got 1.
print(df)

fyi, I know i'm not using all the libraries, I've just accumulated them from all the solutions I've tried. — Gee, Aug 02 '17 at 14:09
possible dupe: https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv — sniperd, Aug 02 '17 at 14:14
Possible duplicate of [How can I convert JSON to CSV?](https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv) — MarkusEgle, Aug 02 '17 at 17:56

Stael · Accepted Answer · 2017-08-03T08:44:25.093

2

I'm not sure what you want, but does this work for you?

import json
import pandas as pd

with open('sample3.json') as f: # this ensures opening and closing file
    a = json.loads(f.read())

data = a["x"]

df = pd.DataFrame(data)

print(df.transpose())

my output:

          d  da  h  i      s              t
-tst1   df4  8C  0  1  False  1501394756245
-tst2  df&*  8C  0  0   True  1501394946296

you can then do:

df.transpose().to_csv('myfilename.csv')

in response to your comment, you could do:

import json
import pandas as pd

a = """{"z" : { "y" : { "x" : { "-v" : { "d1" : "8C:F", "d2" : "8.0", "t" : 3, "x" : 45 }, "-u" : { "d1" : "8C", "d2" : "8.00", "t" : 5, "x" : 45 } } } }}"""

js = json.loads(a)

print pd.DataFrame.from_dict(js['z']['y']['x'], orient='index')

(the json you posted was missing a }, but I assume that was a copy/paste error)

edited Aug 03 '17 at 08:44

answered Aug 02 '17 at 14:24

Stael

2,619
15
19

Yes!! This is perfect. Exactly what I wanted...Will this scale to large datasets efficiently? – Gee Aug 02 '17 at 14:32
1

for certain values of 'large' and 'efficient', yes. :) – Stael Aug 02 '17 at 14:38
Could I bother you for a little more help? I have a second json file like this ` {"z" : { "y" : { "x" : { "-v" : { "d1" : "8C:F", "d2" : "8.0", "t" : 3, "x" : 45 }, "-u" : { "d1" : "8C", "d2" : "8.00", "t" : 5, "x" : 45 } } } } } ` Can the script you gave be modified for this file format? – Gee Aug 02 '17 at 21:23
@Gee you have to de-nest it - I'll add the code to my answer – Stael Aug 03 '17 at 08:43

Nested JSON file flattened and stored as CSV

1 Answers1

Linked