ICA: Cleaning Up Coffee Sales Data

\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Differential \newcommand\dd[2][]{\mathrm{d}^{#1}{#2}} % use as \dd, \dd{x}, or \dd[2]{x}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{{\kern-4mu}\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{\kern-4mu}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Capacitance \newcommand\farad{\unit{F}} \newcommand\F{\farad} \newcommand\microfarad{\micro\farad} \newcommand\muF{\micro\farad} % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch % NOTE: do NOT define \bar -- it is the built-in LaTeX accent used by \bar{x}, % \bar{y} (sample means). Redefining it made those render as the word "bar". \newcommand\barunit{\unit{bar}} \end{aligned}\end{align} \]

Nov 16, 2026 | 250 words | 2 min read

11.2.1. ICA: Cleaning Up Coffee Sales Data#

You’ve been given sales data from a local coffee shop, but it’s messy — just like real data usually is. The dataset needs cleaning before anyone can analyze it. Work through the steps below in small groups (2-4 students).

Required Files#

Download coffee_shop_sales.csv.

Instructions#

Dowload the provided data file (.csv) and complete the following tasks.

  1. View the Data: Open a new python file in VScode and write code to load and inspect the coffee sales data into a pandas DataFrame. Then write down your answers to the following: which column(s) have missing values? Which column’s data type looks wrong an why?

Hint: use the info() function to get more information on the data.

View Sample Solution (1)
import pandas as pd

df = pd.read_csv("coffee_shop_sales.csv")
print(df)
df.info()
  1. Data Types: Fix the data types of columns where data is not stored in a way you’d expect.

Hint: research pandas functions, like replace(), that might help you complete this task.

View Sample Solution (2)
# Remove the "$" and convert price to a number; regex indicates "$" as a character to remove
df["price"] = df["price"].str.replace("$", "", regex=False).astype(float)

# Fix the word "two" in quantity, then convert to numeric
df["quantity"] = df["quantity"].replace("two", 2)
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
  1. Missing Values: Determine how to handle missing values in the dataset. For example, for missing item names, should you drop those rows or label them "Unknown"?

  2. Duplicates: Some rows are exact duplicates (the same order got logged twice). Determine the number of duplicate orders and remove them.

View Sample Solution (4)
# how many exact duplicate rows?
print("Number of Duplicates: ",df.duplicated().sum())      
df = df.drop_duplicates() # drop duplicate rows
  1. Analyze the Data: Now that the data is clean, answer the following:

  • What was the total revenue?

  • Which item sold the most?

View Sample Solution (4)
# Total revenue (price * quantity, summed)
df["total"] = df["price"] * df["quantity"]
df["total"].sum()

# Best-selling item by quantity: displayed in descending order
df.groupby("item")["quantity"].sum().sort_values(ascending=False)
  1. Wrap-Up Discussion (2-3 min):

  • Did every team make the same choices in Step 3? Why might different choices be reasonable?

  • How would leaving the data uncleaned have changed your revenue total?