how to calculate percentages of a multilevel index groupby?

user_3880275 0
Viewed 69

Suppose you're given the following data:

records = [
    {'group': 'A', 'name': 'Chris', 'is_male': True, 'smoker': False}, 
    {'group': 'A', 'name': 'Bob', 'is_male': True, 'smoker': True}, 
    {'group': 'A', 'name': 'Dana', 'is_male': False, 'smoker': False}, 
    {'group': 'B', 'name': 'Alice', 'is_male': False, 'smoker': True}, 
    {'group': 'B', 'name': 'Michael', 'is_male': True, 'smoker': True}, 
    {'group': 'B', 'name': 'Ron', 'is_male': True, 'smoker': True}, 
    {'group': 'C', 'name': 'Rihanna', 'is_male': False, 'smoker': True}, 
    {'group': 'C', 'name': 'Madonna', 'is_male': False, 'smoker': True}, 
    {'group': 'C', 'name': 'Cher', 'is_male': False, 'smoker': False}, 
    {'group': 'C', 'name': 'John', 'is_male': True, 'smoker': True}, 
    
]

data = pd.DataFrame.from_dict(records)

If we do this:

data.groupby(['group', 'is_male', 'smoker']).count().reset_index()

we get:

group   is_male smoker  name
A   False   False   1
A   True    False   1
A   True    True    1
B   False   True    1
B   True    True    2
C   False   False   1
C   False   True    2
C   True    True    1

What I'd like to do is to get the same data, but in percentages, by the size of the group

For example, in group B we'll get 1/3 and 2/3

How can I do that?

pandas python-3.x python
1 Answers

You can try crosstab:

s = pd.crosstab(data['group'], [data['is_male'], data['smoker']], normalize='index')

Output:

is_male     False               True           
smoker      False     True      False     True 
group                                          
A        0.333333  0.000000  0.333333  0.333333
B        0.000000  0.333333  0.000000  0.666667
C        0.250000  0.500000  0.000000  0.250000

To match the long form, use stack:

s.stack(level=(0,1)).reset_index(name='name')

Output:

   group  is_male  smoker      name
0      A    False   False  0.333333
1      A    False    True  0.000000
2      A     True   False  0.333333
3      A     True    True  0.333333
4      B    False   False  0.000000
5      B    False    True  0.333333
6      B     True   False  0.000000
7      B     True    True  0.666667
8      C    False   False  0.250000
9      C    False    True  0.500000
10     C     True   False  0.000000
11     C     True    True  0.250000
user_42384080
Related