如何在Pandas中操作這個嵌套的Json資料？

我有多個從 REST API 中提取的嵌套 JSON 物件，如下所示：

{
"count": 2,
"value": [{
    "teamMember": {
        "id": "ID1",
        "displayName": "NAME1",
        "uniqueName": "UNIQUE1",
        "url": "URL1",
        "imageUrl": "ImgURL1"
    },
    "activities": [{
        "capacityPerDay": 3.5,
        "name": ""
    }],
    "daysOff": [{
        "start": "2021-11-15T00:00:00Z",
        "end": "2021-11-15T00:00:00Z"
    }, {
        "start": "2021-11-26T00:00:00Z",
        "end": "2021-11-26T00:00:00Z"
    }],
    "url": "URL1"
}, {
    "teamMember": {
        "id": "ID2",
        "displayName": "NAME2",
        "uniqueName": "UNIQUE2",
        "url": "URL2",
        "imageUrl": "IMAGEURL2"
    },
    "activities": [{
        "capacityPerDay": 2.9,
        "name": ""
    }],
    "daysOff": [{
        "start": "2021-11-12T00:00:00Z",
        "end": "2021-11-12T00:00:00Z"
    }, {
        "start": "2021-12-02T00:00:00Z",
        "end": "2021-12-02T00:00:00Z"
    }],
    "url": "URL2"
}]}

我想從這個物件中提取一些資訊，最終得到這樣的結果： 這里

我怎么能在熊貓中做到這一點？任何提示將不勝感激！

uj5u.com熱心網友回復：

你有一個帶有陣列的常規 JSON。沒有嵌套的 JSON。您可以以常規方式訪問陣列的內容。

json_string = '''
...    <-- put your JSON here or get the JSON string in a different way
'''
import json
json_object = json.loads(json_string)
print(json_object["value"][0])
print(json_object["value"][1])

uj5u.com熱心網友回復：

如果您因此需要一張平桌

|---|----------------|-------|------|-----|-------|---------|------|-----------|
| 0 | capacityPerDay | start | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | capacityPerDay | end   | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | name           | start | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | name           | end   | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | capacityPerDay | start | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | capacityPerDay | end   | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | name           | start | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 0 | name           | end   | URL1 | ID1 | NAME1 | UNIQUE1 | URL1 | ImgURL1   |
| 1 | capacityPerDay | nan   | nan  | ID2 | NAME2 | UNIQUE2 | URL2 | IMAGEURL2 |
| 1 | name           | nan   | nan  | ID2 | NAME2 | UNIQUE2 | URL2 | IMAGEURL2 |

您可能只使用這樣pd.json_normalize()的幾個explode()：

import pandas as pd

#your_json = {smth}
pd.json_normalize(your_json['value']).explode('activities').explode('daysOff').explode('activities').explode('daysOff')

當然，有很多方法可以寫得更清楚，但至少現在你知道該往哪個方向看。

uj5u.com熱心網友回復：

由于字典可能包含超過 1 個 json 值，我有限的知識表明我們可能必須使用回圈，如果其他人有更有效的方法，請分享并啟發我們，但這里是轉換這個原始的作業版本json 轉換成你需要的資訊。

import json

json_string={
"count": 2,
"value": [{
    "teamMember": {
        "id": "ID1",
        "displayName": "NAME1",
        "uniqueName": "UNIQUE1",
        "url": "URL1",
        "imageUrl": "ImgURL1"
    },
    "activities": [{
        "capacityPerDay": 3.5,
        "name": ""
    }],
    "daysOff": [{
        "start": "2021-11-15T00:00:00Z",
        "end": "2021-11-15T00:00:00Z"
    }, {
        "start": "2021-11-26T00:00:00Z",
        "end": "2021-11-26T00:00:00Z"
    }],
    "url": "URL1"
}, {
    "teamMember": {
        "id": "ID2",
        "displayName": "NAME2",
        "uniqueName": "UNIQUE2",
        "url": "URL2",
        "imageUrl": "IMAGEURL2"
    },
    "activities": [{
        "capacityPerDay": 2.9,
        "name": ""
    }],
    "daysOff": [{
        "start": "2021-11-12T00:00:00Z",
        "end": "2021-11-12T00:00:00Z"
    }, {
        "start": "2021-12-02T00:00:00Z",
        "end": "2021-12-02T00:00:00Z"
    }],
    "url": "URL2"
}]}

json_data=[]
count=json_string['count']
for i in range(len(json_string["value"])):
#    print(json_string['value'][i])
    display_name=json_string['value'][i]['teamMember']['displayName']
    activity_capacity=json_string['value'][i]['activities'][0]['capacityPerDay']
    days_start=json_string['value'][i]['daysOff'][0]['start']
    day_end=json_string['value'][i]['daysOff'][0]['end']
    url=json_string['value'][i]['url']
    json_data.append([count,display_name,activity_capacity,days_start,day_end,url])

print(json_data)

標籤：Python json 熊猫

上一篇：決議JSON的Swift問題

下一篇：從JSON回應中讀取時如何轉義“@”？