How to detect if consumer was already in dataframe?-CodePudding

I have a following dataset that contains information if a consumer gave a recommendation or not:

data = {'customer_id': [1, 2, 1, 3], 'recommend': [0, 1, 1, 0]}
df = pd.DataFrame.from_dict(data)

I would like to know if a customer gave 0 recommendation in the past. Desired output would be:

data = {'customer_id': [1, 2, 1, 3], 'recommend': [0, 1, 1, 0], 'past': [0, 0, 1, 0]}
df = pd.DataFrame.from_dict(data)

How can I do it please?

CodePudding user response：

You can do it by first adding a new column with your condition (recommend == 0) and then using groupby together with shift and cummax to obtain the wanted past column. Finally, drop the temporary column created.

Code:

df['equal_zero'] = (df['recommend'] == 0).astype(int)
df['past'] = df.groupby('customer_id')['equal_zero'].shift(1).cummax().fillna(0)
df = df.drop(columns=['equal_zero'])

Result:

  customer_id   recommend   past
0           1           0    0.0
1           2           1    0.0
2           1           1    1.0
3           3           0    0.0

CodePudding user response：

Assuming 'past' is a boolean, (1 if a customer gave a zero in the past, 0 else).

Here is a one-line solution :

df['past'] = df.apply(lambda x: 1 if len(df[(df.customer_id == x.customer_id) & (df.index < x.name) & (df.recommend == 0)]) > 0 else 0, axis=1)

If 'past' is a count value :

df['past'] = df.apply(lambda x: len(df[(df.customer_id == x.customer_id) & (df.index < x.name) & (df.recommend == 0)]), axis=1)

CodePudding user response：

Use custom function per groups with shifting and cumulative max in GroupBy.transform:

df['past'] = (df['recommend'].eq(0)
                             .groupby(df['customer_id'])
                             .transform(lambda x: x.shift(fill_value=False).cummax())
                             .astype(int))

print (df)

   customer_id  recommend  past
0            1          0     0
1            2          1     0
2            1          1     1
3            3          0     0