python - For循环:使用熊猫将字典迭代输出写入excel?
问题描述
我正在尝试遍历其值中包含多个行索引的字典,然后应用 pd.nsmallest 函数为字典中的多组行索引生成前 3 个最小值。但是,我的 for 循环语句似乎有问题,因为我一遍又一遍地覆盖前 3 个值,直到字典中的最后一组值,所以我的最终 excel 文件输出仅显示最后一次运行的 3 行for 循环。
当我使用打印语句时,它按预期工作,并且我得到了字典中所有 16 个值的输出,但是当写入 excel 文件时,它只给了我最后一次在循环中运行的输出
import pandas as pd
from tabulate import tabulate
VA = pd.read_excel('Columnar BU P&L.xlsx', sheet_name = 'Variance by Co')
legcon = VA[['Expense', 'Consolidation', 'Exp Category']]
legcon['Variance Type'] = ['Unfavorable' if x < 0 else 'favorable' for x in legcon['Consolidation']]
d = {'Travel & Entertainment': [1,2,3,4,5,6,7,8,9,10,11], 'Office supplies & Expenses': [13,14,15,16,17],
'Professional Fees':[19,20,21,22,23], 'Fees & Assessments':[25,26,27], 'IT Expenses':[29],
'Bad Debt Expense':[31],'Miscellaneous expenses': [33,34,35,36,37],'Marketing Expenses':[40,41,42],
'Payroll & Related Expenses': [45,46,47,48,49,50,51,52,53,54,55,56], 'Total Utilities':[59,60],
'Total Equipment Maint, & Rental Expense': [63,64,65,66,67,68],'Total Mill Expense':[70,71,72,73,74,75,76,77],
'Total Taxes':[80,81],'Total Insurance Expense':[83,84,85],'Incentive Compensation':[88],
'Strategic Initiative':[89]}
当我这样做时,直接打印输出可以正常工作:
for key,value in d.items():
a = legcon.iloc[value][legcon.iloc[:,1]<0].nsmallest(3,'Consolidation')
print(a)
Expense Consolidation Exp Category Variance Type
5 Transportation - AIR -19054 Travel & Entertainment Unfavorable
9 Meals -9617 Travel & Entertainment Unfavorable
7 Lodging -9439 Travel & Entertainment Unfavorable
Expense Consolidation Exp Category Variance Type
26 Bank Charges -4320 Fees & Assessments Unfavorable
27 Finance Charges -1389 Fees & Assessments Unfavorable
25 Payroll Fees -1145 Fees & Assessments Unfavorable
但是,当我使用以下代码写入 excel 时:
writer = pd.ExcelWriter('testt.xlsx', engine = 'xlsxwriter')
row = 0
for key,value in d.items():
a = legcon.iloc[value][legcon.iloc[:,1]<0].nsmallest(3,'Consolidation')
for i in range(0,16):
a.to_excel(writer, sheet_name = 'test', startrow = row+4, index = False)
writer.save()
我非常感谢有关如何纠正此问题的任何反馈。提前致谢!
解决方案
在朋友的帮助下,我才意识到我的愚蠢错误,我的 for 循环中没有行迭代器可以在下一行打印输出,并且使用下面的代码解决了这个问题(最初我将行迭代器放在我的 df. to_excel 语句):
writer = pd.ExcelWriter('testt.xlsx', engine = 'xlsxwriter')
row = 0
for key,value in d.items():
a = legcon.iloc[value][legcon.iloc[:,1]<0].nsmallest(3,'Consolidation')
a.to_excel(writer, sheet_name = 'Testt', startrow = row, index = False)
row = row+4
writer.save()
推荐阅读
- linux - “错误 429:请求过多。” 使用 wget 从 Zenodo 下载大文件(15G)时发生
- regex - 从匹配中排除字符串的正则表达式
- vscode-remote - 为 VSCode 远程容器中的转发端口指定监听地址 0.0.0.0
- apache-spark - spark中的memory_only与memory_only_ser
- qt - qt-qml-Qt Creator 使用msvc2017 qDebug 打印文字乱七八糟
- allennlp - allennlp.common.checks.ConfigurationError
- android - android中Loaders的真正用法是什么
- java - 队列的静态工厂方法
- php - 引导 php crud。删除后,我想留在同一个地方
- slack - 当 bot-token 发布到公共 Github 存储库时,Slack-app 已从工作区中删除