python列表嵌套字典转dataframe,将字典的JSON嵌套列表转换为DataFrame

How can I convert the following list of dicts (json output) to a pandas DataFrame. I tried

res = {}

for d in list_of_dict:

res.update(d)

It gives me the error:

ValueError: dictionary update sequence element #0 has length 33; 2 is required

Example JSON output, needed converted to DataFrame.

{

"PlanCoverages": [

{

"PlanId": 65860,

"FormularyId": 61855,

"PlanName": "CVS Caremark Performance Standard Control w/Advanced Specialty Control",

"PlanTypeId": 15,

"ChannelId": 1,

"ProductId": 237171,

"MonthId": 202002,

"ControllerId": 884,

"NoteId": null,

"Lives": 3814196,

"DrugListTierId": 2,

"DrugListTierName": "Not reimbursed",

"FormularyTierId": 26,

"FormularyUnifiedTierId": 13,

"UnifiedTierId": 11,

"UnifiedTierName": "Not Covered",

"UnifiedTierShortName": "Not Covered",

"UnifiedTierSort": 11,

"PromotionalTierId": null,

"IsGeneric": false,

"PriorAuthorization": false,

"OtherNote": false,

"StepTherapy": false,

"QuantityLimit": false,

"Variance": false,

"Restrictions": "",

"CoveredAlternatives": 88,

"RecommendedAlternatives": 0,

"SpecialtyPharmacy": false,

"ConditionalPriorAuthorization": false,

"DurableMedicalEquipment": false,

"MedicalBenefit": false,

"OverTheCounter": false

},

{

"PlanId": 69549,

"FormularyId": 63811,

"PlanName": "CVS Caremark Performance Standard Opt-Out w/ Advanced Specialty Control ",

"PlanTypeId": 15,

"ChannelId": 1,

"ProductId": 237171,

"MonthId": 202002,

"ControllerId": 884,

"NoteId": null,

"Lives": 1460242,

"DrugListTierId": 2,

"DrugListTierName": "Not reimbursed",

"FormularyTierId": 26,

"FormularyUnifiedTierId": 13,

"UnifiedTierId": 11,

"UnifiedTierName": "Not Covered",

"UnifiedTierShortName": "Not Covered",

"UnifiedTierSort": 11,

"PromotionalTierId": null,

"IsGeneric": false,

"PriorAuthorization": false,

"OtherNote": false,

"StepTherapy": false,

"QuantityLimit": false,

"Variance": false,

"Restrictions": "",

"CoveredAlternatives": 121,

"RecommendedAlternatives": 0,

"SpecialtyPharmacy": false,

"ConditionalPriorAuthorization": false,

"DurableMedicalEquipment": false,

"MedicalBenefit": false,

"OverTheCounter": false

} ]

}

Here' my full code. It connects to an API, and scraped information on pharmaceuticals.

I need the PlanCoverages of 1330 plans.

import requests

import pandas as pd

from pandas.io.json import json_normalize

import json

headers = {

'Accept': '*/*',

'X-Requested-With': 'XMLHttpRequest',

'Access-Token': 'H-oa4ULGls2Cpu8U6hX4myixRoFIPxfj',

'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.87 Safari/537.36',

'Is-Session-Expired': 'false',

'Referer': 'https://formularylookup.com/',

}

response = requests.get('https://formularylookup.com/Formulary/Coverage/Controller?ProductId=237171&ProductName=Rybelsus&ControllerId=884&ChannelId=1&StateId=all&DrugTypeId=3&Options=PlanCoverages', headers=headers)

df = response.json()

df_normal = json_normalize(df)["PlanCoverages"]#["ControllerCoverages"]

#dff = pd.DataFrame(df_normal)

#dff = json.dumps(df, indent=4, sort_keys=False)

res = {}

for d in df_normal:

res.update(d)

print(res)

Ideal output is, 1 row per plan. So a total of 1330 rows.

解决方案

Something like this will work:

I have assumed your json object is one large string named 'data'.

import pandas as pd

import json

# json object:

json_string = """ { "PlanCoverages": [ { "PlanId": 65860, ... """

# 1) load json object as python variable:

data = json.loads(json_string)

# 2) convert to dataframe:

plan_coverages = pd.DataFrame(data['PlanCoverages'])