JSON file is pulled to Pandas DataFrame in one line. Can't break it down

Question

I am struggling with the conversion of JSON to DataFrame. I get a JSON as a result of API request.

I tried pd.read_json, and pd.json_normalize and nothing helps. The result is always a DataFrame with (1,1) shape. I went through similar requests but looks like json_normalize helps everyone except me.

This is how my code looks today:

import requests
import json
import pandas as pd

response = requests.get("https://******************************/parameters/100135?from=1h",
                        auth = ('*******', '*******'), verify=False)
t = response.text

stud_obj = json.loads(t)
m = pd.json_normalize(stud_obj)

Here is an example of the JSON:

{
 "DataList" : [
  {
   "Parameter" : 100135,
   "Parameter Label" : "TC3; GR53; Irrigation Zones GR53; Row 2 Level 3 (6); Duration Of Waterings {",
   "DataSet" : [
    {
     "Data" : "0:00:00",
     "Time" : "Fri Jun 25 00:00:01 2021"
    },
    {
     "Data" : "0:00:01",
     "Time" : "Fri Jun 25 07:51:33 2021"
    },
    {
     "Data" : "0:00:02",
     "Time" : "Fri Jun 25 07:51:34 2021"
    },
    {
     "Data" : "0:00:03",
     "Time" : "Fri Jun 25 07:51:35 2021"
    }
   ]
  }
 ]
}

Here is a DataFrame as a result:

print(m)
                                                DataList
    0  [{'Parameter': 100135, 'Parameter Label': 'TC3...
m.shape
    (1, 1)

How you would like to see your desired DataFrame? by two distinct columns like `data` and `Time`. — Karn Kumar, Jun 25 '21 at 16:52
Please see if this [SO POST](https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe) helps. — Karn Kumar, Jun 25 '21 at 17:11

Anurag Dabas · Answer 1 · 2021-06-25T18:13:01.447

2

There may be efficient solution but untill you can use:

out=pd.DataFrame(pd.Series(data).explode().tolist()).explode('DataSet',ignore_index=True)
#Here data is your json

Finally:

out=out.join(pd.DataFrame(out.pop('DataSet').tolist()))

output of out:

    Parameter   Parameter Label                                 Data     Time
0   100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...   0:00:00     Fri Jun 25 00:00:01 2021
1   100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...   0:00:01     Fri Jun 25 07:51:33 2021
2   100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...   0:00:02     Fri Jun 25 07:51:34 2021
3   100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...   0:00:03     Fri Jun 25 07:51:35 2021

edited Jun 25 '21 at 18:13

answered Jun 25 '21 at 16:54

Anurag Dabas

23,866
9
21
41

@KarnKumar Thank you sir but it don't seem to be efficient – Anurag Dabas Jun 25 '21 at 17:20
1

thats okay, Solution may vary but it should be working before we go to efficiency :-) i think the link i provide that will be good to follow. – Karn Kumar Jun 25 '21 at 17:29
Oh! It replaced all values with the first one. That's bad. Anurag-Dabas , do you know how to solve this? Sorry, I badly understand on what step of a code it happens. – Oleg Kazanskyi Jun 25 '21 at 18:02
@OlegKazanskyi Fixed it...Kindly have a look ***:)*** – Anurag Dabas Jun 25 '21 at 18:03

score 2 · Answer 2 · answered Jun 25 '21 at 17:47

You can use pd.json_normalize() + pd.DataFrame(), as follows:

Using your loaded json stud_obj:

df_out = pd.json_normalize(stud_obj['DataList']).explode('DataSet').reset_index(drop=True)

df_out = pd.concat([df_out.drop('DataSet', axis=1), pd.DataFrame(df_out['DataSet'].to_list())], axis=1)

Result:

print(df_out)


   Parameter                                                               Parameter Label     Data                      Time
0     100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level 3 (6); Duration Of Waterings {  0:00:00  Fri Jun 25 00:00:01 2021
1     100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level 3 (6); Duration Of Waterings {  0:00:01  Fri Jun 25 07:51:33 2021
2     100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level 3 (6); Duration Of Waterings {  0:00:02  Fri Jun 25 07:51:34 2021
3     100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level 3 (6); Duration Of Waterings {  0:00:03  Fri Jun 25 07:51:35 2021

Thanks @SeaBean for noticing ***:)*** – Anurag Dabas Jun 25 '21 at 18:05 — Anurag Dabas, Jun 25 '21 at 18:05
@OlegKazanskyi Pleased to help! :-) – SeaBean Jun 25 '21 at 18:08 — SeaBean, Jun 25 '21 at 18:08

jch · Answer 3 · 2021-06-25T18:55:55.757

pd.json_normalize() can do the job by itself:

d = { ... your JSON object ... }
pd.json_normalize(d,['DataList','DataSet'],[['DataList','Parameter'],['DataList','Parameter Label']])

     Data             Time            DataList.Parameter     DataList.Parameter Label
0   0:00:00     Fri Jun 25 00:00:01 2021    100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...
1   0:00:01     Fri Jun 25 07:51:33 2021    100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...
2   0:00:02     Fri Jun 25 07:51:34 2021    100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...
3   0:00:03     Fri Jun 25 07:51:35 2021    100135  TC3; GR53; Irrigation Zones GR53; Row 2 Level ...

JSON file is pulled to Pandas DataFrame in one line. Can't break it down

3 Answers3