How To Group By Multiple Columns

I want to group by my dataframe by different columns based on UserId,Date,category (frequency of use per day ) ,max duration per category ,and the part of the day when it is most

Solution 1:

From your question, it looks like you'd like to make a table with each combination and the count. For this, you might consider using the as_index parameter in groupby:

df.category.groupby(["UserId", "Date"], as_index=False).count()

Solution 2:

It looks like you might be wanting to calculate statistics for each group.

grouped = df.groupby(["UserId", "Date","category"])

result = grouped.agg({'category': 'count', 'duration': 'max'})
result.columns = ['group_count','duration_max']

result = result.reset_index()

result
   UserId        Date       category  group_count  duration_max
012020-09-10    System tool            13.436112020-09-11       Calendar            25.705212020-09-11  Phone_and_SMS            17.907392020-09-28   Productivity            130.260492020-09-28         Social            150.285

Solution 3:

You take advantage of pandas.DataFrame.groupby , pandas.DataFrame.aggregate and pandas.DataFrame.rename in following format to generate your desired output in one line:

---

code:

import pandas as pddf= pd.DataFrame({'name': ['Settings','Calendar','Calendar', 'Messages', 'Instagram', 'Drive'],
                   'duration': [3.436, 2.167, 5.7050, 7.907, 50.285, 30.260],
                   'UserId': [1, 1, 1, 1, 2, 2],
                   'category' : ['System_tool', 'Calendar', 'Calendar', 'Phone_and_SMS', 'Social', 'Productivity'],
                   'part_of_day' : ['evening', 'night','night','night','night','night' ],
                   'Date' : ['2020-09-10', '2020-09-11', '2020-09-11', '2020-09-11', '2020-09-28', '2020-09-28'] })

df.groupby(['UserId', 'Date', 'category']).aggregate( count_cat = ('category', 'count'), max_duration = ('duration', 'max'))

out: