0

I would like this code to iterate through my df and write out each loop to a new worksheet inside 1 single workbook. Currently it writes out only the last iteration. It seems to be copying over the worksheet each time vs adding a new sheet. Any help would be so appreciated

from pandas import Series, ExcelWriter
outputDF = pd.DataFrame(columns=['INDEX NAME' , 'TICKER' ,'ANALYST', 'PROFORMA INDEX SHARES' , 'PRICE' , 'MRKT VALUE' , 'WEIGHT'])

firstRow = False
rowcount = 0

for (i, row) in final.iterrows():
    
    rowcount = rowcount + 1
    Index = final.loc[i,'INDEX NAME']
    Ticker = final.loc[i,'TICKER']
    Analyst = final.loc[i,'ANALYST']
    Shares = final.loc[i,'Proforma INDEX SHARES']
    Price = final.loc[i,'PRICE']
    MrktVal = final.loc[i,'MRKT VALUE']
    Weight = final.loc[i,'index_wgt']
    
    if firstRow == False:   
        oldanalyst = Analyst
        firstRow = True
        
        
    if oldanalyst != Analyst or rowcount == len(final):
        if rowcount == len(final):
            # append to outputDF. Just give values to columns with data, othwise it will be blank.
            outputDF = outputDF.append({'INDEX NAME':Index, 'TICKER':Ticker, 'ANALYST':Analyst, 'PROFORMA 
        INDEX SHARES':Shares,'PRICE':Price, 'MRKT VALUE':MrktVal,'WEIGHT':Weight}, ignore_index=True)
            
        #Write out excel file, at last line of unique analyst
        filename =  r'C:\Users\Documents\Work\Weight.xlsx'
        ExcelWriter = pd.ExcelWriter(filename, engine = 'xlsxwriter')
        outputDF.to_excel(ExcelWriter, sheet_name=oldanalyst, index=False)
        workbook = ExcelWriter.book
        worksheet1 = ExcelWriter.sheets[oldanalyst]
        format2 = workbook.add_format({'num_format': '0.00%'})
        worksheet1.set_column('A:C', 22)
        worksheet1.set_column('D:D', 22, frmat2)
        worksheet1.set_column('I:I', 22, format2)
        #(row,column 0 based index)
        #worksheet1.freeze_panes(1,3)
        ExcelWriter.save()
        
        #Clear dataframe
        outputDF = outputDF[0:0]
        
  
        #Set oldanalyst = Analyst
        oldanalyst = Analyst
    
        
    #append to outputDF. Just give values to columns with data, othwise it will be blank.
    outputDF = outputDF.append({'INDEX NAME':Index, 'TICKER':Ticker, 'ANALYST':Analyst, 'PROFORMA INDEX 
    SHARES':Shares,'PRICE':Price, 'MRKT VALUE':MrktVal,'WEIGHT':Weight}, ignore_index=True)

    #print(rowcount)
Ashley Fay
  • 1
  • 1
  • maybe it works like normal `open()` which removes old content when you open for writing and it needs `append mode` to keep previous content. – furas Sep 11 '20 at 03:40
  • see [How to save a new sheet in an existing excel file, using Pandas?](https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas) – furas Sep 11 '20 at 03:43
  • you have to use `pd.ExcelWriter()` before loop, `to_excel()` inside `for`-loop, and `ExcelWriter.save()` after `for`-loop. – furas Sep 11 '20 at 03:45
  • Thank you! I greatly appreciate that guidance. It now works. – Ashley Fay Sep 11 '20 at 13:46

0 Answers0