71|PYTHON – Derek’s Debacle

BYU Student Author : @Brett_Lowe, @Sterling_Lane
Reviewers: @MitchFrei, @Spencer, @Mike_Paulsin, @Trent_Barlow
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
Your 16-year-old son, Derek, just burst into your office and let you know that his driver’s license came in the mail today. He’s been saving up for a car since age 9, and now that he can legally drive, he’s ready to make the big purchase. Knowing that you are financially savvy and will take his wellbeing into consideration, Derek begs you to help him find the right car.

He tells you that he can afford a $21,000 car, but he hasn’t taken any extra fees into consideration. You’ll need to be mindful of taxes, registration costs, insurance premiums, etc. when forming your opinion. You also know from experience that Derek is a terrible driver, so you plan to prioritize safety ratings. Lastly, Derek is notorious for changing his mind, so it’s possible that he may want a different car a year from now. You’ll want to confirm that the resale value after one year is at least $12,000.

Instructions

  1. Download each of the relevant files.
  2. Join each of the files together into a single data frame for analysis
  3. Create a new column called “Total_cost” that represents the sum of the cost of the car and any additional fees. Filter out any cars with a total cost greater than $21,000.
  4. Filter out any cars with a safety rating less than or equal to 4.5.
  5. Filter out any cars with a resale value less than $12,000.
  6. Using the Display function, show Derek the car of his dreams.

Data Files

  1. File 83a contains the ID, make, and model of each car.
  2. File 83b contains the ID, pricing, and resale value after one year for each car.
  3. File 83c contains the ID and safety rating for each car.
Suggestions and Hints

a. Make sure you have the CSV files saved in the same folder as your Python/Jupyter file

Solution

Time to complete: 35 minutes
Rating: Beginner
This challenge was a good practice for some basic table manipulation. I would offer a word of caution to read the prompt carefully!

I did this in Google Collab (very easy, no download required ;), so my comments and code reflect that.

Hint 1: Pay attention to: “Filter out

Code Solution (one possible way to solve):

#import google drive to your Google Colab 
from google.colab import drive
drive.mount('/content/drive')

#import pandas and display
import pandas as pd
from IPython.display import display

#bring in your csv files, which should be imported inside of your drive
df1 = pd.read_csv('/content/drive/MyDrive/Challenge71_Data_1.csv')
df2 = pd.read_csv('/content/drive/MyDrive/Challenge71_Data_2.csv')
df3 = pd.read_csv('/content/drive/MyDrive/Challenge71_Data_3.csv')
#df3.head() #view the table

# merge tables together
df4 = pd.merge(pd.merge(df1, df2, on='Car_ID'), df3, on='Car_ID') #merging on "Car_ID"
# df4.head() #view the table

# create new column of total cost
df4['Total_cost'] = df4['Price_of_car'] + df4['Additional_costs']
# df4.head() #view the table

# filter out total cost > $21000
filtered_df = df4[df4['Total_cost'] < 21000]
# filtered_df #view the table

# Filter out any cars with a safety rating less than or equal to 4.5.
filtered_df = filtered_df[(filtered_df['Safety_rating']> 4.5)]
# filtered_df #view the table

# Filter out any cars with a resale value less than $12,000.
filtered_df = filtered_df[filtered_df['__year_resale_value']> 12000]
# filtered_df #view the table

# Using the Display function, show Derek the car of his dreams.
display(filtered_df) #view the table

Time to complete: 30 minutes
Rating: Beginner

I did this is Visual Studio Code

Code Solution (one possible way to solve):

import necessary libraries
import pandas as pd

#Read CSV files
df_a = pd.read_csv(“Data1.csv”)
df_b = pd.read_csv(“Data2.csv”)
df_c = pd.read_csv(“Data3.csv”)

#Merge data frames on Car ID
new_df = pd.merge(df_a, df_b, on=“Car_ID”)
new_df = pd.merge(new_df, df_c, on=“Car_ID”)

#Create new column titled Total Cost
new_df[“Total_cost”] = new_df[“Price_of_car”] + new_df[“Additional_costs”]

filter out any cars with a total cost greater than $21,000
new_df = new_df[new_df[“Total_cost”] <= 21000]

filter out any cars with a safety rating less than or equal to 4.5
new_df = new_df[new_df[“Safety_rating”] > 4.5]

filter out any cars with a resale value less than $12,000
new_df = new_df[new_df[“__year_resale_value”] >= 12000]

#Show Derek the car of his dreams.
print(new_df.head())