The same analysis in Python
A spreadsheet answers a question once. A notebook answers it every week, and anyone can read how. This lesson rebuilds the East city analysis from the last three lessons in Python with pandas, line by line, so you can rerun it on Monday's new file in under a minute.
By the end you can
- Explain when code beats a spreadsheet: repeatable, reviewable and big enough
- Load, type-check and clean an export with pandas: read_csv, dtypes, to_datetime, str.strip and drop_duplicates
- Do the lookup and the pivot in code with merge, groupby and pivot_table, with a row-count check after each step
- Draw the indexed chart with matplotlib and prove the notebook works with restart and run all
The everyday picture
A cook at a busy food stall makes a famous stew from memory. A pinch of this, a handful of that, cook until it smells right. It tastes great, and it tastes a little different every day. When she is ill, nobody else can make it. When a wedding orders 300 portions, she has to guess how to scale every pinch.
Then she writes the recipe down: 2 kg of onions, 40 minutes on low heat, salt last. Now her assistant can cook it on Monday and get the same stew. A friend can read the card and say "that is too much salt" before anyone eats it. And for 300 portions you multiply the numbers.
Your spreadsheet analysis is cooking from memory. The pivot table remembers the result, and not the 40 clicks that made it. A Python notebook is the recipe card: every step is written down, in order, so it can be repeated, checked and scaled.
Why move the analysis into code
In da-02 to da-04 you cleaned the sales export, looked up each store's city, pivoted sales by week, store and category, and drew a chart that pointed at 9 East stores. It took most of a day. The head of stores now wants the same view every Monday until the problem is fixed. Three reasons make code the better tool from here.
- Repeatable. Next week you change one line, the file name, and run everything again. The same steps run in the same order, with no forgotten click. Rebuilding the spreadsheet by hand takes about two hours each week; the notebook takes under a minute.
- Reviewable. Tomás can read every step and spot a mistake, the same way a friend reads the recipe card. In a spreadsheet the steps live in clicks, hidden filters and formulas in cells nobody opens.
- Big enough. An Excel sheet holds at most 1,048,576 rows, and a Google Sheets file about 10 million cells. One row per sale in East city for 8 weeks is about 54 stores × 1,250 transactions × 56 days, roughly 3.8 million rows. pandas handles a few million rows on a normal laptop.
You will work in a notebook. The two common ones are Jupyter, which you install on your computer, and Google Colab, a free hosted notebook that runs in the browser with a Google account and already has pandas and matplotlib installed. A notebook is a list of cells. You run a cell with Shift + Enter, and its result appears right under it. Behind the page sits the kernel, the running Python that remembers every table you have made so far.
Python is the language. pandas is a library for tables, and matplotlib is a library for charts. In pandas a table is called a DataFrame, and one column of it is a Series. The whole notebook follows the same five steps as your spreadsheet, as the first diagram shows.
One habit matters from the first day. Cells can run in any order you click them, but the notebook is only trustworthy if it gives the same result when run from top to bottom on a fresh kernel. The last section shows how to prove that.
Quick check
You ran all five cells, then edited the cleaning cell and re-ran only the chart cell. The chart looks the same. What is true?
The kernel keeps the table the cleaning cell made the last time it ran. Editing the text of a cell changes nothing until the cell runs again. Saving the file does not run anything, and nothing is deleted.