JSONtoCSV遍歷嵌套物件并將值映射到鍵

我確實有一個 json 格式（但有更多/更深的嵌套物件）

{
    "Group": {
        "Group1": {
            "GroupA": {
                "value": "#fffff",
                "type": "color"

            },
            "GroupB": {
                "value": "#c2c2c2",
                "type": "color"

            },
            "GroupC": {
                "Group_XY": {
                    "value": "#aw13ma",
                    "type": "color"
                }

            }

        },
        "Group2":{
            "GroupA": {
                "value": "#ff123f",
                "type": "color"

            },
            "GroupB": {
                "value": "#fffff",
                "type": "color"

            },
            "GroupD": {
                "value": "#ababab",
                "type": "color"
                
            }

        },
        "Group3": {
            "GroupA": {
                "value": "#fffff",
                "type": "color"

            },
            "GroupC": {
                "Group_XY": {
                    "value": "#12a3ma",
                    "type": "color"
                }

            },
            "GroupD": {
                "value": "#fffff",
                "type": "color"
                
            }

        },
        "Group4": {
            "GroupA": {

            },
            "GroupB": { 
                "value": "#c2c2c2",
                "type": "color"

            },
            "GroupE": {
                "Group_XX": {
                    "Group_A1": {
                        "value": "#12a3ma",
                        "type": "color"
                    }
                }
                
            }

        }
    }  
}

我想要實作的是要比較的 csv/table 輸出：

所以我認為 python 和 pandas 可能是啟動它的正確方法。

import pandas as pd
import json
with open('colorDate.json') as f:
    data = json.load(f)
pd.json_normalize(data,max_level=0)

得到我

現在我需要將下一級的所有鍵轉換為行？！

uj5u.com熱心網友回復：

我不在乎工具堆疊

那么，為什么不使用jq呢？它可以輕松轉換 JSON，甚至可以使用@csv內置和--raw-output(or -r) 選項生成所需的輸出格式。

.Group | to_entries | reduce (
  .[] | (.value | paths(objects) as $p | [$p, getpath($p).value])   [.key]
) as [$path, $value, $column] ({}; ."\($path)"[$column] = $value)

| (keys | map(fromjson) | sort) as $paths
| ($paths | map(length) | max)  as $width
| (map(keys[]) | unique)        as $heads

| [(range($width) | null), $heads[]], ($paths[] as $path
  | ($path | .[:-1] |= map(null))   [range($path | $width - length) | null]
    [."\($path)"[$heads[]]]
) | @csv

,,,"Group1","Group2","Group3","Group4"
"GroupA",,,"#fffff","#ff123f","#fffff",
"GroupB",,,"#c2c2c2","#fffff",,"#c2c2c2"
"GroupC",,,,,,
,"Group_XY",,"#aw13ma",,"#12a3ma",
"GroupD",,,,"#ababab","#fffff",
"GroupE",,,,,,
,"Group_XX",,,,,
,,"Group_A1",,,,"#12a3ma"

演示

個人建議：為了更好的可讀性，我會“合并”那些“空白”行，因為它們不提供額外資訊，同時組合（并在必要時復制）行標題上的路徑項。為此，將 to 擴展paths(objects)到paths(objects | has("value"))僅下降到有價值的專案，并通過更改($path | .[:-1] |= map(null))為 just 來顯示完整路徑$path：

.Group | to_entries | reduce (
  .[] | (.value | paths(objects | has("value")) as $p | [$p, getpath($p).value])   [.key]
) as [$path, $value, $column] ({}; ."\($path)"[$column] = $value)

| (keys | map(fromjson) | sort) as $paths
| ($paths | map(length) | max)  as $width
| (map(keys[]) | unique)        as $heads

| [(range($width) | null), $heads[]], ($paths[] as $path
  | $path   [range($path | $width - length) | null]
    [."\($path)"[$heads[]]]
) | @csv

,,,"Group1","Group2","Group3","Group4"
"GroupA",,,"#fffff","#ff123f","#fffff",
"GroupB",,,"#c2c2c2","#fffff",,"#c2c2c2"
"GroupC","Group_XY",,"#aw13ma",,"#12a3ma",
"GroupD",,,,"#ababab","#fffff",
"GroupE","Group_XX","Group_A1",,,,"#12a3ma"

演示

標籤：Pythonjson熊猫CSV转换器

上一篇：一次讀取兩行CSV，合并值，寫入新的CSV

下一篇：重新排序.csv檔案串列中的列