text: import streamlit as st
  import pandas as pd
  import numpy as np
  from datetime import date
  import operator
  
  import matplotlib as mpl
  import matplotlib.pyplot as plt
  import seaborn as sns
  
  import plotly.graph_objects as go
  import plotly.express as px
  from streamlit_echarts import st_echarts
  import hydralit_components as hc
  from pathlib import Path
  
  from sklearn.preprocessing import StandardScaler,MinMaxScaler
  from sklearn.experimental import enable_iterative_imputer
  from sklearn.impute import IterativeImputer,KNNImputer
  
  from patsy import dmatrices
  import statsmodels.api as sm
  from sklearn.metrics import r2_score, mean_squared_error, mean_absolute_error
  from statsmodels.sandbox.regression.predstd import wls_prediction_std
  
  from math import pi
  import base64
  import os
  
  import xlsxwriter
  from io import BytesIO
  
  import locale
  
  import pyrebase
  
  import plost
  
  from PE_Parameter import *
  
  # Helper Functions Starts here #
  
  # def rename_column(df):
  #     df.columns = [c.strip().upper().replace(' ', '_') for c in df.columns]
  #     df.columns = [c.strip().upper().replace('/', '_') for c in df.columns]
  #     df.columns = [c.strip().upper().replace('-', '_') for c in df.columns]
  #     df.columns = [c.strip().upper().replace('(', '') for c in df.columns]
  #     df.columns = [c.strip().upper().replace(')', '') for c in df.columns]
  #     df.columns = [c.strip().upper().replace('.', '') for c in df.columns]
  #     df.columns = [c.strip().upper().replace('___', '_') for c in df.columns]
  #     df.columns = [c.strip().upper().replace('__', '_') for c in df.columns]
  #     return df
  # st.set_page_config(layout="wide")
  
  # Set Style
  # style_path = Path(__file__).parents[0].__str__()+'/Style/style.css'
  # with open(style_path) as f:
  #     st.markdown(f'<style>{f.read()}</style>', unsafe_allow_html=True)
  
  def rename_column(df):
      df.columns = [c.strip().replace(' ', '_') for c in df.columns]
      df.columns = [c.strip().replace('/', '_') for c in df.columns]
      df.columns = [c.strip().replace('-', '_') for c in df.columns]
      df.columns = [c.strip().replace('(', '') for c in df.columns]
      df.columns = [c.strip().replace(')', '') for c in df.columns]
      df.columns = [c.strip().replace('.', '') for c in df.columns]
      df.columns = [c.strip().replace('___', '_') for c in df.columns]
      df.columns = [c.strip().replace('__', '_') for c in df.columns]
      return df
  
  def gender_name_replace(text):
      if ('F' in text):
          return 'Female'
      elif ('M' in text):
          return 'Male'
      elif ('N' in text):
          return 'Non-Binary'
      else:
          return 'Unknown'
  
  def plot_eth_donut(data):
      # explosion    
      # plt.clf()
      n_exp = len(data['ETHNICITY_NAME'])
      explode = np.repeat(0.02, n_exp).tolist()
      explode = tuple(explode)
      
      centre_circle = plt.Circle((0, 0), 0.65, fc='white')
      # fig_eth = plt.subplots()
      # fig_eth = plt.gcf()
      fig_eth = plt.figure(num = 0,dpi=100)
      # fig_eth, ax_eth = plt.subplots()
      
      plt.pie(data['HC'],  labels=data['ETHNICITY_NAME'],
              autopct='%1.1f%%', pctdistance=0.85,
              explode=explode)
      fig_eth.gca().add_artist(centre_circle)
      plt.tight_layout()
      # plt.title('Ethnicity by Headcount')
      # plt.figure(figsize=(500,500))
      return fig_eth
  
  def plot_gender_donut(data):
      # explosion    
      # plt.clf()
      n_exp = len(data['GENDER_NAME'])
      explode = np.repeat(0.02, n_exp).tolist()
      explode = tuple(explode)
      # data.to_excel('gender_check.xlsx')
      
      # Pie Chart
      plt.pie(data['HC'],  labels=data['GENDER_NAME'],
              autopct='%1.1f%%', pctdistance=0.85,
              explode=explode)
      centre_circle = plt.Circle((0, 0), 0.65, fc='white')
      # fig_gender = plt.gcf()
      fig_gender = plt.figure(num = 1,dpi=100)
      # fig_gender, ax_gender = plt.subplots()
      fig_gender.gca().add_artist(centre_circle)
      plt.tight_layout()
      # plt.title('Ethnicity by Headcount')
      # plt.figure(figsize=(500,500))
      # plt.show()
      return fig_gender
  
  def plot_bar(data,col):    
      scale_max = round(data['COEF_DISPLAY'].max(),2)+0.02
      scale_min = round(data['COEF_DISPLAY'].min(),2)-0.02
      fig = px.bar(data, x=col, y="COEF_DISPLAY", color='STAT', template='seaborn', text=[f'{round(100*j,1)}%' for i,j in zip(data[col],data['COEF_DISPLAY'])])
          
      fig.update_layout(showlegend=False,autosize=False, margin=dict(l=10, r=10, t=10, b=10),yaxis_range=[scale_min,scale_max],paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)')
      fig.update_yaxes(automargin=True, showticklabels=False, visible=False)
      fig.update_xaxes(automargin=True, showticklabels=True, visible=True)
      # fig.update_xaxes(automargin=True, showticklabels=True, visible=True)
      # fig['layout']['yaxis'].update(autorange = True)s
      # fig['layout']['xaxis'].update(autorange = True)
      fig.update_traces(textfont_size=10, textangle=0, textposition="outside", cliponaxis=False)
      return fig
  
  def clean_req_feature(data, feature, valid_feature_list, warning_message, data_type="string", 
                        val_col = 'VALIDATION_MESSAGE',val_flag_col = 'VALIDATION_FLAG'):
      da = data.copy()
      # Check exclusion and remove invalid data
      if (data_type == "numeric") and (feature == 'SALARY'):
          da[feature] = pd.to_numeric(da[feature], errors='coerce')
          da.loc[(da[feature].isna()) | (da[feature]<=0), val_flag_col] = 1
          exclude_feature_num = sum(da[feature].isna()*1)+sum((da[feature]<=0)*1)
      elif data_type == "numeric":
          # Numeric features, exclude nan, strings
          da[feature] = pd.to_numeric(da[feature], errors='coerce')
          exclude_feature_num = sum(da[feature].isna()*1)
          da.loc[da[feature].isna(), val_flag_col] = 1
      elif data_type == 'datetime':
          da[feature] = pd.to_datetime(da[feature], errors='coerce')
          exclude_feature_num = sum(da[feature].isna()*1)
          da.loc[da[feature].isna(), val_flag_col] = 1
  #         da = da.dropna(subset=[feature])
      else:
          # String features, check for nan and valid list     
          if len(valid_feature_list) == 0: 
              # exclude nan only
              check_na_feature = sum(da[feature].isna()*1)/len(da[feature])
              exclude_feature_num = sum(da[feature].isna()*1)
              da.loc[da[feature].isna(), val_flag_col] = 1
  #             da = da.dropna(subset=[feature])
          else:
              # exclude nan, and those not in valid_feature_list
              da[feature] = da[feature].str[0].str.upper()
              exclude_feature_num = sum(~(da[feature].isin(valid_feature_list))*1)
              da.loc[~da[feature].isin(valid_feature_list), val_flag_col] = 1
  #             da = da[da[feature].isin(valid_feature_list)]
  
      # Record message
      if exclude_feature_num>0:
          warning_feature = feature+": exclude "+str(exclude_feature_num)+" invalid or blank records"
          da.loc[da[val_flag_col]==1, val_col] = da[val_col]+" | "+ warning_feature
      else:
          warning_feature = feature+": Successful Run"
      warning_message[feature] = warning_feature
  #     print(warning_message)
      da[val_flag_col]=0
      return da,warning_message
  
  def clean_optional_feature(data, feature, valid_feature_list, warning_message, exclude_col, data_type="string", 
                        val_col = 'VALIDATION_MESSAGE',val_flag_col = 'VALIDATION_FLAG'):
      da = data.copy()
      check_na_feature = sum(da[feature].isna()*1)/len(da[feature])
      
      # Step 1, determine if there is no input, if yes, then this is an optional factor and we should exclude from our model
      if check_na_feature == 1:
          # Step 1, determine if there is no input, if yes, then this is an optional factor and we should exclude from our model
          exclude_col.append(feature)
          warning_feature = feature + ": exclude this optional driver"
          warning_message[feature] = warning_feature
      else:
          # Step 2, clean up data just like the required features
          da, warning_message = clean_req_feature(data = da,feature = feature,valid_feature_list=valid_feature_list,
                                                  warning_message = warning_message, data_type=data_type,val_col = val_col,val_flag_col = val_flag_col)
          
      return da, warning_message, exclude_col
  
  def set_dropped_category(series, dropped_category):
      '''
      Function to move one category to the top of a category column, such that
      it is the one that gets dropped for the OLS
      '''
  
      temp = list(series.cat.categories)
      temp.insert(0, temp.pop(temp.index(dropped_category)))
      series = series.cat.reorder_categories(temp)
  
      return series
  
  # Main Program Starts here #
  
  def run(data=None, df_gender_name=None):
      # 1.1 Setup ************************************
      company_name = 'Client Name'
      version = 'Base Pay'
      starting_salary_flag = 'N'
      target = 'SALARY'
  
      # Setup error message to display on UI
      error_message = {}
      warning_message = {}
      exclude_col = []
      
      # Read Data
      try:
          df = data
      except:
          error_file_read = "Unable to read submission file, Please download and update data template again"
          error_message['File_Read'] = error_file_read
  
      # df.columns = [c.strip().upper().replace(' ', '_') for c in df.columns]
      # df.columns = [c.strip().upper().replace('/', '_') for c in df.columns]
      
      # df.columns = [c.strip().replace(' ', '_') for c in df.columns]
      # df.columns = [c.strip().replace('/', '_') for c in df.columns]
      
      df = rename_column(df)
      
      df_type = df.iloc[0]
      df = df.iloc[1:]
      
      # 2.1 Data Cleaning ****************************
      # Data Validation
      df['VALIDATION_MESSAGE']=""
      df['VALIDATION_FLAG']=0
      # Snapshot Date
      snapshot = np.nan
      try:
          df['SNAPSHOT_DATE'] = df['SNAPSHOT_DATE'].astype("string")
          snapshot = df['SNAPSHOT_DATE'].mode().tolist()[0]
      except:
          print('date exception')
          snapshot = date.today()
          error_snapshot = "Invalid snapshot date, Please check submission format is mm/dd/yyyy in data template"
          error_message['SNAPSHOT_DATE'] = error_snapshot
      df['NOW'] = pd.to_datetime(snapshot)
          
      # 2.2 Clean up All features ******************
      df,warning_message = clean_req_feature(data = df,feature = "EEID",valid_feature_list=[],warning_message = warning_message, data_type="string")
      df,warning_message = clean_req_feature(data = df,feature = "GENDER",valid_feature_list=["F","M","N","U","O"],warning_message = warning_message, data_type="string")
      df,warning_message = clean_req_feature(data = df,feature = "SALARY",valid_feature_list=[],warning_message = warning_message, data_type='numeric')
      df,warning_message = clean_req_feature(data = df,feature = "JOB_LEVEL_OR_COMP_GRADE",valid_feature_list=[],warning_message = warning_message, data_type="string")
      df,warning_message = clean_req_feature(data = df,feature = "JOB_FUNCTION",valid_feature_list=[],warning_message = warning_message,data_type="string")
      df,warning_message = clean_req_feature(data = df,feature = "COUNTRY",valid_feature_list=[],warning_message = warning_message,data_type="string")
      df,warning_message = clean_req_feature(data = df,feature = "LOCATION",valid_feature_list=[],warning_message = warning_message,data_type="string")
      df,warning_message = clean_req_feature(data = df,feature = "FULL_TIME",valid_feature_list=["Y","N"],warning_message = warning_message,data_type="string")
      # df,warning_message = clean_req_feature(data = df,feature = "EXEMPT",valid_feature_list=["Y","N"],warning_message = warning_message,data_type="string")
  
      # Clean up optional features
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "ETHNICITY",valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col, data_type="string")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "PEOPLE_MANAGER",valid_feature_list=["Y","N"],warning_message = warning_message,exclude_col = exclude_col, data_type="string")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "EDUCATION",valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col, data_type="string")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "PROMOTION",valid_feature_list=["Y","N"],warning_message = warning_message,exclude_col = exclude_col, data_type="string")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "PERFORMANCE",valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col, data_type="string")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "DATE_OF_BIRTH",valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col, data_type="datetime")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "DATE_OF_HIRE",valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col, data_type="datetime")
      df,warning_message, exclude_col = clean_optional_feature(data = df,feature = "EXEMPT",valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col,data_type="string")
      
      # Clean up customized features
      standard_col = ['SNAPSHOT_DATE','EEID','SALARY','GENDER','ETHNICITY',
                  'JOB_LEVEL_OR_COMP_GRADE','JOB_FUNCTION','COUNTRY','LOCATION','FULL_TIME',
                  'EXEMPT','PEOPLE_MANAGER','EDUCATION','PROMOTION','PERFORMANCE','DATE_OF_BIRTH','DATE_OF_HIRE']
      all_col = df.columns.tolist()
      cust_col = [x for x in all_col if x not in standard_col]
      df_type = pd.DataFrame(df_type).reset_index()
      df_type.columns = ['COL_NAME','TYPE']
      df_type = df_type[~df_type['COL_NAME'].isin(standard_col)]
      for i, row in df_type.iterrows():
          df,warning_message, exclude_col = clean_optional_feature(data = df,feature = row['COL_NAME'],valid_feature_list=[],warning_message = warning_message,exclude_col = exclude_col, data_type=row['TYPE'])
      
      # Record Message
      df_org = df.copy()
      # df_org.to_excel('Data\data_validate.xlsx')
      before_clean_record = df_org.shape[0]
      
      df = df_org[df_org['VALIDATION_MESSAGE']==""]
      after_clean_record=df.shape[0]
      
      df_validation = df_org[df_org['VALIDATION_MESSAGE']!=""]
      list_validation = [x for x in df_validation.columns.tolist() if x not in ['VALIDATION_MESSAGE','VALIDATION_FLAG','NOW']]
      list_validation = ['VALIDATION_MESSAGE']+list_validation
      df_validation = df_validation[list_validation]
      
      warning_message['OVERVIEW'] = "OVERVIEW:Successfully run "+str(after_clean_record)+" out of "+str(before_clean_record)+" records"
      message = pd.DataFrame.from_dict(warning_message,orient='index')
      message.columns=['NAME']
      message = message['NAME'].str.split(":",expand = True)
      message.columns=['NAME','Message']
      message = message['Message']
      
      # 2.3 Feature Engineering ****************************
      # df_saveID = df['EEID']
      # df = df.set_index('EEID', drop=True)
      # col_list=df.columns.tolist()
      filter_list = [x for x in df.columns.tolist() if x not in exclude_col]
      df = df[filter_list]
      col_list=df.columns.tolist()
      
      print(exclude_col)
      print(filter_list)
      extra = ['VALIDATION_MESSAGE', 'VALIDATION_FLAG', 'NOW']
      exclude_feature = exclude_col    
      include_feature = [x for x in filter_list if x not in extra]
      
      # Calculate Age and Tenure
      try:
          df['AGE'] = np.ceil((df['NOW'] - df['DATE_OF_BIRTH']).dt.days/365)
      except:
          print('AGE: Calculation error')
      try:
          df['TENURE'] = np.ceil((df['NOW'] - df['DATE_OF_HIRE']).dt.days/365)
      except:
          print('TENURE: Calculation error')
      
      numeric_columns = ['SALARY','AGE', 'TENURE']
      if 'AGE' not in col_list:
          numeric_columns.remove('AGE')
      if 'TENURE' not in col_list:
          numeric_columns.remove('TENURE')
      
      # Include customized columns
      numeric_columns_customized = []
      for i, row in df_type.iterrows():
          if row['TYPE'] == 'numeric':
              numeric_columns_customized.append(row['COL_NAME'])
      
      numeric_columns_customized_include = [x for x in numeric_columns_customized if x in include_feature]
      numeric_columns.extend(numeric_columns_customized_include)
      numeric_columns = list(set(numeric_columns))
      
      for c in numeric_columns:
          df.loc[df[c]=='na', c] = np.nan
          df[c] = pd.to_numeric(df[c])
      
      # df.to_excel('edu.xlsx')
      
      # %% Convert string columns to categories
      category_columns = [x for x in col_list if x not in numeric_columns]
      print(category_columns)
      for c in category_columns:
          df[c] = df[c].astype(str).str.strip().astype('category')
      
      feature = []
      baseline = []
      
      df['LOG_SALARY'] = np.log(df['SALARY'])
  
      # Default to category level w/ highest n-count
      cat_cols = list(df.select_dtypes(['category']).columns)
      for x in cat_cols:
          drop_cate = df[x].value_counts().index.tolist()[0]
          #Set Manually
          if x=='GENDER':
              print('Override! Gender',"level dropped:",'M')
              drop_cate = 'M'
          df[x] = set_dropped_category(df[x], drop_cate)
          feature.append(x)
          baseline.append(drop_cate)
  
      df_baseline = pd.DataFrame({'Features': feature,'Baseline': baseline})
      print('\n')
      print(df_baseline.to_markdown())
      
      # 3 Gender Modeling - Linear Regression ****************************
      col_list = df.columns
      
      # Remove all excess columns
      add_exclude_col = ['EEID','SALARY','SNAPSHOT_DATE', 'VALIDATION_MESSAGE', 'VALIDATION_FLAG', 'NOW','DATE_OF_BIRTH','DATE_OF_HIRE']
      add_exclude_col_predict = add_exclude_col+['GENDER','ETHNICITY']
      exclude_col = exclude_col+add_exclude_col
      exclude_col_predict = exclude_col+add_exclude_col_predict
  
      model_col = [x for x in col_list if x not in exclude_col]
      model_col_predict = [x for x in col_list if x not in exclude_col_predict]
      
      # Factors
      f_raw = 'LOG_SALARY ~ GENDER'
      f_discover = model_col[-1] + ' ~ ' + ' + '.join(map(str, model_col[0:len(model_col)-1]))
      f_predict= model_col[-1] + ' ~ ' + ' + '.join(map(str, model_col_predict[0:len(model_col_predict)-1]))
      
      print(f_raw)
      print(f_discover)
      print(f_predict)
      
      # Gender Raw Gap
      y_raw, x_raw = dmatrices(f_raw, df, return_type='dataframe')
      model_raw = sm.OLS(y_raw, x_raw)
      results = model_raw.fit()
  
      df_result = results.summary2().tables[1]
      df_result.reset_index(level=0, inplace=True)
      df_result = df_result.rename(columns={"index":"CONTENT"})
  
      r2_raw = results.rsquared
      r2_adj = results.rsquared_adj
  
      female_coff_raw = df_result.loc[df_result['CONTENT']=="GENDER[T.F]"]['Coef.'].tolist()[0]
      female_pvalue_raw = df_result.loc[df_result['CONTENT']=="GENDER[T.F]"]['P>|t|'].tolist()[0]
      
      # Gender Net Gap
      y_dis, x_dis = dmatrices(f_discover, df, return_type='dataframe')
      model_dis = sm.OLS(y_dis, x_dis)
      results = model_dis.fit()
  
      df_result = results.summary2().tables[1]
      df_result.reset_index(level=0, inplace=True)
      df_result = df_result.rename(columns={"index":"CONTENT"})
  
      r2 = results.rsquared
      r2_adj = results.rsquared_adj
  
      female_coff = df_result.loc[df_result['CONTENT']=="GENDER[T.F]"]['Coef.'].tolist()[0]
      female_pvalue = df_result.loc[df_result['CONTENT']=="GENDER[T.F]"]['P>|t|'].tolist()[0]
      
      nonb_coff = df_result.loc[df_result['CONTENT']=="GENDER[T.N]"]['Coef.'].tolist()[0]
      nonb_pvalue = df_result.loc[df_result['CONTENT']=="GENDER[T.N]"]['P>|t|'].tolist()[0]
      
      # Gender Net Gap - Prediciton
      y_predict, x_predict = dmatrices(f_predict, df, return_type='dataframe')
      model_predict = sm.OLS(y_predict, x_predict)
      results_predict = model_predict.fit()
  
      y_pred = results_predict.predict(x_predict)
      std, lower, upper = wls_prediction_std(results_predict,alpha=0.05)
      std_90, lower_90, upper_90 = wls_prediction_std(results_predict,alpha=0.10)
      std_85, lower_85, upper_85 = wls_prediction_std(results_predict,alpha=0.15)
      std_80, lower_80, upper_80 = wls_prediction_std(results_predict,alpha=0.20)
      std_75, lower_75, upper_75 = wls_prediction_std(results_predict,alpha=0.25)
      std_70, lower_70, upper_70 = wls_prediction_std(results_predict,alpha=0.30)
      
      # Save budget file for prediction
      X_full = x_dis    
      budget_df = pd.DataFrame({'EEID':df['EEID'], 'original': df['LOG_SALARY'], 'GENDER': df['GENDER'],'predicted':y_pred,'pred_lower': lower, 'pred_upper': upper, 'pred_stderr': std})
  #     X_full.to_excel('xfull.xlsx')
  #     df.to_excel('check_final.xlsx')
      budget_df.to_excel('check_final_budget.xlsx')
      # r2 = 0.91
      # Graphs
      # fig_r2_gender_gap = plot_full_pie(r2,'r2')
      # fig_r2_gender_gap = plot_r2(r2)
      # fig_raw_gender_gap = plot_gender_gap(female_coff)
      # fig_net_gender_gap = plot_gender_gap(female_coff)
      
      # Statistics for output
      hc_female = df[(df['GENDER']=='F') | (df['GENDER']=='FEMALE')].shape[0]
      
      avg_pay = df['SALARY'].mean()
      
      # Result Table
      df_result.to_excel('result_regression.xlsx')
      
      df_result_gender = df_result[df_result['CONTENT'].str.contains("GENDER")==True]
      df_result_gender = df_result_gender[['CONTENT','Coef.','P>|t|']]
      df_result_gender.columns = ['CONTENT','COEF','PVALUE']
      df_result_gender['GENDER'] = df_result_gender['CONTENT']
      df_result_gender['GENDER'] = df_result_gender['GENDER'].str.replace("[","")
      df_result_gender['GENDER'] = df_result_gender['GENDER'].str.replace("]","")
      df_result_gender['GENDER'] = df_result_gender['GENDER'].str.split('.').str[-1]
      df_result_gender = df_result_gender.drop(columns=['CONTENT'])
      df_result_gender['COEF_DISPLAY'] = df_result_gender['COEF']
      df_result_gender['STAT'] = "No"
      df_result_gender['STAT_COUNT'] = 0
      df_result_gender.loc[df_result_gender['PVALUE']<=0.05,"STAT"] = "Yes"
      df_result_gender.loc[((df_result_gender['PVALUE']<=0.05) & (df_result_gender['COEF']<0)) ,"STAT_COUNT"] = 1
      df_result_gender = df_result_gender.sort_values(by=['COEF_DISPLAY'], ascending=False)
      baseline_row = pd.DataFrame({'GENDER':'M', 'COEF':0, 'COEF_DISPLAY':0,'PVALUE':1,'STAT':"No",'STAT_COUNT':0},index =[0])
      df_result_gender = pd.concat([baseline_row, df_result_gender[:]]).reset_index(drop = True)
      df_result_gender['GENDER'] = df_result_gender.apply(lambda x: gender_name_replace(text=x['GENDER']),axis=1)
      df_result_gender = df_result_gender[~((df_result_gender['GENDER']=='Unknown') | (df_result_gender['GENDER']=='unknown'))]
      df_result_gender = df_result_gender.reset_index(drop=True)
      df_result_gender.to_excel('result_gender.xlsx')
      fig_gender_bar = plot_bar(df_result_gender,'GENDER')
      
      df_result_eth = df_result[df_result['CONTENT'].str.contains("ETHNICITY")==True]
      df_result_eth = df_result_eth[['CONTENT','Coef.','P>|t|']]
      df_result_eth.columns = ['CONTENT','COEF','PVALUE']
      df_result_eth['ETHNICITY'] = df_result_eth['CONTENT']
      df_result_eth['COEF_DISPLAY'] = df_result_eth['COEF']
      df_result_eth['STAT'] = "No"
      df_result_eth['STAT_COUNT'] = 0
      df_result_eth.loc[df_result_eth['PVALUE']<=0.05,"STAT"] = "Yes"
      df_result_eth.loc[((df_result_eth['PVALUE']<=0.05) & (df_result_eth['COEF']<0)),"STAT_COUNT"] = 1
      
      df_result_eth = df_result_eth.sort_values(by=['COEF_DISPLAY'], ascending=False)
      df_result_eth['ETHNICITY'] = df_result_eth['ETHNICITY'].str.replace("[","")
      df_result_eth['ETHNICITY'] = df_result_eth['ETHNICITY'].str.replace("]","")
      df_result_eth['ETHNICITY'] = df_result_eth['ETHNICITY'].str.split('.').str[-1]
      df_result_eth = df_result_eth.drop(columns=['CONTENT'])
      df_result_eth = df_result_eth.reset_index(drop=True)
      baseline_eth = df['ETHNICITY'].value_counts()
      # baseline_eth.to_excel('baseline_eth.xlsx')
      # print(baseline_eth.index[0])
      baseline_row = pd.DataFrame({'ETHNICITY':baseline_eth.index[0], 'COEF':0, 'COEF_DISPLAY':0,'PVALUE':1,'STAT':"No",'STAT_COUNT':0},index =[0])
      df_result_eth = pd.concat([baseline_row, df_result_eth[:]]).reset_index(drop = True)
      df_result_eth = df_result_eth[~((df_result_eth['ETHNICITY']=='Unknown') | (df_result_eth['ETHNICITY']=='unknown'))]
      eth_baseline = df_result_eth['ETHNICITY'][0]
      df_result_eth.to_excel('result_eth.xlsx')
      
      
      fig_eth_bar = plot_bar(df_result_eth,'ETHNICITY')
      
      # Gender Table
      df_gender = df.pivot_table(index=['GENDER'],values=['EEID','SALARY'],aggfunc={'EEID':'count','SALARY':'mean'},fill_value=np.nan)
      df_gender.columns = ['_'.join(col).strip() for col in df_gender.columns.values]
      df_gender = df_gender.reset_index()
      df_gender.columns = ['GENDER','HC','AVG_PAY']
      df_gender = df_gender.merge(df_gender_name,on='GENDER',how='left')
      df_gender = df_gender.sort_values(by=['HC'],ascending=False)
      
      df_gender_female_pay = df[df['GENDER'] == 'F']
      df_gender_nonb_pay = df[df['GENDER'] == 'N']
      
      gender_female_pay = df_gender_female_pay['SALARY'].mean()
      gender_nonb_pay = df_gender_nonb_pay['SALARY'].mean()
      
      # df_gender_minor_pay.to_excel('gender.xlsx')
      fig_gender_hc = plot_gender_donut(df_gender)
      
      # Ethnicity Table
      # plt.clf()
      df_eth = None
      eth_minor_pay = None
      if 'ETHNICITY' in include_feature:
          df_eth = df.pivot_table(index=['ETHNICITY'],values=['EEID','SALARY'],aggfunc={'EEID':'count','SALARY':'mean'},fill_value=np.nan)
          df_eth.columns = ['_'.join(col).strip() for col in df_eth.columns.values]
          df_eth = df_eth.reset_index()
          df_eth.columns = ['ETHNICITY_NAME','HC','AVG_PAY']
          df_eth = df_eth.sort_values(by=['HC'],ascending=False)
          fig_eth_hc = plot_eth_donut(data = df_eth)
          df_eth_minor_pay = df[df['ETHNICITY'] != df_eth['ETHNICITY_NAME'][0]]
          eth_minor_pay = df_eth_minor_pay['SALARY'].mean()
          # df_eth_minor_pay.to_excel('eth.xlsx')
      
      # df_gender = df_gender.drop(columns=['GENDER'])
      # df_gender.to_excel('gender.xlsx')
      # df.to_excel('gender.xlsx')
      # print(message.loc[['OVERVIEW']]['Message'])
      
      return df, df_org, df_validation, message, exclude_col, r2_raw, female_coff_raw, female_pvalue_raw, r2, female_coff, female_pvalue, before_clean_record, after_clean_record,hc_female,X_full,budget_df,exclude_feature, include_feature,df_gender, df_eth, fig_gender_hc,fig_eth_hc, avg_pay, gender_female_pay, gender_nonb_pay, eth_minor_pay,fig_gender_bar, fig_eth_bar, df_result_gender, df_result_eth,eth_baseline
  # , fig_gender_bar
  
  def reme(df,budget_df,X_full,factor, project_group_feature, protect_group_class):
      budget_df['adj_lower'] = budget_df['predicted'] - factor * budget_df['pred_stderr']
  
      # Adjust protect group pay only, others set to original pay
      budget_df['adj_salary'] = budget_df['original']
      budget_df.loc[(budget_df[project_group_feature] == protect_group_class) & (budget_df['original'] < budget_df['adj_lower']),'adj_salary'] = budget_df['adj_lower']
      
      # Recalculate pay gap and p value with adjusted salary
      model = sm.OLS(budget_df['adj_salary'], X_full)
      results = model.fit()
  
      budget = np.sum(np.exp(budget_df['adj_salary']) - np.exp(budget_df['original']))
      budget_df['S_Salary'] = np.exp(budget_df['original'])
      budget_df['S_Budget'] = np.exp(budget_df['adj_salary'])-np.exp(budget_df['original'])
      budget_df['S_Adjusted'] = np.exp(budget_df['adj_salary'])
      budget_df['S_AdjInd'] = 0
      budget_df.loc[budget_df['S_Budget'] >0, 'S_AdjInd']=1
  
      # Reporting
      current_total_salary = np.sum(budget_df['S_Salary'])
      Budget_PCT = budget_df['S_Budget']/np.exp(budget_df['original'])
  
      target_position = 1
      resulting_gap = results.params[target_position]
      resulting_pvalues = results.pvalues[target_position]
      adj_count = budget_df['S_AdjInd'].sum()
      adj_average = Budget_PCT[Budget_PCT>0].mean()
      adj_max = Budget_PCT[Budget_PCT>0].max()
      adj_budget_pct = budget/current_total_salary
      
      # print(results.summary())
      # print("***")
      # print("Factor: "+str(factor))
      # print(resulting_gap)
      # print(resulting_pvalues)
      # print(adj_count)
      # print(adj_budget_pct)
      # budget_df.to_excel('check_final_budget.xlsx')
  
      return budget_df, budget, resulting_gap, resulting_pvalues, adj_count, adj_budget_pct
  
  # Set functions
  @st.experimental_memo(show_spinner=False)
  def convert_df(df):
      # IMPORTANT: Cache the conversion to prevent computation on every rerun
      return df.to_csv().encode('utf-8')
  
  # Function
  @st.experimental_memo(show_spinner=False)
  # Download Excel Template
  def get_binary_file_downloader_html(bin_file, file_label='File'):
      with open(bin_file, 'rb') as f:
          data = f.read()
      bin_str = base64.b64encode(data).decode()
      # href = f'<a href="data:application/octet-stream;base64,{bin_str}" download="{bin_file}">{file_label}</a>'
      href = f'<a href="data:application/octet-stream;base64,{bin_str}" download="{os.path.basename(bin_file)}">{file_label}</a>'
      return href
  
  # Download Excel Files
  def get_excel_file_downloader_html(data, file_label='File'):
      bin_str = base64.b64encode(data).decode()
      # bin_str = base64.b64encode(data)
      # href = f'<a href="data:application/octet-stream;base64,{bin_str}" download="{bin_file}">{file_label}</a>'    
      # href = f'<a href="data:application/octet-stream;base64,{bin_str}" download="{file_label}">{file_label}</a>'
      href = f'<a href="data:file/xlsx;base64,{bin_str}" download="{file_label}">{file_label}</a>'
      # href = f'<a href="data:file/xlsx;base64,{b64}" download="new_file.{extension}">Download {extension}</a>'
      return href
  
  @st.experimental_memo(show_spinner=False)
  # Run Goal Seek for insignificant gap and 0 gap
  def reme_gap_seek(df,budget_df,X_full, project_group_feature, protect_group_class, seek_goal, current_pvalue, current_gap, search_step = -0.001):
      factor_range = np.arange(2, -2,search_step)
      threshold = 0.0005
      
      # seek_budget_df = np.nan
      seek_budget_df = pd.DataFrame()
      seek_budget = np.nan
      seek_resulting_gap  = np.nan
      seek_resulting_pvalues =  np.nan
      seek_adj_count = np.nan
      seek_adj_budget_pct = np.nan
      seek_pass = False
      seek_success = False
      
      if current_gap>=0:
          print('current gap is already >= 0')
          seek_pass = False
          seek_success = False
          seek_resulting_gap = current_gap
      else:
          seek_pass = True
          for factor in factor_range:
              budget_df, budget, resulting_gap, resulting_pvalues, adj_count, adj_budget_pct = reme(df,budget_df,X_full,factor, project_group_feature, protect_group_class)
  
              if np.abs(resulting_gap-seek_goal)<=threshold:
                  seek_budget_df = budget_df
                  seek_budget = budget
                  seek_resulting_gap  = resulting_gap
                  seek_resulting_pvalues =  resulting_pvalues
                  seek_adj_count = adj_count
                  seek_adj_budget_pct = adj_budget_pct
                  seek_success = True
  
                  print('Found factor that close gap:' + str(factor))
                  print('Final Gap is '+str(seek_resulting_gap))
                  print('Final p_value is '+str(seek_resulting_pvalues))
                  print('Final Budget is '+str(seek_budget))
                  print('Final Budget % '+str(seek_adj_budget_pct))
                  
                  keep_list = ['EEID','S_Budget','S_Adjusted']
                  seek_budget_df = seek_budget_df[keep_list]
                  seek_budget_df.columns=['EEID','SCENARIO_B_ADJUSTMENT','SCENARIO_B_ADJUSTED_SALARY']
                  # seek_budget_df = seek_budget_df.merge(df,on='EEID',how='inner')
                  seek_budget_df.to_excel('budget_gap.xlsx')
                  break
  
          if seek_budget == np.nan:
              print('no result found')
              seek_success = False
      
      return seek_budget_df,seek_budget,seek_resulting_gap,seek_resulting_pvalues,seek_adj_count, seek_adj_budget_pct,seek_pass,seek_success
      
  @st.experimental_memo(show_spinner=False)
  # Run Goal Seek for insignificant gap and 0 gap
  def reme_pvalue_seek(df,budget_df,X_full, project_group_feature, protect_group_class, seek_goal, current_pvalue, current_gap, search_step= -0.005):
      
      factor_range = np.arange(2, -2,search_step)
      threshold = 0.0005
      
      # seek_budget_df = np.nan
      seek_budget_df = pd.DataFrame()
      seek_budget = np.nan
      seek_resulting_gap  = np.nan
      seek_resulting_pvalues =  np.nan
      seek_adj_count = np.nan
      seek_adj_budget_pct = np.nan
      seek_pass = False
      seek_success = False
      
      if current_pvalue>=0.05:
          print('Current P value already greater than 5%: '+str(current_pvalue))
          seek_pass = False
          seek_resulting_gap = current_gap
      else:
          seek_pass = True
          for factor in factor_range:
              budget_df, budget, resulting_gap, resulting_pvalues, adj_count, adj_budget_pct = reme(df,budget_df,X_full,factor, project_group_feature, protect_group_class)
  
              # if np.abs(resulting_pvalues-seek_goal)<=threshold:
              if resulting_pvalues>=seek_goal:
                  seek_budget_df = budget_df
                  seek_budget = budget
                  seek_resulting_gap  = resulting_gap
                  seek_resulting_pvalues =  resulting_pvalues
                  seek_adj_count = adj_count
                  seek_adj_budget_pct = adj_budget_pct
                  seek_success = True
  
                  print('Found factor that close pvalue:' + str(factor))
                  print('Final Gap is '+str(seek_resulting_gap))
                  print('Final p_value is '+str(seek_resulting_pvalues))
                  print('Final Budget is '+str(seek_budget))
                  print('Final Budget % '+str(seek_adj_budget_pct))
  
                  keep_list = ['EEID','S_Budget','S_Adjusted']
                  seek_budget_df = seek_budget_df[keep_list]
                  seek_budget_df.columns=['EEID','SCENARIO_A_ADJUSTMENT','SCENARIO_A_ADJUSTED_SALARY']
                  # seek_budget_df = seek_budget_df.merge(df,on='EEID',how='inner')
                  seek_budget_df.to_excel('budget_pv.xlsx')                
                  break
  
          if seek_budget == np.nan:
              print('no result found')
              seek_success = False
      
      return seek_budget_df,seek_budget,seek_resulting_gap,seek_resulting_pvalues,seek_adj_count, seek_adj_budget_pct,seek_pass,seek_success
  
  # def exam_col(file_path,display_path):
  #     message = ""
  #     df['Data2'].replace(r'^\s*$', np.nan, regex=True).isna().all()
  
  def display_rename(display_map,feature):
      return [display_map.get(item,item)  for item in feature]
  
  # def analysis(df_submit, run_demo, file_path, display_path, main_page, main_page_info, ci):
  def analysis(df_submit, run_demo, file_path, display_path, main_page, main_page_info):
      # Process df (not demo datafile)    
      with st.spinner('Running analysis, Please wait for it...'):
          m_info = main_page_info.success('Reading Data')
          if run_demo == True:
              # Demo Run
              df = pd.read_excel(file_path,sheet_name="Demo Data")
          else:
              df = pd.read_excel(df_submit,sheet_name="Submission")    
          
          # Convert program column names to display
          df_name = pd.read_excel(display_path,sheet_name="Standard_Col")
          df_cus_name = pd.read_excel(display_path,sheet_name="Custom_Col")
          df_gender_name = pd.read_excel(display_path,sheet_name="Gender")
          
          # Run Checking    
          df_check = df.iloc[1:]
          full_list = df_name['PROGRAM_NAME'].tolist()
          full_list_display = df_name['DISPLAY_NAME'].tolist()
          
          req_list = df_name[df_name['REQUIRE']==1]['PROGRAM_NAME'].tolist()
          # req_list_display = df_name[df_name['REQUIRE']==1]['DISPLAY_NAME'].tolist()
          cus_list = df_cus_name['PROGRAM_NAME'].tolist()
          display_map = dict(zip(full_list, full_list_display))
          
          inc_list = []
          exc_list = []
  
          for col in df_check.columns.tolist():
              if df_check[col].replace(r'^\s*$', np.nan, regex=True).isna().all():
              # if df_check[col].isna():
                  exc_list.append(col)
              else:
                  inc_list.append(col)
          
          error_req_list = [x for x in req_list if x not in inc_list]
          error_req_message = 'The requested data field below has not been provided: '+ ', '.join(error_req_list)+'. Please update and resubmit the template.'
          
          final_req_list = [x for x in req_list if x not in ['EEID','SALARY']]
          display_req_list = display_rename(display_map,final_req_list)
  
          final_optional_list = [x for x in inc_list if x not in (req_list+cus_list+['SNAPSHOT_DATE'])]
          display_optional_list = display_rename(display_map,final_optional_list)
          
          # Display selection Step 3
          config = main_page.form("Configuration")
          with config:
              config.write("Step 3. Confirm Selected Configuration")
              ci_select, optional_select, req_select, = config.columns((0.5, 1, 1)) 
              ci = ci_select.slider(label = 'A: Choose fair pay confidence internal %', value = 95, min_value = 70, max_value = 99, step = 1, help='Setting at 95% means I want to have a pay range so that 95% of the time, the predicted pay falls inside.')
              optional_col = optional_select.multiselect(label = 'C: Select Optional Pay Factors',options=display_optional_list,default=display_optional_list,disabled=False)
              req_col = req_select.multiselect(label = 'B: Required Pay Factors',options=display_req_list,default=display_req_list,disabled=True)
              submitted_form = config.form_submit_button("🚀 Confirm to Run Analysis'")
          
          
          
          print('inc: ')
          print(inc_list)
          print('exc: ')
          print(exc_list)
          print('req: ')
          print(req_list)
          print('cus: ')
          print(cus_list)
          print('error message req: ')
          print(error_req_message)
          print('display req')
          print(display_req_list)
          print('display optional')
          print(display_optional_list)
          
          
          
          
          
          display_map = dict(zip(df_name['PROGRAM_NAME'], df_name['DISPLAY_NAME']))
          # display_gender_map = dict(zip(df_gender_name['PROGRAM_NAME'], df_gender_name['DISPLAY_NAME']))
          
          # Run discovery model:
          m_info = main_page_info.success('Running Gap Analysis')
          df, df_org,  df_validation, message, exclude_col, r2_raw, female_coff_raw, female_pvalue_raw, r2, female_coff, female_pvalue, before_clean_record, after_clean_record,hc_female,X_full,budget_df,exclude_feature, include_feature,df_gender,df_eth,fig_gender_hc,fig_eth_hc,avg_pay, gender_female_pay, gender_nonb_pay, eth_minor_pay,fig_gender_bar, fig_eth_bar,df_result_gender, df_result_eth, eth_baseline = run(df,df_gender_name)     
          
          # , fig_gender_bar
          # ,fig_eth_hc
          print('pvalue'+str(female_pvalue))
          gender_gap_format = str(round(female_coff*100,0))+'%'
          gender_gap_stats = '**NOT** statistically significant'
          if female_pvalue<0.05:
              gender_gap_stats = 'statistically significant'
          
          # Run Reme Pvalue = 7%
          m_info = main_page_info.success('Running Remediation Scenario A: Mitigate Legal Risk')
          seek_budget_df_pv,seek_budget_pv,seek_resulting_gap_pv,seek_resulting_pvalues_pv,seek_adj_count_pv, seek_adj_budget_pct_pv,seek_pass_pv, seek_success_pv = reme_pvalue_seek(df,budget_df,X_full, project_group_feature='GENDER', protect_group_class='F', seek_goal=0.07, current_gap = female_coff, current_pvalue = female_pvalue, search_step = -0.005)
          if seek_success_pv == False:
              print("search p at 0.001 step")
              seek_budget_df_pv,seek_budget_pv,seek_resulting_gap_pv,seek_resulting_pvalues_pv,seek_adj_count_pv, seek_adj_budget_pct_pv,seek_pass_pv, seek_success_pv = reme_pvalue_seek(df,budget_df,X_full, project_group_feature='GENDER', protect_group_class='F', seek_goal=0.07, current_gap = female_coff, current_pvalue = female_pvalue, search_step = -0.001)
          print('pvalue'+str(seek_resulting_pvalues_pv))
          seek_budget_df_pv.to_excel('df_pv.xlsx')
  
          # Run Reme Zero Gap
          m_info = main_page_info.success('Running Remediation Scenario B: Close Gender Gap')
          seek_budget_df_gap,seek_budget_gap,seek_resulting_gap_gap,seek_resulting_pvalues_gap,seek_adj_count_gap, seek_adj_budget_pct_gap,seek_pass_gap, seek_success_gap = reme_gap_seek(df,budget_df,X_full, project_group_feature='GENDER', protect_group_class='F', seek_goal=0, current_gap = female_coff, current_pvalue = female_pvalue, search_step = -0.005)
          if seek_success_gap == False:
              seek_budget_df_gap,seek_budget_gap,seek_resulting_gap_gap,seek_resulting_pvalues_gap,seek_adj_count_gap, seek_adj_budget_pct_gap,seek_pass_gap, seek_success_gap = reme_gap_seek(df,budget_df,X_full, project_group_feature='GENDER', protect_group_class='F', seek_goal=0, current_gap = female_coff, current_pvalue = female_pvalue, search_step = -0.001)
          print('pvalue'+str(seek_resulting_pvalues_gap))  
          seek_budget_df_gap.to_excel('df_gap.xlsx')
          
          # Create download file for remediation
          reme_download_flag = 0
          df_reme_org = df.drop(columns=['VALIDATION_MESSAGE', 'VALIDATION_FLAG', 'NOW','LOG_SALARY'])
          if operator.not_(seek_budget_df_pv.empty):
              df_reme_ind = seek_budget_df_pv.merge(seek_budget_df_gap,on='EEID',how='inner').merge(df_reme_org,on = 'EEID',how='inner')
              list_reme = [x for x in df_reme_ind.columns.tolist() if x not in ['EEID','GENDER','SALARY']]
              list_reme = ['EEID','GENDER','SALARY']+list_reme
              df_reme_ind = df_reme_ind[list_reme]
              reme_download_flag = 1
          elif (seek_budget_df_pv.empty) and (operator.not_(seek_budget_df_gap.empty)):
              df_reme_ind = seek_budget_df_gap.merge(df_reme_org,on = 'EEID',how='inner')
              list_reme = [x for x in df_reme_ind.columns.tolist() if x not in ['EEID','GENDER','SALARY']]
              list_reme = ['EEID','GENDER','SALARY']+list_reme
              df_reme_ind = df_reme_ind[list_reme]
              reme_download_flag = 1
          # df_reme_ind.to_excel('df_reme_ind.xlsx')
          
          # Run Remediation Messages
          message_budget_pv = np.nan
          if seek_pass_pv == False:
              message_budget_pv = '0 - gender pay gap is currently not statistically significant.'
              message_budget_pv_overview = message_budget_pv
          elif (seek_pass_pv == True) and (seek_success_pv == False):
              message_budget_pv = 'No results found, please contact our consultant for more information.'
              message_budget_pv_overview = message_budget_pv
          else:
              if seek_budget_pv> 1000000:
                  message_budget_pv = str(locale.format("%.2f", round(seek_budget_pv/1000000,2), grouping=True))+' Million'+'\n'+'('+str(round(seek_adj_budget_pct_pv*100,0))+'% of Pay)'
              else:
                  message_budget_pv = str(locale.format("%d", round(seek_budget_pv/1000,0), grouping=True))+' Thousand'+'\n'+'('+str(round(seek_adj_budget_pct_pv*100,0))+'% of Pay)'
              message_budget_pv_overview = "Raising women's pay by $"+message_budget_pv+' will reduce gap to ' + '-2.2%' + ' and become statistically insignificant.'
  
          message_budget_gap = np.nan
          if seek_pass_gap == False:
              message_budget_gap = '0 - Women earn more than men, so no adjustment is necessary.'
          elif (seek_pass_gap == True) and (seek_success_gap == False):
              message_budget_gap = 'No results found, please contact our consultant for more information.'
          else:
              if seek_budget_gap> 1000000:
                  message_budget_gap = str(locale.format("%.2f", round(seek_budget_gap/1000000,2), grouping=True))+' Million'+'\n'+'('+str(round(seek_adj_budget_pct_gap*100,2))+'% of Pay)'
              else:
                  message_budget_gap = str(locale.format("%d", round(seek_budget_gap/1000,0), grouping=True))+' Thousand'+'\n'+'('+str(round(seek_adj_budget_pct_gap*100,2))+'% of Pay)'
  
          scenario = ['Current','A','B']
          action = ['🏁 No change','✔️ Mitigate legal risk \n'+'✔️ Reduce the gender gap to a statistical insignificant level.','✔️ Mitigate legal risk \n'+'✔️ Completely close gender gap \n'+'✔️ Become a market leader (Top 1%)\n']
          budget = ['0',message_budget_pv,message_budget_gap]
          
          if abs(seek_resulting_gap_gap)<0.0005:
                  seek_resulting_gap_gap = 0
          net_gap = [female_coff,seek_resulting_gap_pv,seek_resulting_gap_gap]
          net_gap = [f'{i*100:.1f}%' for i in net_gap]
  
          # Show exclude and include features
          include_feature = [display_map.get(item,item)  for item in include_feature]
          exclude_feature = [display_map.get(item,item)  for item in exclude_feature]
          
          include_feature_text =  ', '.join(include_feature)
          exclude_feature_text =  ', '.join(exclude_feature)
          # st.sidebar.options = st.sidebar.markdown('Pay drivers included in model:' + include_feature_text)
          
          # Run data validation
          m_info = main_page_info.success('Output Data Validation')
          demo_validation = convert_df(df_validation)
          
          output = BytesIO()
          writer = pd.ExcelWriter(output, engine='xlsxwriter')
          df_validation.to_excel(writer, index=False, sheet_name='Sheet1')
          workbook = writer.book
          worksheet = writer.sheets['Sheet1']
          
          for column in df_validation:
              column_width = max(df_validation[column].astype(str).map(len).max(), len(column))+3
              col_idx = df_validation.columns.get_loc(column)
              writer.sheets['Sheet1'].set_column(col_idx, col_idx, column_width)
          cell_format = workbook.add_format()
  
          cell_format.set_pattern(1)  # This is optional when using a solid fill.
          cell_format.set_bg_color('yellow')
          worksheet.write('A1', 'Please see the instructions for a valid record.',cell_format)
          
          writer.save()
          processed_data = output.getvalue()
                  
          # Display run is successful message    
          m_info = main_page_info.success('View Result: '+message.loc[['OVERVIEW']][0])
          
          # Display Overview
  #         main_page.markdown("""---""")
  #         overview_1, overview_2, overview_3,overview_4 = main_page.columns((1, 0.001, 0.001, 0.001))
  #         overview_A1, overview_A2, overview_B1,overview_B2 = main_page.columns((1, 1, 1, 1))
          
  #         # overview_1.image('Picture/overview.jpg',use_column_width='auto')
  #         if r2>0.7:
  #             if female_pvalue>0.05:
  #                 overview_1.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 150%; opacity: 0.7'>  Congratulation!  </h1>", unsafe_allow_html=True)
  #                 if female_coff<-0.05:
  #                     overview_1.markdown('You have a net gender pay gap of '+gender_gap_format+' and it is '+ gender_gap_stats + '. Your pay gap presents a <font color=Green> **low** </font> legal risk. However, you have a <font color=Orange> **larger** </font> gap than the market. A larger negative gap generally results in a statistically significant status which increases legal risk in the long run. As a precaution, you can routinely repeat this analysis to monitor the pay gap. An alternative is to consider closing the pay gap - see Scenario B below.', unsafe_allow_html=True)                    
  #                 elif female_coff>=-0.05 and female_coff<0:
  #                     overview_1.markdown('You have a net gender pay gap of '+gender_gap_format+' and it is '+ gender_gap_stats + '. Your pay gap is at <font color=Green> **low** </font> legal risk. You are also in alignment with the market! We recommend periodic monitoring of the pay gap, for example before and after merit increases, mergers and acquisitions, organizational restructuring, and relevel of key jobs. An alternative is to consider closing the pay gap - see Scenario B below.', unsafe_allow_html=True)
  #                 else:
  #                     overview_1.markdown('You have a net gender pay gap of '+gender_gap_format+' and it is '+ gender_gap_stats + '. Your pay gap is at <font color=Green> **low** </font> legal risk. You are a <font color=Green> **market leader** </font> in gender pay equaity (Only 1% of companies have higher female earnings than men all else equal). We recommend periodic monitoring of the pay gap, for example before and after merit increases, mergers and acquisitions, organizational restructuring, and relevel of key jobs.', unsafe_allow_html=True)
  #             else:
  #                 if female_coff>0:
  #                     overview_1.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 150%; opacity: 0.7'>  Congratulation!  </h1>", unsafe_allow_html=True)
  #                     overview_1.markdown('You have a net gender pay gap of '+gender_gap_format+' and it is '+ gender_gap_stats + '. Your pay gap is at <font color=Green> **low** </font> legal risk. You are a <font color=Green> **market leader** </font> in gender pay equaity (Only 1% of companies have higher female earnings than men all else equal). We recommend periodic monitoring of the pay gap, for example before and after merit increases, mergers and acquisitions, organizational restructuring, and relevel of key jobs.', unsafe_allow_html=True)
  #                 else:
  #                     message = 'You have a net gender pay gap of '+gender_gap_format+' and it is '+ gender_gap_stats + '. This result poses a <font color=Orange> **high** </font> legal risk. You should consider to reducing it to a statistically insignificant level - See Scenario A below. An alternative is to consider closing the pay gap - see Scenario B below.'
  #                     # overview_1.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 150%; opacity: 0.7'> ⚠️ Be mindful of legal risks  </h1>", unsafe_allow_html=True)
  #                     overview_1.markdown(message, unsafe_allow_html=True)
                      
  #                     # message = 'You have a net gender pay gap of '+gender_gap_format+'. It is '+ gender_gap_stats + ' which can make you more prone to gender-related litigation. Consider to reducing it to a statistically insignificant level (Scenario A) or completely closing the gap (Scenario B).'
  #                     # with overview_1:
  #                     #     hc.info_card(title='Watch out for legal risk', content=message, theme_override=get_hc_theme('warning'), key='main_message')
  #                     # with overview_A1:
  #                     #     hc.info_card(title='Scenario A - Budget', content=message_budget_pv, theme_override=get_hc_theme('good'), key='A1')
  #                     # with overview_A2:
  #                     #     hc.info_card(title='Scenario A - Result', content=net_gap[1], theme_override=get_hc_theme('good'), key='A2')
  #                     # with overview_B1:
  #                     #     hc.info_card(title='Scenario B - Budget', content=message_budget_gap, theme_override=get_hc_theme('good'), key='B1')
  #                     # with overview_B2:
  #                     #     hc.info_card(title='Scenario B - Result', content=net_gap[2], theme_override=get_hc_theme('good'), key='B2')
                      
  #         else:
  #             overview_1.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 150%; opacity: 0.7'> Contact Us </h1>", unsafe_allow_html=True)
  #             overview_1.markdown('The default pay drivers are <font color=Orange> **not sufficient** </font> to account for the variation of wages between employees. To improve the model robustness, you may include additional pay drivers such as high potential, cost centre, skills and so on in the template. Please contact us for a free consultation if you are not sure.', unsafe_allow_html=True)            
  #             main_page.markdown("""---""")
  #             st.stop()
              
          main_page.markdown("""---""")
          m_col1_but_col1, m_col1_but_col2, m_col1_but_col3, m_col1_but_col4 = main_page.columns((0.5, 0.5, 1 , 1))
          # m_col1_but_col2, m_col1_but_col3 = main_page.columns((1, 1))
          # Display headcount, Successful Run, Female Percent, download validation file
          # m_col1_but_col1.metric('💬 Submission Record',before_clean_record)
          # m_col1_but_col2.metric('🏆 Successful Run',after_clean_record)
          # m_col1_but_col3.metric('👩 Female %',round(hc_female/after_clean_record,2)*100)
          
          # with m_col1_but_col2:
          #     df_eth.to_excel('eth2.xlsx')
          #     # plost.donut_chart(data=df_eth,theta='HC',color='ETHNICITY', title = 'Ethnicity %', legend = 'right', use_container_width = True)
          #     plost.donut_chart(data=df_eth,theta='HC',color='ETHNICITY_NAME', title = 'Eth')
          
          m_col1_but_col1.metric('Submitted Entry',before_clean_record)
          m_col1_but_col1.metric('Processed Entry',after_clean_record)
          m_col1_but_col1.metric('Invalid Entry',before_clean_record - after_clean_record)
          if operator.not_(df_validation.empty):
              m_col1_but_col1.markdown(get_excel_file_downloader_html(processed_data, 'Invalid Entry.xlsx'), unsafe_allow_html=True)
              m_col1_but_col1.markdown("🖱️ 'Save link as...'")
          
          avg_pay_info = str(locale.format("%d", round(avg_pay/1000,0), grouping=True))+'k'
          m_col1_but_col2.metric('Ave Overall Salary',avg_pay_info)
          
          gender_female_pay_info = str(locale.format("%d", round(gender_female_pay/1000,0), grouping=True))+'k'
          gender_female_pay_delta = str(locale.format("%d", round(gender_female_pay/1000,0) - round(avg_pay/1000,0), grouping=True))+'k'
          # m_col1_but_col2.metric('Avg Non-Male Salary',gender_minor_pay_info, delta=gender_minor_pay_delta)
          m_col1_but_col2.metric('Avg Female Salary',gender_female_pay_info)
          
          gender_nonb_pay_info = str(locale.format("%d", round(gender_nonb_pay/1000,0), grouping=True))+'k'
          gender_nonb_pay_delta = str(locale.format("%d", round(gender_nonb_pay/1000,0) - round(avg_pay/1000,0), grouping=True))+'k'
          # m_col1_but_col2.metric('Avg Non-Male Salary',gender_minor_pay_info, delta=gender_minor_pay_delta)
          m_col1_but_col2.metric('Avg Non-Binary Salary',gender_nonb_pay_info)
          
          eth_major_name = 'Avg Non-'+ df_eth['ETHNICITY_NAME'][0] + ' Salary'
          eth_minor_pay_info = str(locale.format("%d", round(eth_minor_pay/1000,0), grouping=True))+'k'
          eth_minor_pay_delta = str(locale.format("%d", round(eth_minor_pay/1000,0) - round(avg_pay/1000,0), grouping=True))+'k'
          # m_col1_but_col2.metric(eth_major_name,eth_minor_pay_info, delta = eth_minor_pay_delta)
          m_col1_but_col2.metric(eth_major_name,eth_minor_pay_info)
          
          # m_col1_but_col1_2.metric('Submission Record',before_clean_record)
          # m_col1_but_col1_2.metric('Successful Run',after_clean_record)
          
          m_col1_but_col3.markdown("<h1 style='text-align: center; vertical-align: top; font-size: 100%'>Gender Distribution</h1>", unsafe_allow_html=True)
          m_col1_but_col3.pyplot(fig_gender_hc)
          
          m_col1_but_col4.markdown("<h1 style='text-align: center; vertical-align: top; font-size: 100%'>Ethnicity Distribution</h1>", unsafe_allow_html=True)
          m_col1_but_col4.pyplot(fig_eth_hc)
              
          # main_page.markdown("""---""")
          
          # main_page.pyplot(fig_gender_hc)
  #         test1, test2, test3 = main_page.columns((1, 1, 1))
          
  #         test1.plotly_chart(fig_gender_bar, use_container_width=True)
          
          # main_page.markdown("""---""")
          
          with st.expander("What pay drivers are supplied:"):
              inc_col, exc_col = st.columns((1, 1))
              inc_col.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Pay drivers you supplied:  </h1>", unsafe_allow_html=True)
              inc_col.markdown(include_feature_text, unsafe_allow_html=True)       
              exc_col.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Pay drivers you haven't supplied:  </h1>", unsafe_allow_html=True)        
              exc_col.markdown(exclude_feature_text, unsafe_allow_html=True)
          
          # r2= 0.9
          
          # Show R2
          main_page.markdown("""---""")
          metric_R2_1, metric_R2_2, metric_R2_3 = main_page.columns((1, 1.2, 1.3))            
          metric_R2_1.markdown("<h1 style='text-align: left; vertical-align: bottom; font-size: 150%; color: #3498DB; opacity: 0.7'>Robustness</h1>", unsafe_allow_html=True)
          # metric_R2_1.plotly_chart(fig_r2_gender_gap, use_container_width=True)
          with metric_R2_1:
              r2_options = get_r2_option(r2)
              st_echarts(options=r2_options,height="200px") 
          
          metric_R2_2.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Benchmark</h1>", unsafe_allow_html=True)
          metric_R2_2.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> 🌐 70% ~ 100%  </h1>" "  \n"  "Robustness measures whether the standard model adequately explains compensation decisions. For instance, 80% means that the standard model explains 80% of the pay difference between employees.", unsafe_allow_html=True)
          metric_R2_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Observation</h1>", unsafe_allow_html=True)
          if r2>=0.7:
              metric_R2_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Align with market  </h1>", unsafe_allow_html=True)
              metric_R2_3.markdown("Great! Pay drivers are **sufficient** to account for the variation of wages between employees. Let us look at the pay gap.", unsafe_allow_html=True)
          else:
              metric_R2_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Below market  </h1>" , unsafe_allow_html=True)
              metric_R2_3.markdown("The default compensation drivers are <font color=Orange> **not robust** </font> in drawing conclusions on the salary gap. In general, we can improve the model robustness by adding additional drivers such as talent potential, cost centre, skills and so on. Please contact us for an open consultation.", unsafe_allow_html=True)
              st.stop()
          # metric_R2_3.markdown("<h1 style='text-align: center; vertical-align: bottom; color: Black; background-color: #3498DB; opacity: 0.7; border-style: dotted'>Observation</h1>", unsafe_allow_html=True)
          
          # Show Gender Gap
          main_page.markdown("""---""")
          metric_net_gap_1, metric_net_gap_2, metric_net_gap_3 = main_page.columns((1, 1.1, 1.4))            
          metric_net_gap_1.markdown("<h1 style='text-align: left; vertical-align: bottom; font-size: 150%; color: #3498DB; opacity: 0.7'> Gender Gap </h1>", unsafe_allow_html=True)
          metric_net_gap_1.plotly_chart(fig_gender_bar, use_container_width=True)
          
          metric_net_gap_2.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Benchmark</h1>", unsafe_allow_html=True)
          metric_net_gap_2.write("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> 🌐 -3% ~ 1% </h1>" "Pay gap measures for every dollar paid to male employees, how much (less) or more goes to non-male employees. For example pay gap at -10% means that on average women are paid 10% less compared to men all else equal. In US, gender gap typically ranges between -3% and +1%. ", unsafe_allow_html=True)    
          
          num_gender_sig = df_result_gender['STAT_COUNT'].sum()
          # num_gender_sig = 0
          metric_net_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Observation</h1>", unsafe_allow_html=True)
          if num_gender_sig>0:
              metric_net_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Legal Risk - High </h1>", unsafe_allow_html=True)
              metric_net_gap_3.write('There is/are '+ str(num_gender_sig) + ' statistically significant gender pay gap shown with a ' + '<font color=Orange> **Red** </font>' +' bar. A significant gender pay gap means - we are more than 95% certain that the gap exists after incorporates all of the legitimate determinants of pay (such as differences of skill, effort, and responsibility). From a legal perspective:',unsafe_allow_html=True) 
              metric_net_gap_3.write('* Statistically significant gap - Strong evidence of gender pay discrimination' +'\n'+'* Non statistically significant gap - No evidence of gender pay discrimination, the gap is likely due to random chance',unsafe_allow_html=True)
              metric_net_gap_3.write('You may consider reducing the pay gap to a statistically insignificant level to reduce legal risk.')
          else:
              metric_net_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Legal Risk - Low </h1>", unsafe_allow_html=True)
              metric_net_gap_3.write('There is no negative statistically significant gender pay gap shown in the chart. A significant gender pay gap means - we are more than 95% certain that the gap exists after incorporates all of the legitimate determinants of pay (such as differences of skill, effort, and responsibility). From a legal perspective:',unsafe_allow_html=True) 
              metric_net_gap_3.write('* Statistically significant gap - Strong evidence of gender pay discrimination' +'\n'+'* Non statistically significant gap - No evidence of gender pay discrimination, the gap is likely due to random chance',unsafe_allow_html=True)
              metric_net_gap_3.write('As a precaution, you can routinely repeat this analysis to monitor the pay gap. An alternative is to consider completely closing the pay gap to zero.')                
    
          # Show Ethnicity Gap
          main_page.markdown("""---""")
          metric_eth_gap_1, metric_eth_gap_2, metric_eth_gap_3 = main_page.columns((1, 1.1, 1.4))            
          metric_eth_gap_1.markdown("<h1 style='text-align: left; vertical-align: bottom; font-size: 150%; color: #3498DB; opacity: 0.7'> Ethnicity Gap </h1>", unsafe_allow_html=True)
          metric_eth_gap_1.plotly_chart(fig_eth_bar, use_container_width=True)
          
          metric_eth_gap_2.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Benchmark</h1>", unsafe_allow_html=True)
          metric_eth_gap_2.write("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> 🌐 -10% ~ 5% </h1>" "Pay gap measures for every dollar paid to ethnic majority, how much (less) or more goes to ethnicity minority. For example pay gap at -10% means that on average black are paid 10% less compared to white all else equal. In US, ethnicity gap typically ranges between -10% and +5%. ", unsafe_allow_html=True)    
          
          num_eth_sig = df_result_eth['STAT_COUNT'].sum()
          # num_gender_sig = 0
          metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Observation</h1>", unsafe_allow_html=True)
          if num_eth_sig>0:
              metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Legal Risk - High </h1>", unsafe_allow_html=True)
              metric_eth_gap_3.write('There is/are '+ str(num_gender_sig) + ' statistically significant ethnicity pay gap shown with a ' + '<font color=Orange> **Red** </font>' +' bar. A significant ethnicity pay gap means - we are more than 95% certain that the gap exists after incorporates all of the legitimate determinants of pay (such as differences of skill, effort, and responsibility). From a legal perspective:',unsafe_allow_html=True) 
              metric_eth_gap_3.write('* Statistically significant gap - Strong evidence of ethnicity pay discrimination' +'\n'+'* Non statistically significant gap - No evidence of ethnicity pay discrimination, the gap is likely due to random chance',unsafe_allow_html=True)
              metric_eth_gap_3.write('You may consider reducing the pay gap to a statistically insignificant level to reduce legal risk.')
          else:
              metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Legal Risk - Low </h1>", unsafe_allow_html=True)
              metric_eth_gap_3.write('There is no negative statistically significant ethnicity pay gap shown in the chart. A significant ethnicity pay gap means - we are more than 95% certain that the gap exists after incorporates all of the legitimate determinants of pay (such as differences of skill, effort, and responsibility). From a legal perspective:',unsafe_allow_html=True) 
              metric_eth_gap_3.write('* Statistically significant gap - Strong evidence of ethnicity pay discrimination' +'\n'+'* Non statistically significant gap - No evidence of gender pay discrimination, the gap is likely due to random chance',unsafe_allow_html=True)
              metric_eth_gap_3.write('As a precaution, you can routinely repeat this analysis to monitor the pay gap. An alternative is to consider completely closing the pay gap to zero.')        
      
      
  #         main_page.markdown("""---""")
  #         metric_eth_gap_1, metric_eth_gap_2, metric_eth_gap_3 = main_page.columns((1, 1.25, 1.25))            
  #         metric_eth_gap_1.markdown("<h1 style='text-align: left; vertical-align: bottom; font-size: 150%; color: #3498DB; opacity: 0.7'> Ethnicity Gap </h1>", unsafe_allow_html=True)
  #         # metric_net_gap_1.markdown('<font color=Orange> **Red** </font>'+' bar indicates statisical significant finding', unsafe_allow_html=True)
  #         # metric_net_gap_1.markdown('For every $100 made by male', unsafe_allow_html=True)
  #         # metric_net_gap_1.plotly_chart(fig_net_gender_gap, use_container_width=True)
  #         metric_eth_gap_1.plotly_chart(fig_eth_bar, use_container_width=True)
  #         # metric_net_gap_1.plotly_chart(fig_eth_bar, use_container_width=True)
         
  #         # with metric_net_gap_1:
  #         #     gender_gap_options = get_gender_gap_option(female_coff)
  #         #     st_echarts(options=gender_gap_options,height="200px")
          
  #         metric_eth_gap_2.write("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> 🌐 > -10% </h1>" "For example pay gap at -10% means that on average black are paid 10% less compared to white. In US, ethnicity gender gap varies between -10% and +5%.", unsafe_allow_html=True)
  #         # metric_eth_gap_2.write('#')
  #         metric_eth_gap_2.write('<font color=Orange> **Red** </font>'+' bar means the pay gap is statistical significant.  A significant ethnicity pay gap indicates over 95% certain that gap exists after incorporates all of the legitimate determinants of pay (such as differences of skill, effort, and responsibility). From a legal perspective, it is used by courts to justify ethnicity pay discrimination.', unsafe_allow_html=True)
          
  #         # metric_net_gap_2.markdown('<font color=Orange> **Red** </font>'+' bar indicates statisical significant finding', unsafe_allow_html=True)
  
  #         metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Observation</h1>", unsafe_allow_html=True)
  # #         female_pvalue = 0.04
  # #         female_coff = 0.02
          
  #         print(female_coff)
  #         print(female_pvalue)
  #         if female_pvalue>0.05:
  #             metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Legal Risk - Low </h1>", unsafe_allow_html=True)
  #             metric_eth_gap_3.markdown("Congratulation! The gender gap is statistically insignificant, meaning that your legal risk is mimumum and defensible on strong statistical grounds.", unsafe_allow_html=True)
  #             if female_coff<-0.05:
  #                 metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Pay Gap - Below market  </h1>", unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown("You have a larger pay gap than the market. A larger negative gap generally results in a statistically significant status which increases legal risk. As a precaution, you can routinely repeat this analysis to monitor the pay gap. An alternative is to consider closing the pay gap", unsafe_allow_html=True)    
  #             elif female_coff>=-0.05 and female_coff<0:
  #                 metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Pay Gap - Align with market  </h1>", unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown('You are in alignment with the market! To be the market leader, you can consider narrowing the gap to 0%.', unsafe_allow_html=True)
  #             else:
  #                 metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Pay Gap - Market leader!  </h1>", unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown("Congratulation! Your female employees earn in average more than men. Only 1% of companies have reached your great standing!", unsafe_allow_html=True)
  #         else:
  #             if female_coff>0:
  #                 metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Green; font-size: 110%; opacity: 0.7'> ✔️ Legal Risk - Low </h1>", unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown("Congratulation! Your female employees earn in average more than men. Only 1% of companies have reached your great standing!", unsafe_allow_html=True)
  #             else:
  #                 metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Legal Risk - High </h1>", unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown('The gender gap is **statistically significant**, which can make you more prone to gender-related litigation.', unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown("<h1 style='text-align: left; vertical-align: bottom;color: Orange; font-size: 110%; opacity: 0.7'> ⚠️ Pay Gap - Below market  </h1>", unsafe_allow_html=True)
  #                 metric_eth_gap_3.markdown("You may consider reducing the pay gap to a statistically insignificant level to reduce legal risk", unsafe_allow_html=True)
  
          # Remediation Scenarios
          main_page.markdown("""---""")
          # reme_col1, reme_col2 = main_page.columns((1, 1))
          # result_pvalue = [female_pvalue,seek_resulting_pvalues_pv,seek_resulting_pvalues_gap]
  
          df_reme = pd.DataFrame({'Scenario': scenario, 'How do I do this?': action, 'What is my budget?': budget, 'What is the gap after adjustment?': net_gap})
  
          cell_hover = {  # for row hover use <tr> instead of <td>
                          'selector': 'td:hover',
                          'props': [('background-color', 'lightgrey')]
                      }
          index_names = {
                          'selector': '.index_name',
                          'props': 'font-style: italic; color: darkgrey; font-weight:normal;'
                      }
          headers = {
                          'selector': 'th:not(.index_name)',
                          'props': 'background-color: #3498DB; color: white; text-align: center; '
                      }
          styler = df_reme.style.hide_index().set_table_styles([cell_hover, index_names, headers], overwrite=False).set_properties(**{
      'white-space': 'pre-wrap'})
  
          main_page.markdown("<h1 style='text-align: left; vertical-align: bottom;color: #3498DB; font-size: 150%; opacity: 0.7'>Remediation Scenarios</h1>", unsafe_allow_html=True)
          
          # Download individual employee recommendation
          if reme_download_flag == 1:
              output_reme = BytesIO()
              writer_reme = pd.ExcelWriter(output_reme, engine='xlsxwriter')
              df_reme_ind.to_excel(writer_reme, index=False, sheet_name='Sheet1')
              workbook_reme = writer_reme.book
              worksheet_reme = writer_reme.sheets['Sheet1']
  
              for column in df_reme_ind:
                  column_width = max(df_reme_ind[column].astype(str).map(len).max(), len(column))+3
                  col_idx = df_reme_ind.columns.get_loc(column)
                  writer_reme.sheets['Sheet1'].set_column(col_idx, col_idx, column_width)
              cell_format = workbook_reme.add_format()
  
              cell_format.set_pattern(1)  # This is optional when using a solid fill.
              cell_format.set_bg_color('yellow')
              worksheet_reme.write('A1', 'EEID',cell_format)
  
              writer_reme.save()
              processed_reme = output_reme.getvalue()
              
              # main_page.download_button(label='💰 Download Salary Adjustment',data = processed_reme,file_name= 'Salary Adjustment.xlsx')
              reme_col1, reme_col2 = main_page.columns((1, 4))
              reme_col1.markdown("🖱️ 'Save link as...'")
              reme_col2.markdown(get_excel_file_downloader_html(processed_reme, 'Salary Adjustment.xlsx'), unsafe_allow_html=True)
          
          main_page.write(styler.to_html(), unsafe_allow_html=True)
          st.stop()
  