I have a pandas data frame which looks like this:
Age Sex
23 Male
34 Female
38 Female
32 Male
33 Female
20 Male
34 Male
I need to group it and tabulate it so that it looks like this
Age Male Female
21 - 25 2 0
26 - 30 0 0
31 - 35 2 2
36 - 40 0 1
How can I do this is Pandas?
You can do so as follows.
Start by setting a 'count' column to 1:
df['count'] = 1
Now make another column of the age group. In the array in the next line, place whatever boundaries you like:
df['age_group'] = pd.cut(df.Age, [20, 31, 36])
Now, all you need to do is pivot the table using the age group as the index, gender as the columns, the count as values, and the aggregation as sum:
>>> df.pivot_table('count', index='age_group', columns='Sex', aggfunc='sum')
Sex Female Male
age_group
(20, 31] NaN 1
(31, 36] 2 2
If you want, you can further change the NaNs with fillna:
>>> df.pivot_table('count', index='age_group', columns='Sex', aggfunc='sum').fillna(0)
Sex Female Male
age_group
(20, 31] 0 1
(31, 36] 2 2
