I am working on a back-in-stock alert problem where users provides email id, the product he/ she is interested in, 3 stores from where he can pick it up when it is back in stock. I have the data in a pandas dataframe where there are email id, product id, 3 stores mapped against it.
import pandas as pd
import numpy as np
df = pd.DataFrame([['nick_1@gmail.com', 9999, 1000, 1001, 1002, np.nan ],
['sam_s@gmail.com', 9999, 1000, 1001, 1002, np.nan ],
['george_l@gmail.com', 9999, 1000, 1001, 1500, np.nan ],
['fred_g@gmail.com', 9777, 1500, 1501, 1155, np.nan ],
['katie_l@gmail.com', 9777, 1500, 1501, 1155, np.nan ]],
columns=['CUSTOMER_EMAIL', 'PRODUCT_ID', 'STORE_1', 'STORE_2', 'STORE_3', 'SEND_NOTIFICATION'])
I need to set the column SEND_NOTIFICATION as True / False based on the stock availability in another data frame below:
stock = pd.DataFrame([[9999, 1000, 2],
[9999, 1002, 1 ],
[9977, 1500, 1 ]
], columns=['PRODUCT_ID', 'STORE_ID',
'STOCK_AVAILABLE'])
Here there are two items available in stock for product 9999 in store ID 1000. So notification can be sent for max two users who signed up. In this case SEND_NOTIFICATION column for nick_1@gmail.com, sam_s@gmail.com can be set to True. Row george_l@gmail is set to False as there is not enough stock available in any of the store mapped against him.
Product 9977 has one item in stock in store ID 1500. So one user fred_g@gmail.com can be set to True, while katie_l is set to False due to lack of stock
The output should look like below:
I am thinking of a naive approach of looping through each item in stock data frame, searching for product-stock combinations as many times as available stock.
for index, row in stock_data.iterrows():
stock_available = row["SOH"]
while stock_available >= 0:
stock_available -= 1
<search for product id - store id combination in other dataframe and mark records as true>
Is there a better way to achieve this outcome? I think pandas cumsum is something I could use, but not able to wrap my head around how to implement the logic.