CODEWITHSUNDEEP
pythonpandascsvappend
Shaoyi
Shaoyi

Reputation: 1

Pandas - appending to existing csv file problem

I am collecting data by using API, and use pandas.to_csv() function by setting as following:

path, mode='a',header=False, index=False'

However, the result is quite weird. The first row collected by API is inserted to other columns instead of the columns I have created.

How to solve this problem? enter image description here

Upvotes: 0

Views: 1618

Answers (1)

mozway
mozway

Reputation: 262484

The file in which you wrote your output did not have a newline character (\n) at the end of the last line. Thus, the following data is added at the end of this line.

Here is an example:

with open('/tmp/file.csv', 'w') as f:
    f.write('1,2')
pd.DataFrame([[3,4],[5,6]]).to_csv('/tmp/file.csv', mode='a', header=0, index=False)

output:

1,23,4
5,6

You either need to ensure that the first write appends a newline at the end of the last line, or you can fix your file using:

with open('/tmp/file.csv', 'a') as f:
    f.write('\n')

right before you to_csv export.

output:

1,2
3,4
5,6

Here is a smarter way to fix your file, it will check if the last character of the file is \n and if not will append one:

with open('/tmp/file.csv', 'r+') as f: # open file as read/append
    f.seek(0, 2)          # go to end of file
    f.seek(f.tell()-1, 0) # come back one chacacter
    if f.read() != '\n':  # if last character is not \n
        f.write('\n')     # then append \n

Upvotes: 1

Related Questions