Find users most frequent recommandations based on input queries

Question

I have a input query table in the following:

    query
0  orange
1   apple
2    meat

which I want to make against the user query table as following

   user       query
0    a1      orange
1    a1  strawberry
2    a1        pear
3    a2      orange
4    a2  strawberry
5    a2       lemon
6    a3      orange
7    a3      banana
8    a6        meat
9    a7        beer
10   a8       juice

Given a query in input query, I want to match it to query by other user in user query table, and return the top 3 ranked by total number of counts.

For example, orange in input query, it matches user a1,a2,a3 in user query where all have queried orange, other items they have query are strawberry (count of 2), pear, lemon, banana (count of 1).

The answer will be strawberry （since it has max count), pear, lemon (since we only return top 3).

Similar reasoning for apple (no user query therefore output 'nothing') and meat query.

So the final output table is

    query   recommend
0  orange  strawberry
1  orange        pear
2  orange       lemon
3   apple     nothing
4    meat     nothing

Here is the code

import pandas as pd
import numpy as np
# Create sample dataframes
df_input = pd.DataFrame( {'query': {0: 'orange', 1: 'apple', 2: 'meat'}} )
df_user = pd.DataFrame( {'user': {0: 'a1', 1: 'a1', 2: 'a1', 3: 'a2', 4: 'a2', 5: 'a2', 6: 'a3', 7: 'a3', 8: 'a6', 9: 'a7', 10: 'a8'}, 'query': {0: 'orange', 1: 'strawberry', 2: 'pear', 3: 'orange', 4: 'strawberry', 5: 'lemon', 6: 'orange', 7: 'banana', 8: 'meat', 9: 'beer', 10: 'juice'}} )

target_users = df_user[df_user['query'].isin(df_input['query'])]['user']
mask_users=df_user['user'].isin(target_users)
mask_queries=df_user['query'].isin(df_input['query'])
df1=df_user[mask_users & mask_queries]
df2=df_user[mask_users]
df=df1.merge(df2,on='user').rename(columns={"query_x":"query", "query_y":"recommend"})
df=df[df['query']!=df['recommend']]
df=df.groupby(['query','recommend'], as_index=False).count().rename(columns={"user":"count"})
df=df.sort_values(['query','recommend'],ascending=False, ignore_index=False)
df=df.groupby('query').head(3)
df=df.drop(columns=['count'])
df=df_input.merge(df,how='left',on='query').fillna('nothing')
df

Where df is the result. Is there any way to make the code more concise?

user_11246056 · Answer

Unless there is a particular reason to favor pears over bananas (since they both count for one), I would suggest a more idiomatic way to do it:

import pandas as pd

df_input = pd.DataFrame(...)
df_user = pd.DataFrame(...)

df_input = (
    df_input
    .assign(
        recommend=df_input["query"].map(
            lambda x: df_user[
                (df_user["user"].isin(df_user.loc[df_user["query"] == x, "user"]))
                & (df_user["query"] != x)
            ]
            .value_counts(subset="query")
            .index[0:3]
            .to_list()
            if x in df_user["query"].unique()
            else "nothing"
        )
    )
    .explode("recommend")
    .fillna("nothing")
    .reset_index(drop=True)
)

print(df_input)
# Output
    query   recommend
0  orange  strawberry
1  orange      banana
2  orange       lemon
3   apple     nothing
4    meat     nothing

Find users most frequent recommandations based on input queries

1 Answers