Pandas Append to CSV

Python Pandas allows us to manipulate and manage data efficiently. We can create and manage DataFrames and perform various operations on them. It also allows us to read an external CSV or excel file, import DataFrames, work on them, and save them back. One interesting feature in saving data is the append mode using the parameter a, which can be used to append data to an already existing CSV file.

This article will introduce how to append data to CSV using Pandas.

import pandas as pd

df = pd.DataFrame([[6,7,8],
                   [9,12,14],
                   [8,10,6]], columns = ['a','b','c'])

print(df)

df.to_csv(r"C:\Test\data.csv", index = False)

df2 = pd.DataFrame([[4,1,3],
                   [6,7,2],
                   [5,9,4]], columns = ['a','b','c'])

print(df2)

df2.to_csv(r"C:\Test\data.csv", mode = 'a', header = False, index = False)

Output:

   a   b   c
0  6   7   8
1  9  12  14
2  8  10   6
   a  b  c
0  4  1  3
1  6  7  2
2  5  9  4

pandas append to csv.png

The saved CSV file will have both the DataFrames with the data of the df2 appended to the original file.

There is also another additional feature that we can add to this code. With just a few code lines, we can ensure that the to_csv() function will create a file if it doesn’t exist and skip headers if it already exists. We will use the with statement for the exception handling and open() to open a file.

import pandas as pd

df = pd.DataFrame([[6,7,8],
                   [9,12,14],
                   [8,10,6]], columns = ['a','b','c'])

print(df)

df.to_csv(r"C:\Test\data.csv", index = False)

df2 = pd.DataFrame([[4,1,3],
                   [6,7,2],
                   [5,9,4]], columns = ['a','b','c'])

print(df2)

with open(r"C:\Test\data.csv", mode = 'a') as f:
    df2.to_csv(f, header=f.tell()==0,index = False)

Output:

   a   b   c
0  6   7   8
1  9  12  14
2  8  10   6
   a  b  c
0  4  1  3
1  6  7  2
2  5  9  4

The tell() method of the file object returns the current cursor position. Therefore, if the file is empty or doesn’t exist, f.tell==0 is True, so that header is set to True; otherwise, header is set to False.