Warm tip: This article is reproduced from serverfault.com, please click

remove last empty line from pandas to csv using lineterminator

发布于 2020-11-27 08:28:40

I have a requirement where I have to split some columns as first row and the remaining as second row.

I have store them in one dataframe such as :

columnA   columnB   columnC   columnD
   A         B         C         D

to a text file sample.txt:

A,B
C,D

This is the code :

cleaned_data.iloc[:, 0:1].to_csv("report_csv.txt", encoding='utf-8', index=False, header=False, line_terminator='')
cleaned_data.iloc[:,1:].to_csv("report_csv.txt", encoding='utf-8', index=False, header=False, mode='a', line_terminator='')

It should produce as expected in sample.txt. However, there is third line which is empty and I dont want it to exist. I tried lineterminator='', it does not work for '' but it works such as ' ' or 'abc' etc..

I'm sure there is better way of producing the sample text file than using what I've written. I'm up for other alternative.

Still, how can I remove the last empty line? I'm using python 3.8

Questioner
user6308605
Viewed
0
Ben 2020-11-29 21:53:20

I'm not able to reproduce your issue, but it might be the case that your strings in the dataframe contain trailing line breaks. I'm running Pandas 0.23.4 on linux

import pandas
print(pandas.__version__)

I created what I think your dataframe contains using the command

df = pandas.DataFrame({'colA':['A'], 'colB': ['B'], 'colC':['C'], 'colD':['D']})

To check the contents of a cell, you could use df['colA'][0].

The indexing I needed to grab the first and second columns was

df.iloc[:, 0:2]

and the way I got to a CSV did not rely on lineterminator

df.iloc[:, 0:2].to_csv("report_csv.txt", encoding='utf-8', index=False, header=False)
df.iloc[:,2:].to_csv("report_csv.txt", encoding='utf-8', index=False, header=False, mode='a')

When I run

with open('report_csv.txt','r') as file_handle:
dat = file_handle.read()

I get 'A,B\nC,D\n' from dat.

To get no trailing newline on the last line, use to_string()

with open('output.txt','w') as file_handle:
    file_handle.write(df.iloc[:, 0:2].to_string(header=False,index=False)+"\n")
    file_handle.write(df.iloc[:,2:].to_string(header=False,index=False))

Then we can verify the file is formatted as desired by running

with open('output.txt','r') as file_handle:
    dat = file_handle.read()

The dat contains 'A B\nC D'. If spaces are not an acceptable delimiter, they could be replaced by a , prior to writing to file.