CODEWITHSUNDEEP
pythonexcelpandasappend
Michael
Michael

Reputation: 559

Put all info into one worksheet(excel, python)

# Conbine all the accounts in one sheet
writer = pd.ExcelWriter('toupi-all.xlsx')
for y in duplicate:
    print(y)
    filterYTD=YTD[(YTD["LOGIN"]==y)]
    combine=filterYTD.groupby(["LOGIN"]["EQUITY_CHANGE","TRUE_PROFIT","SWAP","total_cost","cost_to_loss","WITHDRAW",'DEPOSIT',"EMAIL"].sum().sort_values(["EQUITY_CHANGE"],ascending=False).round(2)
    combine=combine.merge(intra,on='LOGIN',how='left')
    combine=combine.merge(tags,on="LOGIN",how='left')
    combine.to_excel(writer)
writer.save()
os.startfile('toupi-all.xlsx')

The logic is not right; just only the last account will appear in the excel; one account is one row. How can I have all the rows in one worksheet? I just dont know how to google this problem; sad

Upvotes: 0

Views: 35

Answers (1)

Toby Petty
Toby Petty

Reputation: 4680

pandas to_excel takes an argument startrow - if you want all data on the same sheet you need to include this in your loop to prevent each iteration from over-writing the previous iteration's data. Also you can use the header parameter to prevent the columns being added to the sheet in each iteration.

Something like this:

writer = pd.ExcelWriter('toupi-all.xlsx')
startrow = 0
for y in duplicate:
    print(y)
    filterYTD=YTD[(YTD["LOGIN"]==y)]
    combine=filterYTD.groupby(["LOGIN"]["EQUITY_CHANGE","TRUE_PROFIT","SWAP","total_cost","cost_to_loss","WITHDRAW",'DEPOSIT',"EMAIL"].sum().sort_values(["EQUITY_CHANGE"],ascending=False).round(2)
    combine=combine.merge(intra,on='LOGIN',how='left')
    combine=combine.merge(tags,on="LOGIN",how='left')
    combine.to_excel(writer, startrow=startrow, header=not bool(startrow))
    startrow += len(combine)

writer.save()
os.startfile('toupi-all.xlsx')

Upvotes: 1

Related Questions