Multiplying pandas dataframe column according to multiple conditions

Question

I would like to multiply a column (or create a new one with the multiplied values) based on two conditions. So I tried :

c1 = df['Mean']=='SEPA' and df['Engagement'] == 'M'
c2 = df['Mean']!='SEPA' and df['Engagement'] == 'M'
df.loc[c1, ['Amount Eq Euro']] *= 62
df.loc[c2, ['Amount Eq Euro']] *= 18

Here is the dataframe

    Mean    Engagement  Amount Eq Euro
2   CB (PAYPAL) S   50.0
3   CB  S   50.0
4   CB  S   50.0
5   CB (PAYPAL) M   20.0
6   CB  S   75.0
... ... ... ...
6238    CB  S   30.0
6239    CB  S   80.0
6240    SEPA    M   10.0
6241    CB  S   100.0
6242    NaN M   10.0

But it returned:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-13-bbd424e0088f> in <module>()
      3 #                                            df['Amount Eq Euro'] * 18)
      4 
----> 5 c1 = df['Mean']=='SEPA' and df['Engagement'] == 'M'
      6 c2 = df['Mean']!='SEPA' and df['Engagement'] == 'M'
      7 df.loc[c1, ['Amount Eq Euro']] *= 62

/usr/local/lib/python3.7/dist-packages/pandas/core/generic.py in __nonzero__(self)
   1328     def __nonzero__(self):
   1329         raise ValueError(
-> 1330             f"The truth value of a {type(self).__name__} is ambiguous. "
   1331             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
   1332         )

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

score 0 · Answer 1 · answered Jun 15 '21 at 07:49

It's a not clear what exact values you want. If you want to have c1 and c2 as Series with the mean for each groups, you can what is below. If you only want one output (float) you can use groupby.

c1 = df.loc[(df['Mean'] == 'SEPA') & (df['Engagement'] == 'M', 'Amount Eq Euro').mean()
c2 = df.loc[(df['Mean'] != 'SEPA') & (df['Engagement'] == 'M', 'Amount Eq Euro').mean()
c = c1 + c2
df.loc[c, 'Amount Eq Euro'] *= 62
df.loc[c, 'Amount Eq Euro'] *= 18

Groupby:

c = df.loc[df['Engagement'] == 'M'].groupby('SEPA')['Amount Eq Euro'].mean()

score 0 · Answer 2 · answered Jun 15 '21 at 07:50

The or and and python statements require truth-values. For pandas these are considered ambiguous so you should use "bitwise" | (or) or & (and) operations: Ref

Replace c1 and c2 with:

c1 = (df['Mean']=='SEPA') & (df['Engagement'] == 'M')
c2 = (df['Mean']!='SEPA') & (df['Engagement'] == 'M')

Multiplying pandas dataframe column according to multiple conditions

2 Answers2