python - Python - Extract data from nested dictionary into Excel
问题描述
I have a nested dictionary and I want to do the following:
- Create excel worksheet based on name of dictionary (Excel sheets called dict1, dict2 etc based on example below)
- Create headers in the Excel sheet that match the dictionary - so cell A1 =
key1
, cell B1 =key2
- Populate the data that I have in the dictionary into the relevant columns (e.g col A2,A3,A4 etc to have
data1
,data2
,data3
(and the same for the rest of the columns)
Input dictionary example:
nested_dict = {'dict_1': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']},
'dict_2': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']}}
Desired output in Excel
key1 | key2|key3|
data1| 1 |value1
data2| 2 |value2
data3| 3 |value3
I am currently using openpyxl to try to achieve this but not exactly sure how to iterate through the nested dictionary correctly and access create worksheets for each dictionary then repeat for the rest of the data.
for k,v in nested_dict.items:
for sheetName in wb.sheetnames:
wb.create_sheet(k)
ws.cell(row=1, column=1).value
wb.save('test.xlsx')
Could someone point me in the right direction?
解决方案
U can use 'pandas' package
import pandas as pd
nested_dict = {'dict_1': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']},
'dict_2': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']}
}
writer = pd.ExcelWriter('test.xlsx')
for sheet,data in nested_dict.items():
df = pd.DataFrame(data)
df.to_excel(writer, sheet_name=sheet,index=False)
writer.save()
推荐阅读
- git - Git:在本地分支中进行编辑,以推回远程仓库
- regex - 查找并部分替换 Notepad++ 正则表达式
- azure-functions - 持久功能 Blob 触发器
- javascript - 如果填充,更改输入字段颜色
- sql - 过去 6 个月内每个月听超过 10 分钟的人
- javascript - 关于使用 webpack 在 javascript 中的语法
- jsp - Tomcat 9 和 Web 应用程序子目录的类路径问题
- javascript - 节点 Postgres Pub/Sub - 保留剩余的连接槽
- mod-rewrite - mod rewrite 3 个子域重定向到自己(通用重定向)
- c# - 如何使用 HashCode.Combine 避免冲突?