For循环：使用pandas将字典迭代输出写入excel？

Question

我试图迭代一个字典，它的值中包含多个行索引，然后应用pd.nsmallest函数为字典中的多组行索引生成前3个最小值。然而，我的for循环语句似乎有点问题，因为我一遍又一遍地覆盖前3个值，直到字典中的最后一组值，所以我的最终excel文件输出在for循环的最后一次运行中只显示了3行

当我使用print语句时，它会按预期工作，我会得到字典中所有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()   

我的输出如下所示，并没有显示所有exp类别：

如果您能提供任何有关如何纠正此问题的反馈，我将不胜感激。提前谢谢

Answer 1

在一位朋友的帮助下，我刚刚意识到我的愚蠢错误，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()