479

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.

2
  • 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)' Commented Oct 20, 2014 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. Commented Apr 17, 2019 at 8:46

7 Answers 7

1000

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))
Sign up to request clarification or add additional context in comments.

6 Comments

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?
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!
How to write header for the first file and rest of the rows gets automatically appended to it?
@Etisha something like df.to_csv(output_path, mode='a', header=not os.path.exists(output_path))
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.
|
284

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

2 Comments

Thou it is not harmful but I don't think you need a context manager for using to_csv() method.
Do we really need with open('my_csv.csv', 'a') as f:??
102
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

3 Comments

It's missing a mode='a' as a parameter to to_csv (ie df.to_csv(f, mode='a', header=f.tell()==0)
@GabrielaMelo That was passed in the function open(filename, 'a').
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:
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)

2 Comments

What could we do if the column order does not match?
@JasonGoal df = df.reindex(sorted(df.columns), axis=1); see stackoverflow.com/a/11067072/9095840.
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)

Comments

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

2 Comments

I'm not able to find the added value here over stackoverflow.com/a/17975690/3429115
It does not add the pandas file to existing csv .
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)`

2 Comments

what's the benefit of using a context manager here?
how is this any different from using open as a context manager?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.