In [1]:
%matplotlib inline
In [116]:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import ipywidgets as widgets
from datetime import date
from sqlalchemy import create_engine
In [3]:
import warnings
warnings.filterwarnings('ignore')
In [4]:
plt.style.use('ggplot')
plt.rcParams['font.family'] = 'DejaVu Sans'
plt.rcParams['figure.figsize'] = [8, 6]
In [59]:
# Get the data for analysis
engine = create_engine("postgresql://Joachim:@localhost/tm470")
conn = engine.connect()
model_results_df = pd.read_sql_table('model_results_v', conn, index_col='id')
pay_options_df = pd.read_sql_table('pay_options_v', conn, index_col='id')
options_data_df = pd.read_sql_table('pay_options_data', conn, index_col='id')
pay_ranges_df = pd.read_sql_table('pay_ranges_v', conn, index_col='payband')
conn.close()
In [44]:
## Create separate DataFrames
consolidated_df = model_results_df[model_results_df['pay_option_type']=='Consolidated']
consolidated_df = consolidated_df.drop(['bonus_costs','bonus_percent'], axis=1)
bonus_df = model_results_df[model_results_df['pay_option_type']=='Non-consolidated']
bonus_df = bonus_df.drop(['consolidated_costs', 'consolidated_percent', 'uplift_costs', 'uplift_percent'], axis=1)
In [45]:
consolidated_df
Out[45]:
In [46]:
options_data_df.pivot(index='payband_id', columns='key', values='value')
Out[46]:
In [60]:
pay_ranges_df
Out[60]:
In [136]:
print('£{:,}'.format(int(consolidated_df['uplift_costs'].values[0])))
In [138]:
print('{:,}% of the pay bill'.format(consolidated_df['uplift_percent'].values[0]))
In [145]:
cons_option = pay_options_df[pay_options_df['type']=='Consolidated']
cons_option
Out[145]:
In [154]:
options_data_df[options_data_df['pay_option_id']==cons_option.index[0]].pivot(
index='payband_id', columns='key', values='value')
Out[154]:
In [ ]: