9|PYTHON – Fred’s Fairly Reliable

Challenges
, , ,
1

BYU Student Author: @Saul_Esplin, @Brett_Lowe
Reviewers: @Kylie_Larsen17, @Michael_Barney, @Mike_Paulsin, @Christian
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
It’s January of 2013 and your firm, Brad’s Barely Legitimate consulting services, has been hired by Fred’s Fairly Reliable car dealership. Fred’s son, Tom, took control of the family business in 2012 and led the dealership to one of the worst years it has seen in decades. Fred attributes the steep downturn to Tom’s “million-dollar idea”—parking Toyota Priuses, exclusively, on the lot. Tom disagrees and chalks it up to bad luck. Per Fred’s desperate request, it’s your job to settle this dispute and identify a better selection of cars to show at the dealership. You’ll be tasked with recommending the top 5 cars based on the following metrics:

  1. Most popular
  2. Dealership margin
  3. Fuel efficiency

Prior to starting your analysis, Fred mentions that he’s familiar in his understanding of DataFrames in Python but needs it to be organized to understand it. Don’t let Fred’s Fairly Reliable become Fred’s Fairly Out of Business!

Instructions

  1. Download the Excel data.

  2. Import the necessary libraries (Pandas, NumPy) and load the Excel data in Python into a Pandas DataFrame.

  3. The “Sales_in_thousands” column provides information about how many cars were sold during the last four years. You decide that this is a fair indication of popularity. Create a new column in the DataFrame to flag the top 10 best-selling cars based on the “Sales_in_thousands” column by labeling each row either “True” (in the top 10 cars) or “False” (not in the top 10 cars).

  4. To ensure that they’re excluded from your report, you also decide to identify the least popular cars. Create another new column in the DataFrame to flag the bottom 5 worst-selling cars based on the “Sales_in_thousands” column.

  5. To maximize dealership margins, you decide to draw attention to some of the most lucrative cars. Using NumPy, create a new column that marks cars with margins greater than or equal to 35% as “High”, marks cars that are less than 35% but greater than or equal to 20% as “Medium”, and marks cars that are less than 20% as “Low”.

  6. Fred is interested in qualifying for a tax credit offered by his state for selling fuel-efficient cars. Create a column that identifies any car that may qualify for a tax credit. Any car above 27mpg may qualify.

  7. Create a new DataFrame that identifies the “Top Tier” cars that fall within the top tier of each parameter.

  8. Post your finished DataFrame to Fred and Tom, and make a recommendation based on your analysis regarding which five cars they should begin selling on the lot.

Data Files

[details=“Suggestions and Hints”]

Solution

Challenge9_PYTHON_Solution.txt .
Solution Video: Challenge 9|PYTHON – Fred’s Fairly Reliable