lambda function with if...else in agg on dataframe-CodePudding

I am trying to calculate aggregate using a lambda function with if... else.

My dataframe looks like this

    States  Sales
0   Delhi   0.0
1   Kerala  2.5
2   Punjab  5.0
3   Haryana 7.5
4   Delhi   10.0
5   Kerala  12.5
6   Punjab  15.0
7   Haryana 17.5

Expected summary table should like this

States  Sales
Delhi   10.0
Haryana 25.0
Kerala  15.0
Punjab  20.0

I tried using the following code

df.groupby('States').agg({
                    'Sales':lambda x: np.sum(x) if (x>7) else 0
                    })

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

What am I doing wrong?

CodePudding user response：

 df.groupby('States')['Sales'].agg(lambda x: sum(x) if x.ge(7).any() else 0).to_frame('Sales')

         Sales
States       
Delhi    10.0
Haryana  25.0
Kerala   15.0
Punjab   20.0

CodePudding user response：

If compare by (x>7) ouput is Series with True and Falses, for test if match at least value use any:

df1 = df.groupby('States').agg({'Sales':lambda x: np.sum(x) if (x>7).any() else 0})
print (df1)
         Sales
States        
Delhi     10.0
Haryana   25.0
Kerala    15.0
Punjab    20.0

If need replace all values lower or equal like 7 to 0 and then aggregate sum use:

df2 = df['Sales'].where(df['Sales'].gt(7), 0).to_frame().groupby(df['States']).sum()
print (df2)
         Sales
States        
Delhi     10.0
Haryana   25.0
Kerala    12.5
Punjab    15.0