将带有嵌套对象的 JSON 转换为 Pandas Dataframe [英] Convert JSON with nested objects to Pandas Dataframe

问题描述

我正在尝试从 url 加载 json 并转换为 Pandas 数据帧，以便数据帧看起来像下面的示例.

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.

我试过 json_normalize，但它复制了列，每个数据类型(值和字符串值)一个.有没有比这种方法更简单的方法，然后在创建数据框后删除和重命名列?我想保留 stringValue.

I've tried json_normalize, but it duplicates the columns, one for each data type (value and stringValue). Is there a simpler way than this method and then dropping and renaming columns after creating the dataframe? I want to keep the stringValue.

    Person ID   Position ID     Job ID  Manager
0   192         936             93      Tom



my_json = {

    "columns": [
        {
            "alias": "c3",
            "label": "Person ID",
            "dataType": "integer"
        },
        {
            "alias": "c36",
            "label": "Position ID",
            "dataType": "string"
        },
        {
            "alias": "c40",
            "label": "Job ID",
            "dataType": "integer",
            "entityType": "job"
        },
        {
            "alias": "c19",
            "label": "Manager",
            "dataType": "integer"
        },
     ],
    "data": [
        {
            "c3": {
                "value": 192,
                "stringValue": "192"
            },
            "c36": {
                "value": "936",
                "stringValue": "936"
            },
            "c40": {
                "value": 93,
                "stringValue": "93"
            },
            "c19": {
                "value": 12412453,
                "stringValue": "Tom"
            }
        }
    ]
}

推荐答案

如果 c19 是字符串类型，这应该可以工作

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)

这篇关于将带有嵌套对象的 JSON 转换为 Pandas Dataframe的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！