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? — user5386938
– user5386938, Commented May 27, 2021 at 18:06
i tried the list[0] but doesnt work since the result is a type string. — StackUser
– StackUser, Commented May 27, 2021 at 18:11
Convert your string to a python object using json.loads and then load it into df stackoverflow.com/a/4917044/5125264 — Matt
– Matt, Commented May 27, 2021 at 18:17
i tried that, thats when i get the output which is a list of string. — StackUser
– StackUser, Commented May 27, 2021 at 18:31

wwii · Accepted Answer · 2021-05-27 18:43:43Z

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.

Ryan M · Accepted Answer · 2022-05-19 17:15:19Z

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, 2022 at 17:15

Ryan M♦

20.6k35 gold badges75 silver badges85 bronze badges

answered May 27, 2021 at 18:44

Ayushi Ranjan

214 bronze badges

Collectives™ on Stack Overflow

Load json file with string into pandas dataframe in python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related