python - 使用规范化展平三重嵌套 JSON

Question

我正在尝试展平以下内容，但它仅适用于非三重嵌套的 JSON。

工作代码:

导入json

import pandas as pd 
from pandas.io.json import json_normalize

data = [{'masterName': 'AAAAAAAAAAA',
         'shortname': 'AA',
         'info': {
              'name': 'randomka'
         },
         'mainNames': [{'date': '2019-05-16', 'NumberOne': 1111},
                       {'date': '2019-06-22', 'NumberOne': 2222}]}
       ]

result = json_normalize(data, 'mainNames', ['masterName', 'shortname',
                                          ['info', 'name']],errors='ignore')

不工作:

data2 = [{"masterName": "AAAAAAAAAAA",
          "mainNames": [
            {
                "numbers": [{
                        "date": "2019-05-16",
                        "NumberOne": 222}],
                "name": "randomka"
            },
            {
                "numbers": [{
                        "date": "2019-05-16",
                        "NumberOne": 222}],
                "name": "randomka"
            }
        ]
    }]

    json_normalize(data2, 'mainNames', ['masterName'],errors='ignore')

返回时:

我已经在 json_normalize 代码中尝试了替代的 record_paths 和 metas，但我无法使其适用于这个三重嵌套的 JSON。换句话说，我不能一次性拿走所有的专栏。

我尝试过的替代方案有效且看起来很接近:

json_normalize(data2, ['mainNames','numbers'], ['masterName'],errors='ignore') 

输出几乎是一个 Excel View ，数据按列排列。根据评论请求的预期 View :

UPD:数据可能有多个数字分支:

data2 = [{"masterName": "AAAAAAAAAAA",
          "mainNames": [
            {
                "numbers": [{
                        "date": "2019-05-16",
                        "NumberOne": 222}],
                "name": "randomka"
            },
            {
                "numbers": [{
                        "date": "2019-05-16",
                        "NumberOne": 222},
{
                        "date": "2019-07-01",
                        "NumberOne": 341}],
                "name": "randomka"
            }
        ]
    }]

Answer 1

正如 @Aayush Mahajan 在评论中所建议的，定义自己的函数可能会更简单。这是使用 data2 的一个:

out = []
data2 = data2[0]                                    # Remove first level
for main in data2["mainNames"]:                     # Iterate "mainNames"
    sub_dict = {"masterName": data2['mainNames']}   # Init new dict (df row) with "mainNames"
    sub_dict.update(main["numbers"][0])             # Add all fields from "numbers"
    sub_dict["name"] = main["name"]                 # Add "name" field

    out.append(sub_dict)                            # append sud dict to list outputs

print(out)
# [{'masterName': 'AAAAAAAAAAA', 'date': '2019-05-16', 'NumberOne': 222, 'name': 'randomka'},
#  {'masterName': 'AAAAAAAAAAA', 'date': '2019-05-16', 'NumberOne': 222, 'name': 'randomka'}]

# create Dataframe with from_dict
df = pd.DataFrame().from_dict(out)
print(df)
#     masterName        date  NumberOne      name
# 0  AAAAAAAAAAA  2019-05-16        222  randomka
# 1  AAAAAAAAAAA  2019-05-16        222  randomka

更新:您可以添加一个内部循环来迭代numbers字段:

out = []
data2 = data2[0]                                    # Remove first level
for main in data2["mainNames"]:                     # Iterate "mainNames"
    for numbers in main["numbers"]:
        sub_dict = {"masterName": data2['masterName']}  # Init new dict (df row) with "mainNames"
        sub_dict.update(numbers)                        # Add all fields from "numbers"
        sub_dict["name"] = main["name"]                 # Add "name" field

        out.append(sub_dict)                            # append sud dict to list outputs

print(out)
# [{'masterName': 'AAAAAAAAAAA', 'date': '2019-05-16', 'NumberOne': 222, 'name': 'randomka'},
#  {'masterName': 'AAAAAAAAAAA', 'date': '2019-05-16', 'NumberOne': 222, 'name': 'randomka'},
#  {'masterName': 'AAAAAAAAAAA', 'date': '2019-07-01', 'NumberOne': 341, 'name': 'randomka'}]

df = pd.DataFrame().from_dict(out)
print(df)
#    NumberOne        date   masterName      name
# 0        222  2019-05-16  AAAAAAAAAAA  randomka
# 1        222  2019-05-16  AAAAAAAAAAA  randomka
# 2        341  2019-07-01  AAAAAAAAAAA  randomka

此外，它仍然可以使用初始的 data2。

希望有帮助!