1

I have the below data :

enter image description here

So for each device there is hours data across 10 channels for Sep, Oct and Nov and these need to be summed up while pivoting on device_id and hence the output will be as shown in the second part of image.

Here is the data in text format:

device_id   month_id channel    hours       brand
214 201711  A           2.311       S
214 201710  A           6.071       S
214 201709  A           0.55        S
214 201711  B           0.603       S
214 201710  B           2.185       S
214 201709  B           2.62        S
214 201711  C           0.82        S
214 201710  C           25.70       S
214 201709  C           17.73       S

Output:             
device_id   A       B           C           brand
214     8.933054    5.412499    44.261665   S

All the data is in a pandas dataframe and I want to use pivot for this.Can someone please help me with this?

cs95
  • 379,657
  • 97
  • 704
  • 746
Shuvayan Das
  • 1,198
  • 3
  • 20
  • 40
  • Can you please clarify on your previous question if the assumption of `== 3` was correct or not? – cs95 Dec 27 '17 at 11:56
  • Hello @cᴏʟᴅsᴘᴇᴇᴅ, Sorry i was busy implementing your solution. Actually in the sample data I posted the condition would be ==3 but in real data there are many records whose values in the current will be more than 3. So for the purposes of the posted ques the assumption is completely correct. Thanks a lot for the help! – Shuvayan Das Dec 27 '17 at 11:59

2 Answers2

1

It looks like you want a pivot_table operation - 

df = df.pivot_table(index=['device_id', 'brand'], 
                    columns='channel', 
                    values='hours', 
                    aggfunc=sum)\
       .reset_index()

df.columns.name = None
df

   device_id brand      A      B      C
0        214     S  8.932  5.408  44.25
cs95
  • 379,657
  • 97
  • 704
  • 746
1

One-liner:

df.groupby(["device_id", "brand", "channel"])["hours"].sum().unstack()

Which gives:

channel              A      B      C
device_id brand                     
214       S      8.932  5.408  44.25
Alexey Trofimov
  • 4,287
  • 1
  • 18
  • 27
  • The other answer is also a "one-liner" (if you remove the axis-renaming step). Also, "one-liners" are overrated. – cs95 Dec 27 '17 at 17:43