<jupyter_start><jupyter_text>Exploratory Data Analysis of supermarket customers behavior  Questions- Calculate the total number of customers in each section- Calculate the total number of customers in each section over time- Display the number of customers at checkout over time- Calculate the time each customer spent in the market- Calculate the total number of customers in the supermarket over time.- Our business managers think that the first section customers visit follows a different pattern than the following ones. Plot the distribution of customers of their first visited section versus following sections (treat all sections visited after the first as “following”).- Estimate the total revenue for a customer (section, revenue per minute)  SETUP<jupyter_code>import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
import seaborn as sns
import plotly.express as px
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Nov 17 08:49:50 2020

Script for iterating over data csv files in a data directory, and saving them in a dictionary of pandas dataframes

@author: hanbo
"""

import os
from pathlib import Path
import pandas as pd

####################################

data_dir= '../data'
files = os.listdir(data_dir)
my_sep = ';'
my_dfs = {}

for file in files:
    
   full_path = Path(data_dir) / Path(file)
   #print(full_path)
   #add each df to a dictionary
   df_name = file.split('.')[0]
   #print(df_name)
   df = pd.read_csv(full_path, sep = my_sep)
   my_dfs[df_name] = df
monday = pd.read_csv("../data/monday.csv", sep=";")
tuesday = pd.read_csv("../data/tuesday.csv", sep=";")
wednesday = pd.read_csv("../data/wednesday.csv", sep=";")
thursday = pd.read_csv("../data/thursday.csv", sep=";")
friday = pd.read_csv("../data/friday.csv", sep=";")

monday["weekday"] = "monday"
tuesday["weekday"] = "tuesday"
wednesday["weekday"] = "wednesday"
thursday["weekday"] = "thursday"
friday["weekday"] = "friday"

# Create a dataframe with all days
df = monday.append(tuesday, ignore_index=True)
df = df.append(wednesday, ignore_index=True)
df = df.append(thursday, ignore_index=True)
df = df.append(friday, ignore_index=True)

# Extract the hour from the timestamp
df["timestamp"] = pd.to_datetime(df["timestamp"])
df["hour"] = df["timestamp"].dt.hour
df["time"] = df["timestamp"].dt.time
data = pd.read_csv("./data/monday.csv", header=0, sep=";", parse_dates=True)
data.head()
data['timestamp'] = pd.to_datetime(data['timestamp'])
data.dtypes
# Encode the customer nr and day into one variable

data["customer_no"]=data["customer_no"].apply(lambda x: str(x)+'_monday')
data["customer_no"]<jupyter_output><empty_output><jupyter_text>DATA ANALYSIS - one day<jupyter_code>#missing customers from checkout
checkout=set(data[data["location"]=="checkout"]["customer_no"].unique())
all_c=set(data["customer_no"].unique())
diff=all_c.difference(checkout)
for cust in diff:
    data=data.append({"timestamp":"2019-09-02 22:00:00","customer_no":cust,
    "location":"checkout"}, ignore_index=True)


data[data["timestamp"]=="2019-09-02 22:00:00"]
data.groupby(["location"])['customer_no'].count().plot.barh()
plt.title("Customer distribution by location")
cust_per_aile=data[data['location']!="checkout"].groupby(["location"])['customer_no'].count()
cust_total=data[data['location']=="checkout"].groupby(["location"])['customer_no'].count()
perc_cust=pd.to_numeric(cust_per_aile.values)/pd.to_numeric(cust_total.values)
p=pd.concat([pd.DataFrame(perc_cust),pd.DataFrame(pd.unique(data['location'][data['location']!="checkout"]))], axis=1)
p.columns=["perc",'location']
p.set_index("location", inplace=True)
p.plot.barh()
plt.title("Percentage of customers per section")
# Nr of customers in each section over time

total_customers_time = pd.DataFrame(df.groupby(["location","hour"])["customer_no"].nunique())
total_customers_time.reset_index(inplace=True)
sns.relplot(x="hour", y="customer_no", hue="location", kind="line", data=total_customers_time)
data[data['location']!="checkout"].groupby("location").resample("30T").nunique()['customer_no'].unstack().plot(legend=False)
plt.title("Customer behavior at diffrent hours of the day")
data.reset_index(inplace=True)
data[data['location']=="checkout"].groupby("location").resample("15T").nunique()['customer_no'].reset_index().set_index("timestamp").plot()
plt.title("Amount of people at the checkout in an interval of 15 mins")
data['diff'] = data.sort_values(['customer_no','timestamp']).groupby('customer_no')['timestamp'].diff()
data[data["diff"].notnull()][["customer_no","diff"]].groupby("diff").nunique()["customer_no"].reset_index().set_index("diff").plot.bar()
plt.tite("How much time do customers spend in the shop?")

# Amount spent by each customer
data[data["diff"].notnull()][["customer_no","diff"]]
data.resample("15T").nunique()["customer_no"].plot()
plt.title("Total customer numbers inside the supermarket over time")
data["hour"]=data.index.hour
data[data["location"] !="checkout"].groupby(["hour","location"]).nunique()["customer_no"].unstack().plot.bar()
data["hour"]=data.index.hour
data[data["location"] =="checkout"].groupby(["hour","location"]).nunique()["customer_no"].unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>DATA ANALYSIS - all days<jupyter_code>def missing_checkout(data):
    checkout=set(data[data["location"]=="checkout"]["customer_no"].unique())
    all_c=set(data["customer_no"].unique())
    diff=all_c.difference(checkout)
    for cust in diff:
        data=data.append({"timestamp":"2019-09-02 22:00:00","customer_no":cust,
        "location":"checkout"}, ignore_index=True)

    return data

def cust_no_name(data, weekday):
    data["customer_no"]=data["customer_no"].apply(lambda x: str(x)+"_"+ weekday)
    return data
monday=pd.read_csv("./data/monday.csv", header=0, sep=";", parse_dates=True)
missing_checkout(monday)
monday["weekday"]="monday"
cust_no_name(monday,"monday")

tuesday=pd.read_csv("./data/tuesday.csv", header=0, sep=";", parse_dates=True)
missing_checkout(tuesday)
tuesday["weekday"]="tuesday"
cust_no_name(tuesday,"tuesday")

wednesday=pd.read_csv("./data/wednesday.csv", header=0, sep=";", parse_dates=True)
missing_checkout(wednesday)
wednesday["weekday"]="wednesday"
cust_no_name(wednesday,"wednesday")

thursday=pd.read_csv("./data/thursday.csv", header=0, sep=";", parse_dates=True)
missing_checkout(thursday)
thursday["weekday"]="thursday"
cust_no_name(thursday,"thursday")

friday=pd.read_csv("./data/thursday.csv", header=0, sep=";", parse_dates=True)
missing_checkout(friday)
friday["weekday"]="friday"
cust_no_name(friday,"friday")

data=pd.concat([monday,tuesday,wednesday,thursday, friday])
data["timestamp"]=pd.to_datetime(data["timestamp"])
data.set_index("timestamp",inplace=True)
data<jupyter_output><empty_output><jupyter_text>Total number of unique customers in a week<jupyter_code>df["customer_no"].nunique()
#1535 unique customers = max. nr of customers in a day
daily_customers = pd.DataFrame(df.groupby("weekday")["customer_no"].nunique())
daily_customers.reset_index(inplace=True)
px.bar(daily_customers, x='weekday', y='customer_no')<jupyter_output><empty_output><jupyter_text>Total number of customers in each section<jupyter_code>total_customers_section = pd.DataFrame(df.groupby("location")["customer_no"].nunique())
total_customers_section.reset_index(inplace=True)
px.bar(total_customers_section, x='location', y='customer_no')<jupyter_output><empty_output><jupyter_text>Total number of customers in each section over time<jupyter_code># By day and hour

data["hour"]=data.index.hour
data[data["location"] =="checkout"].groupby(["hour","weekday"]).nunique()["customer_no"].unstack().plot.bar()
# By day an location 

data[data["location"] !="checkout"].groupby(["location","weekday"]).nunique()["customer_no"].groupby(["location","weekday"]).mean().unstack().plot.bar()
sns.barplot(data=total_customers_time, y='customer_no', x='hour', orient='v')
sns.boxplot(data=total_customers_time, y='customer_no', x='hour', orient='v')
data["hour"]=data.index.hour
data[data["location"] !="checkout"].groupby(["hour","location"]).nunique()["customer_no"].groupby(["hour","location"]).mean().unstack().plot.bar()
px.bar(total_customers_time, x='hour', y='customer_no', color="location")
px.line(total_customers_time, x="hour", y="customer_no", color="location", hover_name="location",line_shape="spline")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18112759643916915, 'fraction_numerical': 0.009020771513353116, 'mean_word_length': 8.218818380743983, 'pattern_counts': {'":': 6, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '527915', 'license': 'MIT', 'n_tokens_mistral': 2898, 'n_tokens_neox': 2820, 'n_words': 530, 'path': 'EDA/EDA_complete.ipynb', 'repo_name': 'lorenanda/Supermarket_MCMC_simulation', 'size': '8425'}