Elegant way to replace values across dataframe without column names

user_10829044 0
Viewed 322

I have a dataframe like as shown below

df = pd.DataFrame({'person_id': [101,101,101,101],
                        'start_date':['NA-NA-NA NA:NA:NA','Nil','06/06/2014 08:00:00 AM','06/06/2014 05:00:00 AM'],
                        'desc':['-','- ',' - ','test-date'],
                        'type':['test-date','actual-version','depat-head','oxygen-parlor']})

I would like to replace the values NA-NA-NA NA:NA:NA, Nil, - with np.nan

I tried the below

df.replace(['NA-NA-NA NA:NA:NA','Nil','-', np.nan)
df.dropna(subset=['person_id','start_date','desc','type'], how='any')

Though the above works to an extent, it is not accurate because it doesn't consider hyphens with spaces - or - etc. Please note that the - in type column SHOULD NOT BE REPLACED.

Can help me replace exact values (but with exceptions of space characters) across dataframe but without using column names?

I expect my output to be like as shown below

person_id              start_date               desc           type
  101               06/06/2014 05:00:00 AM    test-date    oxygen-parlor
pandas series dataframe python string
2 Answers

You could do a replacement on the entire data frame using a regex alternation:

terms = ['NA-NA-NA NA:NA:NA', 'Nil', '-']
regex = r'^\s*(?:' + '|'.join(terms) + r')\s*$'
df = df.replace(regex, np.nan, regex=True)

The regex pattern used here is ^\s*(?:NA-NA-NA NA:NA:NA|Nil|-)\s*$, and will only match the NA string, - ,and Nil when they are the entire content of a column (but not a substring only).

user_18632290 
df.replace(regex=['Nil|NA|^-'], value=np.nan).dropna()

         person_id              start_date       desc           type
3        101            06/06/2014 05:00:00 AM  test-date  oxygen-parlor
user_89869750
Related