# Understanding ETL in Python Through a Practical Project A dashboard is only as good as the data behind it. If dates are text and duplicates are still in the file, every chart is slightly wrong. ETL is the standard way to fix that before any charts exist. In this post I will explain what ETL means and build a small pipeline in Pandas, with real outputs from running it. Then I will show how an open-source dashboard follows the same stages. If Pandas basics are new to you, read [Pandas for Beginners: Cleaning and Analysing Real-World Data](https://pranaavkarthik.hashnode.dev/pandas-for-beginners-cleaning-analysing-data) first. ## What is ETL? ETL stands for Extract, Transform, Load. - **Extract:** read the raw data from its source (here, a CSV). - **Transform:** clean it and add useful columns. - **Load:** save the result somewhere the next step can use it (here, another CSV). That is all. The word "pipeline" just means these steps run in order, every time, the same way. Running one script is better than fixing a file by hand, because you can rerun it when new data arrives. ## The raw file I made `orders_raw.csv`, a small file with problems you will meet in real data: a missing sales and profit value, a duplicated order, a date that is not a date, and column names with spaces. ```text Order ID,Order Date,Ship Date,Region,Category,Sales,Profit,Discount 2001,01/05/2024,01/09/2024,West,Furniture,260.50,41.68,0.0 2002,01/09/2024,01/11/2024,East,Technology,640.00,128.00,0.1 2003,02/14/2024,02/20/2024,West,Office Supplies,18.20,-3.64,0.2 2004,02/20/2024,02/22/2024,Central,Technology,,,0.0 2005,03/02/2024,03/06/2024,East,Furniture,310.00,-31.00,0.3 2005,03/02/2024,03/06/2024,East,Furniture,310.00,-31.00,0.3 2006,03/18/2024,03/19/2024,South,Office Supplies,50.00,12.50,0.0 2007,04/01/2024,04/05/2024,West,Technology,420.00,63.00,0.15 2008,not a date,04/15/2024,Central,Furniture,150.00,9.00,0.1 2009,05/07/2024,05/10/2024,South,Technology,610.00,183.00,0.0 2010,07/21/2024,07/24/2024,East,Office Supplies,21.50,6.45,0.0 ``` ## Step 1: Extract ```python import pandas as pd RAW, OUT = "orders_raw.csv", "orders_clean.csv" df = pd.read_csv(RAW, encoding="latin1") print(df.shape) ``` ```text (11, 8) ``` The `encoding="latin1"` part is worth explaining. Files exported from Excel sometimes contain characters that are not valid UTF-8, and `read_csv` fails with a decode error. `latin1` is a forgiving encoding that reads them. My sample file is plain ASCII so it does not need it, but real exports often do. ## Step 2: Transform, part one (cleaning) Clean before you calculate. If you build columns on dirty data, you carry the dirt forward. ```python df.columns = [c.strip().replace(" ", "_") for c in df.columns] df["Order_Date"] = pd.to_datetime(df["Order_Date"], format="%m/%d/%Y", errors="coerce") df["Ship_Date"] = pd.to_datetime(df["Ship_Date"], format="%m/%d/%Y", errors="coerce") for col in ["Sales", "Profit", "Discount"]: df[col] = pd.to_numeric(df[col], errors="coerce") before = len(df) df = df.dropna(subset=["Sales", "Profit", "Order_Date"]).drop_duplicates() print("rows removed:", before - len(df)) ``` ```text rows removed: 3 ``` What happened here: - Column names lost their spaces, so `Order Date` became `Order_Date`. - `errors="coerce"` turns anything unparseable into a missing value (`NaT` for dates, `NaN` for numbers) instead of crashing. That is how "not a date" became a missing date, and then `dropna` removed it. - We also specify `format="%m/%d/%Y"`. Without it, Pandas guesses, and dates like 01/05/2024 can be read as 1 May instead of 5 January. Three rows went: order 2004 (missing sales and profit), order 2008 (bad date), and the second copy of order 2005. ## Step 3: Transform, part two (feature engineering) Feature engineering means creating new columns from existing ones, so analysis gets easier. We will add five. ```python df["Year"] = df["Order_Date"].dt.year df["Quarter"] = "Q" + df["Order_Date"].dt.quarter.astype(str) df["Days_to_Ship"] = (df["Ship_Date"] - df["Order_Date"]).dt.days.clip(lower=0) df["Profit_Margin"] = (df["Profit"] / df["Sales"].replace(0, float("nan")) * 100).round(2) df["Revenue_Band"] = pd.cut( df["Sales"], bins=[0, 50, 200, 500, df["Sales"].max() + 1], labels=["Low", "Medium", "High", "Premium"], right=False, ) print(df[["Order_ID", "Sales", "Year", "Quarter", "Days_to_Ship", "Profit_Margin", "Revenue_Band"]]) ``` ```text Order_ID Sales Year Quarter Days_to_Ship Profit_Margin Revenue_Band 0 2001 260.5 2024 Q1 4 16.0 High 1 2002 640.0 2024 Q1 2 20.0 Premium 2 2003 18.2 2024 Q1 6 -20.0 Low 4 2005 310.0 2024 Q1 4 -10.0 High 6 2006 50.0 2024 Q1 1 25.0 Medium 7 2007 420.0 2024 Q2 4 15.0 High 9 2009 610.0 2024 Q2 3 30.0 Premium 10 2010 21.5 2024 Q3 3 30.0 Low ``` A few notes on each one: - **Year and Quarter** come from the `.dt` accessor on a datetime column. You need real dates first, which is why cleaning came earlier. - **Days_to_Ship** subtracts two dates to get a timedelta, and `.dt.days` turns it into a number. The `clip(lower=0)` stops negative values from data-entry mistakes. - **Profit_Margin** is profit divided by sales, times 100. Replacing a zero sales value with NaN avoids a divide-by-zero. - **Revenue_Band** uses `pd.cut`, which sorts numbers into bins. With `right=False`, each bin includes its left edge, so a sale of exactly 50 lands in Medium, not Low. Look at order 2006 in the output. It is an easy edge case to get wrong, so test it yourself. Check the bin behaviour in isolation: ```python print(pd.cut(pd.Series([49.99, 50, 200]), bins=[0, 50, 200, 500, 1000], labels=["L", "M", "H", "P"], right=False).tolist()) ``` ```text ['L', 'M', 'H'] ``` ## Step 4: Validate Before saving, check your own work. `assert` stops the script if a condition is false. ```python assert df["Sales"].min() > 0, "Sales has non-positive values" assert df["Days_to_Ship"].min() >= 0, "Negative ship days found" assert df.duplicated().sum() == 0, "Duplicates still present" assert df["Order_Date"].isnull().sum() == 0, "Null order dates found" print("all checks passed") ``` ```text all checks passed ``` This looks boring when it passes. It earns its place the day a new data file breaks something, because the script fails loudly instead of quietly saving bad data. One honest caveat: some of these checks can never fail given the earlier steps. For example, `clip(lower=0)` already guarantees no negative ship days. Good checks should be able to fail, so think about what could actually go wrong with your data. ## Step 5: Load ```python df.to_csv(OUT, index=False) print(pd.read_csv(OUT).shape) print(df["Revenue_Band"].value_counts().sort_index()) ``` ```text (8, 13) Revenue_Band Low 2 Medium 1 High 3 Premium 2 Name: count, dtype: int64 ``` We started with 11 rows and 8 columns. We ended with 8 rows and 13 columns. `index=False` stops Pandas writing the row numbers as an extra column. Reading the file back to confirm the shape is a cheap check against silent mistakes. ## How a real project does this I have been reading the code of a Superstore dashboard built by [Asha Kalavagunta](https://github.com/Ashakalavagunta/sales-analytics-dashboard) (I studied it; I did not write it). Its `etl_pipeline.py` follows the same stages, in a single top-to-bottom script: | Stage | What the project does | |---|---| | Extract | Reads the raw Superstore CSV with `encoding='latin1'` | | Transform (clean) | Parses dates, converts Sales, Profit, Discount and Quantity to numbers, drops nulls and duplicates | | Transform (features) | Adds Year, Month, Quarter, YearQuarter, Days_to_Ship, Profit_Margin and Revenue_Band | | Validate | Assertions on positive sales, ship days, duplicates and null dates | | Load | Writes `cleaned_data.csv` for the Streamlit app | The README describes 9,994 rows and 21 raw columns becoming 30 cleaned columns. The app then reads the cleaned file, so charts and filters never touch raw data. That separation is the real lesson. Cleaning happens once, in one place. Reading it, one thing I would try is making the validation checks able to fail on real problems, for example checking that cleaning actually removed zero-sales rows. That is a suggestion from a learner, not a criticism. ## Common ETL mistakes - **Cleaning after feature engineering.** Build features on clean data only. - **Letting Pandas guess date formats.** Pass `format=`. - **Overwriting the raw file.** Keep the raw CSV untouched and write to a new file. You can always rerun. - **No validation.** A pipeline that never complains is not necessarily correct. - **Hardcoding file names deep in the script.** Put them at the top, as `RAW` and `OUT` are above. ## Wrapping up ETL is just a habit: read, clean, add features, check, save. Pandas gives you everything needed for a small version of it, and the same structure scales to bigger projects. Want to see the full version? Browse the original project by Asha Kalavagunta on [GitHub](https://github.com/Ashakalavagunta/sales-analytics-dashboard), and find more of my learning work at [github.com/Pranaav717](https://github.com/Pranaav717). The next post covers putting a project like this on GitHub: [Git and GitHub for College Students: From Local Project to GitHub Repository](https://pranaavkarthik.hashnode.dev/github-for-beginners-local-project-to-repository).