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

# IMPORTING ALL LIBRARIES
import os
from dotenv import load_dotenv
load_dotenv()
from sqlalchemy.sql import func
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
from sqlalchemy import Column, Integer, String, Float
import numpy as np
import pandas as pd

# CREATING THE ENGINE, SESSION AND EMBEDING THE PASSWORD
passwd = <PASSWORD>("pass")
engine = create_engine(f"postgresql://postgres:{passwd}@localhost/SQL-Challenge")
conn = engine.connect()
Base = automap_base()
Base.prepare(engine, reflect=True)
session = Session(engine)
Base.classes.keys()

# +
# CREATE THE SALARY MIRRORED TABLE
Salary = Base.classes.Salaries

# THIS IS TO MAKE SURE I KNOW THE COLUMN NAME I WILL BE WORKING WITH
first_row = session.query(Salary).first()
first_row.__dict__

# GET ALL THE DATA FROM THE SALARY TABLE
Salary_all = session.query(Salary.salary)
print(Salary_all) 

# CREATING THE EMPTY LIST WHERE ALL THE SALARIES WILL BE APPENDED
sal_list =[]

# ITERATE THROUGH THE THE SALARY SESSION AND APPEND TO LIST
for sal in Salary_all:
   sal_list.append(sal.salary)

# CREATE A PANDAS SERIES WTIH THE LIST CREATED IN THE PREVIOUS STEP
# REPLACED THE "$ ," SYMBOLS USING THE "STR.REPLACE" FUNCTIONS
saldf = pd.Series(sal_list)
saldf = saldf.str.replace('$', '').str.replace(',', '')
saldf =saldf.astype(float)

#len(saldf.index)

# CREATE A NUMPY RANGE FOR THE BINS THAT WILL BE USED IN THE HISTOGRAM PLOT
bins = np.arange(40000,130000, 15000)

# CREATING THE HISTOGRAM USING THE SALARY DATFRAME AND THE BINS CREATED IN THE PREVIOUS STEP
plt.style.use('ggplot')
plt.figure(figsize=(8,6),facecolor='lightgray',edgecolor='red')
plt.hist(saldf, bins=bins, color='Lightblue', alpha=0.8, label='Salaries', edgecolor='gray', linewidth=2)
plt.legend()
plt.title('Common Salary Ranges', size= 20, color='Gray')
plt.xlabel('Salary Ranges', color='black')
plt.ylabel('Count Of Salary', color='black')
plt.grid(color='w', linestyle='solid')
plt.show();



# +
#THIS IS TO CREATE THE MIRROR TABLES THAT WILL BE JOINED
Employee = Base.classes.Employee
Titles = Base.classes.Titles
Salaries = Base.classes.Salaries

#JOIN THE MIRRORED TABLES TO GENERATE THE DATAFRAME THAT WILL USED TO GET THE AVERAGE SALARY BY TITLE
join = session.query( Employee ,Titles, Salaries ).filter(Employee.emp_title_id == Titles.title_id).filter(Employee.emp_no == Salaries.emp_no).statement

# START THE SESSION FOR THE MIRRORED TABLES
joindf = pd.read_sql_query(join, session.bind)

# CAST THE SALARY AS FLOAT TO AVG THE THE SALARY AND ALSO REMOVED THE "$ ," SYMBOLS
joindf['salary_n'] = joindf.salary.replace('[\$,]', '', regex=True).astype(float)

# GET THE MEAN OF THE SALARY BY TITLE
df = joindf[['salary_n', 'title']].groupby('title').mean().round(2)

#df.head(10)
# -

#plt.figure(figsize=(10,6),facecolor='lightgray',edgecolor='blue')
df.plot(kind='barh' , title= 'Sal by Ti', legend=False, fontsize=12)
plt.style.use('ggplot')
plt.title('Salary by Title', size= 20, color='Gray')
plt.ylabel('Titles', color='black', fontsize=14)
plt.xlabel('Salaries', color='black', fontsize=14)
plt.show()


session.close()

StarcoderdataJupyterScriptsDedupFiltered