Create flag if customer ordered in the next month / If total of a column for the next weeks is more-CodePudding

I have a dataframe like so:

    CUSTOMER    WEEK NO  ORDERS
0   Ann         4        1
1   Ann         6        3
2   John        1        1
3   John        7        2

I`d like to add a Flag column that indicates if the customer made an order in the next month / within the next 4 weeks. My ideal output would be something like this:

    CUSTOMER    WEEK NO  ORDERS  FLAG
0   Ann         4        1       1
1   Ann         6        3       0
2   John        1        1       0
3   John        7        2       0

I looked at some examples on this site and derived this code. It seems to work on a few accounts but when I apply to the whole dataframe, everything is flagged as 1. I'm not sure why, I even added the Customer condition but again, when applied to the whole dataframe it doesn`t work:

df['Flag'] = df.apply(lambda x: 1 if df.loc[(df.Week_No >= x.Week_No) &
                        (df.Week_No <= x.Week_No 4) & (x.Customer==df.Customer), 'total_orders'].sum()>=1
                        else 0, axis=1)

CodePudding user response：

Group the dataframe by CUSTOMER, find the differences between WEEK NOs, and then shift the results back to line up how we want. If these differences are <=4, the mask will be True.

df['FLAG'] = 0
mask = df.groupby('CUSTOMER')['WEEK NO'].diff().shift(-1).le(4)
df['FLAG'].mask(mask, 1, inplace=True)

Output:

  CUSTOMER  WEEK NO  ORDERS  FLAG
0      Ann        4       1     1
1      Ann        6       3     0
2     John        1       1     0
3     John        7       2     0