pandas multi-index divide aggregated counts

Question

My raw data is as such

    level0  level1  level2
0   0       A       foo
1   0       A       bar
2   0       B       foo
3   0       B       foo
4   0       B       foo
5   0       B       bar
6   1       A       foo
7   1       A       bar

And then I try to group the counts as such df.groupby(['level0', 'level1', 'level2']).size()

to get this

level0  level1  level2
0       A       foo            1
                bar            1
        B       foo            3
                bar            1
1       A       foo            1
                bar            1
        B       foo            1
                bar            1
2       A       foo            1
                bar            1
        B       foo            1
                bar            1

And now I would simply like to divide the counts of level2 in a ratio of bar/foo to get something like this:

level0  level1      {bar counts/foo counts}
0       A           1.00
        B           0.33
1       A           1.00
        B           1.00
2       A           1.00
        B           1.00

Perhaps I even setup the original groupby wrong but also tried .div, .apply, etc but having a mental block. Thank you for any guidance!

mozway · Accepted Answer · 2024-02-24 19:02:39Z

1

unstack and eval:

tmp = df.groupby(['level0', 'level1', 'level2']).size()

out = (tmp.unstack().eval('bar/foo')
          .reset_index(name='bar/foo')
      )

Or div:

tmp = (df.groupby(['level0', 'level1', 'level2'])
         .size().unstack()
      )

out = (tmp['bar']
       .div(tmp['foo'])
       .reset_index(name='bar/foo')
      )

Output:

   level0 level1   bar/foo
0       0      A  1.000000
1       0      B  0.333333
2       1      A  1.000000

answered Feb 24, 2024 at 19:02

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mozway Over a year ago

@Scott no need for a walrus, out = df.groupby(['level0', 'level1', 'level2']).size().unstack().eval('bar/foo') is enough. I just split it in two since this was OP's original code ;)

Panda Kim · Accepted Answer · 2024-02-24 18:58:55Z

1

Code

# your groupby code
tmp = df.groupby(['level0', 'level1', 'level2']).size() 

# get desired output by xs and div
out = tmp.xs('bar', level=2).div(tmp.xs('foo', level=2))

out:

level0  level1
0       A         1.000000
        B         0.333333
1       A         1.000000

answered Feb 24, 2024 at 18:58

Panda Kim

13.7k2 gold badges8 silver badges15 bronze badges

Comments

Soudipta Dutta · Accepted Answer · 2024-08-16 17:32:47Z

0

import pandas as pd

# Sample DataFrame
data = {
    'col0': [0, 0, 0, 0, 0, 0, 1, 1],
    'col1': ['A', 'A', 'B', 'B', 'B', 'B', 'A', 'A'],
    'col2': ['X', 'Y', 'X', 'X', 'X', 'Y', 'X', 'Y']
}
df = pd.DataFrame(data)
print(df)

res = df.groupby(['col0','col1','col2']).size().unstack(fill_value = 0).eval('ratio = Y/X')\
.fillna(0).groupby(['col0','col1'])['ratio']\
.max().reset_index().rename(columns = {'ratio' : 'Y/X'})
print(res)
'''
   col0 col1       Y/X
0     0    A  1.000000
1     0    B  0.333333
2     1    A  1.000000
'''

answered Aug 16, 2024 at 17:32

Soudipta Dutta

2,0701 gold badge16 silver badges11 bronze badges

Collectives™ on Stack Overflow

pandas multi-index divide aggregated counts

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related