How to update value in pandas data frame based on the condition?

February 10, 2022

I need to replace specific values from the data frame based on the country level value.

df:

Country    Val1    Val2
Argentina   12      C
Argentina   23      34
Argentina   NaN     92
Argentina   -       29
Argentina   U       73
Germany     37      47
Germany     12      23
Germany     U      NaN
Germany     NaN     -
Germany     C       C

df_output:

Country    Val1    Val2
Argentina   12      92
Argentina   23      34
Argentina   NaN     92
Argentina   -       29
Argentina   23      73
Germany     37      47
Germany     12      23
Germany     37      NaN
Germany     NaN     -
Germany     37      47

I need to replace "C" and "U" Value with max Number of that column based on country.

>Solution :

Create index by Country column, then test C, U by DataFrame.isin and replace if match condition by DataFrame.mask by maximal values per groups – but before convert values to numeric:

df = df.set_index('Country')

m = df.isin(['C','U'])

df1 = (df.mask(m, df.apply(pd.to_numeric, errors='coerce')
                    .groupby('Country')
                    .transform('max'))
        .reset_index())

print (df1)
     Country  Val1  Val2
0  Argentina    12  92.0
1  Argentina    23    34
2  Argentina   NaN    92
3  Argentina     -    29
4  Argentina  23.0    73
5    Germany    37    47
6    Germany    12    23
7    Germany  37.0   NaN
8    Germany   NaN     -
9    Germany  37.0  47.0