Convert Json With Nested Objects To Pandas Dataframe

I am trying to load json from a url and convert to a Pandas dataframe, so that the dataframe would look like the sample below. I've tried json_normalize, but it duplicates the colu

Solution 1:

If c19 is of type string, this should work

alias_to_label = {x['alias']: x['label'] for x in my_json["columns"]}
is_str = {x['alias']: ('string' == x['dataType']) for x in my_json["columns"]}

data = []
for x in my_json["data"]:
    data.append({
        k: v["stringValue" if is_str[k] else 'value']
        for k, v in x.items()
    })
df = pd.DataFrame(data).rename(columns=alias_to_label)