How to create a summary DF using data from a primary DF?

Question

This is a small extract of some mock data I am using - it's form what I am calling the "primary" DF. It has multiple customer keys, who each can have multiple devices which could access wifi on a number of days.

Customer Account Key  Device Ref  Date        Data Used (mb)
ABC123                Dev1        03/06/2018  100
ABC123                Dev2        03/06/2018  500
ABC123                Dev3        03/06/2018  250
ABC123                Dev1        04/06/2018  600
ABC123                Dev2        04/06/2018  1000
ABC123                Dev3        04/06/2018  350

I would like to summarise this date in a second DF and it would look like this

Customer_Account_Key Total_Devices Total_Days Total_Data_Used
ABC123               3             2          2800

So far I have managed to create a second DF which has only one row for each of the unique customer account keys

df_users['Customer Account Key'] = df_data['Customer Account Key'].unique()

But I am really struggling to extract summary information from the main DF based on the each of the Customer account keys in my new DF.

I have played around with Groupby and df.loc but I am just not getting anywhere. I am new to Python so I'm not sure if these are the wrong approach or if I'm just not using them correctly.

Any pointers?

Thanks

YOLO · Accepted Answer · 2018-06-05 18:52:33Z

1

You can use groupby + agg function:

# aggregate data
df = df.groupby('Customer').agg({'Account_Key': {'Total_Devices':'nunique'},
                                 'Device_Ref_Date':{'Total_Days':'nunique'},
                                 'Data_Used':{'Total_Data_Used':'sum'}})

# remove multiindex column names
df.columns=df.columns.droplevel()
df = df.reset_index()

print(df)

   Customer  Account_Key  Device_Ref_Date  Data_Used
0   ABC123            3                2       2800

edited Jun 5, 2018 at 18:52

answered Jun 5, 2018 at 18:47

YOLO

22k5 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Thank you so much! Have spent so long trying to work this out and this has solved it :) Now I know this I can adapt it in other ways too.

Collectives™ on Stack Overflow

How to create a summary DF using data from a primary DF?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related