How to append new dataframe rows to a csv using pandas?

Question

I have a new dataframe, how to append it to an existed csv?

I tried the following code:

f = open('test.csv', 'w')
df.to_csv(f, sep='\t')
f.close()

But it doesn't append anything to test.csv. The csv is big, I only want to use append, rather than read the whole csv as dataframe and concatenate it to and write it to a new csv. Is there any good method to solve the problem? Thanks.

Answer 1

To append a pandas dataframe in a csv file, you can also try it.

df = pd.DataFrame({'Time':x, 'Value':y})
with open('CSVFileName.csv', 'a+', newline='') as f:
    df.to_csv(f, index=False, encoding='utf-8', mode='a')
    f.close()

Answer 2

TL:DR Answer from MaxU is correct.

df.to_csv('old_file.csv', header=None, mode='a')

I had the same problem, wishing to append to DataFrame and save to a CSV inside a loop. It seems to be a common pattern. My criteria was:

1. Write back to the same file
2. Don't write data more than necessary.
3. Keep appending new data to the dataframe during the loop.
4. Save on each iteration (in case long running loop crashes)
5. Don't store index in the CSV file.

Note the different values of mode and header. In a complete write, mode='w' and header=True, but in an append, mode='a' and header='False'.

import pandas as pd

# Create a CSV test file with 3 rows
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
test_df = pd.DataFrame(data, columns = ['Name', 'Age']) 
test_df.to_csv('test.csv', mode='w', header=True, index=False)

# Read CSV into a new frame
df = pd.read_csv('test.csv')
print(df)

# MAIN LOOP
# Create new data in a new DataFrame
for i in range(0, 2):
    newdata = [['jack', i], ['jill', i]] 
    new_df  = pd.DataFrame(newdata, columns = ['Name', 'Age']) 

    # Write the new data to the CSV file in append mode
    new_df.to_csv('test.csv', mode='a', header=False, index=False)
    print('check test.csv')

    # Combine the new data into the frame ready for the next loop.
    test_df = pd.concat([test_df, new_df], ignore_index=True)

# At completion, it shouldn't be necessary, but to write the complete data 
test_df.to_csv('completed.csv', mode='w', header=True, index=False)
# completed.csv and test.csv should be identical.

Answer 3

try the following code, it will generate an old file(10 rows) and new file(2 rows) in your local folder. After I append, the new content all mix up:

import pandas as pd
import os 

dir_path = os.path.dirname(os.path.realpath("__file__"))
print(dir_path)

raw_data = {'HOUR': [4, 9, 12, 7, 3, 15, 2, 16, 3, 21], 
        'LOCATION': ['CA', 'HI', 'CA', 'IN', 'MA', 'OH', 'OH', 'MN', 'NV', 'NJ'], 
        'TYPE': ['OLD', 'OLD', 'OLD', 'OLD', 'OLD', 'OLD', 'OLD', 'OLD', 'OLD', 'OLD'], 
        'PRICE': [4, 24, 31, 2, 3, 25, 94, 57, 62, 70]}
old_file = pd.DataFrame(raw_data, columns = ['HOUR', 'LOCATION', 'TYPE', 'PRICE'])
old_file.to_csv(dir_path+"/old_file.csv",index=False)


raw_data = {'HOUR': [2, 22], 
        'LOCATION': ['CA', 'MN'], 
        'TYPE': ['NEW', 'NEW'], 
        'PRICE': [80, 90]}
new_file = pd.DataFrame(raw_data, columns = ['HOUR', 'LOCATION', 'TYPE', 'PRICE'])
new_file.to_csv(dir_path+"/new_file.csv",index=False)


new_file=dir_path+"/new_file.csv"
df=pd.read_csv(new_file)
df.to_csv('old_file.csv', sep='\t', header=None, mode='a')

it will come to:

HOUR    LOCATION    TYPE    PRICE
4   CA  OLD 4
9   HI  OLD 24
12  CA  OLD 31
7   IN  OLD 2
3   MA  OLD 3
15  OH  OLD 25
2   OH  OLD 94
16  MN  OLD 57
3   NV  OLD 62
21  NJ  OLD 70
02CANEW80           
122MNNEW90  

Answer 4

Try this:

df.to_csv('test.csv', sep='\t', header=None, mode='a')
# NOTE:                              ----->  ^^^^^^^^