Replace question mark in pandas dataframe

user_10971797 0
Viewed 3326

When reading dataframes from a source like a csv file, literals like '?' are shown for blank values in pandas. enter image description here

If this is a numerical column and you try replacing with mean like df['weight'].replace('?',df['weight'].mean() ,inplace='True') may not work if it is of type 'Object' and not int64.

In such cases I replace these '?' with NaN because isna() does not work on them directly. Then these NaNs are replaced with 0s and finally use them to replace with the actual values to impute

df['SGOT'].replace('?',np.nan,inplace='True' )
df1['SGOT'].fillna(value=0,inplace=True)

df1['SGOT']=df1['SGOT'].astype(int)
df1['SGOT'].replace(0,df1['SGOT'].mean(),inplace=True )

I am sure there is a better way to do this. Please let me know

pandas python
2 Answers

When you read the data (presumably with pd.read_csv()) is a good time to replace '?' by nan:

df = pd.read_csv(..., na_values='?')

See the docs. na_values can be also be a list or a dict.

By default, this will add '?' to the list of strings that are to be interpreted as NaN (you can change that with keep_default_na=False if you prefer).

user_7581740

You can send a dict as argument to df.replace() where this dict will include the columns names with the values to replace and the new ones. And for the mean value for a column you can specify the argument errors as True. Here is a code to explain :

import pandas as pd
df = pd.DataFrame({'BILIRUBIN': [1, '?', 1, 0.4, 0.7], 
                   'SGOT':[18, '?','?',18, 18], 
                   'ALK_PHOSPHATE':[85, '?', '?', 45, 71]})

replace_dict = {'SGOT': {'?': pd.to_numeric(df.SGOT, errors='coerce').mean()}, 
                'BILIRUBIN':{'?': pd.to_numeric(df.BILIRUBIN, errors='coerce').mean()}, 
                'ALK_PHOSPHATE':{'?': pd.to_numeric(df.ALK_PHOSPHATE, errors='coerce').mean()}}

df.replace(replace_dict, inplace=True)

>>> df
   BILIRUBIN  SGOT  ALK_PHOSPHATE
0      1.000  18.0           85.0
1      0.775  18.0           67.0
2      1.000  18.0           67.0
3      0.400  18.0           45.0
4      0.700  18.0           71.0
user_125344060
Related