I am doing a project using this dataset https://www.kaggle.com/carrie1/ecommerce-data/code?datasetId=1985&sortBy=voteCount
And I need to create a table with the following variables
However the last and first purchase is showing NAN values. How can i fix it? those are the codes that I Wrote
temp = df.groupby(by=['CustomerID', 'InvoiceNo', 'Country'], as_index=False)['AmountSpent'].sum()
basket_price = temp.rename(columns = {'TotalPrice':'Basket Price'})
df['InvoiceDate_int'] = df['InvoiceDate'].astype('int64')
temp = df.groupby(by=['CustomerID', 'InvoiceNo', 'Country'], as_index=False)['InvoiceDate_int'].mean()
df.drop('InvoiceDate_int', axis = 1, inplace = True)
basket_price.loc[:, 'InvoiceDate'] = pd.to_datetime(temp['InvoiceDate_int'])
basket_price = basket_price[basket_price['AmountSpent'] > 0]
basket_price.sort_values('CustomerID')[:6]
transactions_per_user=basket_price.groupby(by=['CustomerID', 'Country'])['AmountSpent'].agg(['count','min','max','mean','sum'])
last_date = basket_price['InvoiceDate'].max().date()
first_registration = pd.DataFrame(basket_price.groupby(by=['CustomerID'])['InvoiceDate'].min())
last_purchase = pd.DataFrame(basket_price.groupby(by=['CustomerID'])['InvoiceDate'].max())
test = first_registration.applymap(lambda x:(last_date - x.date()).days)
test2 = last_purchase.applymap(lambda x:(last_date - x.date()).days)
transactions_per_user.loc[:, 'LastPurchase'] = test2.reset_index(drop = False)['InvoiceDate']
transactions_per_user.loc[:, 'FirstPurchase'] = test.reset_index(drop = False)['InvoiceDate']
transactions_per_user[:5]