Homework # 4 ~ Strategic Planning for Wy’East Coffee solve it on pyhon
A good friend of yours, Madeline, owns a small, rather successful coffee shop near center campus.
She has gathered some data in an Excel spreadsheet over the past week and now wants to analyze this
data to see if it is worth it to open a coffee shop near South campus.
You meet with Madeline, over coffee, and she explains what she wants the program to do. She would
like you to write a program that will do the following:
Determine the total sales by zip code. She wants to enter a zip code (48088, 48093, 48066 or
48026) and then see what the total sales were for that zip code.
Determine the total sales by coffee type. She wants to be able to enter a coffee type (mocha,
latte, regular, decaf or caramel) and see how much money each type produced.
Determine the grand total. She wants to see how much money was made for all 4 zip codes and
all 5 types of coffee.
Determine which coffee type sold the most in a particular zip code. She wants to enter the zip
code and then be told which coffee type sold the most and made the most money.
Determine which zip code sold the most given a particular coffee type. She wants to enter a coffee
type and then see which zip code sold the most and the amount of money that was made
You have listed itcs 1140 as the subject. What subject is itcs 1140?
To solve this problem using Python, you will need to perform the following steps:
1. Read the data from the Excel spreadsheet into your Python program. You can use libraries like `pandas` or `xlrd` to achieve this. Make sure to install these libraries if you haven't already.
2. Once you have the data in your program, you can start implementing the different functionalities.
3. To determine the total sales by zip code, you can create a function that takes a zip code as input and calculates the sum of sales for that zip code. Iterate through your data, filter the rows based on the given zip code, and sum up the sales values.
4. To determine the total sales by coffee type, you can create another function that takes a coffee type as input and calculates the sum of sales for that type. Iterate through your data, filter the rows based on the given coffee type, and sum up the sales values.
5. To calculate the grand total, you can simply calculate the sum of all sales in your dataset.
6. To determine which coffee type sold the most in a particular zip code, you can create a function that takes a zip code as input. Inside this function, filter the rows based on the given zip code, and then find the coffee type with the highest sales in that zip code. You can use the `max()` function and provide a key argument to compare the sales values.
7. To determine which zip code sold the most for a particular coffee type, you can create another function that takes a coffee type as input. Inside this function, filter the rows based on the given coffee type, and then find the zip code with the highest sales for that coffee type. Again, you can use the `max()` function and provide a key argument to compare the sales values.
8. Finally, you can create a user interface (UI) where Madeline can input her desired queries. Use input statements to prompt her for the required information and invoke the appropriate functions based on her inputs. Display the results to the user in a friendly manner.
By following these steps, you should be able to create a program that fulfills Madeline's requirements for analyzing the data from the Excel spreadsheet and provides the desired insights about total sales, sales by zip code and coffee type, grand total, and best selling coffee types and zip codes.