Pandas dataframe values and row condition both depend on other columns

user_13442590 0
Viewed 277

I have a Pandas DataFrame:

import pandas as pd

df = pd.DataFrame({'col1': ['a','a','b','b'],
                   'col2': [1,2,3,4],
                   'col3': [11,12,13,14]})
  col1 col2 col3
0   a   1   11
1   a   2   12
2   b   3   13
3   b   4   14

I need to replace an entry in col2 by some function of the row's col2 and col3 values if the value in col1 is a b, but leave the rows unchanged if the value in col1 is not a b. Say the function is col3 * exp(col2), then applying this to df above would yield

    col1   col2   col3
0   a      1      11
1   a      2      12
2   b      261.1  13
3   b      764.4  14

Ideally this would be vectorised and in-place as my real DataFrame has a few million rows.

This differs from other questions on Stack Overflow as they only require either the new value to not depend on other columns or to change all rows at once. Thank you.

Edit: Corrected the target DataFrame. Had changed the function from exp(col2)+col3 to exp(col2)*col3 without updating the values in the example.

pandas dataframe slice numpy python
3 Answers

Use DataFrame.iloc

import pandas as pd
import numpy as np

df = pd.DataFrame({'col1': ['a', 'a', 'b', 'b'], 'col2': [1, 2, 3, 4], 'col3': [11, 12, 13, 14]})

df.loc[df['col1'] == 'b', 'col2'] = df['col3'] * np.exp(df['col2'])
print(df)

Giving the correct

  col1       col2  col3
0    a    1.00000    11
1    a    2.00000    12
2    b  261.11198    13
3    b  764.37410    14
user_72126860

np.where does the job:

df.col2 = np.where(df.col1 == "b", df.col3 * np.exp(df.col2), df.col2)

It says "for each row: if df has 'b' in col1, then take the value from 2nd argument (which is the function of col2 and col3); if not, then take the value from 3rd argument (which is col2 so it keeps as is).". Applies it to each row in a vectorized fashion.

to get

  col1       col2  col3
0    a    1.00000    11
1    a    2.00000    12
2    b  261.11198    13
3    b  764.37410    14
user_93321870 
import pandas as pd
import numpy as np

df = pd.DataFrame({'col1': ['a','a','b','b'],
                   'col2': [1,2,3,4],
                   'col3': [11,12,13,14]})

def get_exp(col1, col2, col3):
    if col1 == 'b':
        return (col3 * np.exp(col2))
    return col2    


df.col2 = df.apply(lambda x: get_exp(x.col1, x.col2, x.col3), axis=1)
print(df)

Output:

    col1    col2    col3
0   a   1.00000     11
1   a   2.00000     12
2   b   261.11198   13
3   b   764.37410   14
user_47432130
Related