27|PYTHON – Sales Superstars

Challenges
, , ,
1

BYU Student Author: @Spencer
Reviewers: @Parker_Sherwood, @Boston, @Erick_Sizilio
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
Each week, your assignment as an intern is to identify salespeople who exceed the company’s
standard for excellence ($85,000 in sales or more). After spending a few weeks analyzing data and generating reports manually, you’ve decided to try your hand at automating the process. Luckily, your company provides you with access to Python! If successful, you will save both yourself and your fellow interns hours of monotonous work.

Instructions
You must write code that does the following:

  1. Displays an input that requests the file path location of the “Sales_Superstars” Excel sheet.
  2. Reads the “Sales_Superstars” Excel sheet using the file path location that was requested in step 1.
  3. Iterates through every row of the “Employee_Sales” data table that was read in step 2. For every row of data that contains a salesperson who sold over $85,000 worth of goods, add the name of that salesperson to a list.
  4. After iterating through each row of data, display a message box that contains the final list of salespeople who surpassed $85,000 in sales.

Data Files

Suggestions and Hints

I recommend importing both tkinter and tkinter.messagebox to display a message box.

Solution

The following is a list of employees with sales over $85,000:
Clyde Meyer, Anthony Byrd. Melinda Foster, Nina Dolan, Cecil Santos, Omar Thornton, Flora Harris

Solution Code 
#import necessary packages
import pandas as pd
import tkinter
import tkinter.messagebox

# create empty list
sales_superstars = []

# request file path location
directory = input('Please input the file path location of the “Sales_Superstars” Excel sheet.\n ')
if directory[-1] != '\\' : 
    directory = directory + '\\'

# read in “Sales_Superstars” Excel sheet
df = pd.read_excel(directory + 'Challenge27_Sales_Superstars.xlsx')

# iterate through Excel sheet and add salespeople with over $85,000 in sales to list
for index,row in df.iterrows():
    name = row['Name']
    sales = row['Sales']
    if sales > 85000:
        sales_superstars.append(name)
        
# convert list to string for better visual appearance (not necessary)
sales_superstars = ', '.join(sales_superstars)

# display message box with the names of the "Sales Superstars"
tkinter.messagebox.showinfo("Sales Superstars", sales_superstars)

Challenge27_Solution.txt
Solution Video: Challenge 27|PYTHON – Sales Superstars

2

This was a good Python challenge that gave me exposure to tkinter, I hadn’t really used that before! My code had a little less error handling than yours (whoops), so I can obviously improve on that. But overall, I think I did this pretty well. Here’s my code:

import pandas as pd
import tkinter as tk
from tkinter import messagebox

path = input("What is the file path of the Sales_Superstars Excel file?  ")

df = pd.read_excel(path)

sales_stars = []

for i, row in df.iterrows():
    sales = row['Sales']
    if sales >= 85000:
        sales_stars.append(row['Name'])
        print(sales_stars)

# create a message box
messagebox.showinfo("Sales Superstars", sales_stars)
4

This is what I got:
import pandas as pd
from tkinter import Tk, filedialog, messagebox

#Step 1: Request the file path location of the Excel sheet
Tk().withdraw() # Hide the main window
file_path = filedialog.askopenfilename(title=“Select the Sales_Superstars Excel file”, filetypes=[(“Excel Files”, “*.xlsx”)])

#Step 2: Read the “Employee_Sales” Excel sheet
excel_data = pd.read_excel(file_path, sheet_name=‘Employee_Sales’, engine=‘openpyxl’)

#Step 3: Identify sales superstars
superstars = excel_data.loc[excel_data[‘Sales’] > 85000, ‘Name’].tolist()

#Step 4: Display a message box with the final list of sales superstars
if superstars:
message = “Sales Superstars (>$85,000 in sales):\n” + “\n”.join(superstars)
messagebox.showinfo(“Sales Superstars”, message)

1 Like
5

Here’s my solution. It works best in Jupyter:

import pandas as pd

file_path = r"C:\Users\Owner\Desktop\College Random\Challenge27_Sales_Superstars.xlsx"

try:
# Read the Excel file into a DataFrame
df = pd.read_excel(file_path)

# Create an empty list to store results
high_sales_list = []

# Iterate over the DataFrame rows
for index, row in df.iterrows():
    # Assuming the column names are 'Name' and 'Sales'
    name = row['Name']
    sales = row['Sales']

    # Check if the sales number is above 80,000
    if sales > 80000:
        # Append the person's name and their sales number to the list
        high_sales_list.append({'Name': name, 'Sales': sales})

# Print the list of people with sales above 80,000
for item in high_sales_list:
    print(f"Name: {item['Name']}, Sales: {item['Sales']}")

except FileNotFoundError:
print(“File not found.”)
except pd.errors.EmptyDataError:
print(“The Excel file is empty or contains no data.”)
except pd.errors.ParserError:
print(“Error occurred while parsing the Excel file.”)
except KeyError:
print(“One or more required columns were not found in the Excel file.”)

6

import pandas as pd

def find_superstars(file_path):
try:
df = pd.read_excel(file_path)
superstars = df[df[‘Sales’] > 85000][‘Salesperson’].tolist()
print(“Sales Superstars:”)
for superstar in superstars:
print(superstar)
except Exception as e:
print(f"Error reading file: {str(e)}")

def main():
file_path = input("Enter the path of the ‘Sales_Superstars’ Excel sheet: ")
find_superstars(file_path)

if name == “main”:
main()

7

Here is my Techhub training python challenge:

import pandas as pd
import tkinter as tk
from tkinter import messagebox

1

salespeople =
directory = input(‘Please input the directory where the file is located.’)

if directory[-1] != ‘\’:
directory = directory + ‘\’

2

df = pd.read_excel(f’{directory}Challenge27_Sales_Superstars.xlsx’)
df.head()
high_sales = df[df[‘Sales’] >= 85000]

#3
for index, item in high_sales.iterrows():
name = item[‘Name’]
individual_sales = item[‘Sales’]
salespeople.append(f’{name} - {individual_sales}')

print(salespeople)

#4
messagebox.showinfo(“Salespeople List”, “\n”.join(salespeople))