Load json file with string into pandas dataframe in python

Question

I have an example json dataset as :

['[{"id":"123","product":"dell","date":"2019-01-01","sales":5,"created_at":"2019-01-26 15:00:00"}, {"id":"124","product":"apple","date":"2019-01-02","sales":7,"created_at":"2019-01-27 15:00:00"}]']

I would like to create a pandas dataframe from this json data but when i use the json_normalize method i get AttributeError: 'str' object has no attribute 'values'.

The expected output should be like this:

id   product  date        sales   created_at
123.  dell.   2019-01-01.  5.     2019-01-26 15:00:00
124.  apple.  2019-01-02.  7.     2019-01-27 15:00:00

"... an example json dataset as ..." - well, just how did you get that dataset? Perhaps you just loaded it in incorrectly? — , May 27 '21 at 18:06
i tried the list[0] but doesnt work since the result is a type string. — StackUser, May 27 '21 at 18:11
Convert your string to a python object using json.loads and then load it into df https://stackoverflow.com/a/4917044/5125264 — Matt, May 27 '21 at 18:17
i tried that, thats when i get the output which is a list of string. — StackUser, May 27 '21 at 18:31
`which is a list of string` - do it again `json.loads(list_of_string[0]`. — wwii, May 27 '21 at 18:37

score 2 · Accepted Answer · answered May 27 '21 at 18:43

thats when i get the output which is a list of string

If the result of json.load... is like

a = ['[{"id":"123","product":"dell","date":"2019-01-01","sales":5,"created_at":"2019-01-26 15:00:00"}, {"id":"124","product":"apple","date":"2019-01-02","sales":7,"created_at":"2019-01-27 15:00:00"}]']

Then do it again...

>>> b = json.loads(a[0])
>>> b
[{'id': '123', 'product': 'dell', 'date': '2019-01-01', 'sales': 5, 'created_at': '2019-01-26 15:00:00'}, {'id': '124', 'product': 'apple', 'date': '2019-01-02', 'sales': 7, 'created_at': '2019-01-27 15:00:00'}]
>>> pd.DataFrame(b)
    id product        date  sales           created_at
0  123    dell  2019-01-01      5  2019-01-26 15:00:00
1  124   apple  2019-01-02      7  2019-01-27 15:00:00
>>>

Unfortunately you will not have the luxury of knowing in advance that you need to do this. You would need to inspect the data first. Unless you are lucky enough to have the specifications of the thing that is making these jsons.

score 0 · Answer 2 · edited May 19 '22 at 17:15

0

import json
import pandas as pd
x = ['[{"id":"123","product":"dell","date":"2019-01-01","sales":5,"created_at":"2019-01-26 15:00:00"}, {"id":"124","product":"apple","date":"2019-01-02","sales":7,"created_at":"2019-01-27 15:00:00"}]']
# Take 0th element of list
x = x[0]
info = json.loads(x)
df = pd.json_normalize(info)
df

edited May 19 '22 at 17:15

Ryan M

18,333
31
67
74

answered May 27 '21 at 18:44

Ayushi Ranjan

21
4

Load json file with string into pandas dataframe in python

2 Answers2