How to append data in excel using Python

To work with Ms Excel and Python seamlessly we should know how to append data in excel using Python . For this reason we will take two .xlsx file (named demo1.xlsx and demo2.xlsx) which will look like below –

Now we will append data of demo2.xlsx after data of demo1.xlsx . We will do this in two ways –

Append data in excel using Python by pd.concat() method

import pandas as pd

# Reading the demo1.xlsx
df1=pd.read_excel("demo1.xlsx")

# reading the demo2.xlsx
df2=pd.read_excel("demo2.xlsx")

# appending df2 after df1
# df3=df1.append(df2)  
# append method will be deprecated in future version
# so we will use concat method
df3=pd.concat([df1,df2])

# creating a new excel file and save the data
df3.to_excel("demo3.xlsx",index=False)

In the first way we will load both the file in the program as pandas dataframe as df1 and df2 . Then we will create df3 from df1 and df2 using pd.concat() method. After that we will create a new excel file demo3.xlsx and put all the data of df3 into that . Below is the image of demo3.xlsx –

Note

We can also use append() method to append df2 after df1 . But this method will be deprecated in future . So , we should always use pd.concat() method instead of append() method (Recommended by pandas) .

Suppose we have a master excel file in which we have to append data in daily basis . After appending data in daily basis the size of the master file becomes very big . So, it is not feasible to load that huge amount of data every time in pandas dataframe just to append some small amount of data . So we will solve this problem using openpyxl module of Python .

Append data in excel using Python openpyxl

import pandas as pd

# read the demo2.xlsx file
df=pd.read_excel("demo2.xlsx")

# appending the data of df after the data of demo1.xlsx
with pd.ExcelWriter("demo1.xlsx",mode="a",engine="openpyxl",if_sheet_exists="overlay") as writer:
    df.to_excel(writer, sheet_name="Sheet1",header=None, startrow=writer.sheets["Sheet1"].max_row,index=False)

• In the above code first we have imported pandas library and read the demo2.xlsx file as df .
• Then we need to create an ExcelWriter object which will open the demo1.xlsx file in append mode (mode="a") . We have used openpyxl as engine and overlay as if_sheet_exists . By default value of if_sheet_exists is error (which will give ValueError if the sheet exists) . Other values are new (will create a new sheet , name will be determined by the engine) , replace (will delete the old content before writing to the sheet) and overlay (will append the data in the existing sheet without removing the old data)
• After that by to_excel() method we have appended data of demo2.xlsx after the data of demo1.xlsx in the demo1.xlsx file . Here we have used writer.sheets["Sheet1"].max_row as startrow which will automatically calculate the length of previous content .

We can also see the image of new demo1.xlsx for reference –

Note

We also need to install openpyxl to use the above example .To install openpyxl open PowerShell window , type the below code and press Enter key –

pip install openpyxl

Version of pandas should be greater than or equal to 1.4.0 for the above example .

Conclusion

In the first way we need to load data of both the file in memory but in the second way we only need to load the data of that file which will be appended . So second method is more efficient as it takes less memory .

Thank you for reading this Article . If You enjoy it Please Share the article . If you want to say something Please Comment .