# %% [markdown]
# # Alteryx weekly challenge 419

# %% [markdown]
# The below code works with pre-condition that all 3 Alteryx inputs were converted to individual CSV files, stored on local drive and loaded to the notebook.

# %%
import pandas as pd

# %%
# Load the first CSV file
basket = pd.read_csv(r"C:\Users\rpospisil002\Downloads\awc419_basket_contents.csv", header=0)
basket = basket.rename(columns={'Purchase Year':'Year','Stuffed Animal':'Stuffed animal'})
display(basket.head())
display(basket.shape)

# %%
# Load the second CSV file
b_pricelist = pd.read_csv(r"C:\Users\rpospisil002\Downloads\awc419_stuffed_bunny_pricelist.csv",header=0)
display(b_pricelist.head())

# %%
# Load the third CSV file
t_pricelist = pd.read_csv(r"C:\Users\rpospisil002\Downloads\awc419_treats_pricelist.csv",header=0)
display(t_pricelist.head())

# %%
# Create table with prices for Treats
basket_t = pd.merge(basket[['Basket List','Year','Treat']],t_pricelist,on=['Treat','Year'])
basket_t = basket_t.rename(columns={'Treat':'Product'})
display(basket_t.head())

# %%
# Create table with prices for Stuffed animal
basket_p = pd.merge(basket[['Basket List','Year','Stuffed animal']],b_pricelist,on=['Stuffed animal','Year'])
basket_p = basket_p.rename(columns={'Stuffed animal':'Product'})
display(basket_p.head())

# %%
# Combine the separate tables together to create one long fact table
basket_long = pd.concat([basket_t,basket_p])
display(basket_long.head())
display(basket.shape)
display(basket_long.shape)

# %%
# Aggregate table before transforming to wide layout (pivot)
basket_agg = basket_long.groupby(['Basket List','Year']).agg({'Price':'sum'}).reset_index()
display(basket_agg)

# %%
# Create pivot like table
basket_wide = pd.pivot_table(basket_agg,index='Basket List',columns='Year',values='Price').sort_values('Basket List').reset_index()
display(basket_wide)

# %% [markdown]
# Result 1

# %%
# Calculate YtY increases and create an artificial column used for overall aggregation in the next step
basket_wide['pct_increase_2021_to_2022'] = round((basket_wide[2022] - basket_wide[2021])/basket_wide[2021]*100,2)
basket_wide['pct_increase_2022_to_2023'] = round((basket_wide[2023] - basket_wide[2022])/basket_wide[2022]*100,2)
basket_wide['group'] = 'group'
display(basket_wide)

# %% [markdown]
# Result 2

# %%
# Create overall annual aggregations (averages) and calculate expected 2024 fundraising needed
overall_avg = basket_wide.groupby('group').agg({2021:'mean',2022:'mean',2023:'mean','pct_increase_2021_to_2022':'mean','pct_increase_2022_to_2023':'mean'}).reset_index()
overall_avg['expected_2024_fundraising_needed'] = round(overall_avg[2023]*(1+overall_avg['pct_increase_2022_to_2023']/100)*100,2)
display(overall_avg)