Pandas: Write CSV file with Windows line ending

Question

Example:

import pandas as pd
df = pd.DataFrame({'A':[1,2], 'B':[3,4]})
df.to_csv('test.csv', line_terminator='\r\n')

gives the file

,A,B\r
\r\n
0,1,3\r
\r\n
1,2,4\r
\r\n

however, I would like to have

,A,B\r\n
0,1,3\r\n
1,2,4\r\n

How can I achieve this (i.e., \r\n instead of \r\r\n). My operating system is Windows 10.

Answer 1

The following is from a submission to Kaggle. Hope you'll find it useful. I had to write the output loop myself as pandas insisted on adding both CR and LF and it caused problems for Kaagle.

import io

with io.open('../output/submission_{}.csv'.format('22-Sep-2018-Try-Something_with_re'), 'w', newline='\n') as of:
    of.write('fullVisitorId,PredictedLogRevenue\n')
    for ind in out_log.index:
       of.write('{},{}\n'.format(ind, out_log['PredictedLogRevenue'][ind])) 

The main things to notice is the use of io.open, setting newline='\n' in the open call, and adding '\n' at the end of each write call.