python pandas标准化嵌套的json

我有数据

[{"state": "Florida",
         "shortname": "FL",
         "info": {"governor": "Rick Scott"},
         "counties": [{"name": "Dade",
                       "population": 12345,
                       "Attributes": [
                         {
                          "capture_date": "2020-01-29",
                          "Spirit_code": "TRLQR",
                          "value": 1
                         },
                         {
                            "capture_date": "2020-01-29",
                            "Spirit_code": "HAVPN",
                            "value": 57000
                        }

                       ]},
                       {"name": "Broward",
                        "population": 40000,
                         "Attributes": [
                         {
                            "capture_date": "2020-01-29",
                            "Spirit_code": "GMSTP",
                            "value": 14
                        },
                        {
                            "capture_date": "2020-01-29",
                            "Spirit_code": "GWTPN",
                            "value": 11212
                        }
                       ]
                       },
                       {"name": "Palm Beach",
                        "population": 60000,
                        "Attributes": [{
                            "capture_date": "2020-01-29",
                            "Spirit_code": "YGHMN",
                            "value": 154.01
                        },
                        {
                            "capture_date": "2020-01-29",
                            "Spirit_code": "CXZASD",
                            "value": 154.01
                        }]
                       }
         ]},
        {"state": "Ohio",
         "shortname": "OH",
         "info": {"governor": "John Kasich"},
         "counties": [{"name": "Summit", "population": 1234,
                      "Attributes": [{
                            "capture_date": "2020-01-29",
                            "Spirit_code": "QWERTY",
                            "value": 154.01
                        },
                        {
                            "capture_date": "2020-01-29",
                            "Spirit_code": "JKLGH",
                            "value": 154.01
                        }]
         },
                      {"name": "Cuyahoga", "population": 1337,
                      "Attributes": [{
                            "capture_date": "2020-01-29",
                            "Spirit_code": "ASDF",
                            "value": 154.01
                        },
                        {
                            "capture_date": "2020-01-29",
                            "Spirit_code": "POIUY",
                            "value": 154.01
                        }]

                      }],
        }
]

我正在寻找结果:

state,   shortname, name,       population, attirbute.capture_date, attirbute.spirit_code, attirbute.value
florida, FL        ,Dade,       12345     , 2020-0-29             , TRLQR                , 1
florida, FL        ,Dade,       12345     , 2020-0-29             , HAVPN                , 57000
florida, FL        ,Broward,    40000     , 2020-0-29             , GMSTP                , 14
florida, FL        ,Broward,    40000     , 2020-0-29             , GWTPN                , 11212
florida, FL        ,Palm Beach, 60000     , 2020-0-29             , YGHMN                , 154.01
florida, FL        ,Palm Beach, 60000     , 2020-0-29             , YGHMN                , 154.01
florida, FL        ,Palm Beach, 60000     , 2020-0-29             , CXZASD                , 154.01

基本上标准化嵌套json中的键属性。关键字:“属性”。

json_normalize(data["data"], ["counties", "Attributes"], ["state", "shortname", ["counties", "name"], ["counties", "population"]])

我收到错误消息:

TypeError: {'name': 'Dade', 'population': 12345, 'Attributes': [{'capture_date': '2020-01-29', 'Spirit_code': 'TRLQR', 'value': 1}, {'capture_date': '2020-01-29', 'Spirit_code': 'HAVPN', 'value': 57000}]} has non iterable value 12345 for path ['population']. Must be iterable or null.

但是如果我跑:

plots_in = json_normalize(data["data"], ["counties", "Attributes"],
                   ["state", "shortname", ["counties", "name"]])

我得到结果:

  capture_date Spirit_code     value    state shortname counties.name
0   2020-01-29       TRLQR      1.00  Florida        FL          Dade
1   2020-01-29       HAVPN  57000.00  Florida        FL          Dade
2   2020-01-29       GMSTP     14.00  Florida        FL       Broward
3   2020-01-29       GWTPN  11212.00  Florida        FL       Broward
4   2020-01-29       YGHMN    154.01  Florida        FL    Palm Beach
5   2020-01-29      CXZASD    154.01  Florida        FL    Palm Beach
6   2020-01-29      QWERTY    154.01     Ohio        OH        Summit
7   2020-01-29       JKLGH    154.01     Ohio        OH        Summit
8   2020-01-29        ASDF    154.01     Ohio        OH      Cuyahoga
9   2020-01-29       POIUY    154.01     Ohio        OH      Cuyahoga

与填充密钥中的整数有关吗? 因为如果我运行以下命令,那么仍然会出现相同的错误:

plots_in = json_normalize(data["data"], ["counties", "population"])

请解释一下,如果有人知道下面发生了什么?

评论