Python pandas group by column and return most recent modal value

user_14027432 0
Viewed 101

I have the following two dataframes:

  1. One containing a list of all UserIDs
  2. Another containing user web activity. It has the columns UserID, ProductID and Datetime.

Essentially, each row in the second dataframe pertains to an instance of a user viewing a product page on the given datetime.

Feel free to generate sample data with the following code:

import pandas as pd
from datetime import datetime

df1 = pd.DataFrame({'UserID': [f'UID0{i}' for i in range(1, 10)]})

df2 = pd.DataFrame({'UserID': ['UID04', 'UID02', 'UID09', 'UID02', 'UID04', 'UID02', 'UID07', 'UID07', 'UID07', 'UID04', 'UID07', 'UID07'], 
                    'ProductID': ['P017', 'P008', 'P241', 'P340', 'P363', 'P340', 'P166', 'P042', 'P042', 'P042', 'P166', 'P017'], 
                    'Datetime': ['2017-09-10 15:48:09', '2018-05-26 04:52:35', '2017-09-29 18:26:42', '2017-03-06 15:04:58', '2017-09-07 18:44:24', '2016-03-11 05:06:32', '2016-04-11 18:22:19', '2017-09-04 04:44:23', '2018-12-19 07:34:06', '2018-04-09 04:39:55', '2017-04-11 18:22:19','2019-02-11 15:06:32']})
df2['Datetime'] = pd.to_datetime(df2['Datetime'], format='%Y-%m-%d %H:%M:%S')

I would like to obtain the most frequently viewed product by each user. If there are multiple modes, i.e. multiple products with the same highest number of views, the modal product with the most recent view (based on the Datetime column) must be considered. If a user has not viewed any product, we can have a default string like 'NoProduct'.

So for the given sample data, the expected output would be something like this:

UserID
UID01   NoProduct
UID02   P340
UID03   NoProduct
UID04   P042
UID05   NoProduct
UID06   NoProduct
UID07   P042
UID08   NoProduct
UID09   P241

I have only been able to obtain all the modes using the code:

pd.merge(df1, df2.groupby(['UserID'])['ProductID'].agg(pd.Series.mode).to_frame().reset_index(), how='left').fillna('NoProduct')

giving the output:

    UserID  ProductID
0   UID01   NoProduct
1   UID02   P340
2   UID03   NoProduct
3   UID04   [P017, P042, P363]
4   UID05   NoProduct
5   UID06   NoProduct
6   UID07   [P042, P166]
7   UID08   NoProduct
8   UID09   P241

But I have not been able to figure out how to return only a single mode based on the latest date of all modal products for each user. Please suggest the best way to accomplish this.

pandas-groupby pandas dataframe python
1 Answers

Try:

df2["tmp"] = df2.groupby(["UserID", "ProductID"], as_index=False)["ProductID"].transform("count")

df2 = df2.sort_values(by=["tmp", "Datetime"], ascending=[False, False])

x = (
    df1.merge(
        df2.drop_duplicates(subset=["UserID"], keep="first"),
        on="UserID",
        how="left",
    )
    .drop(columns=["Datetime", "tmp"])
    .fillna("No Product")
)
print(x)

Prints:

  UserID   ProductID
0  UID01  No Product
1  UID02        P340
2  UID03  No Product
3  UID04        P042
4  UID05  No Product
5  UID06  No Product
6  UID07        P042
7  UID08  No Product
8  UID09        P241
user_100359850
Related