Counting shared elements within groups with condition

user_5507389 0
Viewed 61

I'm having some difficulties with a Pandas query and would appreciate some help. The DataFrame I'm working with is similar to the following:

data = {
    "cusID": ["001", "002", "003", "004", "001", "003", "002", "001", "003", "002", "004"], 
    "productID": ["A", "A", "A", "A", "B", "B", "C", "D", "D", "E", "E"]
}
df = pd.DataFrame(data)
print(df)

   cusID productID
0    001         A
1    002         A
2    003         A
3    004         A
4    001         B
5    003         B
6    002         C
7    001         D
8    003         D
9    002         E
10   004         E

I would like to get a new DataFrame of customers who have bought 2 or more of the same products. Given the above example, the output DataFrame I'm looking for is the following:

    left_cusID right_cusID result
0          001         003      3
1          002         004      2

The result column is equal to the number of shared products between the two customers.

Also note that for this problem we assume that a customer can't purchase more than one of the same productID.

I thought of this problem for a while before posting it here but I couldn't find the appropiate steps/logic to solve it. At one point I thought I could cross-join the table with itself to have all combinations of left/right cusIDs but I got stuck quickly with that also. Any help would be appreciated.

pandas dataframe python
4 Answers

This is perfect for dot

s = pd.crosstab(df.cusID, df.productID)
s = s.dot(s.T)
s = s.astype(float)
s.values[np.tril_indices(s.shape[0], 0)] = np.nan

s
Out[66]: 
cusID   1    2    3    4
cusID                   
1     NaN  1.0  3.0  1.0
2     NaN  NaN  1.0  2.0
3     NaN  NaN  NaN  1.0
4     NaN  NaN  NaN  NaN


s.where(s.ne(1)).stack()
Out[68]: 
cusID  cusID
1      3        3.0
2      4        2.0
dtype: float64
user_79645270

This is an interesting logic question. I came up with a solution, working off your idea of a cross join. I imagine this could be done more eficiently.

import numpy as np
import pandas as pd

data = {
    "cusID": ["001", "002", "003", "004", "001", "003", "002", "001", "003", "002", "004"], 
    "productID": ["A", "A", "A", "A", "B", "B", "C", "D", "D", "E", "E"]
}
df = pd.DataFrame(data)

result = df.merge(df, on='productID').groupby(['cusID_x','cusID_y']).count().reset_index()
result.columns = ['left_cusID', 'right_cusID', 'result']
mask = np.logical_and(result.left_cusID<result.right_cusID, result.result>1)
result[mask].reset_index(drop='True')
user_98781650

Edit: Please use @BEN_YO's vectorized solution unless memory is a problem. The only possible advantage of this solution is that the creation of intermediate objects could be minimized. But this is not likely to offset the slowness of double for-loop in general.

You can collect the products bought by each customer in a set column using groupby followed by .apply(set). Subsequently, apply set.intersection() to get the common goods, and output only if the length of the intersection is greater than 1.

Code

# collect
df_set = df.groupby("cusID")["productID"].apply(set).sort_index().reset_index()

# Cartesian join by double for loop
ls_ans = []  # [left_cusID, right_cusID, result] to be appended
for i in range(len(df_set)):
    for j in range(i+1, len(df_set)):  # only j > i
        l = len(df_set.at[i, "productID"].intersection(df_set.at[j, "productID"]))
        if l > 1:
            ls_ans.append([df_set.at[i, "cusID"], df_set.at[j, "cusID"], l])

df_ans = pd.DataFrame(ls_ans, columns=["left_cusID", "right_cusID", "result"])

Result

print(df_ans)
  left_cusID right_cusID  result
0        001         003       3
1        002         004       2

Other

Also check out the collected set of productID's (sorted):

print(df_set)
  cusID  productID
0   001  {B, A, D}
1   002  {A, C, E}
2   003  {B, A, D}
3   004     {A, E}
user_32186930 
import pandas as pd

data = {
    "cusID": ["001", "002", "003", "004", "001", "003", "002", "001", "003", "002", "004"],
    "productID": ["A", "A", "A", "A", "B", "B", "C", "D", "D", "E", "E"],
}
dfini = pd.DataFrame(data)

customers_products = {}
customers = set(dfini["cusID"])
products_in_common = []

for customer in customers:
    customers_products.update({customer: set(dfini.loc[dfini.cusID == customer].productID)})


for customer in customers:
    for other in customers - {customer}:
        in_common = customers_products[customer] and customers_products[other]
        products_in_common.append([customer, other, len(in_common)])

data_result = pd.DataFrame(products_in_common, columns=["left", "right", "result"])

Ugly as hell, but it works.

user_140423130
Related