python - 如何在 Python 中将多键 dict 写入 excel?
问题描述
有没有办法将python中的多键dict写入excel文件,其中键分别是行索引和列标题?这是一个例子:
d={('A', 0): 1.0, ('A', 1): 1.0, ('A', 2): 1.0, ('A', 3):1.0, ('B', 0): 1.0, ('B', 1): 1.0, ('B', 2): 1.0, ('B', 3): 1.0}
我正在寻找如下输出:
解决方案
我不认为这种字典有什么特殊功能。但是使用 openpyxl 可以实现。字典的键分别包含行标题和列标题。你的字典的价值就是价值。
使用 openpyxl,代码将如下所示(假设您导入了所需的库):
new_row_no=1 # It will keep info of last row
for key, value in d.items():
column_no=int(key[1])+2 # Here ('A', 0), 0 is the column no
row_no=0
#Update column title here
sh.cell(row=1, column=column_no, value=key[1])
# Do some stuff here to decide which row to insert new value
# Check if row title exists or not
# If exists, find that row(for example which cell of column A contains 'A' as row title)
row_no=int(proper_row_no_finder(sh_to_search=sh, value_to_search=key[0]))
# If row title is new, it means that it is not used, then do extra stuff
if row_no==0:
#if not exists, it means that you have to start working with new row
new_row_no+=1
row_no=new_row_no
sh.cell(row=row_no, column=1, value=key[0]) # Add just row title as it is new
# Now you have both row and column number, you can work with that as you wish
sh.cell(row=row_no, column=column_no, value=value)
wb.save('result.xlsx')
这是辅助函数:
def proper_row_no_finder(**kwargs):
sh_to_search=kwargs['sh_to_search']
value_to_search=kwargs['value_to_search']
xrow=0
for col in sh_to_search.iter_cols(min_row=1, max_col=1):
for xvalue in col:
if xvalue.value==value_to_search:
xrow=xvalue.row # Take the row number, you need it to insert new value
else:
xrow=0
return xrow
推荐阅读
- sap-cloud-sdk - SAP Cloud SDK VDM 生成器是否正确处理 EDMX 注释?
- jquery - jQuery 禁用 preventDefault
- visual-c++ - 从 Visual Studio 2008 升级到 Visual Studio 2019 时单选按钮未正确呈现?
- json - 将 JSON 列表发送到 Spring 控制器会导致 null 值
- python - 我想关联来自不同 DataFrame 的多列
- git - IntelliJ IDEA 中的“Revert Commit”和“Undo Commit”有什么区别?
- python - 有什么方法可以计算python中的平均日期时间并将其可视化吗?
- python - python 3中非常大的数字除法
- python - batch_normalization 中尺寸错误的 beta
- excel - 突出显示属于某个列标题的行的最小值