import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, timedelta
#Load Data
data = pd.read_csv("G:/.shortcut-targets-by-id/1SNU6AKxzBtTEAum9YeqKtgoTzKPg9vZX/Creative/Solver - Tableros Comandos/Supermart Grocery Sales - Retail Analytics Dataset.csv")
data.head(3)

# Transform data in the correct format
data['Order Date']  = pd.to_datetime(data['Order Date'],format='mixed')

#(Optional) 
    #To ensure the information is up-to-date and provides a better perception of the current time.
df_time = datetime.now()-data['Order Date'].max()
data['Order Date'] = data['Order Date']+ df_time
data['Order Date'] = pd.to_datetime(data['Order Date'])
data['Year'] = data['Order Date'].dt.year

data.head(3)


from ydata_profiling import ProfileReport
profile = ProfileReport(data, title="Profiling Report")
profile

#Basic stats to analyses the whole dataset
data.describe(include='all').T

##RFM Process


#Define useful columns to RFM process
df_rfm = data[['Order ID', 'Customer Name','Order Date','Profit']]

# We will consider 'today's' date as the date of the last invoice plus 1 day.
snapshot_date = df_rfm['Order Date'].max() + timedelta(days=1) 

# We group by customer and review Recency, Frequency, and Monetary.
data_process_rfm = df_rfm.groupby(['Customer Name']).agg({
    'Order Date': lambda x: (snapshot_date - x.max()).days, # Recency
    'Order ID': 'count', # Frequency
    'Profit': 'sum'}).reset_index().sort_values(by='Profit',ascending=False) # Monetary

# We rename the columns for greater clarity.
data_process_rfm.rename(columns = { 'Order Date': 'Recency',
                                    'Order ID': 'Frequency',
                                    'Profit': 'MonetaryValue'
                                  }, 
                        inplace=True)


#We divide the data into quartiles for Recency and Monetary Value.
#We use custom labels for the quartiles.

#Recency
r_labels = range(4, 0,-1)  # Descending labels for 4 quartiles.
r_quartiles = pd.qcut(data_process_rfm['Recency'], 4, labels=r_labels)

#Monetary
m_labels = range(1, 5)  # Ascending labels for the 4 quartiles.
m_quartiles = pd.qcut(data_process_rfm['MonetaryValue'], 4, labels=m_labels)

#Frequency
f_labels = range(1, 5)  # Ascending labels for the 4 quartiles
f_quartiles = pd.qcut(data_process_rfm['Frequency'], 4, labels=f_labels)


# We assign the labels to our data.
data_process_rfm = data_process_rfm.assign(R=r_quartiles.values)
data_process_rfm = data_process_rfm.assign(F=f_quartiles.values)
data_process_rfm = data_process_rfm.assign(M=m_quartiles.values)

# We calculate the RFM Score by summing the values of R, F, and M.
data_process_rfm['RFM_Score'] = data_process_rfm[['R', 'F', 'M']].sum(axis=1)

# We visualize the first records of the processed data.
data_process_rfm.head(3)

#Label customer based on RFM_Score
data_process_rfm.loc[data_process_rfm['RFM_Score']>2,'Customer Type']='Bronze'
data_process_rfm.loc[data_process_rfm['RFM_Score']>5,'Customer Type']='Silver'
data_process_rfm.loc[data_process_rfm['RFM_Score']>8,'Customer Type']='Gold'
data_process_rfm.head(3)

#Visualize the RFM process in their dimesions
sns.pairplot(data_process_rfm[['Recency','Frequency','MonetaryValue','Customer Type']], hue= 'Customer Type')

data_export = pd.merge(data,data_process_rfm[['Customer Name','Customer Type']],left_on='Customer Name', right_on='Customer Name')
data_export.head(3)

# Let's assume you already have a DataFrame called df with a 'fecha' column
# Make sure the 'fecha' column is in date format; if not, convert it using pd.to_datetime.

# Get the first and last date from the original DataFrame

date_min = data_export['Order Date'].min()
date_max = data_export['Order Date'].max()
date_max = date_max + pd.offsets.MonthEnd(0)


# Create a date range from the first date to the last date.
date_range = pd.date_range(start=date_min, end=date_max)

# Create a calendar DataFrame based on the date range.
calendar = pd.DataFrame({'date': date_range})

# Create the necessary columns in the calendar DataFrame.
calendar['Code_month'] = calendar['date'].dt.strftime('%Y%m')
calendar['Quarter'] = 'Q' + calendar['date'].dt.quarter.astype(str)
calendar['Semester'] = 'S' + calendar['date'].dt.month.apply(lambda x: 1 if x <= 6 else 2).astype(str)
calendar['Year'] = calendar['date'].dt.year
calendar['Date_LY'] = calendar['date'] - pd.DateOffset(years=1)
calendar['Week_Number'] = calendar['date'].dt.day // 7 + 1
calendar['Month'] = calendar['date'].dt.strftime('%B')
calendar['Num_month'] = calendar['date'].dt.strftime('%m')

# The 'calendar' DataFrame will now contain all the dates between the first and last dates present in your original 'Sales data'
calendar.sort_values(by='date',ascending=False,inplace=True)

calendar.head(3)