Conditional sum on multiple pandas groups, each defined by a set of overlapping column values

user_5752730 0
Viewed 201

I'm trying to perform a conditional sum on groups of rows defined by a list of arbitrary column values. By conditional sum, I mean sum values in one column only if the value in a second column is above a threshold. There can be overlap among the groups and the number of elements in each group can be different.

For example, given the following dataframe:

data = {
   'id': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
   'counter': [10, 9, 8, 7, 6, 5, 4, 3, 2, 1],
   'output': [5, 10, 15, 20, 25, 35, 20, 15, 10, 5]
}
df = pd.DataFrame(data)

>>> df
   id  counter  output
0   1       10       5
1   2        9      10
2   3        8      15
3   4        7      20
4   5        6      25
5   6        5      35
6   7        4      20
7   8        3      15
8   9        2      10
9  10        1       5

And the following inputs (I'm flexible if we need to change their format):

group_ids = {'Group A': [1, 2, 3, 4], 'Group B': [6, 7, 8, 9], 'Group C': [4, 5, 6]}
output_threshold = 12

I would like to generate the following new dataframe, which is the sum of counter for each group defined by the list of group_ids only if output exceeds the specified output_threshold. Bonus points if I can add the title to each of these groups:

title    sum
Group A   15
Group B   12
Group C   18
pandas python
3 Answers

You can use isin to check for values and sum:

mask = (df['output'] > output_threshold).astype(int)
for k,v in group_ids.items():
    df[k] = df['id'].isin(v) * mask * df['counter']

df[group_ids.keys()].sum()

Output (can't quite match your expected):

Group A    15
Group B    12
Group C    18
dtype: int64
user_42384080

Working within dictionaries and rebuilding the dataframe could work as well : 

from collections import defaultdict
from itertools import product
d = defaultdict(list)

#get the product of M and group_ids
for entry, groups in product(M,group_ids.items()):
    #pass in the condition
    if entry['id'] in groups[-1] and entry['output'] > output_threshold:
        #extract relevant counter value
        d[groups[0]].append(entry['counter'])

#sum the list values
d = {k:sum(v) for k,v in d.items()}

#create dataframe
res = pd.DataFrame.from_dict(d,orient='index',columns=['Total'])

res

            Total
Group A     15
Group C     18
Group B     12
user_71757130

The following is a solution to the question in this post, except for the fact that the id groups CANNOT be overlapping.

You may want to create an inverse_group dictionary, map the id and groupby:

inverse_groups={x:k for k,v in group_ids.items() for x in v}

(df[df['output']>output_threshold]
   .groupby(df['id'].map(inverse_groups))
   .counter.sum()
)

Output:

id
Group A     8
Group B     7
Group C    18
Name: counter, dtype: int64

Full credit goes to @QuangHoang. This was his first reply to my question, which he completely re-wrote to satisfy the overlapping id condition.

user_57527300
Related