how to append columns in existing excel sheet using panda in python

Question

import pandas as pd

from pandas import ExcelWriter

trans=pd.read_csv('HMIS-DICR-2011-12-Manipur-Bishnupur.csv')
df=trans[["April 10-11","May 10-11","June 10-11","July 10-11","August 10-11","September 10-11","October 10-11","November 10-11","December 10-11","January 10-11","February 10-11","March 10-11","April 11-12","May 11-12","June 11-12","July 11-12","August 11-12","September 11-12","October 11-12","November 11-12","December 11-12","January 11-12","February 11-12","March 11-12"]]
writer1 = ExcelWriter('manipur1.xlsx')
df.to_excel(writer1,'Sheet1',index=False)
writer1.save()

this code successfully writes the data in a sheet 1 but how can append data of another data frame(df) from different excel file(mention below) into existing sheet(sheet1) "manipur1" excel file

for example: my data frame is like:

 trans=pd.read_csv('HMIS-DICR-2013-2014-Manipur-Bishnupur.csv')
    df=trans[["April 12-13","May 12-13","June 12-13","July 12-13","August 12-13","September 12-13","October 12-13","November 12-13","December 12-13","January 12-13","February 12-13","March 12-13","April 13-14","May 13-14","June 13-14","July 13-14","August 13-14","September 13-14","October 13-14","November 13-14","December 13-14","January 13-14","February 13-14","March 13-14"]]

Solution

You can only append new data to an existing excel file while loading the existing data into pandas, appending the new data, and saving the concatenated data frame again.

To preserve existing sheets which are supposed to remain unchanged, you need to iterate over the entire workbook and handle each sheet. Sheets to be changed and appended are defined in the to_update dictionary.

# get data to be appended
trans=pd.read_csv('HMIS-DICR-2011-12-Manipur-Bishnupur.csv')
df_append = trans[["April 12-13","May 12-13","June 12-13","July 12-13","August 12-13","September 12-13","October 12-13","November 12-13","December 12-13","January 12-13","February 12-13","March 12-13","April 13-14","May 13-14","June 13-14","July 13-14","August 13-14","September 13-14","October 13-14","November 13-14","December 13-14","January 13-14","February 13-14","March 13-14"]]

# define what sheets to update
to_update = {"Sheet1": df_append}

# load existing data
file_name = 'manipur1.xlsx'
excel_reader = pd.ExcelFile(file_name)

# write and update
excel_writer = pd.ExcelWriter(file_name)

for sheet in excel_reader.sheet_names:
    sheet_df = excel_reader.parse(sheet)
    append_df = to_update.get(sheet)

    if append_df is not None:
        sheet_df = pd.concat([sheet_df, append_df], axis=1)

    sheet_df.to_excel(excel_writer, sheet, index=False)

excel_writer.save()

However, any layouting/formatting in your existing excel will be lost. You can use openpyxl if you want to retain the formatting but this is more complicated.

Comments

[kumud]

In 1 workbook i have 21 sheets and after execution only sheet 1 left rest are deleted..concat perform like a join function. Rows are also appending for example : df_append have 452*24 rowscol and df_temp have 452*12 rowcol so result of concat must be 452*36 and this code gives 904*36..