aggregate and join on the same table in the same operation - python

user_6851715 0
Viewed 40

in R you can easily aggregate and join on the same data.table in one line. I was wondering if there's an equivalent in Python that I could use without having to separately aggregate on the table and then merge it.

this is the R equivalent:

> require(data.table)
> DT = data.table(Col1 = c('A','A','A','B','B','B'), Col2 = c(1,2,3,4,5,6))
> DT[, Col2_mean := mean(Col2), by=.(Col1)]  # this is the line I'm hoping to produce in Python, in one line!
> DT
   Col1 Col2 Col2_mean
1:    A    1         2
2:    A    2         2
3:    A    3         2
4:    B    4         5
5:    B    5         5
6:    B    6         5

Also, is it possible to perform more than one aggregate operation (let's say max(), for example) and merge it at the same time?

for Python, this is the data frame to start with:

import pandas as pd
DF = pd.DataFrame(data={'Col1': ['A', 'A', 'A', 'B', 'B', 'B'], 'Col2': [1,2,3,4,5,6]})
aggregate r merge python
1 Answers

You could do it with pandas with groupby and transform:

>>> df = pd.DataFrame({'Col1': ['A', 'A', 'A', 'B', 'B', 'B'], 'Col2': [1, 2, 3, 4, 5, 6]})
>>> df['Col2_mean'] = df.groupby('Col1')['Col2'].transform('mean')
>>> df
  Col1  Col2  Col2_mean
0    A     1          2
1    A     2          2
2    A     3          2
3    B     4          5
4    B     5          5
5    B     6          5
>>>
user_87083640
Related