<jupyter_start><jupyter_text>Importing data from SQL<jupyter_code>!pip install psycopg2
# dependencies and set up 
from sqlalchemy import create_engine
import pandas as pd
from matplotlib import pyplot as plt
from config import username, password

from sqlalchemy import create_engine
engine = create_engine(f'postgresql://{username}:{password}@localhost:5432/employees')
connection = engine.connect()

# read table
df = pd.read_sql("SELECT * FROM salaries", connection)

df.head()<jupyter_output><empty_output><jupyter_text>BONUS PART 1:<jupyter_code># histogram of most common salary ranges for employees
df["salary"].hist(color="blue")
plt.title("Salary Ranges for Employees")
plt.xlabel("Annual Salary")
plt.ylabel("Number of Employees")
plt.savefig("./histogram_salaries.png")
plt.show()<jupyter_output><empty_output><jupyter_text>BONUS PART 2:  Creating a view in postgres SQL CREATE VIEW avg_salaries_by_title ASSELECT round(avg(s.salary)) as avg_salary, t.titleFROM titles tJOIN employees eON t.title_id = e.emp_title_idJOIN salaries sON e.emp_no = s.emp_noGROUP BY t.title;<jupyter_code># converting view to dataframe
salary_df = pd.read_sql("SELECT * FROM avg_salaries_by_title ORDER BY avg_salary DESC", connection)

# creating variables for plotting
titles = salary_df["title"]
avg_salary = salary_df["avg_salary"]

# creating bar plot
plt.bar(titles, avg_salary, color="green")
plt.title("Average Salary by Job Title")
plt.xlabel("Titles")
plt.ylabel("Average Salary")
plt.xticks(rotation=60)
plt.savefig("./avg_salary_titles.png")
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10977917981072555, 'fraction_numerical': 0.0056782334384858045, 'mean_word_length': 7.481283422459893, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, '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': '97863', 'license': 'MIT', 'n_tokens_mistral': 581, 'n_tokens_neox': 544, 'n_words': 133, 'path': 'EmployeeSQL/.ipynb_checkpoints/employee_db-checkpoint.ipynb', 'repo_name': 'tsubedy/sql-challenge', 'size': '1585'}