If condition with a dataframe [duplicate]

Question

I want if the conditions are true if df[df["tg"] > 10 and df[df["tg"] < 32 then multiply by five otherwise divide by two. However, I get the following error

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

d = {'year': [2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001],
     'day': [1, 2, 3, 4, 1, 2, 3, 4,],
     'month': [1, 1, 1, 1, 2, 2, 2, 2],
     'tg': [10, 11, 12, 13, 50, 21, -1, 23],
     'rain': [1, 2, 3, 2, 4, 1, 2, 1]}
df = pd.DataFrame(data=d)
print(df)


[OUT]

   year  day  month  tg  rain
0  2001    1      1  10     1
1  2001    2      1  11     2
2  2001    3      1  12     3
3  2001    4      1  13     2
4  2001    1      2  50     4
5  2001    2      2  21     1
6  2001    3      2  -1     2
7  2001    4      2  23     1

df["score"] = (df["tg"] * 5) if ((df[df["tg"] > 10]) and (df[df["tg"] < 32])) else (df["tg"] / 2) 

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

What I want

   year  day  month  tg  rain   score
0  2001    1      1  10     1    5
1  2001    2      1  11     2    55
2  2001    3      1  12     3    60
3  2001    4      1  13     2    65
4  2001    1      2  50     4    25
5  2001    2      2  21     1    42
6  2001    3      2  -1     2    0.5
7  2001    4      2  23     1    46

You are looking for where: pandas.pydata.org/docs/reference/api/… — foglerit
– foglerit, Commented Nov 4, 2021 at 16:12
Although not the most efficient solution, it is also worth being aware of map to apply a scalar function to each element: df["score"] = df["tg"].map(lambda x: x*5 if 10<x<32 else x/2) — alani
– alani, Commented Nov 4, 2021 at 16:15

Sunderam Dubey · Accepted Answer · 2022-05-30 14:32:11Z

5

You can use where:

df['score'] = (df['tg']*5).where(df['tg'].between(10, 32), df['tg']/5)

edited May 30, 2022 at 14:32

Sunderam Dubey

8,84812 gold badges26 silver badges43 bronze badges

answered Nov 4, 2021 at 16:12

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

not_speshal Over a year ago

Very minor but just helping OP match their expected output. I had the same idea btw =D

Quang Hoang · Accepted Answer · 2021-11-04 16:11:27Z

4

Use np.where:

# do you need `inclusive=True`? Expected output says yes, your logic says no
mask = df['tg'].between(10,32, inclusive=False)
df['score'] = df['tg'] * np.where(mask, 5, 1/2)

 # or
 # df['score'] = np.where(mask, df['tg'] * 5, df['tg']/2)

Output:

   year  day  month  tg  rain  score
0  2001    1      1  10     1    5.0
1  2001    2      1  11     2   55.0
2  2001    3      1  12     3   60.0
3  2001    4      1  13     2   65.0
4  2001    1      2  50     4   25.0
5  2001    2      2  21     1  105.0
6  2001    3      2  -1     2   -0.5
7  2001    4      2  23     1  115.0

answered Nov 4, 2021 at 16:11

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Comments

BENY · Accepted Answer · 2021-11-04 16:23:01Z

2

Let try to fix it with for loop

[x * 5 if (x > 10 and x < 32) else (x / 2) for x in df['tg']]
Out[64]: [5.0, 55, 60, 65, 25.0, 105, -0.5, 115]

answered Nov 4, 2021 at 16:23

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

simondvt · Accepted Answer · 2021-11-04 16:25:22Z

2

You can use df.loc

mask = (df["tg"] > 10) & (df["tg"] < 32)
df.loc[mask, "score"] = df["tg"] * 5
df.loc[~mask, "score"] = df["tg"] / 2

answered Nov 4, 2021 at 16:25

simondvt

3344 silver badges14 bronze badges

Comments

sammywemmy · Accepted Answer · 2021-11-04 20:44:56Z

For a more detailed explanation of your error message, have a look at this from the pandas' docs.

Another option, in addition to the already excellent answers, is the case_when function from pyjanitor, which could be a helpful abstraction, especially for multiple conditions, or you probably need to preserve Pandas extension dtypes:

# pip install git+https://github.com/pyjanitor-devs/pyjanitor.git
import pandas as pd
import janitor

df.case_when(
      df.tg.gt(10) & df.tg.le(32), # condition
      df.tg.mul(5), # result if True
      df.tg.div(2), # result if False
      column_name='score')
 
   year  day  month  tg  rain  score
0  2001    1      1  10     1    5.0
1  2001    2      1  11     2   55.0
2  2001    3      1  12     3   60.0
3  2001    4      1  13     2   65.0
4  2001    1      2  50     4   25.0
5  2001    2      2  21     1  105.0
6  2001    3      2  -1     2   -0.5
7  2001    4      2  23     1  115.0

More examples can be found here

Collectives™ on Stack Overflow

If condition with a dataframe [duplicate]

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Linked

Related