JSON list items to dataframe

Question

I am using an API that has changed its spec and now my JSON feed is slightly different.

BEFORE:

{"code":2000,"message":"SUCCESS","data":
    {"1":
        {"id":1,
        "name":"Amanda",
        "score":"57.36%",
        "average":"53.47%"
        }
     }
}

Then, I used something to this effect:

import json
import pandas as pd

jsonfile = 'file.json'
with open(jsonfile) as j:
  data = json.load(j)
  rows = [v for k, v in data["data"].items()]

  df = pd.DataFrame(rows, columns=['id', 'name', 'score', 'average'])

Source AFTER:

{"status":"success","code":0,"data":
    {"data":
        [
            {
            "id":1,
            "name":"Robert",
            "score":"48.85%",
            "average":"40.52%"
             }
         ]
     }
}

So I'm attempting to adjust using some of the resources:

What I've tried so far:

import json
import pandas as pd
from pandas import json_normalize

jsonfile = 'file.json'
with open(jsonfile) as j:
  data = json.load(j)

  df = json_normalize(data, ['data'])

I've also tried: df = pd.DataFrame.from_records(data)

I get the following:

TypeError: {....} for path data. Must be list or null.

What am I missing here?

Correction: When using this method: https://stackoverflow.com/questions/48687857/python-json-list-to-pandas-dataframe I get the following output: ``` Dataframe: code data status 0 0 [{'id': 1, 'name': 'Robert', 'score': '48.92%', 'a... success 1 0 success ``` — p3hndrx, Nov 16 '21 at 16:30
`json_normalize(data, ['data', 'data'])`. the `data` is wrapped in `data`, so your record_path should require 1 more 'data'. — Emma, Nov 16 '21 at 16:40

JSON list items to dataframe

0 Answers0