How do I replace values with symbols appended to the end of it with NaN in a Pandas dataframe

user_10916052 0
Viewed 191

I am trying to clean my pandas dataset, however I can't figure out a way to remove the weird values with #,*,x appended at the end of the value. I would like to replace all those values with np.nan.

I have attached a link below of how my dataset looks like.

https://imgur.com/gallery/5l5XhF1

pandas data-cleaning dataframe
3 Answers
user_97586600

Use set_index for get only numeric columns, then apply because to_numeric working with each column (Series) separately for replace non numeric to NaNs with errors='coerce' parameter:

non_num_cols = ['date']
df = df.set_index(non_num_cols).apply(pd.to_numeric, errors='coerce').reset_index()
user_29010020

Option 1 : you want to replace junk with the cleaned values

replace_dict = {
        '#': '',
        '*': ''
    }

for key in replace_dict.keys():
    df = df.applymap(lambda x: x.replace(key, replace_dict[key]) if type(x) is str else x)

Option 2 : you want to replace junk with np.nan

import pandas
import re

replace_char = ['#', '*']
for char in replace_char:
    df = df.applymap(lambda x: np.nan if re.search('.*' + char, str(x)) else x)
user_57365150
Related