从Python将分层JSON数据写入Excel xls吗?

问题描述

我想将一些数据从python写入xlsx.我目前将其存储为JSON，但是从Python中取出的内容并不重要.单个文章的JSON如下所示:

I want to write some data from python to xlsx. I currently have it stored as JSON, but it doesn't matter what it is going out of Python. Here's what the JSON for a single article would look like:

{
   'Word Count': 50
   'Key Words': {
                  ['Blah blah blah', 'Foo', ... ] }
   'Frequency': {
                  [9, 12, ... ] }
   'Proper Nouns': {
                  ['UN', 'USA', ... ] }
   'Location': 'Mordor'
}

我检查了XlsxWriter模块，但无法弄清楚如何转换大小不必相同的分层数据(请注意两个数据对象"之间的专有名词的数量).

I checked out the XlsxWriter module but can't figure out how to translate hierarchical data that is not necessarily the same size (note the number of proper nouns between the two data "objects").

我希望数据看起来像什么

What I want the data to look like:

有指针吗?

推荐答案

由于您的结构可以任意嵌套，因此建议使用递归来实现:

As your structures can be arbitrarily nested, I would suggest using recursion to achieve this:

from collections import OrderedDict
import xlsxwriter
import json

def json_to_excel(ws, data, row=0, col=0):
    if isinstance(data, list):
        row -= 1
        for value in data:
            row = json_to_excel(ws, value, row+1, col)
    elif isinstance(data, dict):
        max_row = row
        start_row = row
        for key, value in data.iteritems():
            row = start_row
            ws.write(row, col, key)
            row = json_to_excel(ws, value, row+1, col)
            max_row = max(max_row, row)
            col += 1
        row = max_row
    else:
        ws.write(row, col, data)

    return row

text = """
[
    {
        "Source ID": 123,
        "WordCount": 50,
        "Key Words": ["Blah blah blah", "Foo"],
        "Frequency": [9, 12, 1, 2, 3],
        "Proper Nouns": ["UN", "USA"],
        "Location": "Mordor"
    },
    {
        "Source ID": 124,
        "WordCount": 50,
        "Key Words": ["Blah blah blah", "Foo"],
        "Frequency": [9, 12, 1, 2, 3],
        "Proper Nouns": ["UN", "USA"],
        "Location": "Mordor"
    }
]
"""

data = json.loads(text, object_pairs_hook=OrderedDict)
wb = xlsxwriter.Workbook("output.xlsx")
ws = wb.add_worksheet()
json_to_excel(ws, data)
wb.close()

这将为您提供一个输出文件，如下所示:

This would give you an output file looking like:

这篇关于从Python将分层JSON数据写入Excel xls吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！