<jupyter_start><jupyter_text>Extra (Optional Work)As you examine the data, you are overcome with a creeping suspicion that the dataset is fake. You surmise that your boss handed you spurious data in order to test the data engineering skills of a new employee. To confirm your hunch, you decide to take the following steps to generate a visualization of the data, with which you will confront your boss:1. Import the SQL database into Pandas. (Yes, you could read the CSVs directly in Pandas, but you are, after all, trying to prove your technical mettle.) This step may require some research. Feel free to use the code below to get started. Be sure to make any necessary modifications for your username, password, host, port, and database name:   ```sql   from sqlalchemy import create_engine   engine = create_engine('postgresql://localhost:5432/')   connection = engine.connect()   ```* Consult [SQLAlchemy documentation](https://docs.sqlalchemy.org/en/latest/core/engines.htmlpostgresql) for more information.* If using a password, do not upload your password to your GitHub repository. See [https://www.youtube.com/watch?v=2uaTPmNvH0I](https://www.youtube.com/watch?v=2uaTPmNvH0I) and [https://help.github.com/en/github/using-git/ignoring-files](https://help.github.com/en/github/using-git/ignoring-files) for more information.  Import dependencies<jupyter_code>import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import numpy as np
from config import db_password
db_user = 'postgres'
db_host = 'localhost'
db_port = 5432

db_name = "SQL_Homework(9)"

engine = create_engine(f"postgres://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}")
connection = engine.connect()
data_employees = pd.read_sql("SELECT * FROM employees", engine)
data_employees.head()
data_salaries = pd.read_sql("SELECT * FROM salaries", engine)
data_salaries.head()
data_title = pd.read_sql("SELECT * FROM titles", engine)
data_title.head()
combined_data = pd.merge(data_employees, data_title, on="title_id", how="outer")

combined_data2 = pd.merge(combined_data, data_salaries, on="emp_no", how="outer")
combined_data2.head()
combined_data2.dtypes
#2. Create a histogram to visualize the most common salary ranges for employees.
plt.hist(combined_data2["salary"], label="Salary($/Yr)")
plt.xlabel("Salary($/Yr)")
plt.ylabel("Number of Employees")
plt.legend()
plt.title("Salary Ranges for Employees")

plt.savefig("#2 - Salary Ranges")
plt.show()
#3. Create a bar chart of average salary by title.
# convert the salary datatype from object to float
import locale
import pandas as pd
locale.setlocale(locale.LC_ALL,'')
combined_data2['salary']=combined_data2.salary.map(lambda x: locale.atof(x.strip('$')))
combined_data2.dtypes
#calculate salary mean, group by the job title
salary_group = combined_data2.groupby("title")["salary"].mean()
salary_group.head()
#create the bar chart 
salary_group.plot(kind="bar", label="Avg Salary ($/Yr)")
plt.xlabel("Job Title")
plt.ylabel("Average Salary ($/Year)")
plt.title("Average Salary for Jobs")
plt.legend()

plt.savefig("#3 - Average Salary")
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11697393515575334, 'fraction_numerical': 0.008264462809917356, 'mean_word_length': 5.977827050997782, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '332810', 'license': 'ADSL', 'n_tokens_mistral': 1020, 'n_tokens_neox': 977, 'n_words': 311, 'path': 'optional.ipynb', 'repo_name': 'jinahporter/sql-challenge', 'size': '3146'}