CODEWITHSUNDEEP
pythonjsonpython-2.7pandasdataframe
Anagha
Anagha

Reputation: 3699

How to convert JSON to a Dataframe in python

I have the below JSON format, I need to convert this to a dataframe in python. Please let me know, how to go about it.

JSON :

 User Patterns
[{"Jane": [{"Thermostat": 20, "Days": [1, 2], "Hour": 6, "Minute": 43}],  
"John": [{"Thermostat": 18, "Days": [1, 2], "Hour": 0, "Minute": 15}], 
"Jen": [{"Thermostat": 22, "Days": [1, 2], "Hour": 10, "Minute": 1}]}]

I want my dataframe to look like :

User    Thermostat   Days     Hour   Minute
Jane    20           [1,2]    6      43  
John    18           [1,2]    0      15 
Jen     22           [1,2]    10     1

Upvotes: 1

Views: 2490

Answers (1)

piRSquared
piRSquared

Reputation: 294516

jstr = """[{"Jane": [{"Thermostat": 20, "Days": [1, 2], "Hour": 6, "Minute": 43}],  
"John": [{"Thermostat": 18, "Days": [1, 2], "Hour": 0, "Minute": 15}], 
"Jen": [{"Thermostat": 22, "Days": [1, 2], "Hour": 10, "Minute": 1}]}]"""

pd.DataFrame.from_dict(
    {k: v[0] for k, v in json.loads(jstr)[0].items()}, 'index'
).rename_axis('User').reset_index()

enter image description here

If you want to split out the Days column

df = pd.DataFrame.from_dict(
    {k: v[0] for k, v in json.loads(jstr)[0].items()}, 'index'
).rename_axis('User').reset_index()

df.drop('Days', 1).join(
    pd.DataFrame(df.Days.tolist()).rename(columns='Day{}'.format))

enter image description here

You can functionalize this:

def read_my_json(jstr):
    return pd.DataFrame.from_dict(
        {k: v[0] for k, v in json.loads(jstr)[0].items()}, 'index'
    ).rename_axis('User').reset_index()

Upvotes: 4

Related Questions