Pandas: using groupby

Question

I have dataframe

ID    domain    category    active_seconds
111   vk.com    Social_network   42
111   facebook.com   Social_network   18
222   vk.com      Social_network     50
222   gmail.com   E-mail    50

If I use

df.groupby(['category', 'domain']).agg({'ID': pd.Series.nunique, 'active_seconds': np.sum}).rename(columns={'ID': 'all_users', 'active_seconds': 'all_time'}.reset_index()

I get with it

category domain all_users all_time Social_network vk.com 2 92 Social_network facebook.com 1 18 E-mail gmail.com 1 50

But is any way to get report in this format:

category          domain     all_users     all_time
Social_network                   2            110
                   vk.com        2             92
                  facebook.com   1             18

E-mail                           1             50
                   gmail.com     1             50                   

Answer 1

You can create new DataFrame by agg sum and nunique and add new level of MultiIndex.from_arrays, last concat with sort_index:

#omit reset_index
df1 = df.groupby(['category', 'domain'])
        .agg({'ID': pd.Series.nunique, 'active_seconds': np.sum})
        .rename(columns={'ID': 'all_users', 'active_seconds': 'all_time'})

df2 = df1.groupby('category').agg({'all_users': 'nunique', 'all_time': 'sum'})

df2.index = pd.MultiIndex.from_arrays([df2.index, [''] * len(df2.index)],
                                       names=('category','domain'))
print (df2)
                       all_time  all_users
category       domain                     
E-mail                       50          1
Social_network              110          2

print (pd.concat([df1,df2]).sort_index())
                             all_time  all_users
category       domain                           
E-mail                             50          1
               gmail.com           50          1
Social_network                    110          2
               facebook.com        18          1
               vk.com              92          2

Another solution for new DataFrame is create new column by assign and set_index:

df2 = df1.groupby('category').agg({'all_users': 'nunique', 'all_time': 'sum'})
         .assign(domain='')
         .set_index('domain', append=True)
print (df2)
                       all_time  all_users
category       domain                     
E-mail                       50          1
Social_network              110          2