A tiny shop sells pens, notebooks, mugs, tea, and coasters. Which product sells the most? Which category brings in the most money? Before answering, its CSV needs a little attention: a duplicated order, a missing quantity, a missing price, and an unnamed category.
This is a fictional dataset built for learning. We'll clean it with pandas, calculate revenue, and draw a Matplotlib chart. The screenshots show the script actually running in PythonIDE, which I build, on an iPhone 18 Pro simulator. They use development build 1.7.9 (4).
1. Give the mess a clear meaning
The example embeds CSV text so you can run one file without downloading anything. StringIO lets read_csv treat that text as a file. To use your own CSV later, replace that line with pd.read_csv('sales.csv').
Each order ID represents one line in this tiny dataset. Order 110 appears twice and should count once. In a real multi-line order export, order ID alone would not be a safe duplicate key: include the line ID or inspect exact duplicate rows.
2. Make missing-data decisions explicit
We keep an unknown category as Other rather than losing the sale. Numeric conversion uses errors='coerce', so invalid numeric text becomes missing instead of quietly surviving as a string.
For the missing Tea price, groupby('product').transform('median') gives each row the median known price for its own product. fillna uses that value only where a price is missing. Both known Tea prices are $6 here, so that is the estimate.
That assumption belongs to this exercise. Real discounts or price changes need a better rule or review. A product without any known price would remain missing and be excluded by the next step.
The missing quantity is excluded. Inventing a quantity would invent revenue. We also remove non-positive quantities and negative prices for this positive-sales-only example; a returns ledger would need different rules. Quantities in these sample rows are whole numbers.
3. Ask two different questions
Revenue is quantity multiplied by price. Named aggregation gives each category a unit count and a revenue total. Sorting by revenue makes the chart easy to read.
For the best seller, we group by product and sum units, then use idxmax. That answers 'most units sold'. It does not answer 'most money earned'. With a tie, idxmax returns the first maximum; return all tied products if your report needs that.
4. Run the complete example
Save this as pandas_sales_lesson.py in PythonIDE and run it. The example uses pandas and Matplotlib.
import pandas as pd
import matplotlib.pyplot as plt
from io import StringIO
# Fictional orders: missing values and one duplicate.
csv = '''order,product,category,qty,price
101,Notebook,Stationery,3,8.50
102,Pen,Stationery,10,1.50
103,Mug,Home,2,12.00
104,Tea,Food,4,6.00
105,Notebook,Stationery,2,8.50
106,Mug,Home,,12.00
107,Tea,Food,3,
108,Pen,Stationery,5,1.50
109,Coaster,,4,3.00
110,Tea,Food,2,6.00
110,Tea,Food,2,6.00
'''
raw = pd.read_csv(StringIO(csv))
# 1. Clean, without guessing unknown quantities.
df = raw.drop_duplicates(subset='order').copy()
df['category'] = df['category'].fillna('Other')
for col in ['qty', 'price']:
df[col] = pd.to_numeric(df[col], errors='coerce')
# Fill a missing price only from that product's median.
medians = df.groupby('product')['price'].transform('median')
df['price'] = df['price'].fillna(medians)
df = df.dropna(subset=['qty', 'price'])
df = df.loc[(df['qty'] > 0) & (df['price'] >= 0)].copy()
df['qty'] = df['qty'].astype(int)
# 2. Calculate revenue, then aggregate by category.
df['revenue'] = df['qty'] * df['price']
summary = df.groupby('category').agg(
units=('qty', 'sum'),
revenue=('revenue', 'sum'),
).sort_values('revenue', ascending=False)
# 3. "Best selling" means most units, not most revenue.
units = df.groupby('product')['qty'].sum()
best = units.idxmax()
print('FICTIONAL SALES REPORT')
print(f'Rows: {len(raw)} raw -> {len(df)} clean')
print(summary.to_string(float_format=lambda x: f'{x:.2f}'))
print(f'\nRevenue: ${df.revenue.sum():.2f}')
print(f'Best seller: {best} ({units[best]} units)')
print('Excluded: one duplicate and one missing quantity.')
print('Tea price filled from known Tea prices: $6.00.')
# 4. Plot the same totals that appear in the report.
ax = summary['revenue'].plot.bar(
color=['#3B82F6', '#22C55E', '#F59E0B', '#A855F7'],
rot=0, figsize=(7, 4),
)
ax.set(title='Revenue by category (fictional data)',
xlabel='Category', ylabel='Revenue (USD)')
for bar in ax.patches:
ax.annotate(f'${bar.get_height():.2f}',
(bar.get_x() + bar.get_width()/2,
bar.get_height()),
ha='center', va='bottom', fontsize=9)
ax.set_ylim(0, summary.revenue.max() * 1.18)
plt.tight_layout()
plt.show()
5. Check the result before trusting the chart
The actual run reduced 11 rows to 9: one duplicate and one order without a quantity were excluded.
- Stationery: 20 units, $65.00
- Food: 9 units, $54.00
- Home: 2 units, $24.00
- Other: 4 units, $12.00
- Total revenue: $155.00
- Best-selling product by units: Pen, 15 units
The chart uses the same summary as the printed report, so its four bars match those totals. Notice the useful distinction: Pen sells the most units, but Tea earns $54, compared with Pen's $22.50. Unit popularity and revenue tell different stories.
Try one change
Change the best-seller calculation to group by product and sum revenue. Which product wins now? Then remove the known Tea prices: what happens to the row whose price we filled? Those two changes test whether you understand the business question and the cleaning assumption.
PythonIDE: https://apps.apple.com/us/app/id6753987304
AI disclosure: OpenAI Codex helped draft the English article and example code under my direction. I reviewed the article and requested revisions to the example and screenshots before publication.
Top comments (0)