DataFrame.to_csv not using correct line terminator value

[bbibentyo]

Method 1:

        data_frame.to_csv(file_path, sep=self.delimiter, float_format='%.2f',
                                  index=False, line_terminator='\n')

Method 2:

        with open(file_path, mode='w', newline='\n') as f:
            data_frame.to_csv(f, sep=self.delimiter, float_format='%.2f',
                              index=False)

Problem description

I noticed a strange behavior when using pandas.DataFrame.to_csv method on Windows (pandas version 0.20.3). When calling the method using method 1 with a file path, it's creating a new file using the \r line terminator, I had to use method two to make it work.

[gfyoung]

Can you provide as a reproducible example? I suspect that this is a Windows problem of unnecessarily inserting carriage-returns, but can't confirm yet.

[lautjy]

Same problem for me on Windows 10,
Python 3.6.3 :: Anaconda custom (64-bit),
Pandas: 0.20.3

• Method 1 does NOT respect line_terminator='\n' . Resulting CSV file has Windows line-endings \r\n.
• Method 2 works. Resulting CSV file has Unix line-endings. (thanks @bibenb1 )

[deflatSOCO]

I found that this problem is still in v0.23.
Here are some imformations under my Windows environment.

Code Sample, a copy-pastable example if possible

initialization

• code

import pandas as pd
data = pd.DataFrame({
    "integer":[1,2,3],
    "string_with_lf":["abc","d\nef","g\nh\n\ni"],
    "char":["X","Y","Z"]
})
print(data)

• output

   integer string_with_lf char
0        1            abc    X
1        2          d\nef    Y
2        3      g\nh\n\ni    Z

Method 1

• code

data.to_csv("test.csv", sep=",", float_format='%.2f',index=False, line_terminator='\n',encoding='utf-8')
print(pd.read_csv("test.csv"))
print("-------")
with open("test.csv", mode='rb') as f:
    print(f.read())
print(data)

• output

   integer   string_with_lf char
0        1              abc    X
1        2          d\r\nef    Y
2        3  g\r\nh\r\n\r\ni    Z
-------
b'integer,string_with_lf,char\r\n1,abc,X\r\n2,"d\r\nef",Y\r\n3,"g\r\nh\r\n\r\ni",Z\r\n'

Method 2

• code

with open("test2.csv", mode='w', newline='\n') as f:
    data.to_csv(f, sep=",", float_format='%.2f',index=False, line_terminator='\n',encoding='utf-8')
 
print(pd.read_csv("test2.csv"))
print("-------")
with open("test2.csv", mode='rb') as f:
    print(f.read())

• output

   integer string_with_lf char
0        1            abc    X
1        2          d\nef    Y
2        3      g\nh\n\ni    Z
-------
b'integer,string_with_lf,char\n1,abc,X\n2,"d\nef",Y\n3,"g\nh\n\ni",Z\n'

Problem description

As seen in "Method 1" sample, when using to_csv() directly, all \ns (both inside each elements and line terminators) are converted to \r\n, even though line_terminator='\n' is set.

Expected Output

Expect "Method 1" to output the csv described in "Method 2" sample.

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.4.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 142 Stepping 10, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.23.0
pytest: 3.3.2
pip: 10.0.1
setuptools: 38.4.0
Cython: 0.27.3
numpy: 1.14.0
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 6.2.1
sphinx: 1.6.6
patsy: 0.5.0
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.2
openpyxl: 2.4.10
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml: 4.1.1
bs4: 4.6.0
html5lib: 0.9999999
sqlalchemy: 1.2.1
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

Related issues

• to_csv does not always handle line_terminator correctly #17365 (to_csv does not always handle line_terminator correctly)

[gfyoung]

So this is definitely a Windows thing. I ran your samples on a Linux machine, and they produce the same output as your Method 2 in both cases. Thus, it would the case that Windows is quietly inserting carriage returns in the file.

[jreback]

this is a duplicate of #17365 - though these examples are slightly better so can close the other issue

further i suspect we have this issue recorders elsewhere - can u search

[deflatSOCO]

Sorry for late reply.
I'm new to github and this commnity, so I don't know what "issue recorder" means.

[gfyoung]

I'm new to github and this commnity, so I don't know what "issue recorder" means.

@deflatSOCO : Don't worry about that. That was more addressed to me. Actually, he mostly likely meant "recorded" instead of "recorders"

@jreback : There are similar-sounding issues, but they are all either closed or insufficiently overlapping.

[deflatSOCO]

@gfyoung Thanks for reply.
Can I submit PR for this one?

[gfyoung]

@deflatSOCO : Go for it!