将python dict写入excel

Question

我有一个非常长的python dict，其中有一些键，里面有更多的dict，如下面的示例所示

{
       "dscpTagValue": {"data": 10,
                        "accuracy": Low Drop"}
       "description": "Latency Insensitive"
}
,
{
       "dscpTagValue": {"data": 10,
                        "accuracy": Low Drop"}
       "description": "Latency Insensitive"
}
{
       "dscpTagValue": {"data": 10,
                        "accuracy": Low Drop"}
       "description": "Latency Insensitive"
}

如何将其导出到excel？有些dict中可能包含多个字典，有些dict中可能没有其他dict，只有键和值

Answer 1

我强烈建议您使用pandas进行这些数据管理操作。使用字典列表，例如：

l = [{'dscpTagValue': {'data': 10, 'accuracy': 'Low Drop'},
  'description': 'Latency Insensitive'},
 {'dscpTagValue': {'data': 9, 'accuracy': 'Low Drop'},
  'description': 'Latency Insensitive'},
 {'dscpTagValue': {'data': 8, 'accuracy': 'Medium Drop'},
  'description': 'Latency Sensitive'}]

您可以使用数据框的to_excel将其写入excel：

import pandas as pd
df = pd.DataFrame(l)
print(df.to_string())
#                             dscpTagValue          description
#0    {'data': 10, 'accuracy': 'Low Drop'}  Latency Insensitive
#1     {'data': 9, 'accuracy': 'Low Drop'}  Latency Insensitive
#2  {'data': 8, 'accuracy': 'Medium Drop'}    Latency Sensitive

df.to_excel("my_excel_file.xlsx", sheet_name='my sheet name')

Answer 2

考虑到数据是一个字典列表，您可以使用csv.DictWriter()并使用最大键指定csv的列名。大概是这样的：

data = [
        {
            "dscpTagValue": {"data": 10, "accuracy": "Low Drop"},
            "description": "Latency Insensitive"
        },
        .
        .
        .
]

import csv

len_keys = [len(d.keys()) for d in data] # no. of keys of each dictionary
csv_columns = list(data[np.argmax(len_keys)]) # csv column headers == longest dict (keys)

try:
    with open('file.csv', 'w') as f:
        writer = csv.DictWriter(f, fieldnames=csv_columns)
        writer.writeheader()
        for d in data:
            writer.writerow(d)
except IOError:
    print('IOError')