Python Pandas Append Data to Excel: A Step Guide – Python Pandas Tutorial

In this tutorial, we will use an example to show you how to append data to excel using python pandas library.

In order to append data to excel, we should notice two steps:

• How to read data from excel using python pandas
• How to write data (python dictionary) to excel correctly

We will introduce these two steps in detail.

What data we will append?

We will append a python dictionary, here is an example:

data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42],'City':['wuhan','chongqin','beijing','shanghai']}

Here Name, Age and City is the data header.

Then we can use this dictionary to create a DataFrame object to save.

df = pd.DataFrame(data, index= None)

In order to append data to excel, we should read an excel file to get original data, then append our data and save.

How to read excel data and append?

We can use example code below to read and append.

        df_source = None
        if os.path.exists(excel_name):
            df_source = pd.DataFrame(pd.read_excel(excel_name, sheet_name=sheet_name))
        if df_source is not None:
            df_dest = df_source.append(df)
        else:
            df_dest = df

Then we can use to_excel() function to save data to excel.

df_dest.to_excel(writer, sheet_name=sheet_name, index = False, columns=columns)

The full code is below:

import  pandas  as pd
import os
#data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42]}
def append_data_to_excel(excel_name, sheet_name, data):

    with pd.ExcelWriter(excel_name) as writer:
        columns = []
        for k, v in data.items():
            columns.append(k)

        df = pd.DataFrame(data, index= None)

        df_source = None
        if os.path.exists(excel_name):
            df_source = pd.DataFrame(pd.read_excel(excel_name, sheet_name=sheet_name))
        if df_source is not None:
            df_dest = df_source.append(df)
        else:
            df_dest = df

        df_dest.to_excel(writer, sheet_name=sheet_name, index = False, columns=columns)

data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42],'City':['wuhan','chongqin','beijing','shanghai']}
append_data_to_excel('test.xlsx', 'person',data)
data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[29,34,29,42]}
append_data_to_excel('test.xlsx', 'person',data)
append_data_to_excel('test.xlsx', 'person',data)

Run append_data_to_excel() function, we can append our data to existent excel file.