Perform operations on numbers in dataframe through a function with an if statement

Question

I have been working with pandas to analyze and perform some lengthy operations on a dataset through defined functions (for convenience and also since I use the same functions in operations not involving pandas). I am trying to perform some operations based on which number is larger, using if and else statements.

I have not been able to find a workaround in other answers. Here is a short simplified example of what sort of logical operations I am trying to perform:

import pandas as pd

df = pd.DataFrame({"A": [177,166,155,125,146,149,192,160,111,85], 
                   "B": [26.2,27,26.8,23.4,23.3,17.5,26.4,25.7,18.9,15.8], 
                   "C": [9.2,99.1,29.3,8.6,8,7.2,10,39.4,47.25,4.5,]})

x = 'A'
y = 'B'
z = 'C'

def test(a,b,c):
    h = a*b/c
    return h

df['D'] = test(df[x],df[y],df[z])

Functions have been working out for me so far like that:

print(df['D'])

0    504.065217
1     45.227043
2    141.774744
3    340.116279
4    425.225000
5    362.152778
6    506.880000
7    104.365482
8     44.400000
9    298.444444
Name: D, dtype: float64

I am looking to get such operations working:

def test2(a,b,c):
    if a > b:
        return a*c
    else:
        return b*c

df['E'] = test2(df[x],df[y],df[z])
print(df['E'])

I am getting the obvious error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Possible duplicate of [Select rows from a DataFrame based on values in a column in pandas](https://stackoverflow.com/questions/17071871/select-rows-from-a-dataframe-based-on-values-in-a-column-in-pandas) — Erfan, Aug 05 '19 at 18:53

score 1 · Accepted Answer · answered Aug 05 '19 at 18:42

1

You need:

df['E'] = df.apply(lambda x: test2(x['A'], x['B'], x['C']) ,1)

Output:

   A    B       C       E
0   177 26.2    9.20    1628.40
1   166 27.0    99.10   16450.60
2   155 26.8    29.30   4541.50
3   125 23.4    8.60    1075.00
4   146 23.3    8.00    1168.00
5   149 17.5    7.20    1072.80
6   192 26.4    10.00   1920.00
7   160 25.7    39.40   6304.00
8   111 18.9    47.25   5244.75
9   85  15.8    4.50    382.50

answered Aug 05 '19 at 18:42

harvpan

8,571
2
18
36

1

Thanks! I did not realize how does lambda actually work – KZ123 Aug 05 '19 at 19:02

Alexandre B. · Answer 2 · 2019-08-05T18:57:03.533

Another solution is to use np.where:

df["E"] = np.where(df.A > df.B, df.A*df.C, df.B*df.C)
print(df)
#      A     B      C         E
# 0  177  26.2   9.20   1628.40
# 1  166  27.0  99.10  16450.60
# 2  155  26.8  29.30   4541.50
# 3  125  23.4   8.60   1075.00
# 4  146  23.3   8.00   1168.00
# 5  149  17.5   7.20   1072.80
# 6  192  26.4  10.00   1920.00
# 7  160  25.7  39.40   6304.00
# 8  111  18.9  47.25   5244.75
# 9   85  15.8   4.50    382.50

In this situation, np.where seems to be more efficient than apply (40 times faster). Here the efficiency results:

import timeit

def numpy_where(df):
    def func():
        df["E"] = np.where(df.A > df.B, df.A*df.C, df.B*df.C)
    return func


def pandas_apply(df):
    def test2(a, b, c):
        if a > b:
            return a*c
        return b*c

    def func():
        df["E"] = df.apply(lambda x: test2(x['A'], x['B'], x['C']), 1)
    return func

t = timeit.Timer(numpy_where(df))
print(t.timeit(100))
# 0.0711541

t = timeit.Timer(pandas_apply(df))
print(t.timeit(100))
# 2.8589093

score 0 · Answer 3 · answered Aug 05 '19 at 18:54

#try this (still using a function):

def test2(row):
    if row['A'] > row['B']:
        return row['A'] * row['C']
    else:
        return row['B']*row['C']

df['E'] = df.apply(test2, axis=1)



    A   B       C       D           E
0   177 26.2    9.20    504.065217  1628.40
1   166 27.0    99.10   45.227043   16450.60
2   155 26.8    29.30   141.774744  4541.50
3   125 23.4    8.60    340.116279  1075.00
4   146 23.3    8.00    425.225000  1168.00
5   149 17.5    7.20    362.152778  1072.80
6   192 26.4    10.00   506.880000  1920.00
7   160 25.7    39.40   104.365482  6304.00
8   111 18.9    47.25   44.400000   5244.75
9   85  15.8    4.50    298.444444  382.50

But using np.where is the best solution.

Perform operations on numbers in dataframe through a function with an if statement

3 Answers3