Python Pandas : group by in group by and average?
316,216
Solution 1
If you want to first take mean on the combination of ['cluster', 'org']
and then take mean on cluster
groups, you can use:
In [59]: (df.groupby(['cluster', 'org'], as_index=False).mean()
.groupby('cluster')['time'].mean())
Out[59]:
cluster
1 15
2 54
3 6
Name: time, dtype: int64
If you want the mean of cluster
groups only, then you can use:
In [58]: df.groupby(['cluster']).mean()
Out[58]:
time
cluster
1 12.333333
2 54.000000
3 6.000000
You can also use groupby
on ['cluster', 'org']
and then use mean()
:
In [57]: df.groupby(['cluster', 'org']).mean()
Out[57]:
time
cluster org
1 a 438886
c 23
2 d 9874
h 34
3 w 6
Solution 2
I would simply do this, which literally follows what your desired logic was:
df.groupby(['org']).mean().groupby(['cluster']).mean()
Related videos on Youtube
Author by
UserYmY
Updated on May 16, 2020Comments
-
UserYmY almost 4 years
I have a dataframe like this:
cluster org time 1 a 8 1 a 6 2 h 34 1 c 23 2 d 74 3 w 6
I would like to calculate the average of time per org per cluster.
Expected result:
cluster mean(time) 1 15 ((8+6)/2+23)/2 2 54 (74+34)/2 3 6
I do not know how to do it in Pandas, can anybody help?
-
EdChum almost 9 yearsSorry are you wanting
df.groupby(['org','cluster']).mean()
? this isn't that meaningful for your dataset as opposed to this:df.groupby(['cluster','org']).mean()
-
UserYmY almost 9 years@EdChum thanks for your help, but I want is none of them. But the "average of average times per org". Please see the expected result (edited)
-
-
UserYmY almost 9 yearsBut I want one number per cluster ( average of average of time per org ). So the result is only cluster and average time