How do you compute the mean number of counts within the subgroups of each group after a multi column group by in Pandas?

user_6515530 0
Viewed 115

I have some data like this:

+---------+----------+----------+
| groupX  | groupY   | id       |
+---------+----------+----------+
| A       |  1       | e13r2    |
| B       |  2       | efwu1    |
| A       |  1       | efgi3    |
| B       |  4       | eoij9    |
+---------+----------+----------+

df = pd.DataFrame([['A',1,'e13r2'],['B',2,'efwu1'],['A',1,'efgi3'],['B',4,'eoij9']],
                   columns=['groupX','groupY','id'])

Allow me to demonstrate what I am aiming for:

I want to group by groupX, and then group by groupY. So for instance, this would give us everything in group A. Group A is further divided into subgroups. In this case, there is only subgroup 1 within group A. Within this subgroup are two ids: e13r2 and efgi3. Similarly, within group B are the subgroups 2 and 4. Subgroup 2 contains id efwu1 and subgroup 4 contains id eoij9.

So group A contains one subgroup. This subgroup has two ids within it. On average, the subgroups within group A have 2 ids within them.

Group B contains two subgroups. Subgroup 2 contains one id and subgroup 4 contains one id. On average, the subgroups within group B have 1 id within them.

So I am taking the mean number of counts within the subgroups of each group.

I am looking for a Pandas command that would return this:

+---------+--------------------------------+
| groupX  | mean counts within subgroups   |
+---------+--------------------------------+
| A       |  2                             |
| B       |  1                             |
+---------+--------------------------------+

My attempt so far is this:

df.groupby(['groupX','groupY']).count()

But I cannot see how to proceed from here.

pandas-groupby pandas python
3 Answers

you can chain groupby in this instance.

level=0 is refering to your index, namely groupX you could also use the index name directly.

df1 = df.groupby(['groupX','groupY'])['id'].size().groupby(level=0).mean()
#or
#df.groupby(['groupX','groupY'])['id'].size().groupby('groupX').mean()
#or
#df.groupby(['groupX','groupY'])['id'].size().mean(level=0)


groupX
 A           2
 B           1
user_93751020

You can do another groupby on groupX: df.groupby(['groupX','groupY']).count().groupby(['groupX']).mean()

Output:

        id
groupX    
A        2
B        1
user_53270680

Try this:

>>> df.groupby(["groupX", "groupY"]).count().mean(level = 0)

        id
groupX    
A        2
B        1
user_137432330
Related