使用Python从JSON嵌套列表和字符串数组中提取值

Question

我正试图从JSON数据集中提取马萨诸塞州波士顿多个街区的坐标，但我一直在努力只获取每个城市的第一对坐标；下面是Roslindale坐标的小版本。

"features": [{
    "type": "Feature",
    "properties": {
      "Name": "Roslindale",
      "Acres": 1605.5682375,
      "SqMiles": 2.51,
    },
    "geometry": {
      "type": "MultiPolygon",
      "coordinates": [
        [
          [
            [
              -71.125927174853857,
              42.272013107957406
            ],
            [
              -71.125927174853857,
              42.272013107957406
            ]
          ]
        ],
        [
          [
            [
              -71.125830766767592,
              42.272212845889705
            ],
            [
              -71.125830766767592,
              42.272212845889705
            ]
          ]
        ],
        [
          [
            [
              -71.125767203228904,
              42.272315958536389
            ],
            [
              -71.125767203228904,
              42.272315958536389
            ]
          ]
        ]
      ]
    }
  },

现在，我已经使用

for data in boston_neighborhoods:
    neighborhood_name = data['properties']['Name']
    neighborhood_id = data['properties']['Neighborhood_ID']
    neighborhood_size = data['properties']['SqMiles']
    neighborhood_latlon = data['geometry']['coordinates']
    neighborhood_lat = neighborhood_latlon
    neighborhood_lon = neighborhood_latlon

    neighborhoods = neighborhoods.append({'Neighborhood': neighborhood_name,
                                          'Neighborhood_ID': neighborhood_id,
                                          'SqMiles': neighborhood_size,
                                          'Latitude': neighborhood_lat,
                                          'Longitude': neighborhood_lon}, ignore_index=True)

返回多个坐标对，但我只需要第一对，下面是我现在返回的示例输出：

Latitude                   |           Longitude     
--------------------------------------------------------
[[[[-71.12592717485386,    |    [[[[-71.12592717485386, 
42.272013107957406], [...  |    42.272013107957406], [...    

Answer 1

这可能有些过分，但是^{}使查询像这样的嵌套JSON结构变得非常容易

遍历文档时，首先需要获取数组（[*]）中的每个元素，然后为每个元素选择一个对象（Python字典）中的项。您将选择properties下的邻域，然后选择Name（properties.Name）。对于类似嵌套的属性，也可以执行相同的操作

坐标位于geometry.coordinates下，这是一个坐标对数组数组数组

import jmespath
import pandas as pd

query = """
[*].{ 
    Neighborhood: properties.Name,
    Neighborhood_ID: properties.Neighborhood_ID, 
    SqMiles: properties.SqMiles, 
    Latitude: geometry.coordinates[0][0][0][0], 
    Longitude: geometry.coordinates[0][0][0][1] 
}
"""

compiled = jmespath.compile(query)
result = compiled.search(boston_neighborhoods)

df = pd.DataFrame.from_records(result)
#   Neighborhood Neighborhood_ID  SqMiles   Latitude  Longitude
# 0   Roslindale            None     2.51 -71.125927  42.272013