1

I have a table that is looks like follows:

name  type    val 
A     online  12
B     online  24
A     offline 45
B     online  32
A     offline 43
B     offline 44

I want to dataframe in such a manner that it can be groupby with multiple cols name & type, which also have additional columns that return the count of the record with val being added of the same type records. It should be like follows:

name    type    count   val
A       online  1       12
        offline 2       88
B       online  2       56
        offline 1       44

I have tried pd.groupby(['name', 'type'])['val'].sum() that gives the addition but unable to add the count of records.

abhi1610
  • 721
  • 13
  • 28

2 Answers2

2

Add parameter sort=False to groupby for avoid default sorting and aggregate by agg with tuples with new columns names and aggregate functions, last reset_index for MultiIndex to columns:

df1 = (df.groupby(['name', 'type'], sort=False)['val']
        .agg([('count', 'count'),('val', 'sum')])
        .reset_index())
print (df1)
  name     type  count  val
0    A   online      1   12
1    B   online      2   56
2    A  offline      2   88
3    B  offline      1   44
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

You can try pivoting i.e 

df.pivot_table(index=['name','type'],aggfunc=['count','sum'],values='val')

             count sum
               val val
name type             
A    offline     2  88
     online      1  12
B    offline     1  44
     online      2  56
Bharath M Shetty
  • 30,075
  • 6
  • 57
  • 108