Writing a pandas DataFrame into a csv file with some empty rows

Question

I create a one-column pandas DataFrame that contains only strings. One row is empty. When I write the file on disk, the empty row gets an empty quote "" while I want no quote at all. Here's how to replicate the issue:

import pandas as pd
df = "Name=Test\n\n[Actual Values]\nLength=12\n"
df = pd.DataFrame(df.split("\n"))
df.to_csv("C:/Users/Max/Desktop/Test.txt", header=False, index=False)

The output file should be like this:

Name=Test
[Actual Values]

Length=12

But instead is like this:

Name=Test
[Actual Values]
""
Length=12

Is there a way to instruct pandas not to write the quotes and leaves an empty row in the output text file? Thank you, a lot.

I would have thought that quoting=2 would have done this... but it doesn't seem to... get a Error: single empty field record must be quoted, interesting. — Andy Hayden
– Andy Hayden, Commented Dec 18, 2013 at 19:07

aensm · Accepted Answer · 2013-12-18 21:39:03Z

2

There is a parameter for DataFrame.to_csv called na_rep. If you have None values, it will replace them with whatever you pass into this field.

import pandas as pd
df = "Name=Test\n"
df += "\n[Actual Values]\n"
df += "Length=12\n"
df = pd.DataFrame(df.split("\n"))
df[df[0]==""] = None
df.to_csv("pandas_test.txt", header=False, index=False, na_rep=" ")

Unfortunately, it looks like passing in na_rep="" will print quotes into the csv. However, if you pass in a single space (na_rep=" ") it looks better aesthetically...

Of course you could always write your own function to output a csv, or simply replace the "" in the output file using:

f = open(filename, 'r')
text = f.read()
f.close()
text = text.replace("\"\"","")
f = open(filename, 'w')
f.write(text)
f.close()

And here's how you could write your own to_csv() method:

def to_csv(df, filename, separator):
    f = open(filename, 'w')
    for col in df.values:
        for row in col:
            f.write(row + separator)
    f.close()

edited Dec 18, 2013 at 21:39

answered Dec 18, 2013 at 20:58

aensm

3,65710 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1242808 Over a year ago

Actually the way you do it replaces the "" quotes by a blank space. What I need is no blank space at all, just an empty row (sorry if it was unclear!).

300 Over a year ago

Hi @user1242808. Did find a way to do above? (What I need is no blank space at all, just an empty row) I am trying to exactly same and found this thread but has no answer here.

Collectives™ on Stack Overflow

Writing a pandas DataFrame into a csv file with some empty rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related