Pandas – Append dataframe to existing CSV

Pandas is a powerful data manipulation library in python. It not only allows you to write to CSV files but you can also append data to an existing CSV file. In this tutorial, we’ll look at how to append a pandas dataframe to an existing CSV file.

Write to CSV in append mode

To append a dataframe row-wise to an existing CSV file, you can write the dataframe to the CSV file in append mode using the pandas to_csv() function. The following is the syntax:

df.to_csv('existing_data.csv', mode='a')

Note that if you do not explicitly specify the mode, the to_csv() function will overwrite the existing CSV file since the default mode is 'w'.

Example

Let’s illustrate the above usage with the help of an example. We have an exiting CSV file storing the marks obtained by some students in an online test. This is how the CSV file looks on opening it in Excel.

The CSV file test_scores.csv has the Name and scores of five students in Maths, Science, and History. Now, let’s create a dataframe with names and marks of students who took the exam at a later date and append it to the existing CSV file of the marks.

import pandas as pd

# data of students and their marks
data = {
    'Name': ['Mike', 'Anita', 'Eric'],
    'Maths': [98, 63, 48],
    'Science': [90, 71, 52],
    'History': [99, 57, 41]
}
# dataframe from dictionary
df = pd.DataFrame(data)
# display the dataframe
print(df)

Output:

    Name  Maths  Science  History
0   Mike     98       90       99
1  Anita     63       71       57
2   Eric     48       52       41

Now that we have the dataframe created, let’s write it to the test_scores.csv file in the append mode.

df.to_csv('test_scores.csv', mode='a', index=False, header=False)

You have to be careful of the index and header when appending data to an existing CSV file. Note that we provided additional arguments index=False and header=False since we did not want the dataframe to be appended with an index and a header. Let’s see how the CSV file looks now by opening it in Excel.

You can see that additional data from the dataframe df has been appended row-wise to the existing CSV file.

Alternatively, you can also read the original CSV as a dataframe, append additional data to it and then write the combined dataframe as a CSV file. Note that writing to a CSV file in append mode is a good way to append rows to an existing CSV file since it doesn’t require you to read the original file as a dataframe into memory.

For more on the pandas to_csv() function, refer to its documentation.

With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having pandas version 1.0.5