Json到CSV问题 [英] Json to CSV issues

问题描述

我正在使用熊猫对一些json数据进行规范化.当一个以上的节是一个对象或一个数组时，我会陷入这个问题.

I am using pandas to normalize some json data. I am getting stuck on this issue when more than 1 section is either an object or an array.

如果我在Car上使用record_path，它将在第二个中断.

If i use the record_path on Car it breaks on the second.

关于如何获得这样的信息以在每辆汽车和每个位置的CSV中创建一行的任何指针?

Any pointers on how to get something like this to create a line in the csv per Car and per Location?

[
    {
        "Name": "John Doe",
        "Car": [
            "Car1",
            "Car2"
        ],
        "Location": "Texas"
    },
    {
        "Name": "Jane Roe",
        "Car": "Car1",            
        "Location": [
            "Illinois",
            "Kansas"
        ]
    }
]

这是输出

Name,Car,Location
John Doe,"['Car1', 'Car2']",Texas
Jane Roe,Car1,"['Illinois', 'Kansas']"

这是代码:

with open('file.json') as data_file:
    data = json.load(data_file)
df = pd.io.json.json_normalize(data, errors='ignore')

希望这样结束:

Name,Car,Location
John Doe,Car1,Texas
John Doe,Car2,Texas
Jane Roe,Car1,Illinois
Jane Roe,Car1,Kansas

答案很有效，直到我遇到带有额外数据的json文件.这就是带有额外值的文件.

The answers worked great until I ran into a json file with extra data. This what a file looks like with the extra values.

{
    Customers:[
    {
        "Name": "John Doe",
        "Car": [
            "Car1",
            "Car2"
        ],
        "Location": "Texas",
        "Repairs: {
            "RepairLocations": {
                "RepairsCompleted":[
                    "Fix1",
                    "Fix2"
                ]
            }
        }
    },
    {
        "Name": "Jane Roe",
        "Car": "Car1",            
        "Location": [
            "Illinois",
            "Kansas"
        ]
    }
]
}

这就是我要去的地方.我认为它是这种格式中可读性最高的，但是至少所有键都应该

Here is what I am going for. I think its the most readable in this format but anything would at least should all the keys

Name,Car,Location,Repairs:RepairLocation
John Doe,Car1,Texas,RepairsCompleted:Fix1
John Doe,Car1,Texas,RepairsCompleted:Fix2
John Doe,Car2,Texas,RepairsCompleted:Fix1
John Doe,Car2,Texas,RepairsCompleted:Fix2
Jane Roe,Car1,Illinois,
Jane Roe,Car1,Kansas,

关于获得第二部分的任何建议吗?

Any suggestions on getting this second part?

推荐答案

您正在寻找这样的东西:

You're looking for something like this:

def expand($keys):
    . as $in
    | reduce $keys[] as $k ( [{}];
        map(. + { 
            ($k): ($in[$k] | if type == "array" then .[] else . end)
        })
    ) | .[];
(.[0] | keys_unsorted) as $h
| $h, (.[] | expand($h) | [.[$h[]]]) | @csv

^REPL演示

这篇关于Json到CSV问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！