将嵌套的JSON解析为数据框 [英] Parsing nested JSON into dataframe

问题描述

我正在尝试将JSON字符串的最低粒度解析为熊猫数据帧.

I am trying to parse a JSON string to its lowest granularity to a panda dataframe.

首先，我尝试了read_json:

First I tried read_json:

jsonData = pd.read_json(apiRequest)

但是大部分数据仍嵌套在networkRank下.

But a large chunk of the data is still nested under networkRank.

然后我尝试了json_normalize，但是这次我错过了更高一级的数据，例如纬度和经度.

Then I tried json_normalize, but this time I am missing the data one level higher such as latitude and longitude.

result = json_normalize(json_data['networkRank'])

我还尝试将解析"为嵌套结构并从头开始构建数据帧，但是此代码会导致错误:

I also tried to parse "into" the nested structure and construct the data frame from scratch, but this code results in error:

result_nested = json_normalize(json_data, 'networkRank', ['longitude', 'latitude', ['networkRank', 'type3G', 'downloadSpeed']])

目标

要将JSON数据解析为具有所有字段的平面表，这意味着将纬度，经度和距离数据附加到图

推荐答案

此函数以递归方式调用自身以拼合字典和列表.

This function recursively calls itself to flatten dictionaries and lists.

from collections import OrderedDict

def flatten(json_object, container=None, name=''):
    if container is None:
        container = OrderedDict()
    if isinstance(json_object, dict):
        for key in json_object:
            flatten(json_object[key], container=container, name=name + key + '_')
    elif isinstance(json_object, list):
        for n, item in enumerate(json_object, 1):
            flatten(item, container=container, name=name + str(n) + '_')
    else:
        container[str(name[:-1])] = str(json_object)
    return container

示例:

flatten([1, 2, 3])
OrderedDict([('1', '1'), ('2', '2'), ('3', '3')])

flatten([1, 2, 3], name='x')
OrderedDict([('x1', '1'), ('x2', '2'), ('x3', '3')])

flatten({'a': [1, 2, 3], 'b': 4, 'c': {'d': [5, 6], 'e': 7}}, name='x')
OrderedDict([('xa_1', '1'),
             ('xa_2', '2'),
             ('xa_3', '3'),
             ('xc_e', '7'),
             ('xc_d_1', '5'),
             ('xc_d_2', '6'),
             ('xb', '4')])

响应:

# j = json string
>>> pd.DataFrame(flatten(j), index=[0]).T
                                                      0
perMinuteLimit                                       10
distance                                             10
perMonthCurrent                                       0
longitude                                     35.751607
perMonthLimit                                      2000
latitude                                      -6.162959
perMinuteCurrent                                      0
networkRank_1_networkId                            6402
networkRank_1_type3G_sampleSizeSpeed                 29
networkRank_1_type3G_averageRssiAsu        9.5429091136
networkRank_1_type3G_pingTime                  320.9600
networkRank_1_type3G_networkType                      3
networkRank_1_type3G_averageRssiDb    -69.5664329624972
networkRank_1_type3G_networkName                Vodacom
networkRank_1_type3G_networkId                     6402
networkRank_1_type3G_downloadSpeed            1508.1304
networkRank_1_type3G_uploadSpeed               893.7692
networkRank_1_type3G_reliability      0.804236452826138
networkRank_1_type3G_sampleSizeRSSI                 948
networkRank_1_networkName                       Vodacom
networkRank_2_networkId                            6400
networkRank_2_type3G_sampleSizeSpeed                 21
networkRank_2_type3G_averageRssiAsu       15.3537142857
networkRank_2_type3G_pingTime                  259.0000
networkRank_2_type3G_networkType                      3
networkRank_2_type3G_averageRssiDb    -61.4563389583101
networkRank_2_type3G_networkName                   tiGO
networkRank_2_type3G_networkId                     6400
networkRank_2_type3G_downloadSpeed             516.0000
networkRank_2_type3G_uploadSpeed               320.4211
networkRank_2_type3G_reliability      0.911904765537807
networkRank_2_type3G_sampleSizeRSSI                 935
networkRank_2_networkName                          tiGO
networkRank_3_networkId                            6403
networkRank_3_type3G_sampleSizeSpeed                 21
networkRank_3_type3G_averageRssiAsu       13.2729999375
networkRank_3_type3G_pingTime                  194.5556
networkRank_3_type3G_networkType                      3
networkRank_3_type3G_averageRssiDb    -58.1521092977699
networkRank_3_type3G_networkName                 Airtel
networkRank_3_type3G_networkId                     6403
networkRank_3_type3G_downloadSpeed            1080.2500
networkRank_3_type3G_uploadSpeed               572.1579
networkRank_3_type3G_reliability      0.554680264185345
networkRank_3_type3G_sampleSizeRSSI                 587
networkRank_3_networkName                        Airtel
network_type                                       None
apiVersion                                            2

这篇关于将嵌套的JSON解析为数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！