I'm working on a project with Python where I need to do some robust comparisons across columns in a dataframe. I'm not sure how to do these, and with the dataset being very large, don't want to waste dev time waiting for it to run.
My DF has many columns, but these columns are the ones involved in these comparisons: ID (int) PRODUCT ID (int) PURCHASE_ID (int) PURCHASE_DATE (date YYYY-MM-DD) DAYS_SUPPLY (decimal) VISIT_DATE (date YYYY-MM-DD)
What I'm trying to do is:
- Create a new column, NEXT_PURCHASE, where it calculates the day that the customer's supply should run out and they'll make a new purchase. This would be the PURCHASE_DATE + DAYS_SUPPLY
- Using the new date in NEXT_PURCHASE, can we find a person (ID) with a PURCHASE_ID that matches that NEXT_PURCHASE date? If so, can we grab the PRODUCT_ID from the NEXT_PURCHASE and compare it to the PRODUCT_ID from that first PURCHASE_DATE?
- When we compare those PRODUCT_IDs, can we create new dataframes for each type of behavior that we would see? Cases would be:
Case 1: Person purchases the same item on both dates (PRODUCT_ID is the same)
Case 2: Person purchases a different item on both dates (PRODUCT_ID is different
across both dates)
Case 3: Person purchases an item on the first date, but there is no PURCHASE_ID found
for that person on the date calculated in NEXT_PURCHASE
I'm also trying to figure out a fourth case of people who hadn't made a purchase on an initial VISIT_DATE, but eventually did make a purchase.
I've never done such an in-depth set of comparisons in a python df, so I'm not sure if its even possible. I eventually want to use these groupings to feed into a machine learning model to try to predict purchase behavior (there are other factors that would help contribute to this, but these are the main transformations needed to support those). Purchase behaviors would be those groups - no change, change to new, no longer making purchases, new customer.
I tried to do this with Snowpark in Python, but I am so unfamiliar with that since its so new; I'm thinking maybe if I can create it in Python with a pandas DF, I can then translate for use.