Identify if customer from previous month has sales in this month using pandas

user_9586532 0
Viewed 763

I have a dataset which includes periods, customer and the amount sold. What I'm trying to determine is for customers who were sold to in the period 1 less than the current period, has that customer been sold to in this period (e.g. has a customer stopped purchasing all together in the most recent period):

My dataset looks like:

    PERIOD  CUSTOMER    AMOUNTSOLD
0   2020-08 C1  1000
1   2020-08 C2  1000
2   2020-08 C3  1000
3   2020-09 C1  1000
4   2020-09 C2  1000

I've thought about splitting the dataset into different dataframes dependent on the period, then checking if the the customer exists in the corresponding periods dataframe, but I don't think this would be a particularly efficient way to do it.

From Manakins comment, my expected output would be:

CUSTOMER    EXISTS IN PREVIOUS PERIOD AND CURRENT PERIOD
C1  Y
C2  Y
C3  N

Help would be very much appreciated,

Thank you

pandas time-series dataframe python
2 Answers

Here we need to use some rolling/window functions.

First, we need to ensure the datetime is a proper datetime. Then we can measure the datediff (assuming your df is sorted) and create a boolean to check for customers in previous months.

df['PERIOD']  = pd.to_datetime(df['PERIOD'])
df['timedelta'] = df.groupby(['CUSTOMER'])['PERIOD'].diff() 

df['Exists']= np.where(df.groupby('CUSTOMER')['CUSTOMER'].transform('size') > 1, 1,0)

df.loc[df.groupby('CUSTOMER')['PERIOD'].idxmax()] 

      PERIOD CUSTOMER  AMOUNTSOLD timedelta  Exists
3 2020-09-01       C1        1000   31 days       1
4 2020-09-01       C2        1000   31 days       1
2 2020-08-01       C3        1000       NaT       0
user_93751020

You can do it like this if you want to have it for a certain month:

currentmonth = "2020-09"

lastmonth = "2020-08"

df[df["PERIOD"] == lastmonth][df[df["PERIOD"] == lastmonth]["CUSTOMER"].isin(df[df["PERIOD"] == currentmonth]["CUSTOMER"])]
user_102898720
Related