Spark: write a CSV with null values as empty columns

Question

I'm using PySpark to write a dataframe to a CSV file like this:

df.write.csv(PATH, nullValue='')

There is a column in that dataframe of type string. Some of the values are null. These null values display like this:

...,"",...

I would like them to be display like this instead:

...,,...

Is this possible with an option in csv.write()?

Thanks!

Is there a need to set nullValue? If you remove the option, does it default to write nothing at all? — absolutelydevastated
– absolutelydevastated, Commented Aug 30, 2019 at 12:28
It does the same thing with and without nullValue. Setting nullValue='' was my first attempt to fix the problem, which didn't work. — Antoine Pietri
– Antoine Pietri, Commented Aug 30, 2019 at 12:29
You can try to do df.fillna('').write.csv(PATH) instead. Basically force all the null columns to be an empty string. — absolutelydevastated
– absolutelydevastated, Commented Aug 30, 2019 at 12:31
I'm not sure this will work, empty strings are also written as "" in the output CSV. — Antoine Pietri
– Antoine Pietri, Commented Aug 30, 2019 at 12:36
Interesting. If your dataframe is not too large, you might want to consider converting it to a Pandas dataframe then do the write. That might work. — absolutelydevastated
– absolutelydevastated, Commented Aug 30, 2019 at 12:37

Kafels · Accepted Answer · 2019-08-30 18:03:13Z

20

Easily with emptyValue option setted

emptyValue: sets the string representation of an empty value. If None is set, it use the default value, "".

from pyspark import Row
from pyspark.shell import spark

df = spark.createDataFrame([
    Row(col_1=None, col_2='20151231', col_3='Hello'),
    Row(col_1=2, col_2='20160101', col_3=None),
    Row(col_1=3, col_2=None, col_3='World')
])

df.write.csv(PATH, header=True, emptyValue='')

Output

col_1,col_2,col_3
,20151231,Hello
2,20160101,
3,,World

answered Aug 30, 2019 at 18:03

Kafels

4,0891 gold badge18 silver badges33 bronze badges

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

2 Comments

newbie Over a year ago

It's strange. When I use df.write.format('csv').option('nullValue', None).save(PATH), it writes empty value. But when I use it like df.write.csv(PATH, nullValue=None), it writes emtpy string value - double quotes

Paul McMillan Over a year ago

I see the same behavior you indicate with setting nullValue, but setting emptyValue as the answer suggests seems to produce the desired output.

Collectives™ on Stack Overflow

Spark: write a CSV with null values as empty columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related