在將JSON轉換為DF時，將JSON部分編號作為列包含在df中

我有一個嵌套的 JSON，如下所示：

{
"group": {
    "groupname": "grp1",
    "groupid": 1,
    "city": "London"
},
"persons": {
    "0": {
        "name": "john",
        "age": 12,
        "gender": "M",
        "groupid": 1
    },
    "1": {
        "name": "maat",
        "age": 15,
        "gender": "M",
        "groupid": 1
    },
    "2": {
        "name": "chrissle",
        "age": 10,
        "gender": "F",
        "groupid": 1
    },
    "3": {
        "name": "stacy",
        "age": 11,
        "gender": "F",
        "groupid": 1
    },
    "4": {
        "name": "mark",
        "age": 12,
        "gender": "M",
        "groupid": 1
    },
    "5": {
        "name": "job",
        "age": 12,
        "gender": "M",
        "groupid": 1
    }
},
"group": {
    "groupname": "grp1",
    "groupid": 2,
    "city": "NewYork"
},
"persons": {
    "0": {
        "name": "will",
        "age": 12,
        "gender": "M",
        "groupid": 2
    },
    "1": {
        "name": "phil",
        "age": 15,
        "gender": "M",
        "groupid": 2
    },
    "2": {
        "name": "winnie",
        "age": 10,
        "gender": "F",
        "groupid": 2
    }
}
}

我想將這兩個部分group分別persons分成兩個 df。

對于第二個 df persons，我想將節號包括為列，如下所示：

id    name    age    gender    groupid
0     john     12      M         1
1     maat     15      M         1
2     chrissle 10      F         1

我已將 JSON 加載為 dict 串列并將其轉換為 df：

data= pd.DataFrame.from_dict(data)

然后我可以得到persons

personsdf= personsdf['persons']

但是，這會給我一個 df，其中有一列包含每個人部分的 dict 行。

我在下面嘗試取消嵌套字典行：

finaldf= pd.DataFrame()
for index, row in personsdf.iterrows():
    row_data=row['personsdf']
    row_data = pd.DataFrame.from_dict(row_data)
    row_data = row_data.T
    finaldf= finaldf.append(row_data, ignore_index=True)

但是后來我得到了除了丟失的節號之外的所有列。有沒有更好的方法來解決這個問題？

uj5u.com熱心網友回復：

如果我理解正確，您想創建兩個資料框：一個用于組，第二個用于人員：

data = [
    {
        "group": {"groupname": "grp1", "groupid": 1, "city": "London"},
        "persons": {
            "0": {"name": "john", "age": 12, "gender": "M", "groupid": 1},
            "1": {"name": "maat", "age": 15, "gender": "M", "groupid": 1},
            "2": {"name": "chrissle", "age": 10, "gender": "F", "groupid": 1},
            "3": {"name": "stacy", "age": 11, "gender": "F", "groupid": 1},
            "4": {"name": "mark", "age": 12, "gender": "M", "groupid": 1},
            "5": {"name": "job", "age": 12, "gender": "M", "groupid": 1},
        },
    },
    {
        "group": {"groupname": "grp1", "groupid": 2, "city": "NewYork"},
        "persons": {
            "0": {"name": "will", "age": 12, "gender": "M", "groupid": 2},
            "1": {"name": "phil", "age": 15, "gender": "M", "groupid": 2},
            "2": {"name": "winnie", "age": 10, "gender": "F", "groupid": 2},
        },
    },
]

df1 = pd.DataFrame([d["group"] for d in data])
df2 = pd.DataFrame(
    [{"id": k, **v} for d in data for k, v in d["persons"].items()]
)
print(df1)
print(df2)

印刷：

  groupname  groupid     city
0      grp1        1   London
1      grp1        2  NewYork

  id      name  age gender  groupid
0  0      john   12      M        1
1  1      maat   15      M        1
2  2  chrissle   10      F        1
3  3     stacy   11      F        1
4  4      mark   12      M        1
5  5       job   12      M        1
6  0      will   12      M        2
7  1      phil   15      M        2
8  2    winnie   10      F        2

標籤：Python json 熊猫

上一篇：通過布爾索引陣列分配Numpy陣列

下一篇：將字串從資料庫轉換為資料框