Granular Impactful Insights w/ Predictive Capabilities

import pandas as pd
import glob
import warnings
warnings.filterwarnings("ignore")
import plotly.express as px
import seaborn as sns
files = glob.glob(r'/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/*.xls*')
files
['/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/4 - April 2019_Revised - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/5 - May 2020  - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/7-July 2018_Revised - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/7 - July 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/08 - August 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2019 - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/04 - April 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/8 - August 2019 - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/1 - January 2020  - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/3-March 2018_Revised - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/9 - September 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/02 - February 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/4 - April  2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10-October 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/09 - September 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/9-September 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/02 - February 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2020  - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/02 - February  2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2022.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/09 - September 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - Novermber 2022.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/08 - August 2023.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/8-August 2018 -Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/3 - March 2019_Revised - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/1 - January 2019_Revised - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/07 - July 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/9 - September 2019 - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/8 - August 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/6 - June  2019  - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/05 - May 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/08 - August 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/06 - June 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/6-June 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11-November 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/5 - May 2019 - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/06 - June 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/06 - June 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/07 - July 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12-December 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/04 - April 2022  - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/2 - February 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - November 2020  - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/1-January 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/03 - March 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/03 - March 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/5-May 2018 - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/05 - May 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/03 - March  2021 -  Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/4-April 2018_Revised - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/2 - February 2019_Revised - Revised for 2020_Revised.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - November 2019  - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/3 - March 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/09 - September 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/07 - July 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2019 - Revised for 2020.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/2-February 2018_Revised - Revised for 2019.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/01 - January  2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/05 - May 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/01 - January 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/01 - January 2023.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - November 2021 - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2022 - Revised for 2023.xlsx',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/6 - June 2020 - Revised for 2021.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/04 - April 2021  - Revised for 2022.xls',
 '/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/7 - July  2019 Revised - Revised for 2020.xls']
len(files)
70
df_list = []
categories=[
    
    {"category":"Food Stores","start":'Food Stores',"end":'Total Food Stores'},
    {"category":"Liqour Stores","start":'Alcohol Sales Food Stores',"end":'Total Alcohol'},
    {"category":"Motor Vehicle Stores","start":'Motor Vehicle Dealers',"end":'Total Vehicles'},
    {"category":"Service Sation Stores","start":'Gasoline Service Station',"end":'Total Gasoline Service Station'},
    {"category":"Apparel Stores","start":'Apparel Stores',"end":'Total Apparel Stores'},
    {"category":"Building Material Stores","start":'Building Materials & Hardware',"end":'Total Bldg. Mat. & Hardware'},
    {"category":"Furniture Stores","start":'Furniture',"end":'sub-total Furniture'},
    {"category":"Appliances and Electronics Stores","start":'Appliances & Electronics',"end":'sub-total Appliances'},
    {"category":"Marine Supplies Stores","start":'Marine Supplies',"end":'Total Marine Supplies'},
    {"category":"Other Stores","start":'Other',"end":'sub-total Other'},
    {"category":"Tourism Related Stores","start":'Tourist Related Stores',"end":'sub-total Tourist Demand'},
    {"category":'Pharmaceuticals Stores',"start":'Pharmaceuticals',"end":'Total Pharmeucticals'},
]

cats= ['Food Stores', 'Liqour Stores', 'Motor Vehicle Stores',
       'Service Sation Stores', 'Apparel Stores',
       'Building Material Stores', 'Furniture Stores',
       'Appliances and Electronics Stores', 'Marine Supplies Stores',
       'Other Stores', 'Tourism Related Stores', 'Pharmaceuticals Stores']
rsi_cats= ['Food Stores', 'Liqour Stores', 'Motor Vehicle Stores',
       'Service Sation Stores', 'Apparel Stores',
       'Building Material Stores', 'Miscelleanous Stores',
       'Miscelleanous Stores', 'Miscelleanous Stores',
       'Miscelleanous Stores', 'Miscelleanous Stores', 'Miscelleanous Stores']


all_bounds = [x['start'] for x in categories] + [x['end'] for x in categories]

for f in files:
    print(f)
    data = pd.read_excel(f,
             usecols=[1,2,5,6,9],names=['id','store','prev_year','curr_year','comments'])
    report_date = data[data['curr_year'].notnull()]['curr_year'].iloc[0]
    data['report_date'] = report_date
    data['source']=f
    
    ending_index = data.loc[data['store'].str.contains('Total Miscellaneous',na=False)].index[0]
    
    df = data.iloc[:ending_index+1]
    
    df['store'] = df['store'].str.strip()
    
 

    #Boundary Check
    assert all([bound in df['store'].unique() for bound in all_bounds]) 
    
    cat_df_list=[]
    for cat in categories:
        start_index = df.loc[df['store']==cat['start']].index[0]
        end_index = df.loc[df['store']==cat['end']].index[0]
        cat_df = df.loc[start_index+1:end_index-1,:]
        cat_df['category']= cat['category']
        cat_df_list.append(cat_df)
    
    df2 = pd.concat(cat_df_list)
    df2['rsi_category'] = df2['category'].replace(dict(zip(cats,rsi_cats)))
    df_list.append(df2)
    
rsi = pd.concat(df_list)
                                                  
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/4 - April 2019_Revised - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/5 - May 2020  - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/7-July 2018_Revised - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/7 - July 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/08 - August 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2019 - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/04 - April 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/8 - August 2019 - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/1 - January 2020  - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/3-March 2018_Revised - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/9 - September 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/02 - February 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/4 - April  2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10-October 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/09 - September 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/9-September 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/02 - February 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2020  - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/02 - February  2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2022.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/09 - September 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - Novermber 2022.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/08 - August 2023.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/8-August 2018 -Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/3 - March 2019_Revised - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/1 - January 2019_Revised - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/07 - July 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/9 - September 2019 - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/8 - August 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/6 - June  2019  - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/05 - May 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/08 - August 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/06 - June 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/6-June 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11-November 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/5 - May 2019 - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/06 - June 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/06 - June 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/07 - July 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12-December 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/04 - April 2022  - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/2 - February 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - November 2020  - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/1-January 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/03 - March 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/03 - March 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/5-May 2018 - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/05 - May 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/03 - March  2021 -  Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/4-April 2018_Revised - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/2 - February 2019_Revised - Revised for 2020_Revised.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - November 2019  - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/3 - March 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/09 - September 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/07 - July 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/12 - December 2019 - Revised for 2020.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/2-February 2018_Revised - Revised for 2019.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/01 - January  2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/05 - May 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/01 - January 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/01 - January 2023.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/11 - November 2021 - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/10 - October 2022 - Revised for 2023.xlsx
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/6 - June 2020 - Revised for 2021.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/04 - April 2021  - Revised for 2022.xls
/Users/quinnweekes/Library/CloudStorage/OneDrive-GovernmentofBermuda/Department of Statistics/RSI/RSI_Monthly/7 - July  2019 Revised - Revised for 2020.xls
rsi
id store prev_year curr_year comments report_date source category rsi_category
103 501 Arnold's Super Market 1807.2 1830.7 NaN April 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Food Stores Food Stores
104 503 Lindo's Family Foods (Warwick) 2966.04153 3152.44123 NaN April 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Food Stores Food Stores
105 505 Miles 1669 1855 NaN April 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Food Stores Food Stores
106 506 Lindo's Family Foods (Devonshire) 3927 3976 NaN April 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Food Stores Food Stores
107 507 Supermart & Somers St. George 3049 3374 NaN April 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Food Stores Food Stores
... ... ... ... ... ... ... ... ... ...
87 601 Hamilton Pharmacy 411.322 420.65 NaN July 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Pharmaceuticals Stores Miscelleanous Stores
88 602 Caeser's Pharmacy 310.09679 305.48909 NaN July 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Pharmaceuticals Stores Miscelleanous Stores
89 603 Bermuda Drug 5195.54 5420.431 NaN July 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Pharmaceuticals Stores Miscelleanous Stores
90 604 Peoples Pharmacy 1297.43314 1386.14644 NaN July 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Pharmaceuticals Stores Miscelleanous Stores
91 605 Robertson's 309.265 334.754 NaN July 2019 /Users/quinnweekes/Library/CloudStorage/OneDri... Pharmaceuticals Stores Miscelleanous Stores

7697 rows × 9 columns

rsi['diff'] = rsi['curr_year']
rsi['report_date'] = pd.to_datetime(rsi['report_date'])
pd.to_datetime(rsi['report_date'])
103   2019-04-01
104   2019-04-01
105   2019-04-01
106   2019-04-01
107   2019-04-01
         ...    
87    2019-07-01
88    2019-07-01
89    2019-07-01
90    2019-07-01
91    2019-07-01
Name: report_date, Length: 7697, dtype: datetime64[ns]
rsi.columns
Index(['id', 'store', 'prev_year', 'curr_year', 'comments', 'report_date',
       'source', 'category', 'rsi_category', 'diff'],
      dtype='object')
rsi['comments']=rsi['comments'].fillna('None')

rsi_id = 804 # Predicting closures or a downturn beside on sentiment

#rsi_id = 107  #Large mover

filtered_df = rsi[rsi['id']==rsi_id].sort_values('report_date')

fig= px.line(filtered_df,y='curr_year',x='report_date',hover_data=['prev_year','comments'],
            title = f"Category: {filtered_df['category'].iloc[0]}, Store: Hidden")

fig.show()
Jan 2018Jul 2018Jan 2019Jul 2019Jan 2020Jul 2020020406080
Category: Apparel Stores, Store: Hiddenreport_datecurr_year
rsi['comments']=rsi['comments'].fillna('None')

#rsi_id = 804 # Predicting closures or a downturn beside on sentiment

rsi_id = 107  #Large mover

filtered_df = rsi[rsi['id']==rsi_id].sort_values('report_date')

fig= px.line(filtered_df,y='curr_year',x='report_date',hover_data=['prev_year','comments'],
            title = f"Category: {filtered_df['category'].iloc[0]}, Store: Hidden")

fig.show()
20182019202020212022202305001000150020002500
Category: Motor Vehicle Stores, Store: Hiddenreport_datecurr_year