CODEWITHSUNDEEP
python
Gee
Gee

Reputation: 21

Nested JSON file flattened and stored as CSV

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)

Upvotes: 1

Views: 5427

Answers (1)

Stael
Stael

Reputation: 2689

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)

Upvotes: 2

Related Questions