How do I replace a string from one column using the data from two other columns (pandas)

user_10875381 0
Viewed 53

My df looks something like this:

col1 col2 col3
0.98 0.01 SP
1 0 SP
0.89 SP 0.1
0.97 SP 0.02
0.96 0 SP

I have some idea of how to code this but not quite there,

I want the df to become this:

col1 col2 col3
0.98 0.01 0.01
1 0 0
0.89 0.01 0.1
0.97 0.01 0.02
0.96 0 0.04

This is an idea of what I am trying to do but it's not quite right

df = df.apply(lambda x: float(1 - x['col1'] - x['col2']) if x['col3'] == "SP" else x, axis=1)

df = df.apply(lambda x: float(1 - x['col1'] - x['col3']) if x['col2'] == "SP" else x, axis=1)

pandas dataframe python
3 Answers

Welcome to Stackoverflow, Kyle.

Using pd.Series.where gives short, readable code:

df = pd.DataFrame(
    {'col1': [  0.98, 1, 0.89, .97, 0.96],
    'col2': [0.01, 0, 'SP', 'SP', 0],
    'col3': ['SP', 'SP', 0.1, 0.02, 'SP']}
    )

df['col2'].where(df['col2'].apply(type) != str,df['col1'], inplace=True)
df['col3'].where(df['col3'].apply(type) != str, df['col2'], inplace = True)

pd.Series.apply(type) just calls python's type on each value in the series, returning a series of the results

By the way, I wrote out code that created your data from a dictionary using pd.DataFrame(...) to create it. In the future, it will be helpful to others if you include this part (or similar) in your example so that they can copy and paste. This is part of a minimal reproducible example and makes getting high quality answers faster and easier. If you are loading your data with pd.read_csv() you can use DataFrame.to_dict() to get a copy and paste ready output.

user_116098340

Solution #1: Use mask with replace and astype(float):

df['col2'] = df['col2'].mask(df['col2'] == 'SP', 1 - df['col1'].replace('SP', 0).astype(float) - df['col3'].replace('SP', 0).astype(float))
df['col3'] = df['col3'].mask(df['col3'] == 'SP', 1 - df['col1'].replace('SP', 0).astype(float) - df['col2'].replace('SP', 0).astype(float))

If column 1 never has strings, you can simplify with:

df['col2'] = df['col2'].mask(df['col2'] == 'SP', 1 - df['col1'] - df['col3'].replace('SP', 0).astype(float))
df['col3'] = df['col3'].mask(df['col3'] == 'SP', 1 - df['col1'] - df['col2'].replace('SP', 0).astype(float))

Out[1]: 
   col1  col2  col3
0  0.98  0.01  0.01
1  1.00     0     0
2  0.89  0.01   0.1
3  0.97  0.01  0.02
4  0.96     0  0.04

Solution #2: Alternatively, use mask with pd.to_numeric() since you have strings:

df['col2'] = df['col2'].mask(df['col2'] == 'SP', 1 - pd.to_numeric(df['col1'], errors='coerce') - pd.to_numeric(df['col3'], errors='coerce'))
df['col3'] = df['col3'].mask(df['col3'] == 'SP', 1 - pd.to_numeric(df['col1'], errors='coerce') - pd.to_numeric(df['col2'], errors='coerce'))
df

If column 1 never has strings, you can simplify with:

df['col2'] = df['col2'].mask(df['col2'] == 'SP', 1 - df['col1'] - pd.to_numeric(df['col3'], errors='coerce'))
df['col3'] = df['col3'].mask(df['col3'] == 'SP', 1 - df['col1'] - pd.to_numeric(df['col2'], errors='coerce'))
df

Out[2]: 
   col1  col2  col3
0  0.98  0.01  0.01
1  1.00     0     0
2  0.89  0.01   0.1
3  0.97  0.01  0.02
4  0.96     0  0.04
user_63667700

You can try fillna:

s = df[['col2','col3']].apply(pd.to_numeric, errors='coerce')
SPs = s.isna()

fill= 1 - (s.mask(SPs,0).astype(float).sum(1) + df['col1'])

df[['col2','col3']] = s.apply(lambda x: x.fillna(fill))
user_42384080
Related