python add extra column to existing csv and fill the column with empty values

Question

I have this CSV:

A	B
1	3
2	4

I would like to add a 2 new headers and for each existing row add an empty value for this new column:

"column1"	col2	New1	New 2
"timestamp"	"N/A"
"N/A"	"string element"
2	"string"

The flat CSV is comma separated:

"column1",col2
"timestamp","N/A"
"N/A","string element"
2,"string"

I tried this but it removes all the double quotes and the "N/A" values

import pandas as pd
df = pd.read_csv(fileName)
df["New1"] = ""
outcsv=fileName
df.to_csv(outcsv, index=False)

The output is:

column1,col2,New1
timestamp,
,string element
2,string

but I would need this output:

"column1",col2,New1,New2
"timestamp","N/A",,
"N/A","string element",,
2,"string",,

Mabbus · Accepted Answer · 2021-08-06 11:05:33Z

2

This code gives the desired output.

keep_default_na keeps the N/A. quoting = csv.QUOTE_NONE keeps the quotes.

import pandas as pd
import csv as csv
fileName = "a.txt"
df = pd.read_csv(fileName,keep_default_na=False, quoting=csv.QUOTE_NONE)
df["New1"] = ""
df["New2"] = ""
outcsv = fileName
df.to_csv(outcsv, index=False,quoting=csv.QUOTE_NONE)

answered Aug 6, 2021 at 11:05

Mabbus

722 silver badges11 bronze badges

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

2 Comments

suvayu Over a year ago

Nicely copy pasted from my answer, and doing the last bit of work, without giving any credit!

Mabbus Over a year ago

Sorry. But I didn´t read your answer until after writing my own answer. I guess we just wrote the same thing since it´s the way to do it.

suvayu · Accepted Answer · 2021-08-06 11:02:48Z

2

You need to pass keep_default_na=False, otherwise the "N/A"s in the existing CSV gets recognised as NaN, which is then written as the empty string when you write out the new file. Try this:

df = pd.read_csv(fileName, keep_default_na=False)
# do your thing
df.to_csv(fileName)

For more details, see the docs, particularly, read the options keep_default_na and na_values.

EDIT: to respond to the comment about quoting, I think it's a bit harder, because it seems to me the options are either you quote minimally, or quote everything, which is contrary to the requirement of having the new columns as empty string while retaining the original quotes. I would play around with the quoting option in DataFrame.to_csv.

edited Aug 6, 2021 at 11:02

answered Aug 6, 2021 at 10:42

suvayu

4,7143 gold badges33 silver badges40 bronze badges

2 Comments

ClaudioM Over a year ago

thanks @suvayu it works. how can I keep also the double quotes in the output?

suvayu Over a year ago

@ClaudioM That's a bit more difficult I think, see my edit

Collectives™ on Stack Overflow

python add extra column to existing csv and fill the column with empty values

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related