disable default na parsing in pandas read_csv only for specific column

user_8356294 0
Viewed 397

I need to read a csv file as pandas dataframe but keep certain column exactly as it is without filling in NaN.

from io import StringIO
import pandas as pd

main_ts_str = """key,dt,value
5013,2020-06-19,NULL
NA,2020-06-18,25.5
"""
df = pd.read_csv(StringIO(main_ts_str))

This code will give me the following

      key          dt  value
0  5013.0  2020-06-19    NaN
1     NaN  2020-06-18   25.5

What I want is for the 'key' column to keep the original value while let the 'value' column be filled with NaN. (Something like the dataframe below)

    key          dt value
0  5013  2020-06-19   NaN
1    NA  2020-06-18  25.5

I know I can disable the NA parsing by setting keep_default_na but this will disable na parsing for all columns.

There is also a way to use na_values to specify column specific NA strings. But my issue is I have many of these 'value' columns that I do not know their column names ahead of time. The only thing I know before I read in the file is the file has a 'key' column and I want to load anything in this column as it is.

Any suggestion would be highly appreciated.

pandas dataframe python
1 Answers

You can use the converters parameter from read_csv and pass a dictionary for converting values only in certain columns (key column in this specific example).

df = pd.read_csv(StringIO(main_ts_str), converters={'key':str})

print(df)
    key          dt  value
0  5013  2020-06-19    NaN
1    NA  2020-06-18   25.5

print(df.info())
...
 0   key     2 non-null      object
 1   dt      2 non-null      object
 2   value   1 non-null      float64
user_131441430
Related