How to create a sales table from JSON files using Python

Question

I have a JSON file like this and have got troubles generating a table with COLUMNS as Name, number, countrycode(first item in price), currency

{"a": [{"Name": "name1",
  "number": "number1",
  "defaultPrice": {"p": "232", "currency": "CAD"},
  "prices": {"DZ": {"p": "62", "currency": "RMB"},
   "AU": {"p": "73", "currency": "AUD"},
  "lg": "en"}},
 {"Name": "name2",
  "number": "number2",
  "defaultPrice": {"p": "233", "currency": "CAD"},
  "prices": {"DZ": {"p": "63", "currency": "RMB"},
  "US": {"p": "72", "currency": "USD"},
  "Lg": "en"}}]}

The problem is that I get traceback just at the parsing:

Traceback (most recent call last):
  File "test.py", line 49, in <module>
    val = ast.literal_eval(mystr)
  File "/anaconda3/lib/python3.7/ast.py", line 46, in literal_eval
    node_or_string = parse(node_or_string, mode='eval')
  File "/anaconda3/lib/python3.7/ast.py", line 35, in parse
    return compile(source, filename, mode, PyCF_ONLY_AST)
  File "<unknown>", line 38
 SyntaxError: unexpected EOF while parsing

What I used was

mystr='''
....
'''
val = ast.literal_eval(mystr)
val1 = json.loads(json.dumps(val))
val2 = val1['a'][0]['Name']
print pd.DataFrame(val2, columns=["Name"])

Thanks for any help!!

Also, `json.loads(json.dumps())` seems a bit redundant as `ast` *should* resolve the data structure to `dict` — C.Nivs, Mar 18 '19 at 17:40
I was able to get this to run just copying the file into `mystr = ''''''` then running `val = json.loads(mystr)` — C.Nivs, Mar 18 '19 at 17:41

C.Nivs · Accepted Answer · 2019-03-18T18:00:57.607

0

To get a file loaded into a dictionary using the json module, json.load will take a file handle:

import json

with open('yourfile.json') as fh:
    val = json.load(fh)

type(val)
dict

# to get the structure you're looking for
val1 = val.get('a')

type(val1)
list
# Now you can iterate over it, or throw it into pandas if you want a
# table-like data structure

val1[0].keys()
dict_keys(['Name', 'number', 'defaultPrice', 'prices'])

Using your string approach

You can do this with the string approach you have in your original post as well:

import json

with open('yourfile.json') as fh:
    mystr = fh.read()

# Note, this is json.loads, which takes a string arg not a file handle
val = json.loads(mystr)

val
{'a': [{'Name': 'name1', 'number': 'number1', 'defaultPrice': {'p': '232', 'currency': 'CAD'}, 'prices': {'DZ': {'p': '62', 'currency': 'RMB'}, 'AU': {'p': '73', 'currency': 'AUD'}, 'lg': 'en'}}, {'Name': 'name2', 'number': 'number2', 'defaultPrice': {'p': '233', 'currency': 'CAD'}, 'prices': {'DZ': {'p': '63', 'currency': 'RMB'}, 'US': {'p': '72', 'currency': 'USD'}, 'Lg': 'en'}}]}

type(val)
dict


# To put this into pandas

import pandas as pd

val1 = val.get('a')

df = pd.DataFrame(val1)

Where df looks like:

    Name                        ...                                                                     prices
0  name1                        ...                          {'DZ': {'p': '62', 'currency': 'RMB'}, 'AU': {...
1  name2                        ...                          {'DZ': {'p': '63', 'currency': 'RMB'}, 'US': {...

[2 rows x 4 columns]

Pandas will impute the column names as the keys in the list of dictionaries

edited Mar 18 '19 at 18:00

answered Mar 18 '19 at 17:46

C.Nivs

12,353
2
19
44

Thanks a lot!! It worked! Really helped me. Could you pls also give an example of throwing it into pandas for a table-like data structure? – milk bear Mar 18 '19 at 17:57
@milkbear see newest edit, the same method will work for both approaches of loading the data – C.Nivs Mar 18 '19 at 18:01
Thanks!!!! Almost there. I just got one last question. I use this code and generated name and number successfully, but got error in currency. Name,number, currency = [],[],[] for result in val1: Name.append(result['packageName']) number.append(result['sku']) currency.append(result['prices']['currency']) df = pd.DataFrame(Name, number,currency) – milk bear Mar 18 '19 at 19:05
`DataFrame` expects a `list` of `dict` by default. To do what you want, you'll need a `pd.Series` and you can add those to an existing frame, I'll make a final edit – C.Nivs Mar 18 '19 at 19:09
looking at your data, [this](https://stackoverflow.com/questions/30522724/take-multiple-lists-into-dataframe) question might be a good bit of help – C.Nivs Mar 18 '19 at 19:21
@milkbear I think the answer in your linked question answers your issue with data formatting – C.Nivs Mar 18 '19 at 20:45

How to create a sales table from JSON files using Python

1 Answers1

Using your string approach