Pyton script export only haf the information from the json-CodePudding

I have python script but when is export the file print only "y", "meas", "label" whitout any data. I have no errors when run the scrpt. Can you advise to fix that? I cant post the link and the json.

import requests
from pandas.io.json import json_normalize
import json
import pandas as pd
import numpy as np

# try this

total_df = pd.DataFrame()
request_list = ["LInk",]

# read each link
for r in request_list:
    resp = requests.get(url=r)
    df = json_normalize(resp.json())

    item_list = df['dataset']
    current_df = pd.DataFrame()
    
    

    for i in item_list:
        try:
            current_df.loc[0,'y'] = i['data'][0]['y']
        except:
            current_df.loc[0,'y'] = np.nan
        try:
            current_df.loc[0,'meas'] = i['meas']
        except:
            current_df.loc[0,'meas'] = np.nan
        try:
            current_df.loc[0,'label'] = i['label']
        except:
            current_df.loc[0,'label'] = np.nan

    total_df = pd.concat([total_df,current_df])


total_df.to_excel('C:/Users/svetl/Onedrive/Desktop/work/output.xlsx',index=False)

input json

{
    "dataset": [
        {
            "data": [
                {
                    "y": 36.5,
                    "t": 1632429000000
                }
            ],
            "meas": "\u00b0C",
            "label": "t_test",
            "fill": false,
            "pointRadius": 0,
            "borderWidth": 1.5,
            "backgroundColor": "#8000804f",
            "borderColor": "#8000807f",
            "label2": "(Avg: 36.50 \u00b0C, Min: 36.50 \u00b0C, Max: 36.50 \u00b0C, Start: 36.50 \u00b0C, End: 36.50 \u00b0C, Diff: 0.00 \u00b0C)"
        }
    ],
    "additionalInfo": {
        "zoom": {
            "xmin": 1632429000000,
            "xmax": 1632429000000,
            "ymin": 32,
            "ymax": 44
        }
    }
}

CodePudding user response：

so the issue is with the way you handle the JSON. The key dataset has a list of dict items as value. You try to access the dict item directly while ignoring they are part of a list.

Look at the edited part below, and notice the change from
i['data'][0]['y']
to
i[0]['data'][0]['y']
(and the other changes when accessing i)

    for i in item_list:
        try:
            current_df.loc[0,'y'] = i[0]['data'][0]['y']
        except Exception as e:
            print(e)
            current_df.loc[0,'y'] = np.nan
        try:
            current_df.loc[0,'meas'] = i[0]['meas']
        except Exception as e:
            print(e)
            current_df.loc[0,'meas'] = np.nan
        try:
            current_df.loc[0,'label'] = i[0]['label']
        except Exception as e:
            print(e)
            current_df.loc[0,'label'] = np.nan

Sidenote:

this is deprecated:

from pandas.io.json import json_normalize

you need to change it to:

from pandas import json_normalize

CodePudding user response：

It's working but if you have many data blocks now printing only first one and many links to put in the next rows.