python：多层嵌套的树状json数据和excel数据的转换

Pyecharts
字数统计: 1.7k   |   阅读时长≈ 8 分钟

问题

目标：实现多层嵌套的树状json数据和excel数据的转换

工具：python

json数据转化结果 
  
  data = [
    {
        "name": "Flora",
        "itemStyle": {"color": "#da0d68"},
        "children": [
            {"name": "Black Tea", "value": 1, "itemStyle": {"color": "#975e6d"}},
            {
                "name": "Floral",
                "itemStyle": {"color": "#e0719c"},
                "children": [
                    {
                        "name": "Chamomile",
                        "value": 1,
                        "itemStyle": {"color": "#f99e1c"},
                    },
                    {"name": "Rose", "value": 1, "itemStyle": {"color": "#ef5a78"}},
                    {"name": "Jasmine", "value": 1, "itemStyle": {"color": "#f7f1bd"}},
                ],
            },
        ],
    },
    {
        "name": "Fruity",
        "itemStyle": {"color": "#da1d23"},
        "children": [
            {
                "name": "Berry",
                "itemStyle": {"color": "#dd4c51"},
                "children": [
                    {
                        "name": "Blackberry",
                        "value": 1,
                        "itemStyle": {"color": "#3e0317"},
                    },
                    {
                        "name": "Raspberry",
                        "value": 1,
                        "itemStyle": {"color": "#e62969"},
                    },
                    {
                        "name": "Blueberry",
                        "value": 1,
                        "itemStyle": {"color": "#6569b0"},
                    },
                    {
                        "name": "Strawberry",
                        "value": 1,
                        "itemStyle": {"color": "#ef2d36"},
                    },
                ],
            },
            {
                "name": "Dried Fruit",
                "itemStyle": {"color": "#c94a44"},
                "children": [
                    {"name": "Raisin", "value": 1, "itemStyle": {"color": "#b53b54"}},
                    {"name": "Prune", "value": 1, "itemStyle": {"color": "#a5446f"}},
                ],
            },
            {
                "name": "Other Fruit",
                "itemStyle": {"color": "#dd4c51"},
                "children": [
                    {"name": "Coconut", "value": 1, "itemStyle": {"color": "#f2684b"}},
                    {"name": "Cherry", "value": 1, "itemStyle": {"color": "#e73451"}},
                    {
                        "name": "Pomegranate",
                        "value": 1,
                        "itemStyle": {"color": "#e65656"},
                    },
                    {
                        "name": "Pineapple",
                        "value": 1,
                        "itemStyle": {"color": "#f89a1c"},
                    },
                    {"name": "Grape", "value": 1, "itemStyle": {"color": "#aeb92c"}},
                    {"name": "Apple", "value": 1, "itemStyle": {"color": "#4eb849"}},
                    {"name": "Peach", "value": 1, "itemStyle": {"color": "#f68a5c"}},
                    {"name": "Pear", "value": 1, "itemStyle": {"color": "#baa635"}},
                ],
            },
            {
                "name": "Citrus Fruit",
                "itemStyle": {"color": "#f7a128"},
                "children": [
                    {
                        "name": "Grapefruit",
                        "value": 1,
                        "itemStyle": {"color": "#f26355"},
                    },
                    {"name": "Orange", "value": 1, "itemStyle": {"color": "#e2631e"}},
                    {"name": "Lemon", "value": 1, "itemStyle": {"color": "#fde404"}},
                    {"name": "Lime", "value": 1, "itemStyle": {"color": "#7eb138"}},
                ],
            },
        ],
    },
    {
        "name": "Sour/\nFermented",
        "itemStyle": {"color": "#ebb40f"},
        "children": [
            {
                "name": "Sour",
                "itemStyle": {"color": "#e1c315"},
                "children": [
                    {
                        "name": "Sour Aromatics",
                        "value": 1,
                        "itemStyle": {"color": "#9ea718"},
                    },
                    {
                        "name": "Acetic Acid",
                        "value": 1,
                        "itemStyle": {"color": "#94a76f"},
                    },
                    {
                        "name": "Butyric Acid",
                        "value": 1,
                        "itemStyle": {"color": "#d0b24f"},
                    },
                    {
                        "name": "Isovaleric Acid",
                        "value": 1,
                        "itemStyle": {"color": "#8eb646"},
                    },
                    {
                        "name": "Citric Acid",
                        "value": 1,
                        "itemStyle": {"color": "#faef07"},
                    },
                    {
                        "name": "Malic Acid",
                        "value": 1,
                        "itemStyle": {"color": "#c1ba07"},
                    },
                ],
            },
            {
                "name": "Alcohol/\nFremented",
                "itemStyle": {"color": "#b09733"},
                "children": [
                    {"name": "Winey", "value": 1, "itemStyle": {"color": "#8f1c53"}},
                    {"name": "Whiskey", "value": 1, "itemStyle": {"color": "#b34039"}},
                    {
                        "name": "Fremented",
                        "value": 1,
                        "itemStyle": {"color": "#ba9232"},
                    },
                    {"name": "Overripe", "value": 1, "itemStyle": {"color": "#8b6439"}},
                ],
            },
        ],
    },
    {
        "name": "Green/\nVegetative",
        "itemStyle": {"color": "#187a2f"},
        "children": [
            {"name": "Olive Oil", "value": 1, "itemStyle": {"color": "#a2b029"}},
            {"name": "Raw", "value": 1, "itemStyle": {"color": "#718933"}},
            {
                "name": "Green/\nVegetative",
                "itemStyle": {"color": "#3aa255"},
                "children": [
                    {
                        "name": "Under-ripe",
                        "value": 1,
                        "itemStyle": {"color": "#a2bb2b"},
                    },
                    {"name": "Peapod", "value": 1, "itemStyle": {"color": "#62aa3c"}},
                    {"name": "Fresh", "value": 1, "itemStyle": {"color": "#03a653"}},
                    {
                        "name": "Dark Green",
                        "value": 1,
                        "itemStyle": {"color": "#038549"},
                    },
                    {
                        "name": "Vegetative",
                        "value": 1,
                        "itemStyle": {"color": "#28b44b"},
                    },
                    {"name": "Hay-like", "value": 1, "itemStyle": {"color": "#a3a830"}},
                    {
                        "name": "Herb-like",
                        "value": 1,
                        "itemStyle": {"color": "#7ac141"},
                    },
                ],
            },
            {"name": "Beany", "value": 1, "itemStyle": {"color": "#5e9a80"}},
        ],
    },
    {
        "name": "Other",
        "itemStyle": {"color": "#0aa3b5"},
        "children": [
            {
                "name": "Papery/Musty",
                "itemStyle": {"color": "#9db2b7"},
                "children": [
                    {"name": "Stale", "value": 1, "itemStyle": {"color": "#8b8c90"}},
                    {
                        "name": "Cardboard",
                        "value": 1,
                        "itemStyle": {"color": "#beb276"},
                    },
                    {"name": "Papery", "value": 1, "itemStyle": {"color": "#fefef4"}},
                    {"name": "Woody", "value": 1, "itemStyle": {"color": "#744e03"}},
                    {
                        "name": "Moldy/Damp",
                        "value": 1,
                        "itemStyle": {"color": "#a3a36f"},
                    },
                    {
                        "name": "Musty/Dusty",
                        "value": 1,
                        "itemStyle": {"color": "#c9b583"},
                    },
                    {
                        "name": "Musty/Earthy",
                        "value": 1,
                        "itemStyle": {"color": "#978847"},
                    },
                    {"name": "Animalic", "value": 1, "itemStyle": {"color": "#9d977f"}},
                    {
                        "name": "Meaty Brothy",
                        "value": 1,
                        "itemStyle": {"color": "#cc7b6a"},
                    },
                    {"name": "Phenolic", "value": 1, "itemStyle": {"color": "#db646a"}},
                ],
            },
            {
                "name": "Chemical",
                "itemStyle": {"color": "#76c0cb"},
                "children": [
                    {"name": "Bitter", "value": 1, "itemStyle": {"color": "#80a89d"}},
                    {"name": "Salty", "value": 1, "itemStyle": {"color": "#def2fd"}},
                    {
                        "name": "Medicinal",
                        "value": 1,
                        "itemStyle": {"color": "#7a9bae"},
                    },
                    {
                        "name": "Petroleum",
                        "value": 1,
                        "itemStyle": {"color": "#039fb8"},
                    },
                    {"name": "Skunky", "value": 1, "itemStyle": {"color": "#5e777b"}},
                    {"name": "Rubber", "value": 1, "itemStyle": {"color": "#120c0c"}},
                ],
            },
        ],
    },
    {
        "name": "Roasted",
        "itemStyle": {"color": "#c94930"},
        "children": [
            {"name": "Pipe Tobacco", "value": 1, "itemStyle": {"color": "#caa465"}},
            {"name": "Tobacco", "value": 1, "itemStyle": {"color": "#dfbd7e"}},
            {
                "name": "Burnt",
                "itemStyle": {"color": "#be8663"},
                "children": [
                    {"name": "Acrid", "value": 1, "itemStyle": {"color": "#b9a449"}},
                    {"name": "Ashy", "value": 1, "itemStyle": {"color": "#899893"}},
                    {"name": "Smoky", "value": 1, "itemStyle": {"color": "#a1743b"}},
                    {
                        "name": "Brown, Roast",
                        "value": 1,
                        "itemStyle": {"color": "#894810"},
                    },
                ],
            },
            {
                "name": "Cereal",
                "itemStyle": {"color": "#ddaf61"},
                "children": [
                    {"name": "Grain", "value": 1, "itemStyle": {"color": "#b7906f"}},
                    {"name": "Malt", "value": 1, "itemStyle": {"color": "#eb9d5f"}},
                ],
            },
        ],
    },
    {
        "name": "Spices",
        "itemStyle": {"color": "#ad213e"},
        "children": [
            {"name": "Pungent", "value": 1, "itemStyle": {"color": "#794752"}},
            {"name": "Pepper", "value": 1, "itemStyle": {"color": "#cc3d41"}},
            {
                "name": "Brown Spice",
                "itemStyle": {"color": "#b14d57"},
                "children": [
                    {"name": "Anise", "value": 1, "itemStyle": {"color": "#c78936"}},
                    {"name": "Nutmeg", "value": 1, "itemStyle": {"color": "#8c292c"}},
                    {"name": "Cinnamon", "value": 1, "itemStyle": {"color": "#e5762e"}},
                    {"name": "Clove", "value": 1, "itemStyle": {"color": "#a16c5a"}},
                ],
            },
        ],
    },
    {
        "name": "Nutty/\nCocoa",
        "itemStyle": {"color": "#a87b64"},
        "children": [
            {
                "name": "Nutty",
                "itemStyle": {"color": "#c78869"},
                "children": [
                    {"name": "Peanuts", "value": 1, "itemStyle": {"color": "#d4ad12"}},
                    {"name": "Hazelnut", "value": 1, "itemStyle": {"color": "#9d5433"}},
                    {"name": "Almond", "value": 1, "itemStyle": {"color": "#c89f83"}},
                ],
            },
            {
                "name": "Cocoa",
                "itemStyle": {"color": "#bb764c"},
                "children": [
                    {
                        "name": "Chocolate",
                        "value": 1,
                        "itemStyle": {"color": "#692a19"},
                    },
                    {
                        "name": "Dark Chocolate",
                        "value": 1,
                        "itemStyle": {"color": "#470604"},
                    },
                ],
            },
        ],
    },
    {
        "name": "Sweet",
        "itemStyle": {"color": "#e65832"},
        "children": [
            {
                "name": "Brown Sugar",
                "itemStyle": {"color": "#d45a59"},
                "children": [
                    {"name": "Molasses", "value": 1, "itemStyle": {"color": "#310d0f"}},
                    {
                        "name": "Maple Syrup",
                        "value": 1,
                        "itemStyle": {"color": "#ae341f"},
                    },
                    {
                        "name": "Caramelized",
                        "value": 1,
                        "itemStyle": {"color": "#d78823"},
                    },
                    {"name": "Honey", "value": 1, "itemStyle": {"color": "#da5c1f"}},
                ],
            },
            {"name": "Vanilla", "value": 1, "itemStyle": {"color": "#f89a80"}},
            {"name": "Vanillin", "value": 1, "itemStyle": {"color": "#f37674"}},
            {"name": "Overall Sweet", "value": 1, "itemStyle": {"color": "#e75b68"}},
            {"name": "Sweet Aromatics", "value": 1, "itemStyle": {"color": "#d0545f"}},
        ],
    },
	]

excel数据转化结果

name1 color1 name2 color2 name3 color3
Flora #da0d68 Black Tea #975e6d 0 0
Flora #da0d68 Floral #e0719c Chamomile #f99e1c
Flora #da0d68 Floral #e0719c Rose #ef5a78
Flora #da0d68 Floral #e0719c Jasmine #f7f1bd
Fruity #da1d23 Berry #dd4c51 Blackberry #3e0317
Fruity #da1d23 Berry #dd4c51 Raspberry #e62969
Fruity #da1d23 Berry #dd4c51 Blueberry #6569b0
Fruity #da1d23 Berry #dd4c51 Strawberry #ef2d36
Fruity #da1d23 Dried Fruit #c94a44 Raisin #b53b54
Fruity #da1d23 Dried Fruit #c94a44 Prune #a5446f
Fruity #da1d23 Other Fruit #dd4c51 Coconut #f2684b
Fruity #da1d23 Other Fruit #dd4c51 Cherry #e73451
Fruity #da1d23 Other Fruit #dd4c51 Pomegranate #e65656

tips: excel数据仅展示部分

Part1: 多层嵌套json数据→excel数据

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
import pandas as pd

df = pd.DataFrame()
for i in data:
    x = pd.json_normalize(i,
                          record_path=['children', 'children'], 
                          meta=[['children', 'name'], ['children', 'itemStyle', 'color'], "itemStyle", "name"],
                          record_prefix='children->',
                          meta_prefix='father->',
                          sep='->',
                          errors='ignore')
    df = pd.concat([df, x],axis=0)
df.to_csv('1.csv')

1、json_normalize用法：

  • **第一个参数：**转换的json数据

  • **record_path：**搜索路径。如果不设置这个参数，默认将json第一层数据转为dataframe，内层数据将还是字典格式。如果要找到children里的数据，则需设定路径，格式为：每一层的名字组成的列表。这里名字需为json数据中每一层的列表名。此例中，我们有3层数据，要找到做内层的children，需要设置为[‘children’, ‘children’]。

    这里可能会报这样的错误：KeyError: ‘children’

    是因为有的数据有一个children，有的数据有children的children，所以他无法识别只有一个children数据的路径，所以这里我们需要手动检查一下数据，把只有一个children的数据加一个空的children参数**, ‘children’: [{‘name’: 0}]**，这样就没问题啦。

  • **meta：**添加额外列。如果我们成功取到最内层的children数据，我们还想再补充上它的上一级中的参数，就可使用这个参数。**格式：**还是按照搜索路径的方式，依次添加在列表中。最外层的数据不需要列表，直接写名字就行。

  • **record_prefix’和meta_prefix：**当不同级别数据中有重名时使用，否则报错。

  • **errors：**忽略缺少key的情况。数据中，有的children的key有，有的children的key没有，这样就会报错。写上就可以忽略这些错误正常执行。

2、处理完每一组数据，把数据追加到上一次循环好的dataframe中。

3、最后输出csv格式数据。

Part2: excel数据→多层嵌套json数据

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import pandas as pd

df = pd.read_csv("旭日图数据.csv")

data = []
for first in df["name1"].unique():
    first_dict = {}
    first_dict["name"] = first
    first_dict["itemStyle"] = {"color": df[df["name1"] == first]["color1"].unique()[0]}
    first_list = []
    first_data = df[df["name1"] == first]

    for second in first_data["name2"].unique():
        second_dict = {}
        second_dict["name"] = second
        second_dict["itemStyle"] = {"color": first_data[first_data["name2"] == second]["color2"].unique()[0]}

        if (first_data[first_data["name2"] == second]["name3"].unique()[0] == '0'):
            second_dict["value"] = 1
        else:
            second_list = []
            second_data = first_data[first_data["name2"] == second]

            for third in second_data["name3"].unique():
                third_dict = {}
                third_dict["name"] = third
                third_dict["itemStyle"] = {"color": second_data[second_data["name3"] == third]["color3"].unique()[0]}
                third_dict["value"] = 1
                second_list.append(third_dict)
            second_dict["children"] = second_list

        first_list.append(second_dict)
    first_dict["children"] = first_list

    data.append(first_dict)

**第一层循环：**先取出name1的数据，unique()一下（去掉重复），遍历每个名字。

​ 创建一个字典，里面存放“名字”和“颜色”，

​ 再创建一个列表，用来存放“children”，

​ 筛选出name1的数据，用于下一级循环

第二层循环：（思路和第一层一样）

​ 这里强调的是由于有的数据只有一个children，有的有两个，所以这里判断一下，如果name3为0，即没有第三层，则赋value=1，否则进行第三层循环

第三层循环：（思路和第一层一样）

​ 遍历完之后就要把数据合一起了（本层的数据加到上一层的列表中，上一层的列表赋值到上一层的字典中）

  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！

扫一扫，分享到微信

微信分享二维码
  • Copyrights © 2022-2023 发现美的眼睛
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信