🐼 Pandas DataFrame: what it is, why learn it, and your first ETL

In simple words: Pandas is a library: a package of ready-made Python code that someone else wrote, which you install once and then use in your programs. Pandas is made for table data, meaning data in rows and columns, like an Excel sheet, a CSV file or a SQL table. With it you can read a file, fix bad values, filter rows, calculate totals and save the result, usually in a few lines.

Its main object is the DataFrame (a table in memory). It is free and open source. Wes McKinney started it in 2008, and the name comes from panel data, an economics word for tables of data. Everyone imports it with the short name pd.

🎬 In the animation: the panda mascot pops up, waves and introduces itself. Three fact cards appear: table data, a Python library, and the name. At the bottom, CSV, Excel, SQL and JSON chips fly into one small table: many sources go in, one DataFrame comes out.

Real-time scenario: every morning the Hyderabad Biryani Express manager gets an Excel file of yesterday's orders and adds up the totals by hand. A five-line pandas script reads the file and prints the totals per city in one second, every day, with no copy-paste mistakes.

# pd is the short name everyone uses
import pandas as pd

print(pd.__version__)

sales = pd.DataFrame({
    "city": ["Hyderabad", "Vijayawada", "Pune"],
    "orders": [120, 45, 85],
})
print(sales)
print("Total orders:", sales["orders"].sum())

3.0.5
         city  orders
0   Hyderabad     120
1  Vijayawada      45
2        Pune      85
Total orders: 250

Watch out: install with pip install pandas in a terminal (or !pip install pandas in a notebook cell), not inside your Python file. And never name your own file pandas.py: Python would import your file instead of the real library, and pd.DataFrame would fail.

In simple words: a DataFrame is a table that lives in your computer's memory. Learn these words, because every pandas error message and every interview uses them:

• Row: one record, for example one food order.
• Column: one field for all records, for example city. All values in a column have the same type.
• Index: the labels of the rows, 0, 1, 2 … by default. It is not your data; it is how pandas finds a row.
• Column labels: the names on top, in df.columns.
• Value (cell): one item where a row and a column meet.
• dtype: the data type of a column: int64 whole numbers, float64 decimal numbers, str text, bool True/False, datetime64 dates.
• Series: one column on its own (values plus the index). A DataFrame is a set of Series that share one index.

🎬 In the animation: five yellow sticky notes, each a loose Python list, fly into a neat table one by one. Then the parts light up in turn: the teal index, the purple column labels, the blue row 2, the pink city column (a Series), the green cell "Dosa", and the dtype badges under each column. Finally df.shape shows (5, 4).

Real-time scenario: an order API returns a list of lists (or a list of dictionaries). One call to pd.DataFrame() turns it into a table you can sort, filter and save.

import pandas as pd

# a messy Python list: one inner list per food order
rows = [
    [101, "Hyderabad", "Biryani", 250.0],
    [102, "Pune", "Pizza", 180.5],
    [103, "Vijayawada", "Dosa", 90.0],
    [104, "Hyderabad", "Haleem", 320.0],
    [105, "Pune", "Biryani", 260.0],
]
df = pd.DataFrame(rows, columns=["order_id", "city", "item", "amount"])
print(df)
print(df.shape)              # (rows, columns)
print(df.columns.tolist())   # the column labels
print(df.index)              # the row labels
print(df.dtypes)             # the type of each column

   order_id        city     item  amount
0       101   Hyderabad  Biryani   250.0
1       102        Pune    Pizza   180.5
2       103  Vijayawada     Dosa    90.0
3       104   Hyderabad   Haleem   320.0
4       105        Pune  Biryani   260.0
(5, 4)
['order_id', 'city', 'item', 'amount']
RangeIndex(start=0, stop=5, step=1)
order_id      int64
city            str
item            str
amount      float64
dtype: object

Two more ways to look at a DataFrame: head() shows the first rows, and info() prints a full report: columns, how many values are not missing, dtypes and memory.

import pandas as pd
df = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105],
    "city": ["Hyderabad", "Pune", "Vijayawada", "Hyderabad", "Pune"],
    "item": ["Biryani", "Pizza", "Dosa", "Haleem", "Biryani"],
    "amount": [250.0, 180.5, 90.0, 320.0, 260.0],
})
print(df.head(2))            # first 2 rows (default is 5)
df.info()                    # columns, non-null counts, dtypes, memory

city = df["city"]            # one column
print(type(city))            # a Series
print(df.loc[2])             # one row, by its index label
print(df.loc[2, "item"])     # one cell: row 2, column "item"

   order_id       city     item  amount
0       101  Hyderabad  Biryani   250.0
1       102       Pune    Pizza   180.5
<class 'pandas.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 4 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   order_id  5 non-null      int64  
 1   city      5 non-null      str    
 2   item      5 non-null      str    
 3   amount    5 non-null      float64
dtypes: float64(1), int64(1), str(2)
memory usage: 357.0 bytes
<class 'pandas.Series'>
order_id           103
city        Vijayawada
item              Dosa
amount            90.0
Name: 2, dtype: object
Dosa

Watch out: df.shape, df.columns and df.dtypes have no brackets (they are values), but df.head() and df.info() need them (they are methods). info() prints by itself, so print(df.info()) prints an extra None. pandas 2 vs 3: pandas 2 showed text columns as object and the class as pandas.core.frame.DataFrame; pandas 3 shows str and pandas.DataFrame.

In simple words: plain Python works on one value at a time, so you write the loop, the dictionary and the if checks yourself for every job. Pandas works on a whole column at once. You describe what you want ("sum the amount for each city") and pandas does the looping inside fast compiled code (this is called vectorised code). The result: less code, fewer bugs and much more speed.

The second reason is jobs. Pandas is the common language of data work in Python, and it appears in job descriptions and interviews for Data Engineer, Data Analyst, Data Scientist and ML Engineer roles.

🎬 In the animation: on the left, a robot walks down the orders one by one and drops each amount into a city bucket, so the totals tick up slowly. On the right, the one pandas line splits all rows into city groups at once and the bars grow straight away: "done in one step". When the robot finally finishes, both sides show the same answer. Job badges appear at the bottom.

Real-time scenario: a data engineer reads order files from S3, removes bad rows, and writes Parquet files in an AWS Lambda or Glue job. An analyst makes a monthly sales report for each city. An ML engineer fills missing values before training a model. A tester compares the row counts of the source and the target table.

import pandas as pd
rows = [
    [101, "Hyderabad", "Biryani", 250.0],
    [102, "Pune", "Pizza", 180.5],
    [103, "Vijayawada", "Dosa", 90.0],
    [104, "Hyderabad", "Haleem", 320.0],
    [105, "Pune", "Biryani", 260.0],
]

# Plain Python: you write the loop and the dictionary yourself
totals = {}
for order_id, city, item, amount in rows:
    totals[city] = totals.get(city, 0) + amount
print(totals)

# Pandas: describe WHAT you want; pandas does the looping
df = pd.DataFrame(rows, columns=["order_id", "city", "item", "amount"])
print(df.groupby("city")["amount"].sum())

{'Hyderabad': 570.0, 'Pune': 440.5, 'Vijayawada': 90.0}
city
Hyderabad     570.0
Pune          440.5
Vijayawada     90.0
Name: amount, dtype: float64

More one-liners that replace whole loops: filter rows, add a column for every row, sort.

import pandas as pd
df = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105],
    "city": ["Hyderabad", "Pune", "Vijayawada", "Hyderabad", "Pune"],
    "amount": [250.0, 180.5, 90.0, 320.0, 260.0],
})
big = df[df["amount"] > 200]                   # filter rows
print(big["order_id"].tolist())

df["gst"] = (df["amount"] * 0.05).round(2)     # new column for every row
print(df.sort_values("amount", ascending=False).head(2))

[101, 104, 105]
   order_id       city  amount   gst
3       104  Hyderabad   320.0  16.0
4       105       Pune   260.0  13.0

Speed check on 1,000,000 rows (the file is made in card 4). These times are from the machine that built this page; yours will be different, but pandas is usually several times faster than the plain loop.

import csv, time
import pandas as pd

start = time.perf_counter()
totals = {}
with open("big.csv") as f:                       # 1,000,000 rows
    for row in csv.DictReader(f):
        totals[row["city"]] = totals.get(row["city"], 0) + float(row["amount"])
loop_s = time.perf_counter() - start

start = time.perf_counter()
s = pd.read_csv("big.csv").groupby("city")["amount"].sum()
pandas_s = time.perf_counter() - start

print(f"plain loop: {loop_s:.2f} s")
print(f"pandas:     {pandas_s:.2f} s")
print("same answer:", all(abs(totals[c] - s[c]) < 0.01 for c in s.index))

plain loop: 1.45 s
pandas:     0.28 s
same answer: True

Watch out: do not loop over a DataFrame with for or iterrows() to do maths. Use column operations such as df["a"] * df["b"], groupby() and filters. A loop over rows throws away the speed you came for.

In simple words: pandas loads the whole table into the RAM (the working memory) of one computer. So it is perfect for small to medium data: from a few KB up to a few GB, depending on how much RAM the machine has. It is not built for:

• data bigger than the RAM of one machine (hundreds of GB, TB);
• work spread over many machines (a cluster);
• live streams where events arrive one by one all day (Kafka, Kinesis);
• many users reading and writing the same data at the same time (that is a database's job).

When data gets too big, you have options: read it in chunks; load only the columns you need (usecols=) with smaller dtypes; use Polars (a fast DataFrame library that uses all CPU cores) or DuckDB (a SQL engine inside Python that can work on files bigger than RAM); or use PySpark on a cluster (AWS Glue Spark jobs, EMR, Databricks) or a data warehouse.

🎬 In the animation: three coloured zones sit above a road of data sizes. Four trucks drive in and park in their zone: a 5 MB orders file and 2 GB of app clicks stay in the pandas zone, 80 GB of logs go to Polars and DuckDB, and 5 TB of payments go to PySpark. Then the "great for" and "not the right tool for" lists appear. The sizes are a rough guide, not exact limits.

Real-time scenario: the daily orders file (50 MB) is cleaned with pandas inside a Lambda function. Three years of click logs (3 TB) in S3 are processed with a Glue Spark job instead.

First make a 1,000,000-row test file (it is used in cards 3, 4 and 5):

import numpy as np
import pandas as pd

n = 1_000_000
rng = np.random.default_rng(42)          # fixed seed: same data every run
big = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "city": rng.choice(["Hyderabad", "Pune", "Vijayawada"], n),
    "amount": rng.uniform(50, 900, n).round(2),
})
big.to_csv("big.csv", index=False)
print(big.shape)

(1000000, 3)

Now read it in pieces of 250,000 rows. Only one piece is in memory at a time:

import pandas as pd

# Read 250,000 rows at a time instead of all 1,000,000 at once
total = 0
for chunk in pd.read_csv("big.csv", chunksize=250_000):
    print(len(chunk), "rows in this chunk")
    total += chunk["amount"].sum()
print("Grand total:", round(total, 2))

250000 rows in this chunk
250000 rows in this chunk
250000 rows in this chunk
250000 rows in this chunk
Grand total: 475197075.45

The same total per city with Polars and DuckDB (both were installed for this test: pip install polars duckdb):

import duckdb
import polars as pl

# Polars: a DataFrame library built for speed on one machine
print(pl.read_csv("big.csv").group_by("city").agg(pl.col("amount").sum().round(2)).sort("city").rows())

# DuckDB: run SQL directly on a file, no server needed
print(duckdb.sql("""
    SELECT city, round(SUM(amount), 2) AS amount
    FROM 'big.csv' GROUP BY city ORDER BY city
""").fetchall())

[('Hyderabad', 158377919.11), ('Pune', 158600968.52), ('Vijayawada', 158218187.82)]
[('Hyderabad', 158377919.11), ('Pune', 158600968.52), ('Vijayawada', 158218187.82)]

Watch out: chunks only help when the job can be done piece by piece, like sums, counts and filters. A full sort, a median or a join across the whole file needs all the data at once.

In simple words: every value of the DataFrame sits in RAM. Number columns use a fixed size per value: int64 and float64 take 8 bytes, int32 and float32 take 4. So 1,000,000 int64 numbers = 8,000,000 bytes ≈ 7.6 MB. Text takes more, depending on its length. df.memory_usage(deep=True) tells you the bytes per column; deep=True counts the real size of the text.

A category column stores each different text only once and keeps a small number code per row. It is perfect for repeated values like city names, and here it shrinks the city column from 14.9 MB to 1.0 MB. (In this page, 1 MB = 1024 × 1024 bytes.)

Rule of thumb (rough guidance, not an exact number): keep about 5 to 10 times the file size free in RAM. Sorting, merging and grouping make temporary copies, so the peak is much higher than the table itself. In our test, a 21.4 MB CSV became a 30.2 MB DataFrame, and one sort plus one merge pushed the extra peak RAM to about 112 MB, roughly 5 times the file.

🎬 In the animation: a green RAM stick appears and a counter ticks up to 1,000,000 rows. Bars show the memory of each column and the total of 30.2 MB. Then the "dtype diet" badge appears: the bars shrink and turn green, and the total drops to 8.6 MB. On the right, five measured bars compare the same data on disk (CSV, Parquet) and in RAM. The panda states the rule of thumb.

Real-time scenario: on a laptop with 8 GB of RAM, a 500 MB CSV is comfortable. A 3 GB CSV will probably run out of memory, so you load fewer columns, use smaller dtypes, read it in chunks, or switch to DuckDB or Polars.

import numpy as np
import pandas as pd

n = 1_000_000
rng = np.random.default_rng(42)
df = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "city": rng.choice(["Hyderabad", "Pune", "Vijayawada"], n),
    "amount": rng.uniform(50, 900, n).round(2),
})
MB = 1024 * 1024
print(df.dtypes)
print((df.memory_usage(deep=True) / MB).round(1))      # per column, in MB
print("Total:", round(df.memory_usage(deep=True).sum() / MB, 1), "MB")

# The "dtype diet": smaller types for the same data
small = df.astype({"order_id": "int32", "city": "category", "amount": "float32"})
print((small.memory_usage(deep=True) / MB).round(1))
print("Total:", round(small.memory_usage(deep=True).sum() / MB, 1), "MB")

order_id      int64
city            str
amount      float64
dtype: object
Index        0.0
order_id     7.6
city        14.9
amount       7.6
dtype: float64
Total: 30.2 MB
Index       0.0
order_id    3.8
city        1.0
amount      3.8
dtype: float64
Total: 8.6 MB

File size on disk is not the same as size in RAM. Parquet is compressed on disk but takes the normal size once loaded:

import os
import pandas as pd

MB = 1024 * 1024
df = pd.read_csv("big.csv")
df.to_parquet("big.parquet", index=False)
print("CSV file on disk:    ", round(os.path.getsize("big.csv") / MB, 1), "MB")
print("Parquet file on disk:", round(os.path.getsize("big.parquet") / MB, 1), "MB")
print("DataFrame in RAM:    ", round(df.memory_usage(deep=True).sum() / MB, 1), "MB")

# pandas 2 stored text as Python objects ("object" dtype); see the cost:
old_style = df.astype({"city": object})
print("Text as object dtype:", round(old_style.memory_usage(deep=True).sum() / MB, 1), "MB")
print("City column as object:", round(old_style["city"].memory_usage(deep=True) / MB, 1), "MB")

CSV file on disk:     21.4 MB
Parquet file on disk: 6.7 MB
DataFrame in RAM:     30.2 MB
Text as object dtype: 69.3 MB
City column as object: 54.0 MB

Measure the peak memory of a real job (the number changes a little from run to run):

# resource works on Linux and macOS only (not on Windows)
import resource
import pandas as pd

def peak_mb():
    return resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024   # Linux gives KB

before = peak_mb()
df = pd.read_csv("big.csv")                       # a 21.4 MB CSV file
by_amount = df.sort_values("amount")              # sorting makes a copy
joined = df.merge(df[["order_id"]], on="order_id")  # a merge makes another
print(f"Extra peak RAM used: about {peak_mb() - before:.0f} MB")

Extra peak RAM used: about 112 MB

Watch out: float32 keeps only about 7 digits. It is fine for one amount like 456.78, but adding millions of float32 values loses paise. Keep money totals in float64. Also, in pandas 2 the same text column was an object column and took 69.3 MB instead of 30.2 MB in total; pandas 3 stores text in a compact str dtype (backed by pyarrow when it is installed).

In simple words: the pandas code is the same everywhere; only the way you install and start it changes. You usually develop in an editor or a notebook (a notebook, like Jupyter, runs code cell by cell and shows tables right under the cell), and then run it as a script on a schedule in production.

• Your laptop: VS Code or PyCharm for .py scripts; Jupyter Notebook or JupyterLab for exploring. Install with pip install pandas inside a virtual environment.
• Google Colab and Kaggle Notebooks: free notebooks in the browser, pandas already installed. Best for learning and practice.
• AWS: Lambda (small, event-driven jobs; pandas comes from a Lambda layer such as the AWS SDK for pandas layer; at most 15 minutes and 10,240 MB of memory per run), Glue Python shell jobs (scheduled scripts without Spark), SageMaker notebooks (analysis and ML) and EC2 (your own server).
• Databricks: notebooks on a cluster. pandas runs on one machine of the cluster (the driver); use PySpark for big data there.
• Docker or a Linux server: the script runs in a container or on a server, started by cron or an Airflow DAG.

🎬 In the animation: the panda sits in the middle of a map. Lines draw out to five places (laptop, Docker, free notebooks, AWS, Databricks), a pink dot travels along each line, and each card pops up with its tools. The chip says "same pandas code everywhere".

Real-time scenario: you try the cleaning logic in Colab, turn it into a script in VS Code, and deploy it as a Lambda function that runs whenever a new orders file lands in an S3 bucket.

import platform
import sys

import pandas as pd

# Run this first on any new machine or notebook
print("Python:", sys.version.split()[0])
print("pandas:", pd.__version__)
print("OS:    ", platform.system())

Python: 3.13.16
pandas: 3.0.5
OS:     Linux

A Lambda handler that reads a CSV from S3, cleans it and writes it back. We tested it on this page with moto (a fake, in-memory S3 for tests), not on real AWS. In real AWS, the function needs a pandas layer and an IAM role that can read and write the bucket.

import io
import boto3
import pandas as pd      # comes from a Lambda layer, e.g. AWS SDK for pandas

s3 = boto3.client("s3")

def lambda_handler(event, context):
    obj = s3.get_object(Bucket=event["bucket"], Key=event["key"])
    raw = pd.read_csv(obj["Body"])                       # Extract
    df = raw.drop_duplicates().dropna(subset=["price"])  # Transform
    out = io.StringIO()
    df.to_csv(out, index=False)
    s3.put_object(Bucket=event["bucket"], Key="clean/orders.csv",
                  Body=out.getvalue())                   # Load
    return {"rows_in": len(raw), "rows_out": len(df)}

# local test (in AWS, Lambda calls lambda_handler for you)
print(lambda_handler({"bucket": "hbe-orders", "key": "raw/orders_raw.csv"}, None))
keys = s3.list_objects_v2(Bucket="hbe-orders")["Contents"]
print([k["Key"] for k in keys])

{'rows_in': 8, 'rows_out': 6}
['clean/orders.csv', 'raw/orders_raw.csv']

Watch out: different machines have different versions, so write them down in requirements.txt (for example pandas==3.0.5). Lambda cannot pip install while it runs; the library must be in a layer or in your deployment package (250 MB unzipped limit, layers included).

In simple words: ETL means Extract, Transform, Load, the everyday job of a data engineer. Extract means reading the raw data from where it lives into a DataFrame. pandas has a read_ function for each source: read_csv, read_excel (needs openpyxl), read_json, read_parquet (needs pyarrow) and read_sql.

After reading, always look before you change anything: shape, dtypes, missing values (isna()), duplicates (duplicated()) and the different values in a text column (unique()).

🎬 In the animation: the truck drives onto the belt at the Extract station. Each line of orders_raw.csv flashes pink and slides into the table as a row. Then six problem cells turn orange: " pune " with spaces, HYDERABAD and hyderabad in different case, a missing price (NaN), a cancelled order, and a duplicate row.

Real-time scenario: every night the restaurant app drops orders_raw.csv into an S3 bucket. The ETL job extracts it first thing in the morning.

Make the sample file (steps 7 to 10 use it):

# The sample file for steps 7 to 10:
# 8 orders, with problems on purpose
raw = """order_id,city,qty,price,status
101,Hyderabad,2,250,delivered
102, pune ,1,180.5,delivered
103,Vijayawada,3,,delivered
104,HYDERABAD,1,320,cancelled
105,Pune,2,260,delivered
105,Pune,2,260,delivered
106,Vijayawada,1,240,delivered
107,hyderabad,2,95,delivered
"""
with open("orders_raw.csv", "w") as f:
    f.write(raw)
print("orders_raw.csv written")

orders_raw.csv written

import pandas as pd

df = pd.read_csv("orders_raw.csv")        # Extract: file -> DataFrame
print(df)
print(df.dtypes)
print("Missing values per column:")
print(df.isna().sum())
print("Duplicate rows:", df.duplicated().sum())
print("City values:", df["city"].unique().tolist())

   order_id        city  qty  price     status
0       101   Hyderabad    2  250.0  delivered
1       102       pune     1  180.5  delivered
2       103  Vijayawada    3    NaN  delivered
3       104   HYDERABAD    1  320.0  cancelled
4       105        Pune    2  260.0  delivered
5       105        Pune    2  260.0  delivered
6       106  Vijayawada    1  240.0  delivered
7       107   hyderabad    2   95.0  delivered
order_id      int64
city            str
qty           int64
price       float64
status          str
dtype: object
Missing values per column:
order_id    0
city        0
qty         0
price       1
status      0
dtype: int64
Duplicate rows: 1
City values: ['Hyderabad', ' pune ', 'Vijayawada', 'HYDERABAD', 'Pune', 'hyderabad']

Reading from S3. An s3:// path works only after pip install s3fs, and you need AWS credentials (from aws configure on a laptop, or an IAM role in Lambda and Glue). This machine does not have s3fs, so you see the real error first; the boto3 way works without s3fs. Tested with moto (fake S3), not real AWS.

import boto3
import pandas as pd

# Way 1: an s3:// path. Needs the extra package s3fs (pip install s3fs)
try:
    df = pd.read_csv("s3://hbe-orders/raw/orders_raw.csv")
except ImportError as e:
    print("ImportError:", e)

# Way 2: boto3 downloads the object, pandas reads its body
s3 = boto3.client("s3")
obj = s3.get_object(Bucket="hbe-orders", Key="raw/orders_raw.csv")
df = pd.read_csv(obj["Body"])
print(df.shape)

ImportError: Install s3fs to access S3
(8, 5)

Watch out: the price column is float64, not int64, because one value is missing and NaN (Not a Number, pandas' "missing" marker) is a float. Spaces in " pune " are kept as they are, so "Pune" and " pune " count as two different cities until you clean them.

In simple words: Transform means fixing and reshaping the data so it can be trusted. The common moves are: remove duplicates (drop_duplicates()), clean text (.str.strip() removes spaces at the ends, .str.title() makes "HYDERABAD" into "Hyderabad"), handle missing values (dropna() or fillna()), keep only the rows you need (a filter), add new columns, and group with groupby().

A filter like df[df["status"] == "delivered"] works in two parts: the inside makes True or False for every row, and df[...] keeps the rows that are True. Each step returns a new DataFrame, so assign it back to df.

🎬 In the animation: the Data Chef panda works down a checklist. The duplicate row turns red and falls off, the messy city cells flash and become "Pune" and "Hyderabad", the row with no price falls off, a magnifying glass checks the status column and the cancelled order falls off. A green total column slides in, and the totals fly into a pink summary table: Hyderabad 690.0, Pune 700.5, Vijayawada 240.0.

Real-time scenario: the finance team needs revenue per city for delivered orders only. Duplicates (an app retry), cancelled orders and rows without a price must not be counted.

import pandas as pd

df = pd.read_csv("orders_raw.csv")
print("start:", len(df), "rows")

df = df.drop_duplicates()                               # 1. remove repeated rows
print("after drop_duplicates:", len(df))

df["city"] = df["city"].str.strip().str.title()        # 2. " pune " -> "Pune"

df = df.dropna(subset=["price"])                        # 3. remove rows with no price
print("after dropna:", len(df))

df = df[df["status"] == "delivered"]                    # 4. keep delivered orders
print("after filter:", len(df))

df["total"] = df["qty"] * df["price"]                   # 5. add a new column
print(df)

summary = df.groupby("city", as_index=False)["total"].sum()   # 6. group
print(summary)

start: 8 rows
after drop_duplicates: 7
after dropna: 6
after filter: 5
   order_id        city  qty  price     status  total
0       101   Hyderabad    2  250.0  delivered  500.0
1       102        Pune    1  180.5  delivered  180.5
4       105        Pune    2  260.0  delivered  520.0
6       106  Vijayawada    1  240.0  delivered  240.0
7       107   Hyderabad    2   95.0  delivered  190.0
         city  total
0   Hyderabad  690.0
1        Pune  700.5
2  Vijayawada  240.0

Changing values the right way. To change values in some rows, use one .loc[rows, column] call:

import warnings
import pandas as pd

df = pd.read_csv("orders_raw.csv")

# WRONG: two brackets in a row = "chained assignment". It changes a temporary copy.
with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    df[df["status"] == "cancelled"]["price"] = 0
print(type(caught[0].message).__name__)
print(df.loc[3, "price"])                 # still 320.0: nothing changed

# RIGHT: one .loc[rows, column] call
df.loc[df["status"] == "cancelled", "price"] = 0
print(df.loc[3, "price"])                 # 0.0

ChainedAssignmentError
320.0
0.0

Watch out: after removing rows, the index keeps the old labels (0, 1, 4, 6, 7). Add .reset_index(drop=True) if you want 0 to 4 again. pandas 2 vs 3: in pandas 3, copy-on-write is always on, so chained assignment like df[mask]["price"] = 0 never changes df and pandas warns with ChainedAssignmentError. pandas 2 showed a SettingWithCopyWarning and sometimes did change the data.

In simple words: Load means saving the result where people and programs will use it. Choose the format by the reader:

• CSV (to_csv): plain text, opens in Excel; good for people and small files.
• Parquet (to_parquet, needs pyarrow): compressed, column-wise and it keeps the dtypes; the standard format of data lakes on S3, read by Athena, Glue and Spark.
• SQL table (to_sql): for apps and dashboards. if_exists can be "fail" (the default), "replace" or "append".

🎬 In the animation: the Load station lights up. The clean table flies into orders_clean.csv, the summary flies into city_sales.parquet and into the sales.db database, and each target gets a green "written" badge. A dashed card shows the S3 version for real projects, and the table read back from the database proves the load worked.

Real-time scenario: the clean orders go to S3 as Parquet for the analytics team, and the city totals go into a database table that feeds the morning sales dashboard.

import os
import sqlite3
import pandas as pd

# (the same Transform as step 8, in a short form)
df = pd.read_csv("orders_raw.csv").drop_duplicates().dropna(subset=["price"])
df["city"] = df["city"].str.strip().str.title()
df = df[df["status"] == "delivered"]
df["total"] = df["qty"] * df["price"]
summary = df.groupby("city", as_index=False)["total"].sum()

df.to_csv("orders_clean.csv", index=False)               # a CSV file
summary.to_parquet("city_sales.parquet", index=False)    # a Parquet file
with sqlite3.connect("sales.db") as con:                 # a database table
    n = summary.to_sql("city_sales", con, if_exists="replace", index=False)
    print(n, "rows written to table city_sales")
    print(pd.read_sql("SELECT * FROM city_sales", con))

for name in ["orders_clean.csv", "city_sales.parquet", "sales.db"]:
    print(name, os.path.getsize(name) > 0)
print(pd.read_parquet("city_sales.parquet").equals(summary))

3 rows written to table city_sales
         city  total
0   Hyderabad  690.0
1        Pune  700.5
2  Vijayawada  240.0
orders_clean.csv True
city_sales.parquet True
sales.db True
True

With SQLAlchemy, the same code works for MySQL or PostgreSQL; only the connection string changes, for example "postgresql+psycopg2://user:password@host:5432/sales" (that driver must be installed). Here append adds 3 more rows to the 3 already in the table, so the count is 6:

import pandas as pd
from sqlalchemy import create_engine

summary = pd.read_parquet("city_sales.parquet")

# Only this connection string changes for MySQL or PostgreSQL
engine = create_engine("sqlite:///sales.db")
summary.to_sql("city_sales", engine, if_exists="append", index=False)
print(pd.read_sql("SELECT COUNT(*) AS n FROM city_sales", engine))

   n
0  6

To S3: df.to_parquet("s3://bucket/clean/orders.parquet") needs s3fs and AWS credentials, like reading. Without s3fs, write to a buffer and upload it with boto3, as in the Lambda example in card 6.

Watch out: forgetting index=False saves the index as an extra, nameless column. When you read the file back, it shows up as "Unnamed: 0":

import pandas as pd

summary = pd.DataFrame({"city": ["Pune"], "total": [700.5]})
summary.to_csv("with_index.csv")              # forgot index=False
print(pd.read_csv("with_index.csv").columns.tolist())

['Unnamed: 0', 'city', 'total']

In simple words: put each stage in its own function: extract() reads, transform() cleans and groups, load() saves. A main() function calls them in order. Small functions are easy to test one by one and easy to reuse. Printing (or logging) the row count after each stage is your first data quality check: 8 rows in, 5 clean rows, 3 city totals.

if __name__ == "__main__": means "run main() only when this file is started directly", so another file can import the functions without running the whole job.

🎬 In the animation: the panda in a hard hat starts the job. The terminal prints the three log lines one by one while a crate rides the belt from Extract to Transform to Load; its label changes from "8 rows" to "5 rows" to "3 rows" and each station lights up with its count. Schedule chips appear: cron, Airflow, AWS Glue and a Lambda S3 trigger.

Real-time scenario: the script is saved as etl_orders.py and runs every morning at 6:00 from cron or an Airflow DAG, or as an AWS Glue Python shell job. The sales dashboard is fresh before the managers log in.

"""etl_orders.py - a small daily ETL job with pandas."""
import sqlite3

import pandas as pd


def extract(path):
    df = pd.read_csv(path)
    print(f"extract:   {len(df)} rows from {path}")
    return df


def transform(df):
    df = df.drop_duplicates().dropna(subset=["price"])
    df["city"] = df["city"].str.strip().str.title()
    df = df[df["status"] == "delivered"]
    df["total"] = df["qty"] * df["price"]
    summary = df.groupby("city", as_index=False)["total"].sum()
    print(f"transform: {len(df)} clean rows, {len(summary)} cities")
    return df, summary


def load(df, summary, db_path):
    df.to_csv("orders_clean.csv", index=False)
    summary.to_parquet("city_sales.parquet", index=False)
    with sqlite3.connect(db_path) as con:
        summary.to_sql("city_sales", con, if_exists="replace", index=False)
    print(f"load:      {len(summary)} rows -> {db_path}")


def main():
    raw = extract("orders_raw.csv")
    clean, summary = transform(raw)
    load(clean, summary, "sales.db")
    print(summary)


if __name__ == "__main__":
    main()

extract:   8 rows from orders_raw.csv
transform: 5 clean rows, 3 cities
load:      3 rows -> sales.db
         city  total
0   Hyderabad  690.0
1        Pune  700.5
2  Vijayawada  240.0

Watch out: make the job safe to run twice. With if_exists="append", a second run would add the same rows again and double the totals. Use "replace" for a full refresh, or delete that day's rows before inserting. A job that gives the same result however many times it runs is called idempotent.