CODEWITHSUNDEEP
pythonpandascsvexport-to-csv
LN_P
LN_P

Reputation: 1488

Pandas df to csv inserts blank lines

I have a pandas data frame 'data' that I want to export to csv. It works fine, but some lines are completely messed up. I use: data.to_csv('data.csv')

I tried altering the encoding and dropping index, but it didn't work. The first picture are the correct rows, and the second one the rows that got messed up. When looking at the messed up rows i figured the problem might be that the text data uses the delimiter \r to separate lines and is exported incorrectly. How can I fix this?

what it looks like for correct rows

what the incorrect rows look like

Thanks!

Upvotes: 5

Views: 6091

Answers (3)

Muhammad Yasirroni
Muhammad Yasirroni

Reputation: 2137

For those that came here because of looping df.to_csv inside context manager, use lineterminator to fix it.

import pandas as pd
import numpy as np


with open('report_.csv', 'w') as f:
    for _ in range(4):  # loop here
        df = pd.DataFrame(np.random.random((3,4)))

        df.to_csv(f,
                  index=True,  # to include index
                  header=True,  # to include header
                  lineterminator='\n',  # to remove whitespace between row
                 )
        f.write('\n')  # remove this to delete whitespace between DataFrame

From my original answer here

Upvotes: 0

GeneW
GeneW

Reputation: 21

Just change the line terminator:

data.to_csv('data.csv',line_terminator = '\r')

Upvotes: 2

pissall
pissall

Reputation: 7399

Change the separator to \t or | or something other than ,. You have commas in your strings, which are messing up your comma separation.

Note that your csv's are nothing but text files separating elements using commas. If you have commas in uneven places, it's gonna make extra separation and hence, going to mess up your file.

Upvotes: -1

Related Questions