Python展平多级JSON

问题描述

我正在尝试将JSON转换为CSV文件，可用于进一步分析.我的结构存在的问题是，当我转换JSON文件时，我有很多嵌套的字典/列表.

I am trying to convert JSON to CSV file, that I can use for further analysis. Issue with my structure is that I have quite some nested dict/lists when I convert my JSON file.

我尝试使用大熊猫json_normalize()，但它只会使第一级扁平化.

I tried to use pandas json_normalize(), but it only flattens first level.

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

api_key = xxxx
secret = xxxx
endpoint = xxxx

cs = CloudStack(endpoint=endpoint,
                key=api_key,
                secret=secret)

virtual_machines = cs.virtMach()

test = json_normalize(virtual_machines["virtualmachine"])

test.to_csv("test.csv", sep="|", index=False)

有什么主意如何讨好整个JSON文件，以便为单个(在本例中为虚拟机)条目创建到CSV文件的单行输入吗?我尝试了这里发布的几种解决方案，但是我的结果始终是仅将第一级展平.

Any idea how to flatter whole JSON file, so I can create single line input to CSV file for single (in this case virtual machine) entry? I have tried couple of solutions posted here, but my result was always only first level was flattened.

这是示例JSON(在这种情况下，我仍然将"securitygroup"和"nic"输出为JSON格式:

This is sample JSON (in this case, I still get "securitygroup" and "nic" output as JSON format:

{
    "count": 13,
    "virtualmachine": [
        {
            "id": "1082e2ed-ff66-40b1-a41b-26061afd4a0b",
            "name": "test-2",
            "displayname": "test-2",
            "securitygroup": [
                {
                    "id": "9e649fbc-3e64-4395-9629-5e1215b34e58",
                    "name": "test",
                    "tags": []
                }
            ],
            "nic": [
                {
                    "id": "79568b14-b377-4d4f-b024-87dc22492b8e",
                    "networkid": "05c0e278-7ab4-4a6d-aa9c-3158620b6471"
                },
                {
                    "id": "3d7f2818-1f19-46e7-aa98-956526c5b1ad",
                    "networkid": "b4648cfd-0795-43fc-9e50-6ee9ddefc5bd"
                    "traffictype": "Guest"
                }
            ],
            "hypervisor": "KVM",
            "affinitygroup": [],
            "isdynamicallyscalable": false
        }
    ]
}

谢谢您，并致以最诚挚的问候，博斯特让

Thank you and best regards,Bostjan

推荐答案

感谢gyx-hh，此问题已得到解决:

Thanks to gyx-hh, this has been resolved:

我使用了以下函数(可以在此处找到详细信息):

I used following function (details can be found here):

def flatten_json(y):
    out = {}

    def flatten(x, name=''):
        if type(x) is dict:
            for a in x:
                flatten(x[a], name + a + '_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '_')
                i += 1
        else:
            out[name[:-1]] = x

    flatten(y)
    return out

不幸的是，这完全使整个JSON扁平化，这意味着，如果您具有多级JSON(许多嵌套字典)，则可能会将所有内容扁平化为带有大量列的单行.

This unfortunately completely flattens whole JSON, meaning that if you have multi-level JSON (many nested dictionaries), it might flatten everything into single line with tons of columns.

最后我使用的是json_normalize()并指定了我需要的结构.可以在此处找到不错的示例

What I used in the end was json_normalize() and specified structure that I required. Nice example of how to do it that way can be found here.

希望这能帮助某人，并再次感谢gyx-hh提供解决方案.

Hopefully this hepls someone and again thank to gyx-hh for solution.

最诚挚的问候

这篇关于Python展平多级JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！