conditional sums for pandas aggregate

Question

I just recently made the switch from R to python and have been having some trouble getting used to data frames again as opposed to using R's data.table. The problem I've been having is that I'd like to take a list of strings, check for a value, then sum the count of that string- broken down by user. So I would like to take this data: ``` A_id B C 1: a1 "up" 100 2: a2 "down" 102 3: a3 "up" 100 3: a3 "up" 250 4: a4 "left" 100 5: a5 "right" 102 ``` And return: ``` A_id_grouped sum_up sum_down ... over_200_up 1: a1 1 0 ... 0 2: a2 0 1 0 3: a3 2 0 ... 1 4: a4 0 0 0 5: a5 0 0 ... 0 ``` Before I did it with the R code (using data.table) ``` >DT[ ,list(A_id_grouped, sum_up = sum(B == "up"), + sum_down = sum(B == "down"), + ..., + over_200_up = sum(up == "up" & < 200), by=list(A)]; ``` However all of my recent attempts with Python have failed me: ``` DT.agg({"D": [np.sum(DT[DT["B"]=="up"]),np.sum(DT[DT["B"]=="up"])], ... "C": np.sum(DT[(DT["B"]=="up") & (DT["C"]>200)]) }) ``` Thank you in adva…

Accepted Answer

An old question; I feel a better way, and avoiding the apply, would be to create a new dataframe, before grouping and aggregating:

```
df = df.set_index('A_id')

outcome = {'sum_up' : df.B.eq('up'),
           'sum_down': df.B.eq('down'),
           'over_200_up' : df.B.eq('up') & df.C.gt(200)}

outcome = pd.DataFrame(outcome).groupby(level=0).sum()

outcome
 
      sum_up  sum_down  over_200_up
A_id                               
a1         1         0            0
a2         0         1            0
a3         2         0            1
a4         0         0            0
a5         0         0            0
```

Another option would be to unstack before grouping; however, I feel it is a longer, unnecessary process:

```
(df
  .set_index(['A_id', 'B'], append = True)
  .C
  .unstack('B')
  .assign(gt_200 = lambda df: df.up.gt(200))
  .groupby(level='A_id')
  .agg(sum_up=('up', 'count'), 
       sum_down =('down', 'count'), 
       over_200_up = ('gt_200', 'sum')
      )
)

sum_up  sum_down  over_200_up
A_id                               
a1         1         0            0
a2         0         1            0
a3         2         0            1
a4         0         0            0
a5         0         0            0
```

conditional sums for pandas aggregate

Solution

Problem