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.
Try this:
df.to_csv('test.csv', sep='\t', header=None, mode='a')
# NOTE: -----> ^^^^^^^^
, separator and then you are adding new entries using \tTL: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:
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.
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()
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
df.to_csv('old_file.csv', header=None, mode='a') should do the trick