[Python] Groupby and Aggregate Analytics

I am trying to create aggregate anaytics based on a df. Here is a demo of my data.

input=pd.DataFrame({'ID':['A','A','A','A','B','B','B','B','C','C','C','C'],
'Year':[2000,2000,2000,2000,2000,2000,2000,2000,2001,2001,2001,2001],
'Item':['a1','a2','a3','a4','b1','b2','b3','b4','c1','c2','c3','c4'],
'Price':[1,3,4,5,2,4,7,3,5,7,6,1],
'Label 1':[1,1,0,0,1,1,1,0,0,0,0,1],
'Label 2':[0,1,1,0,0,1,1,0,1,0,0,1]})


My output looks like following output. My goal is for each ID and Year, I count eachLabel that is equal to 1. In addition, I also want to calculate the total price. For example, for company A, item 'a1' and 'a2' have 'Label 1' of 1, so the 'Label 1 Count'=2. The 'Label 1 Price' is 11+13=4.

output=pd.DataFrame({'ID':['A','B','C'],
'Year':[2000,2000,2001],
'Label 1 Count':[2,3,1],
'Label 2 Count':[2,2,2],
'Label 1 Total Price':[4,13,1],
'Label 2 Total Price':[7,11,6],
'Item Total':[4,4,4]})


What I have done is:

input['Label 1 Price']=input['Label 1']*input['Price']
input['Label 2 Price']=input['Label 2']*input['Price']

input.groupby(['ID','Year']).agg({'Label 1':'sum',
'Label 2':'sum',
'Label 1 Price':'sum',
'Label 2 Price':'sum',
'Item':'count'})


Is there a more elegant solution? Also, my raw dataset has way more "Label", so I don't want to manually create "Label x Price".

Continue reading...