嵌套的JSON转换为CSV - 通用方法

Question

我是 Python 的新手，正在尝试将嵌套的 json 文件转换成 cvs，但遇到了困难。为此，我首先加载了 json，然后使用 json_normalize 进行转换，以便输出漂亮的格式化结果，接着使用 pandas 包将规范化部分输出到 cvs。

我的示例 json：

[{
 "_id": {
   "id": "123"
 },
 "device": {
   "browser": "Safari",
   "category": "d",
   "os": "Mac"
 },
 "exID": {
   "$oid": "123"
 },
 "extreme": false,
 "geo": {
   "city": "London",
   "country": "United Kingdom",
   "countryCode": "UK",
   "ip": "00.000.000.0"
 },
 "viewed": {
   "$date": "2011-02-12"
 },
 "attributes": [{
   "name": "gender",
   "numeric": 0,
   "value": 0
 }, {
   "name": "email",
   "value": false
 }],
 "change": [{
   "id": {
     "$id": "1231"
   },
   "seen": [{
     "$date": "2011-02-12"
   }]
 }]
}, {
 "_id": {
   "id": "456"
 },
 "device": {
   "browser": "Chrome 47",
   "category": "d",
   "os": "Windows"
 },
 "exID": {
   "$oid": "345"
 },
 "extreme": false,
 "geo": {
   "city": "Berlin",
   "country": "Germany",
   "countryCode": "DE",
   "ip": "00.000.000.0"
 },
 "viewed": {
   "$date": "2011-05-12"
 },
 "attributes": [{
   "name": "gender",
   "numeric": 1,
   "value": 1
 }, {
   "name": "email",
   "value": true
 }],
 "change": [{
   "id": {
     "$id": "1231"
   },
   "seen": [{
     "$date": "2011-02-12"
   }]
 }]
}]

以下是代码（这里省略了嵌套部分）：

import json
from pandas.io.json import json_normalize


def loading_file():
    #File path
    file_path = #file path here

    #Loading json file
    json_data = open(file_path)
    data = json.load(json_data)
    return data

#Storing avaliable keys
def data_keys(data):
    keys = {}
    for i in data:
        for k in i.keys():
            keys[k] = 1

    keys = keys.keys()

#Excluding nested arrays from keys - hard coded -> IMPROVE
    new_keys = [x for x in keys if
    x != 'attributes' and
    x != 'change']

    return new_keys

#Excluding nested arrays from json dictionary
def new_data(data, keys):
    new_data = []
    for i in range(0, len(data)):
        x = {k:v for (k,v) in data[i].items() if k in keys }
        new_data.append(x)
    return new_data

 def csv_out(data):
     data.to_csv('out.csv',encoding='utf-8')

def main():
     data_file = loading_file()
     keys = data_keys(data_file)
     table = new_data(data_file, keys)
     csv_out(json_normalize(table))

main()

我的当前输出大致如下：

| _id.id | device.browser | device.category | device.os |  ... | viewed.$date |
|--------|----------------|-----------------|-----------|------|--------------|
| 123    | Safari         | d               | Mac       | ...  | 2011-02-12   |
| 456    | Chrome 47      | d               | Windows   | ...  | 2011-05-12   |
|        |                |                 |           |      |              |

我的问题是，我想把嵌套的数组包含在cvs中，所以我必须将它们展平。我无法想出如何使其通用，因此在创建表格时不使用字典的键（numeric、id、name）和值。我必须使其通用，因为attributes和change中的键数量是不确定的。因此，我希望输出结果如下：

| _id.id | device.browser | ... | attributes_gender_numeric | attributes_gender_value | attributes_email_value | change_id | change_seen |
|--------|----------------|-----|---------------------------|-------------------------|------------------------|-----------|-------------|
| 123    | Safari         | ... | 0                         | 0                       | false                  | 1231      | 2011-02-12  |
| 456    | Chrome 47      | ... | 1                         | 1                       | true                   | 1231      | 2011-02-12  |
|        |                |     |                           |                         |                        |           |             |

非常感谢您的支持！ 如果有改进代码和使其更加高效的任何提示，我们将不胜感激。

- An economist

Answer 1

感谢Amir Ziai的博客文章，您可以在这里找到https://medium.com/@amirziai/flattening-json-objects-in-python-f5343c794b10，我成功地使用以下函数将我的数据输出为平面表格。

#Function that recursively extracts values out of the object into a flattened dictionary
def flatten_json(data):
    flat = [] #list of flat dictionaries
    def flatten(y):
        out = {}

        def flatten2(x, name=''):
            if type(x) is dict:
                for a in x:
                    if a == "name": 
                            flatten2(x["value"], name + x[a] + '_')
                    else:  
                        flatten2(x[a], name + a + '_')
            elif type(x) is list:
                for a in x:
                    flatten2(a, name + '_')
            else:
                out[name[:-1]] = x

        flatten2(y)
        return out

#Loop needed to flatten multiple objects
    for i in range(len(data)):
        flat.append(flatten(data[i]).copy())

    return json_normalize(flat) 

我知道这个代码不是完全通用的，因为它用到了名称-值if语句。然而，如果删掉这个例外来创建名称-值字典，那么该代码就可以用于其他嵌入数组了。

- An economist

Answer 2

几周前，我有一个任务是将一个带有嵌套键和值的JSON转换成CSV文件。为了完成这个任务，必须正确处理嵌套键，将它们连接起来作为唯一的标题用于值。最终的代码如下所示，并且可以在这里找到。

def get_flat_json(json_data, header_string, header, row):
    """Parse json files with nested key-vales into flat lists using nested column labeling"""
    for root_key, root_value in json_data.items():
        if isinstance(root_value, dict):
            get_flat_json(root_value, header_string + '_' + str(root_key), header, row)
        elif isinstance(root_value, list):
            for value_index in range(len(root_value)):
                for nested_key, nested_value in root_value[value_index].items():
                    header[0].append((header_string +
                                      '_' + str(root_key) +
                                      '_' + str(nested_key) +
                                      '_' + str(value_index)).strip('_'))
                    if nested_value is None:
                        nested_value = ''
                    row[0].append(str(nested_value))
        else:
            if root_value is None:
                root_value = ''
            header[0].append((header_string + '_' + str(root_key)).strip('_'))
            row[0].append(root_value)
    return header, row

这是一种更为概括的方法，基于一位经济学家对这个问题的回答。

- Marcus Vinicius Melo

Answer 3

以下代码适用于混乱的JSON文件，其中包含相互嵌套7层的字典和列表：

    import csv, json, os
    def parse_json(data):
        a_dict_accum = {}
        for key, val in data.items():
            print("key, val = ", key, val)
            output.writerow([key])
            output.writerow([val])
            if isinstance(val, dict):
                for a_key, a_val in val.items():
                    print("a_key, a_val = ", a_key, a_val)
                    output.writerow([a_key])
                    output.writerow([a_val])
                    a_dict_accum.update({a_key:a_val})
                print("a_dict_accum = ", a_dict_accum)
                parse_json(a_dict_accum)
            elif isinstance(val, list):
                print("val_list = ", val)
                for a_list in val:
                     print("a_list = ", a_list)
                     output.writerow([a_list])
                     if isinstance(a_list, dict):
                         for a_key, a_val in a_list.items():
                             print("a_key, a_val = ", a_key, a_val)
                             output.writerow([a_key])
                             output.writerow([a_val])    
                             a_dict_accum.update({a_key:a_val})
                         print("a_dict_accum = ", a_dict_accum)
                         parse_json(a_dict_accum)
    os.chdir('C://Users/Robert/viirs/20200217_output')
    fileInput = 'night_lights_points.json'
    fileOutput = 'night_lights_points.csv'
    inputFile = open(fileInput) #open json file
    outputFile = open(fileOutput, 'w', newline='') #load csv file
    data = json.load(inputFile) #load json content
    output = csv.writer(outputFile) #create a csv.writer
    output = parse_json(data)
    inputFile.close() #close the input file
    outputFile.close() #close the output file       
                
            

- Rob