# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))

xls = pd.ExcelFile('cust_journey/fin_dist.xlsx')

# ### Reading sheet 1

sheet1 = xls.parse(0)
sheet1.head(20)

sheet1.describe()
sheet1.columns

# ### analyzing data by category, by month

# ### Kids apparel first

nov = sheet1[ (sheet1.Month == 11)]
dec = sheet1[ (sheet1.Month == 12)]
kidslist = 'Children & Infants Apparel'
nov_dec = sheet1[ (sheet1.Month == 11) | (sheet1.Month == 12)]

nov_dec_kids = sheet1[sheet1['Category'].str.contains(kidslist)]
nov_dec_kids

kids_nov = nov[nov['Category'].str.contains(kidslist)]
kids_dec = dec[dec['Category'].str.contains(kidslist)]

kids_dec.dtypes

# +
from matplotlib import rc
# y-axis in bold
rc('font', weight='bold')
 
# Values of each group
bars1 = kids_nov['Lease_Sales']
bars2 = kids_dec['Lease_Sales']

# The position of the bars on the x-axis
# r = [0,1,2,3]

# Names of group and bar width
names = kids_nov['Tenant']
barWidth = 0.4
plt.figure(figsize=(8,8))
# Create brown bars and name them to be able to pass on a legend
one = plt.bar(r, bars1, color='#7f6d5f', edgecolor='white', width=barWidth)
# Create green bars (middle), on top of the first ones and name them as well
two = plt.bar(r, bars2, bottom=bars1, color='#557f2d', edgecolor='white', width=barWidth)
plt.legend([one, two], ['Nov_Sales', 'Dec_Sales'])
# Custom X axis
plt.xticks(r, names, fontweight='bold')
plt.xlabel("Tenant",fontweight='bold' )
plt.savefig('Nov_Dec Kids Apparel', dpi=300, edgecolor='w',bbox_inches="tight")
plt.show()

# -

# ### Electronics

elec_list = 'Electronics'
# nov_dec = sheet1[ (sheet1.Month == 11) | (sheet1.Month == 12)]

nov_dec_elec = sheet1[sheet1['Category'].str.contains(elec_list)]
nov_dec_elec.describe()

# #### This was an experiment, and reset_index fixes the multi-index problem in groupby df
# #### Plotting by 'kids sales' did not work as their were errors in data size mismatch, not sure why
# elec_nov = nov[nov['Category'].str.contains(elec_list)]
# elec_dec = dec[dec['Category'].str.contains(elec_list)]
# df = elec_nov.append(elec_dec)
# df2 = df.groupby(by=['Month','Tenant'])[['Lease_Sales']].sum().reset_index()

import seaborn as sns
sns.set(style="whitegrid")
g = sns.factorplot(x="Tenant", y="Lease_Sales", hue="Month", data=nov_dec_elec,
                   size=6, kind="bar", palette="muted", aspect=.8)
g.despine(left=True)
g.set_xticklabels(rotation=-90)
g.set_ylabels("Lease_Sales")
plt.tight_layout()
plt.savefig('Nov_Dec_Electronics', dpi=300, edgecolor='w',bbox_inches="tight")
plt.show()

# # Family Apparel

fam_list = 'Family Apparel'

nov_dec_fam = sheet1[sheet1['Category'].str.contains(fam_list)]
nov_dec_fam

testdf = sheet1.groupby(['Month','Tenant'])[['Lease_Sales']].sum().reset_index()
testdf

testdf.set_index('Lease_Sales', inplace=True)

testdf.sort_index(ascending=True, inplace=True)

newone= testdf.reset_index()
newone2 = newone[350:]

import seaborn as sns
sns.set(style="whitegrid")
g = sns.factorplot(x="Tenant", y="Lease_Sales", hue="Month", data=newone2,
                   size=6, kind="bar", palette="muted", aspect=2)
g.despine(left=True)
g.set_xticklabels(rotation=-90)
g.set_ylabels("Lease_Sales")
plt.tight_layout()
plt.savefig('Nov_Dec_Fam_Apparel-TEST TEST', dpi=300, edgecolor='w',bbox_inches="tight")
plt.show()

import seaborn as sns
sns.set(style="whitegrid")
g = sns.factorplot(x="Tenant", y="Lease_Sales", hue="Month", data=nov_dec_fam,
                   size=6, kind="bar", palette="muted", aspect=2)
g.despine(left=True)
g.set_xticklabels(rotation=-90)
g.set_ylabels("Lease_Sales")
plt.tight_layout()
plt.savefig('Nov_Dec_Fam_Apparel', dpi=300, edgecolor='w',bbox_inches="tight")
plt.show()

fam_nov = nov[nov['Category'].str.contains(fam_list)]
fam_dec = dec[dec['Category'].str.contains(fam_list)]

sns.set(style="whitegrid")
f, ax = plt.subplots(figsize=(8, 20))
sns.set_color_codes("pastel")
one = sns.barplot(x="Lease_Sales", y="Tenant", data=fam_nov,
            label="Nov", color="r")
sns.set_color_codes("muted")
two = sns.barplot(x="Lease_Sales", y="Tenant", data=fam_dec,
            label="Dec", color="#557f2d")
ax.legend(ncol=2, loc="lower right", frameon=True)
ax.set(xlim=(0, 12000000), ylabel="",
       xlabel="Test")
sns.despine(left=True, bottom=True)
plt.show()



# #### pie plot attempt did not work out
# df = pd.DataFrame( kids_dec['Lease_Sales'], index= [kids_dec['Month']] )
# df.plot(kind='pie', subplots=True, figsize=(8, 8))
# plt.show()

# ### Lease_Sales by store

sales_month = sheet1.groupby(['Tenant'])[['Lease_Sales_PSF']].mean()
sales_month.sort_values(by=['Lease_Sales_PSF'], ascending=True, inplace=True)
sales_month.head(40)

sales_month.plot(kind='bar',figsize=(40,4))
plt.xticks(rotation=90)
# plt.savefig('Nov_Dec Sales PSF by store', dpi=300, edgecolor='w',bbox_inches="tight")
plt.show()

# fig, ax = plt.subplots(figsize=(10,8))
# plt.suptitle('')
# sheet1.boxplot(column=['Month'], by='Lease_Sales', ax=ax)
# plt.show()

# ### Boxplots of Cat vs Lease data

import seaborn as sns
dd = pd.melt(sheet1,id_vars=['Category'], value_vars=['Cat_Sales','Lease_Sales'], var_name='Sales')
sns.boxplot(y='Category', x='value', data=dd, orient="h", hue='Sales')
sns.set(rc={'figure.figsize':(40,40)})
plt.show()

# boxplot is not the best application in this case
dd = pd.melt(sheet1,id_vars=['Category'], value_vars=['Cat_Groc'], var_name='Groc')
sns.boxplot(y='Category', x='value', data=dd, orient="h", hue='Groc')
sns.set(rc={'figure.figsize':(40,40)})
plt.show()









subset=sheet1[['Month', 'Category','Cat_Sales_PSF','Tenant']]
subset.head()

all_tenants = subset.groupby('Tenant')[['Cat_Sales_PSF']].mean()
all_cats = subset.groupby('Category')[['Cat_Sales_PSF']].mean()
month_avg = subset.groupby('Month')[['Cat_Sales_PSF']].mean()

all_cats.sort_values(by=['Cat_Sales_PSF'], ascending=False, inplace=True)

# ### plotting all tenants by their cat sales psf

all_tenants.nlargest(20,'Cat_Sales_PSF')
all_tenants.sort_values(by=['Cat_Sales_PSF'], ascending=False, inplace=True)

fig = all_tenants.plot( kind='barh',figsize=(30,40), legend=True)
plt.title('PSF by Tenant')
fig.tick_params(axis='y', labelleft='off', labelright='on') # to move labels on right
plt.savefig('PSF_Tenant', dpi=300, edgecolor='w',bbox_inches="tight")
plt.xticks(rotation=90)
plt.show()

# ### plot by category

all_cats.nlargest(30,'Cat_Sales_PSF')

all_cats.plot(kind='bar',figsize=(20,8))
plt.xticks(rotation=90)
plt.title('Average Cat Sale PSF for months 11 and 12')
plt.savefig('Average Category Sale for Nov-Dec', dpi=300, edgecolor='w',bbox_inches="tight")
plt.show()

month_avg.nlargest(30,'Cat_Sales_PSF')

subset.plot(x='Month',y='Cat_Sales_PSF',kind='bar', figsize=(20,8))
plt.show()

sale_month=subset.pivot_table(index=['Tenant','Cat_Sales_PSF','Category'])
sale_month.head(10)

sale_month.describe()

sale_month.head(50).plot(kind='bar', figsize=(20,8))
plt.show()

# # Exploring how size of store affects the Cat_Sales_PSF

bins = [1,540,2000, 14400,200000]
labels = ['small', 'medium', 'large', 'huge']
store_sizes = pd.cut(sheet1.Lease_GLA, bins,labels=labels)
sheet1['store_type'] = store_sizes

sheet1.head()

growth_type = sheet1.groupby('store_type')[['Cat_Sales_PSF']].mean()

growth_type.plot(kind='bar',figsize=(20,8))
plt.show()

sheet1.set_index('Category', inplace=True)
sheet1.head()

sale_psf=sheet1['Cat_Sales_PSF']
# store=sheet1['Tenant']
# category=sheet1['Category']
sale_psf.head()
# store.head()

sort_by_psf = sheet1.sort_values(['Cat_Sales_PSF'], ascending=False)
sort_by_psf.head(20)

sort_by_psf.drop_duplicates(inplace=True)
sort_by_psf

sort_by_psf.head(30).plot(kind='barh')
plt.show()















name_loc = xls.parse(2)
name_loc.drop_duplicates(inplace=True)

print("Column headings:", name_loc.columns)

name_loc

name_loc.set_index('Unit # ', inplace=True)

name_loc.head()

name_loc.drop(['Unit #'])
print("Column headings:", name_loc.columns)

name_loc.rename(columns={'Unit # ':'unit', 'Tenant':'store'}, inplace=True)
name_loc.head()

print("Column headings:", name_loc.columns)











StarcoderdataJupyterScriptsDedupFiltered