I've a massive geo json in this form:
{'features': [{'properties': {'MARKET': 'Albany',
'geometry': {'coordinates': [[[-74.264948, 42.419877, 0],
[-74.262041, 42.425856, 0],
[-74.261175, 42.427631, 0],
[-74.260384, 42.429253, 0]]],
'type': 'Polygon'}}},
{'properties': {'MARKET': 'Albany',
'geometry': {'coordinates': [[[-73.929627, 42.078788, 0],
[-73.929114, 42.081658, 0]]],
'type': 'Polygon'}}},
{'properties': {'MARKET': 'Albuquerque',
'geometry': {'coordinates': [[[-74.769198, 43.114089, 0],
[-74.76786, 43.114496, 0],
[-74.766474, 43.114656, 0]]],
'type': 'Polygon'}}}],
'type': 'FeatureCollection'}
阅读 JSON 后:
import json
with open('x.json') as f:
data = json.load(f)
我将数值读入列表,然后再转为数据框:
#to get a list of all markets
mkt=set([f['properties']['MARKET'] for f in data['features']])
#to create a list of market and associated lat long
markets=[(market,list(chain.from_iterable(f['geometry']['coordinates']))) for f in data['features'] for market in mkt if f['properties']['MARKET']==mkt]
df = pd.DataFrame(markets[0:], columns=['a','b'])
数据框的前几行为:
a b
0 Albany [[-74.264948, 42.419877, 0], [-74.262041, 42.4...
1 Albany [[-73.929627, 42.078788, 0], [-73.929114, 42.0...
2 Albany [[-74.769198, 43.114089, 0], [-74.76786, 43.11...
然后,为了展开列b中的嵌套列表,我使用了 pandas concat
:
df1 = pd.concat([df.iloc[:,0:1], df['b'].apply(pd.Series)], axis=1)
但是这样会创建8070列,其中许多列都是NaN。有没有一种方法可以通过市场(列a)对所有纬度和经度进行分组?需要一个由两列组成的百万行数据框。
期望输出如下:
mkt lat long
Albany 42.419877 -74.264948
Albany 42.078788 -73.929627
..
Albuquerque 35.105361 -106.640342
请注意列表元素中的零 ([-74.769198, 43.114089, 0]) 需要被忽略。