python/pandas equivalent to dplyr 1.0.0 summarize(across())

user_8419099 0
Viewed 518

In R, I find the following very useful when dealing with many variables:

library(dplyr)
dat <- group_by(mtcars, cyl) 
summarize(dat, across(c('mpg','disp'), sum), across(c('drat','wt','qsec'), mean))
# A tibble: 3 x 5
    cyl  disp    hp  drat    wt
  <dbl> <dbl> <dbl> <dbl> <dbl>
1     4 1156.   909  4.07  2.29
2     6 1283.   856  3.59  3.12
3     8 4943.  2929  3.23  4.00

Or even better, selecting with pseudo-regex

summarize(dat, across(ends_with('p'), sum), across(ends_with('t'), mean))

In pandas, the equivalent seems to pass variables one-by-one into a dictionary, eg from this gist:

group_agg = df.groupby("group1").agg({
  "var1" : ["mean"], 
  "var2" : ["sum"], 
  "var3" : ["mean"]
  })

Is there a less verbose way to do this operation in pandas, or with some other package?

dplyr pandas r python
3 Answers

For the first scenario, pandas concat suffices :

dat = df.groupby("cyl")

pd.concat([dat[["mpg", "disp"]].sum(), 
           dat[["drat", "wt", "qsec"]].mean()], 
          axis=1)

For the regex/string processing part, verbose is unavoidable :

cols_p = [col for col in df.columns if col.endswith("p")]
cols_t = [col for col in df.columns if col.endswith("t")]

pd.concat((dat[cols_p].sum(), dat[cols_t].mean()), axis=1)

updated Jun 5, 2022:

A cleaner solution, taking a cue from @Richiev post, is to preselect the columns before aggregating:

For solution 1:

# preselected columns
cols = ['mpg', 'disp', 'drat', 'wt', 'qsec']

(dat
.set_index('cyl')
.filter(cols)
.groupby('cyl')
.agg({col:'sum' if col in ('mpg', 'disp') 
      else 'mean' 
      for col in cols})
)
       mpg    disp      drat        wt       qsec
cyl
4    293.3  1156.5  4.070909  2.285727  19.137273
6    138.2  1283.2  3.585714  3.117143  17.977143
8    211.4  4943.4  3.229286  3.999214  16.772143

For solution 2:

# preselect the columns
cols = dat.columns[dat.columns.str.endswith(('p', 't'))]

(dat
.set_index('cyl')
.filter(cols)
.groupby('cyl')
.agg({col:'sum' if col.endswith('p') 
      else 'mean' 
      for col in cols})
)
       disp    hp      drat        wt
cyl
4    1156.5   909  4.070909  2.285727
6    1283.2   856  3.585714  3.117143
8    4943.4  2929  3.229286  3.999214
user_71757130

Assuming you want this for a wide dataframe with many aggregation target columns, a dictionary comprehension could mimic the regex:

group_agg = df.groupby('group1').agg(
    {var: 'sum' if var[-1]=='p' else 'mean' for var in cols})

In general you can build dict-comprehension with other conditions such as dtypes, str in string, count of regex patterns, etc.

user_66928980

Using datar, it is very easy to transition your R code to python code without learning APIs of pandas:

>>> from datar import f
>>> from datar.dplyr import summarize, group_by, across, ends_with
>>> from datar.base import sum, mean
>>> from datar.datasets import mtcars
>>> 
>>> dat = group_by(mtcars, f.cyl)
>>> dat >> summarize(across(ends_with('p'), sum), across(ends_with('t'), mean))
   cyl    disp    hp      drat        wt
0    4  1156.5   909  4.070909  2.285727
1    6  1283.2   856  3.585714  3.117143
2    8  4943.4  2929  3.229286  3.999214

I am the author of the package. Feel free to submit issues if you have any questions.

user_50881650
Related