# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)
import matplotlib.pyplot as plt
import seaborn as sns

%matplotlib inline

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))
        
        
from pylab import rcParams

rcParams['figure.figsize'] = 30, 8

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
"""
# Analysis of Bike Data and predictive modeling with CLV models

## explorative data analysis
"""
"""
The goal of this report is to analyze the customers bases of the different bike companies. Besides explorative data analysis and hypothesis testing (e.g. ANOVA) we will focus on predicting the value of the customer base (CLV)
"""
"""
### Reading the data
Reading in the data from csv files as panda dataframe, Demographic seems to have encoding problems, when encoding errors is set to "strict", setting it to "ignore" fixes the problem
"""
List = pd.read_csv("/kaggle/input/bicycle-store-dataset/Customer List.csv")
Adress = pd.read_csv("/kaggle/input/bicycle-store-dataset/Customer Address.csv")
Demographic = pd.read_csv("/kaggle/input/bicycle-store-dataset/Customer Demographic.csv", encoding_errors="ignore", encoding="ascii")
Transactions = pd.read_csv("/kaggle/input/bicycle-store-dataset/Transactions.csv")
print(len(Demographic), len(Transactions), len(Adress), len(List))
Transactions.columns[:13]
"""
### feature engineering and merging of the data
Extracting only the columns we need from the data, more than 200 columns have been imported from the csv that don't have any values
"""
Demographic_mod = Demographic[Demographic.columns[:13]]
List_mod = List[['first_name', 'last_name', 'gender',
       'past_3_years_bike_related_purchases', 'DOB', 'job_title',
       'job_industry_category', 'wealth_segment', 'deceased_indicator',
       'owns_car', 'tenure', 'address', 'postcode', 'state', 'country',
       'property_valuation', 'Rank', 'Value']]
Transactions_mod = Transactions[Transactions.columns[:13]]

Transactions_mod[Transactions_mod["customer_id"].isnull()]
"""
Merging the data. Because we are performing an "inner join" all the rows that don't have a (matching) customer_id value get dropped. We will first focus on the Transactions and the Demographic dataset as these contain all of the data we need.
"""

data_merged = pd.merge(Demographic_mod, Transactions_mod, on="customer_id", how="inner")
print(len(data_merged.columns))
print(len(data_merged))
data_merged.head()
print(data_merged.columns)
data_merged["brand"].value_counts()
data_merged["full_name"] = data_merged["first_name"] + " "+ data_merged["last_name"]
len(data_merged["past_3_years_bike_related_purchases"])
data_merged["brand"].unique()

# repl = dict(zip(data_merged["brand"].unique(), ["OHM", "Solex", "Trek", "Norco", "Giant", "WeareA2B", np.nan]))
#data_merged_c = data_merged
#dat_merged_c = data_merged_c.replace(repl)
#data_merged_c.head()
data_merged_c = data_merged
data_merged_c.loc[data_merged_c["brand"] == "OHM Cycles", "brand"] = "OHM"
data_merged_c.loc[data_merged_c["brand"] == "Norco Bicycles", "brand"] = "Norco"
data_merged_c.loc[data_merged_c["brand"] == "Giant Bicycles", "brand"] = "Giant"
data_merged_c.loc[data_merged_c["brand"] == "Trek Bicycles", "brand"] = "Trek"
data_merged_c["brand"].unique()

"""
## Create an "age" column
"""
pd.to_datetime(data_merged_c["DOB"])
year = (pd.to_datetime("2021-10-12") - pd.to_datetime(Demographic_mod["DOB"]))/np.timedelta64(1, "Y")
Demographic["age"] = round(year)
round(year)
Demographic_mod["age"] = round(year)
"""
## Plotting
"""
#sns.displot(Demographic_mod, x="past_3_years_bike_related_purchases")
#cus10 = data_merged.loc[data_merged["customer_id"] == 10]
#date = cus10["transaction_date"]
#date.sort()
frequency = pd.DataFrame(data=data_merged["customer_id"].value_counts())
frequency["customer_id"]
Demographic_mod["revenue"] = data_merged.groupby("customer_id")["list_price"].sum()
Demographic_mod
Demographic_mod["avg_spent"] = data_merged.groupby("customer_id")["list_price"].mean()
sns.displot(Demographic_mod["avg_spent"])
bins = [20, 40, 55, 80]
bin_indices = np.digitize(Demographic_mod["age"], bins)
Demographic_mod["age_grp"] = bin_indices
Demographic_mod[["age_grp", "age", "avg_spent"]]
dem_avg = Demographic_mod.groupby("age_grp").mean()
# sns.catplot(Demographic_mod, x="age_grp", y="avg_spent")
plt.title("Avg amount spent per customer")
dem_avg
Demographic_mod["gender"].value_counts()
"""
## Compare frequency for different companies
"""
freq_giant = data_merged.loc[data_merged["brand"] == "Giant"]["customer_id"].value_counts()
data_giant = data_merged.loc[data_merged["brand"] == "Giant"]
freq_trek = data_merged.loc[data_merged["brand"] == "Trek"]["customer_id"].value_counts()
sns.displot(freq_giant)
plt.xlabel("frequency")
plt.title("Buying frequency of Giant customers")
ind = freq_giant.index + 1
ind
"""
Plotting the distribution of the number of bike purchases per person. And checking for normal distribution as this property is a prerequisite for the subsequent statistical tests. (Assumption checks)
"""
"""
## Compare different in total
"""
sns.displot(frequency, x=frequency["customer_id"])
"""
## Compare list_price depending on the company
"""
# plt.rcParams['figure.figsize'] = [1, 5]
%matplotlib inline
sns.catplot(y="list_price", x="brand", kind="point", data=data_merged)
plt.title("Average list prices and confidence intervall for different brands")

"""
## Distribution of age for people under 100
"""

# l = Demographic.loc[Demographic["age"] < 100]
demo_giant = Demographic_mod.loc[ind]
demo_giant = demo_giant.loc[demo_giant["age"] < 70]
sns.displot(demo_giant["age"])
plt.title("Age distribution of Giant customers")
demo_giant["age"]
data_merged_c.to_csv('mycsvfile.csv',index=False)
Demographic_mod.to_csv("demographic_add.csv", index=False)