<jupyter_start><jupyter_text>ExercisesCreate a notebook or python script named `advanced_dataframes` to do your work in for this exercise.For the following exercises, you'll need to load several datasets using the `pydataset` library. (If you get an error when trying to run the import below, use `pip` to install the `pydataset` package.)<jupyter_code>from pydataset import data
import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>When the instructions say to load a dataset, you can pass the name of the dataset as a string to the `data` function to load the dataset. You can also view the documentation for the data set by passing the `show_doc` keyword argument.<jupyter_code>mpg = data('mpg') # load the dataset and store it in a variable
# data('mpg', show_doc=True) # view the documentation for the dataset<jupyter_output><empty_output><jupyter_text>1.Load the `mpg` dataset. Read the documentation for it, and use the data to answer these questions:<jupyter_code>mpg.head()
#1.A: On average, which manufacturer has the best miles per gallon?
mpg['avg_mileage'] = (mpg.cty + mpg.hwy) / 2

mpg.head()

mpg.groupby('manufacturer').avg_mileage.mean()
#mpg.assign(avg_mileage = df.groupby('cty'))

mpg.groupby('manufacturer').avg_mileage.mean().max()
mpg.groupby('manufacturer').avg_mileage.mean().nlargest(1, keep='all')
#1.B: How many different manufacturers are there?

mpg.groupby('manufacturer').avg_mileage.max().count()
#1.C: How many different models are there?
mpg.groupby('model').avg_mileage.max().count()
mpg.model.nunique()
#1.D: Do automatic or manual cars have better miles per gallon?: FALSE

mpg['transtype'] = mpg.trans.str.startswith('auto')

#mpg.groupby('trans').avg_mileage.max()

mpg.head()
mpg.groupby('transtype').avg_mileage.max()
mpg['transcategory'] = np.where(mpg.trans.str.startswith('auto'), 'auto', 'manual')
mpg.head(2)
mpg.groupby('transcategory')[['cty', 'hwy']].mean()<jupyter_output><empty_output><jupyter_text>2. Joining and MergingCopy the `users` and `roles` dataframes from the examples above. - What do you think a `right` join would look like? - An `outer` join? - What happens if you drop the foreign keys from the dataframes and try to merge them?<jupyter_code>users = pd.DataFrame({
    'id': [1, 2, 3, 4, 5, 6],
    'name': ['bob', 'joe', 'sally', 'adam', 'jane', 'mike'],
    'role_id': [1, 2, 3, 3, np.nan, np.nan]
})
users
roles = pd.DataFrame({
    'id': [1, 2, 3, 4],
    'name': ['admin', 'author', 'reviewer', 'commenter']
})
roles
# RIGHT join: only goes up to ID 4, less rows
right_join = pd.merge(users, 
                      roles, 
                      left_on='role_id', 
                      right_on='id', 
                      how='right',
                      indicator=True)
right_join
# OUTER join: fills in all of the uncommon cells of data
pd.merge(users, 
         roles, 
         left_on='role_id', 
         right_on='id', 
         how='outer', 
         indicator=True)
# What happens if you drop the foreign keys from the dataframes and try to merge them?
usersd = users.copy()
rolesd = roles.copy()
usersd.drop(columns=['role_id'])
#rolesd.drop(columns=['name'])
pd.merge(usersd.drop(columns=['role_id']), roles, on='id')
#pd.merge(users, roles, how='outer')<jupyter_output><empty_output><jupyter_text>3. Getting data from SQL databases  3.A: Create a function named `get_db_url`. It should accept a username, hostname, password, and database name and return a url formatted like in the examples in this lesson.<jupyter_code>user = input("Type your username: ")
host = input("Type your hostname: ")
pswd = input("Type your password: ")
dbname = input("Type your database name: ")

def get_db_url(user, host, pswd, dbname):
    return (f"url = f'mysql+pymysql://{user}:{pswd}@{host}/{dbname}'")

get_db_url(user, host, pswd, dbname)<jupyter_output>Type your username: agomez
Type your hostname: codeup
Type your password: <PASSWORD>
Type your database name: fruits<jupyter_text>3.B:Use your function to obtain a connection to the `employees` database.<jupyter_code>from env import host, user, password

url = f'mysql+pymysql://{user}:{password}@{host}/employees'<jupyter_output><empty_output><jupyter_text>3.C: Once you have successfully run a query:- Intentionally make a typo in the database url. What kind of error message do you see?- Intentionally make an error in your SQL query. What does the error message look like?<jupyter_code>sql = """
select*
from employees_with_departments
"""

pd.read_sql(sql, url)
#Intentionally make a typo in the database url. What kind of error message do you see?
url = f'mysql+pymysql://{userd}:{password}@{host}/employees'


sql = """
select*
from employees_with_departments
"""

pd.read_sql(sql, url)
#Intentionally make an error in your SQL query. What does the error message look like?
url = f'mysql+pymysql://{user}:{password}@{host}/employees'


sql = """
select*
from employee_with_departments
"""

pd.read_sql(sql, url)<jupyter_output><empty_output><jupyter_text>3.D: Read the `employees` and `titles` tables into two separate dataframes<jupyter_code>sql = """
select*
from employees
"""

employees = pd.read_sql(sql, url)
employees.head()
sql = """
select*
from titles
"""

titles = pd.read_sql(sql, url)
titles.head()<jupyter_output><empty_output><jupyter_text>3.E:Visualize the number of employees with each title.<jupyter_code>employees.emp_no.count()
titles.emp_no.count()
current_bool = titles.to_date == titles.to_date.max()

current_bool.head()
current_employees_titles = titles[current_bool]
current_employees_titles.head()
current_employees_titles.groupby('title').emp_no.count()
current_title_values = current_employees_titles.title.value_counts()
current_title_values
current_title_values.plot.barh()

plt.xlabel('Number of Employees')
plt.gca().invert_yaxis()<jupyter_output><empty_output><jupyter_text>3.F: Join the `employees` and `titles` dataframes together.<jupyter_code>emp_title = pd.merge(employees, titles, on='emp_no')
emp_title.head()
#rows: employee's demographics over a prd of time<jupyter_output><empty_output><jupyter_text>3.G: Visualize how frequently employees change titles.<jupyter_code>emp_title.groupby('emp_no').title.count()
emp_title.groupby('emp_no').title.count().value_counts()
#value_counts = freq
emp_title.emp_no.value_counts().value_counts()
import matplotlib.pyplot as plt

emp_title.emp_no.value_counts().value_counts().plot.bar()

plt.xticks(rotation=360, ticks = [0, 1, 2], labels = ['No Title Change', '1 Title Change', '2 Title Changes'])


plt.title('Frequency of Title Changes within the Company')
plt.xlabel('Number of Job Titles Held')
plt.ylabel('Number of Employees')<jupyter_output><empty_output><jupyter_text>3.H:For each title, find the hire date of the employee that was hired most recently with that title.<jupyter_code>titles.groupby('title').from_date.max()
#pd.crosstab(dept_emp.dept_name, dept_emp.from_date.max())<jupyter_output><empty_output><jupyter_text>3.I: Write the code necessary to create a cross tabulation of the number of titles by department. (Hint: this will involve a combination of SQL and python/pandas code)<jupyter_code>sql = """
select *
from employees_with_departments
"""

eod = pd.read_sql(sql, url)
eod.head()
pd.crosstab(titles.title, eod.dept_name)<jupyter_output><empty_output><jupyter_text>4. Use your `get_db_url` function to help you explore the data from the chipotle database. Use the data to answer the following questions:<jupyter_code>from env import host, user, password

url = f'mysql+pymysql://{user}:{password}@{host}/chipotle'
sql = """
select*
from orders
"""

orders = pd.read_sql(sql, url)<jupyter_output><empty_output><jupyter_text>4.A:What is the total price for each order?<jupyter_code>orders.head()
orders.item_price.str.replace("$", "")
orders['itemprices'] = orders.item_price.str.replace("$", "").astype(float)
orders.head()
orders.groupby('order_id').itemprices.sum()
pd.DataFrame(orders.groupby('order_id').itemprices.sum())<jupyter_output><empty_output><jupyter_text>4.B:What are the most popular 3 items?<jupyter_code>orders.groupby('item_name').quantity.sum().nlargest(3, keep='all')
orders.groupby('item_name').quantity.sum().nlargest(3, keep='all').plot.barh()<jupyter_output><empty_output><jupyter_text>4.C:Which item has produced the most revenue?<jupyter_code>orders.head()
orders.groupby('item_name').itemprices.sum()
orders.groupby('item_name').itemprices.sum().nlargest(1, keep='all')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13558325493885232, 'fraction_numerical': 0.005879586077140169, 'mean_word_length': 5.744647105471848, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '570744', 'license': 'MIT', 'n_tokens_mistral': 2980, 'n_tokens_neox': 2892, 'n_words': 758, 'path': 'advanced_dataframes.ipynb', 'repo_name': 'o0amandagomez0o/numpy-pandas-visualization-exercises', 'size': '8502'}