python - 将 json 对象转换为 python 中的数据框

Question

我有一个 json object我想转换成数据框。然而，随着

df = pd.read_json("http://ucdpapi.pcr.uu.se/api/gedevents/5.0?pagesize=100&Geography=47%202,49%203")["Result"]
df

我明白了:

0    {'id': 178312, 'relid': 'ISR-1992-1-377-561', ...
1    {'id': 210197, 'relid': 'CAO-2015-3-1076-210',...
2    {'id': 210203, 'relid': 'IRQ-2015-1-448-594', ...
3    {'id': 4233, 'relid': 'ALG-1995-3-1390-1', 'ye...
4    {'id': 76775, 'relid': 'SRI-1996-1-243-98', 'y...

我应该怎么做才能将其转换为逐列数据框，

id         relid                     year    activeyear   ....
178312     ISR-1992-1-377-561        1992    TRUE         ....
210197     CAO-2015-3-1076-210       1996    TRUE         ....

原始数据如下:

{
  "TotalCount": 5,
  "TotalPages": 1,
  "PreviousPageUrl": "",
  "NextPageUrl": "",
  "Result": [
    {
      "id": 178312,
      "relid": "ISR-1992-1-377-561",
      "year": 1992,
      "active_year": true,
      "code_status": "Clear",
      "type_of_violence": 1,
      "conflict_dset_id": "1-37",
      "conflict_new_id": 234,
      "conflict_name": "Israel:Palestine",
      "dyad_dset_id": "377",
      "dyad_new_id": 476,
      "dyad_name": "Government of Israel - Fatah",
      "side_a_dset_id": "666",
      "side_a_new_id": 121,
      "side_a": "Government of Israel",
      "side_b_dset_id": "1049",
      "side_b_new_id": 207,
          "side_b": "Fatah",
........

谢谢!!

更新:使用 urllib 和导入请求工作。另一种可行的方法是编写

0    {'id': 178312, 'relid': 'ISR-1992-1-377-561', ...
1    {'id': 210197, 'relid': 'CAO-2015-3-1076-210',...
2    {'id': 210203, 'relid': 'IRQ-2015-1-448-594', ...
3    {'id': 4233, 'relid': 'ALG-1995-3-1390-1', 'ye...
4    {'id': 76775, 'relid': 'SRI-1996-1-243-98', 'y...

用

变成一个 python 对象

json = df.to_json(orient='index')

然后将其作为 json 对象读出

pd.read_json(json, orient="index")

最终输出如下:enter image description here

Answer 1

这也可以做到:

import pandas as pd
import json
import urllib

response = urllib.urlopen("http://ucdpapi.pcr.uu.se/api/gedevents/5.0?pagesize=100&Geography=47%202,49%203")
data = json.loads(response.read())['Result']
df = pd.DataFrame(data)