447

I want to know if it is possible to use the pandas to_csv() function to add a dataframe to an existing csv file. The csv file has the same structure as the loaded data.

JJJ
  • 1,009
  • 6
  • 19
  • 31
Ayoub Ennassiri
  • 4,606
  • 3
  • 13
  • 9
  • 7
    I think method suggested by @tlingf is better only because he is using build-in functionality of pandas library. He suggests define mode as "a" . "A" stands for APPEND 'df.to_csv('my_csv.csv', mode='a', header=False)' – Ayrat Oct 20 '14 at 13:14
  • 3
    The answer from @KCzar considers both the cases when the CSV file is not there (i.e. add the column header) and when the CSV is already there (so add just the data rows without headers). In any case it uses the "append" mode and a custom separator, along with checks on the number of columns. – TPPZ Apr 17 '19 at 08:46

7 Answers7

945

You can specify a python write mode in the pandas to_csv function. For append it is 'a'.

In your case:

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

The default mode is 'w'.

If the file initially might be missing, you can make sure the header is printed at the first write using this variation:

output_path='my_csv.csv'
df.to_csv(output_path, mode='a', header=not os.path.exists(output_path))
Antonio
  • 19,451
  • 13
  • 99
  • 197
tlingf
  • 9,574
  • 2
  • 13
  • 4
  • 9
    Thanks for the answer. This will allow me append new df on row-wise. But could you let me know how can I append the new df on column-wise? – datanew Nov 09 '18 at 21:30
  • 1
    I was able to accomplish it by re-read the 'my_csv.csv', then concat the new df, and then save it. If you know some easier method, please DO let me know. I appreciate! – datanew Nov 09 '18 at 21:56
  • 17
    How to write header for the first file and rest of the rows gets automatically appended to it? – Etisha Feb 10 '20 at 06:35
  • 72
    @Etisha something like `df.to_csv(output_path, mode='a', header=not os.path.exists(output_path))` – Michele Tonutti May 20 '20 at 08:25
  • 10
    Correct answer, of course, just a note: passing `index=False` will tell `df.to_csv` not to write the row index to the first column. Depending on the application, this might make sense to avoid a meaningless index column. – user35915 Aug 04 '20 at 19:42
  • Without header=False, it tries to squeeze in the added rows' header between the old and the new data in the csv. – Henrik Sep 08 '20 at 19:50
283

You can append to a csv by opening the file in append mode:

with open('my_csv.csv', 'a') as f:
    df.to_csv(f, header=False)

If this was your csv, foo.csv:

,A,B,C
0,1,2,3
1,4,5,6

If you read that and then append, for example, df + 6:

In [1]: df = pd.read_csv('foo.csv', index_col=0)

In [2]: df
Out[2]:
   A  B  C
0  1  2  3
1  4  5  6

In [3]: df + 6
Out[3]:
    A   B   C
0   7   8   9
1  10  11  12

In [4]: with open('foo.csv', 'a') as f:
             (df + 6).to_csv(f, header=False)

foo.csv becomes:

,A,B,C
0,1,2,3
1,4,5,6
0,7,8,9
1,10,11,12
Andy Hayden
  • 359,921
  • 101
  • 625
  • 535
100
with open(filename, 'a') as f:
    df.to_csv(f, header=f.tell()==0)
  • Create file unless exists, otherwise append
  • Add header if file is being created, otherwise skip it
SpiralDev
  • 7,011
  • 5
  • 28
  • 42
  • 3
    It's missing a `mode='a'` as a parameter to `to_csv` (ie `df.to_csv(f, mode='a', header=f.tell()==0)` – Gabriela Melo Dec 09 '19 at 23:08
  • 3
    @GabrielaMelo That was passed in the function open(filename, 'a'). – Piyush Mar 04 '20 at 21:08
  • 5
    I get an extra blank line between every line of data (on Windows, which I guess is vulnerable to that) unless I add some parentheses: `header=(f.tell()==0)` -- and also write : `with open(filename, 'a', newline='') as f:` – David Kaufman Apr 13 '21 at 22:57
25

A little helper function I use with some header checking safeguards to handle it all:

def appendDFToCSV_void(df, csvFilePath, sep=","):
    import os
    if not os.path.isfile(csvFilePath):
        df.to_csv(csvFilePath, mode='a', index=False, sep=sep)
    elif len(df.columns) != len(pd.read_csv(csvFilePath, nrows=1, sep=sep).columns):
        raise Exception("Columns do not match!! Dataframe has " + str(len(df.columns)) + " columns. CSV file has " + str(len(pd.read_csv(csvFilePath, nrows=1, sep=sep).columns)) + " columns.")
    elif not (df.columns == pd.read_csv(csvFilePath, nrows=1, sep=sep).columns).all():
        raise Exception("Columns and column order of dataframe and csv file do not match!!")
    else:
        df.to_csv(csvFilePath, mode='a', index=False, sep=sep, header=False)
KCzar
  • 1,024
  • 1
  • 9
  • 11
6

Initially starting with a pyspark dataframes - I got type conversion errors (when converting to pandas df's and then appending to csv) given the schema/column types in my pyspark dataframes

Solved the problem by forcing all columns in each df to be of type string and then appending this to csv as follows:

with open('testAppend.csv', 'a') as f:
    df2.toPandas().astype(str).to_csv(f, header=False)
Grant Shannon
  • 4,709
  • 1
  • 46
  • 36
2

This is how I did it in 2021

Let us say I have a csv sales.csv which has the following data in it:

sales.csv:

Order Name,Price,Qty
oil,200,2
butter,180,10

and to add more rows I can load them in a data frame and append it to the csv like this:

import pandas

data = [
    ['matchstick', '60', '11'],
    ['cookies', '10', '120']
]
dataframe = pandas.DataFrame(data)
dataframe.to_csv("sales.csv", index=False, mode='a', header=False)

and the output will be:

Order Name,Price,Qty
oil,200,2
butter,180,10
matchstick,60,11
cookies,10,120
Ahtisham
  • 9,170
  • 4
  • 43
  • 57
0

A bit late to the party but you can also use a context manager, if you're opening and closing your file multiple times, or logging data, statistics, etc.

from contextlib import contextmanager
import pandas as pd
@contextmanager
def open_file(path, mode):
     file_to=open(path,mode)
     yield file_to
     file_to.close()


##later
saved_df=pd.DataFrame(data)
with open_file('yourcsv.csv','r') as infile:
      saved_df.to_csv('yourcsv.csv',mode='a',header=False)`
ai-shwarya
  • 170
  • 1
  • 4