Replace Specific Value In Pandas Dataframe Column, Else Convert Column To Numeric

Given the following pandas dataframe +----+------------------+-------------------------------------+--------------------------------+ | | AgeAt_X | AgeAt_Y

Solution 1:

We can loop through each column and check if the sentence is present. If we get a hit, we replace the sentence with NaN with Series.str.replace and right after convert it to numeric with Series.astype, in this case float:

df.dtypes
AgeAt_X     object
AgeAt_Y     object
AgeAt_Z    float64
dtype: object

sent = 'Older than 100'for col in df.columns:
    if sent indf[col].values:
        df[col] = df[col].str.replace(sent, 'NaN')
        df[col] = df[col].astype(float)

print(df)
   AgeAt_X  AgeAt_Y  AgeAt_Z
0      NaN      NaN    74.13
1      NaN      NaN    58.46
2      NaN     8.40    54.15
3      NaN      NaN    57.04
4      NaN    57.04      NaN

df.dtypes
AgeAt_X    float64
AgeAt_Y    float64
AgeAt_Z    float64
dtype: object

Solution 2:

If I understand you correctly, you can replace all occurrences of Older than 100 with np.nan with a single call to DataFrame.replace. If all remaining values are numeric, then the replace will implicitly change the data type of the column to numeric:

# Minimal example DataFrame
df = pd.DataFrame({'AgeAt_X': ['Older than 100', np.nan, np.nan],
                   'AgeAt_Y': ['Older than 100', np.nan, 8.4],
                   'AgeAt_Z': [74.13, 58.46, 54.15]})
df
          AgeAt_X         AgeAt_Y  AgeAt_Z
0  Older than 100  Older than 10074.131             NaN             NaN    58.462             NaN             8.454.15

df.dtypes
AgeAt_X     object
AgeAt_Y     object
AgeAt_Z    float64
dtype: object# Replace occurrences of 'Older than 100' with np.nan in any column
df.replace('Older than 100', np.nan, inplace=True)

df
   AgeAt_X  AgeAt_Y  AgeAt_Z
0      NaN      NaN    74.131      NaN      NaN    58.462      NaN      8.454.15

df.dtypes
AgeAt_X    float64
AgeAt_Y    float64
AgeAt_Z    float64
dtype: object