Multilevel Indexing with Groupby

user_14961635 0
Viewed 25

Being new to python I'm struggling to apply other questions about the groupby function to my data. A sample of the data frame :

ID    Condition    Race      Gender      Income
1         1        White      Male          1
2         2        Black     Female         2
3         3        Black      Male          5
4         4        White     Female         3
...

I am trying to use the groupby function to gain a count of how many black/whites, male/females, and income (12 levels) there are in each of the four conditions. Each of the columns, including income, are strings (i.e., categorical).

I'd like to get something such as

Condition    Race       Gender     Income    Count
   1         White       Male         1        19
   1         White      Female        1        17
   1         Black       Male         1        22
   1         Black      Female        1        24
   1         White       Male         2        12
   1         White      Female        2        15
   1         Black       Male         2        17
   1         Black      Female        2        19
  ...

Everything I've tried has come back very wrong so I don't think I'm anywhere near right, but I"m been using variations of

Data.groupby(['Condition','Gender','Race','Income'])['ID'].count()

When I run the above line I just get a 2 column matrix with an indecipherable index (e.g., f2df9ecc...) and the second column is labeled ID with what appear to be count numbers. Any help is appreciated.

pandas
1 Answers

if you would investigate the resulting dataframe you would see that the columns are inside the index so just reset the index...

df = Data.groupby(['Condition','Gender','Race','Income'])['ID'].count().reset_index()

that was mainly to demonstrate but since you what you want you can sepcify the argument 'as_index' as following:

df = Data.groupby(['Condition','Gender','Race','Income'],as_index=False)['ID'].count()

also since you want the last column to be 'count' :

df = df.rename(columns={'ID':'count'})
user_88938270
Related