Apply A Function On A Column Of A Dataframe Depending On The Value Of Another Column And Then Groupby

Assume the dataframe with column 'A' and column 'condition' as reproduced by the code below. example = pd.DataFrame({'A': range(10), 'condition': [0,1,0,1,2,0,1,2,2,1]}) I want to

Solution 1:

You can use np.where to multiply the values in column 'A' by 2 if the values in column 'B' is either 0 or 2.

example['A'] = np.where(example['condition'].isin([0,2]), example['A']*2,example['A'])

To perform summation on A if condition columns satisfy the criteria, you can first include a new column in your dataframe example which states whether A is > or < than 2.5 then perform aggregation over this dataframe.

example['check_A'] =np.where(example['A']>2.5,1,0)
new = example.groupby(['condition','check_A'])['A'].apply(lambda c: c.abs().sum())

Solution 2:

First we get all the rows where condition is 0 or 2. Then we multiply the A values by two of these rows and use GroupBy.sum while using query to filter all the rows where A >= 2.5

m = example['condition'].isin([0,2])
example['A'] = np.where(m, example['A'].mul(2), example['A'])
grpd = example.query('A.ge(2.5)').groupby('condition', as_index=False)['A'].sum()

Output

   condition   A
0          0  28
1          1  18
2          2  76

---

Details GroupBy.sum:

First we use query to get all the rows where A >= 2.5:

example.query('A.ge(2.5)')

    A  condition
2   4          0
3   3          1
4   8          2
5  10          0
6   6          1
7  14          2
8  16          2
9   9          1

Then we use groupby on condition to get each group of unique values, in this case all rows with 0, 1 and 2:

for _, d in grpd.groupby('condition', as_index=False):
    print(d, '\n')

    A  condition
2   8          0
5  20          0 

   A  condition
3  3          1
6  6          1
9  9          1 

    A  condition
4  16          2
7  28          2
8  32          2 

So if we have the seperate groups, we can use .sum method to sum the whole A column:

for _, d in grpd.groupby('condition', as_index=False):
    print(d['A'].sum(), '\n')

28 

18 

76 

Solution 3:

You were quite close in your original attempt. In particular, I would bring the condition out into its own separate function to enhance readability, and then apply the function to the data frame with axis=1:

def f(row):
    if row["condition"] == 0 or row["condition"] == 2:
        return(int(row["A"] * 2))
    return(row["A"])   # Base condition 

example['B'] = example.apply(f, axis=1)   # Apply to rows of 'example' df

example.drop("condition", axis=1, inplace=True)

example

    A   condition   B
0   0   0   0
1   1   1   1
2   2   0   4
3   3   1   3
4   4   2   8
5   5   0   10
6   6   1   6
7   7   2   14
8   8   2   16
9   9   1   9

Then, to apply your groupby operation:

example[example["A"] > 2.5].groupby("condition")["A"].apply(lambda x: np.sum(np.abs(x)))

condition
0     5
1    18
2    19
Name: A, dtype: int64

Solution 4:

try this,

df.loc[df['condition']%2==0, 'A'] = df['A']*2

O/P:

    A  condition
0   0          0
1   1          1
2   4          0
3   3          1
4   8          2
5  10          0
6   6          1
7  14          2
8  16          2
9   9          1