Pandas: parse CSV differentiating between the missing value sentinel and a string that happens to equal it

user_1319998 0
Viewed 92

The R readr read_csv function allows passing quoted_na=FALSE, which allows the parser to distinguish between the unquoted NA meaning a missing value, and a quoted "NA" meaning a string that happens to equal NA.

Is there a way for the read_csv function from Python Pandas to have similar behaviour?

For example, the CSV

var_1,var_2
"NA",NA

I would like to ideally be parsed as

    var_1  var_2
0      NA    NaN

The argument na_values does not appear to be relevant: it appears to be applied to the strings after CSV parsing that removes the quotes.

For example, something like the below seems to still not be able to distinguish the two cases:

pd.read_csv('na.txt', na_values=['NA'], keep_default_na=False)

as in, both values are parsed as NaN.

pandas readr csv r python
1 Answers

You can try this. Using pd.read_csv with parameter quoting=3.

#test.csv
A,B,C,D
NA,1,2,3
"NA",NA,4,5
6,7,NA,"NA"

 df = pd.read_csv(r'test.csv',quoting=3,na_values=['NA'],keep_default_na=False)

     A    B    C     D
0   NaN  1.0  2.0     3
1  "NA"  NaN  4.0     5
2     6  7.0  NaN  "NA"

Note :

If keep_default_na is False, and na_values are specified, only the NaN values specified na_values are used for parsing.

user_124164530
Related