<jupyter_start><jupyter_text>Bonus Analysis<jupyter_code># import dependancies
import os 
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from config import dbname, username, password, server
import matplotlib.pyplot as plt
# connect to database
engine = create_engine(f'postgresql://{username}:{password}@{server}/{dbname}')
conn= engine.connect()
# review tables in database
# https://stackoverflow.com/questions/21310549/list-database-tables-with-sqlalchemy/21346185
m = MetaData()
m.reflect(engine)
for table in m.tables.values():
    print(table.name)
# Create a histogram to visualize the most common salary ranges for employees.
salaries = pd.read_sql('SELECT * from salaries', conn)
salaries
print(salaries['salary'].min())
print(salaries['salary'].max())
print(len(salaries))
# https://datatofish.com/plot-histogram-python/
# https://realpython.com/python-histograms/
x = salaries['salary']
plt.hist(x, bins = 9, color ='green', alpha =.5)
plt.xlabel('Salaries for Pewlett Hackard')<jupyter_output><empty_output><jupyter_text>The histogram shows over half of the 300k records falling into the 40-50K range<jupyter_code># Create a bar chart of average salary by title.
# first get unique titles from titles table
unique_titles = pd.read_sql("SELECT distinct title from titles", conn)
unique_titles 
# test connection
salaries = pd.read_sql("SELECT * FROM salaries", conn)
salaries.head()
# look at columns for title table, salaries above
title = pd.read_sql_table('titles', conn)
title.head(2)

# look at columns for employees, need to join off title_id
emp = pd.read_sql_table('employees', conn)
print(len(emp))
emp.head(2)

# create query to join employee, salaries and titles to then get average
# https://stackoverflow.com/questions/51739196/using-jupyter-notebook-to-convert-sql-into-panda-data-frame

query = """
        SELECT e.emp_no, e.emp_title_id, t.title, s.salary
        FROM employees AS e
        LEFT JOIN titles AS t
            ON e.emp_title_id = t.title_id
        LEFT JOIN salaries AS s
            ON e.emp_no = s.emp_no
        
        """
combined_salary = pd.read_sql(query,conn)
len(combined_salary)
combined_salary.head()
# get averages by title
average_sal_by_title = combined_salary.groupby('title').mean()['salary']
average_sal_by_title
# place into bar chart
salary_bar = average_sal_by_title
salary_bar_plot = salary_bar.plot.bar(title = "Average Salary by Title")
salary_bar_plot.set_ylabel('Salary')
salary_bar_plot.set_xlabel('Title')<jupyter_output><empty_output><jupyter_text>According to the above barchart there is very little difference in average salary amongst all positions. It appears an assistant engineer averages the same as a senior engineer which would mean no salary increase for a promotion.<jupyter_code># search employee name for 499942
emp_49942 = emp.loc[emp['emp_no']== 499942]
emp_49942<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10220713073005093, 'fraction_numerical': 0.01935483870967742, 'mean_word_length': 5.254777070063694, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '603498', 'license': 'ADSL', 'n_tokens_mistral': 999, 'n_tokens_neox': 931, 'n_words': 270, 'path': 'SQL_Challenge_Bonus_Analysis.ipynb', 'repo_name': 'JeniLam/SQL_Challenge', 'size': '2945'}