Aggregate groups based on index from external object

user_2988730 0
Viewed 56

I have a dataframe with a categorical column and some other stuff:

>>> np.random.seed(0xFEE7)
>>> df = pd.DataFrame({'A': np.random.randint(10, size=10), 
                       'B': np.random.randint(10, size=10),
                       'C': np.random.choice(['A', 'B'], size=10)})
>>> df
   A  B  C
0  0  0  B
1  4  0  B
2  6  6  A
3  8  3  B
4  0  2  A
5  8  4  A
6  4  1  B
7  8  7  A
8  4  4  A
9  1  1  A

I'd like to be able to apply a groupby done on C to another dataframe. For example, let's say

>>> ser = df['A'] - df['B']

I would like to be able to use the indices in df.groupby(['C', D]) to compute the means of ser. I can compute the group means for a single column, e.g., C like this:

>>> (df['A'] - df['B']).groupby(df['C']).mean()
C
A   -1.250000
B    3.666667
dtype: float64

However, using a multi-index does not work:

>>> (df['A'] - df['B']).groupby(df[['C', 'D']]).mean()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\jfoxrabinovitz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\series.py", line 1678, in groupby
    return groupby_generic.SeriesGroupBy(
  File "C:\Users\jfoxrabinovitz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\groupby.py", line 403, in __init__
    grouper, exclusions, obj = get_grouper(
  File "C:\Users\jfoxrabinovitz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\grouper.py", line 617, in get_grouper
    Grouping(
  File "C:\Users\jfoxrabinovitz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\groupby\grouper.py", line 345, in __init__
    raise ValueError(f"Grouper for '{t}' not 1-dimensional")
ValueError: Grouper for '<class 'pandas.core.frame.DataFrame'>' not 1-dimensional
pandas-groupby pandas python
1 Answers

You can use a series.groupby for this usecase which is faster than dataframe.groupby.

Since we already have a calculated series and we get the mean of the result using a grouper column, its better we use the grouper column in the series.groupby and then .mean() which aggregates the results:

(df['A'] - df['B']).groupby(df['C']).mean()

EDIT:

For multiple keys, you can use df.assign to assign a helper column and use dataframe.groupby to group on multiple keys:

df.assign(k=(df['A'] - df['B'])).groupby(['C','D'])['k'].mean()
#k is our helper column(series)
user_98406370
Related