From: Dana Whitfield, operations lead at Juniper Lane
Re: One table for the Monday meeting
Every week I get three exports: the North store, the South store and the web shop. Each comes from a different system, so the columns have different names, the dates are written differently, the North file puts a dollar sign in the price, product names are typed every which way, and there are always a few repeated rows. The South file even adds a TOTAL line at the bottom.
I need two things:
orders: one table with the columns order_id, order_date (real dates), product (the official product name), qty, unit_price (a number), source (north, south or web) and revenue, with no junk rows or repeats.report: revenue per product (rows) and month (columns), with 0 where a product sold nothing that month.
The files are store_north.csv, store_south.csv and web_orders.csv. Each step is one part of the job; your notebook carries over from step to step.
This project puts the Beginner and Intermediate skills together on a job analysts do every week: combining exports from different systems. You'll rename columns to one standard, remove blank rows and summary lines, convert prices and dates, stack the files, fix product names against an official list, drop repeats and build the report.
Work through the steps in order. Each step checks the tables it changed, and your own notebook carries over to the next step, just like a real analysis. If you reload the page or jump ahead, the notebook starts from a clean copy of the work up to that step.