解析嵌套的JSON并将其写入CSV

问题描述

我正在为这个问题而苦苦挣扎.我有一个JSON文件，需要将其放到CSV中，如果结构是扁平的，没有深层嵌套的项目，就可以了.

I'm struggling with this problem. I have a JSON file and needs ti put it out to CSV, its fine if the structure is kind of flat with no deep nested items.

但是在这种情况下，嵌套的RACES弄乱了我.

But in this case the nested RACES is messing me up.

我将如何以这种格式获取数据:

How would I go about getting the data in a format like this:

VENUE, COUNTRY, ITW, RACES__NO, RACES__TIME

每个对象和对象中的每个种族?

for each object and each race in the object?

{
    "1": {
        "VENUE": "JOEBURG",
        "COUNTRY": "HAE",
        "ITW": "XAD",
        "RACES": {
            "1": {
                "NO": 1,
                "TIME": "12:35"
            },
            "2": {
                "NO": 2,
                "TIME": "13:10"
            },
            "3": {
                "NO": 3,
                "TIME": "13:40"
            },
            "4": {
                "NO": 4,
                "TIME": "14:10"
            },
            "5": {
                "NO": 5,
                "TIME": "14:55"
            },
            "6": {
                "NO": 6,
                "TIME": "15:30"
            },
            "7": {
                "NO": 7,
                "TIME": "16:05"
            },
            "8": {
                "NO": 8,
                "TIME": "16:40"
            }
        }
    },
    "2": {
        "VENUE": "FOOBURG",
        "COUNTRY": "ABA",
        "ITW": "XAD",
        "RACES": {
            "1": {
                "NO": 1,
                "TIME": "12:35"
            },
            "2": {
                "NO": 2,
                "TIME": "13:10"
            },
            "3": {
                "NO": 3,
                "TIME": "13:40"
            },
            "4": {
                "NO": 4,
                "TIME": "14:10"
            },
            "5": {
                "NO": 5,
                "TIME": "14:55"
            },
            "6": {
                "NO": 6,
                "TIME": "15:30"
            },
            "7": {
                "NO": 7,
                "TIME": "16:05"
            },
            "8": {
                "NO": 8,
                "TIME": "16:40"
            }
        }
    }, ...
}

我想像这样将其输出到CSV:

I would like to output this to CSV like this:

VENUE, COUNTRY, ITW, RACES__NO, RACES__TIME
JOEBERG, HAE, XAD, 1, 12:35
JOEBERG, HAE, XAD, 2, 13:10
JOEBERG, HAE, XAD, 3, 13:40
...
...
FOOBURG, ABA, XAD, 1, 12:35
FOOBURG, ABA, XAD, 2, 13:10

所以首先我得到正确的密钥:

So first I get the correct keys:

self.keys = self.data.keys()
keys = ["DATA_KEY"]
for key in self.keys:
    if type(self.data[key]) == dict:
        for k in self.data[key].keys():
            if k not in keys:
                if type(self.data[key][k]) == unicode:
                    keys.append(k)
                elif type(self.data[key][k]) == dict:
                    self.subkey = k
                    for sk in self.data[key][k].values():
                        for subkey in sk.keys():
                            subkey = "%s__%s" % (self.subkey, subkey)
                            if subkey not in keys:
                                keys.append(subkey)

然后添加数据:

但是如何?

对于您熟练的forlooper来说，这应该是一个有趣的过程. ;-)

This should be a fun one for you skilled forloopers. ;-)

推荐答案

我只收集第一个对象的键，然后假定其余格式是一致的.

I'd collect keys only for the first object, then assume that the rest of the format is consistent.

以下代码还将嵌套对象限制为一个；您没有指定当一个以上时会发生什么.具有两个或两个以上相同长度的嵌套结构可以工作(将它们压缩"在一起)，但是，如果您具有不同长度的结构，则需要做出明确选择来处理这些结构.用空列压缩以填充或写出这些条目的乘积(A x B行，每次找到B条目都从A重复信息).

The following code also limits the nested object to just one; you did not specify what should happen when there is more than one. Having two or more nested structures of equal length could work (you'd 'zip' those together), but if you have structures of differing length you need to make an explicit choice how to handle those; zip with empty columns to pad, or to write out the product of those entries (A x B rows, repeating information from A each time you find a B entry).

import csv
from operator import itemgetter


with open(outputfile, 'wb') as outf:
    writer = None  # will be set to a csv.DictWriter later

    for key, item in sorted(data.items(), key=itemgetter(0)):
        row = {}
        nested_name, nested_items = '', {}
        for k, v in item.items():
            if not isinstance(v, dict):
                row[k] = v
            else:
                assert not nested_items, 'Only one nested structure is supported'
                nested_name, nested_items = k, v

        if writer is None:
            # build fields for each first key of each nested item first
            fields = sorted(row)

            # sorted keys of first item in key sorted order
            nested_keys = sorted(sorted(nested_items.items(), key=itemgetter(0))[0][1])
            fields.extend('__'.join((nested_name, k)) for k in nested_keys)

            writer = csv.DictWriter(outf, fields)
            writer.writeheader()

        for nkey, nitem in sorted(nested_items.items(), key=itemgetter(0)):
            row.update(('__'.join((nested_name, k)), v) for k, v in nitem.items())
            writer.writerow(row)

对于您的示例输入，将产生:

For your sample input, this produces:

COUNTRY,ITW,VENUE,RACES__NO,RACES__TIME
HAE,XAD,JOEBURG,1,12:35
HAE,XAD,JOEBURG,2,13:10
HAE,XAD,JOEBURG,3,13:40
HAE,XAD,JOEBURG,4,14:10
HAE,XAD,JOEBURG,5,14:55
HAE,XAD,JOEBURG,6,15:30
HAE,XAD,JOEBURG,7,16:05
HAE,XAD,JOEBURG,8,16:40
ABA,XAD,FOOBURG,1,12:35
ABA,XAD,FOOBURG,2,13:10
ABA,XAD,FOOBURG,3,13:40
ABA,XAD,FOOBURG,4,14:10
ABA,XAD,FOOBURG,5,14:55
ABA,XAD,FOOBURG,6,15:30
ABA,XAD,FOOBURG,7,16:05
ABA,XAD,FOOBURG,8,16:40

这篇关于解析嵌套的JSON并将其写入CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！