Pandas Data Analysis: Explore, Summarise and Report
Use this hub to choose a Pandas analysis task and open its detailed tutorial. Start with inspection and record selection, then summarise measures, compare groups, analyse time periods and prepare a checked report. Follow the sales workflow below for practice.
Show Table of Contents ↓Practice with sales, products and customers
Start with a question and define each row 🔝
Decide what one row represents, which records belong in the report and which measures answer your question. In the workflow below, one row is one order. We report paid orders with known non-negative amounts and valid dates. Missing amounts remain in a review table. Start with inspection, choose the analysis population, calculate summaries and reconcile totals before exporting. For a full cleaning workflow, visit Data Cleaning and String Operations; for loading files, use Input and Output.
Inspect the table and its data types 🔝
- head() and tail() — preview a few rows
- info() — inspect types and non-missing counts
- dtypes — check column types
- columns — inspect field labels
- shape, size and ndim — check dimensions and the number of cells
- Display settings — adjust how tables are displayed
A preview is not a validation check. Inspect missing values, identifiers and duplicate business keys before interpreting statistics.
Select the records and fields to analyse 🔝
- loc — select by labels and Boolean conditions
- iloc — select by integer position
- at — get or set a single value by label
- Condition-based row selection — select rows using Boolean conditions
- query() — express row filters using a query string
- str.contains() — select records using text conditions
- str.contains().sum() — count rows matching a text condition
Keep a record of exclusions. loc and iloc use square brackets; they are indexers. Use explicit parentheses when combining Boolean conditions.
Calculate descriptive statistics 🔝
- describe() — get counts, central values, spread and percentiles
- count() — count available values per column or row
- sum() — calculate totals with a missing-value policy
- mean() — calculate arithmetic averages
- min() — find minimum values
- max() — find maximum values
- std() — measure spread
Select the measure columns deliberately. Numeric IDs are identifiers, not quantities to average. Report the available count alongside a mean when data are incomplete. A mean and median describe different aspects of a distribution.
Count categories, create bands and compare groups 🔝
- value_counts() — count values or combinations and calculate shares
- unique() — inspect distinct values without frequencies
- groupby() — summarise measures for each group
- pd.cut() — assign numeric values to fixed reporting bands
- sort_values() — order records or summaries
- nlargest() — select the largest values
Distinguish row counts from non-missing value counts and revenue totals. State whether percentages refer to all rows or only known values. Test exact bin boundaries before using a band report.
Join and reshape data for reports 🔝
- merge() — combine tables using keys and check join cardinality
- pivot() — reshape data with unique index-column combinations
- pivot_table() — reshape with an explicit aggregation
- set_index() — choose row labels
- reset_index() — turn index labels into columns
- get_dummies() — encode categories as indicator columns
- DataFrames and NumPy arrays — build labelled tables from arrays, preserve numeric columns and export selected values.
A join can multiply rows when keys repeat. Check key uniqueness, use merge validation and reconcile totals. pivot() requires unique combinations; pivot_table() aggregates them, so choose its aggregation explicitly.
Analyse dates, periods and moving windows 🔝
- to_datetime() — parse dates with a known format
- to_timedelta() — represent elapsed durations
- resample() — aggregate observations into time periods
- rolling() — calculate moving-window measures
Sort dates before rolling calculations. A three-row window and a three-day window are different. Decide whether a period with no recorded orders represents zero activity or missing source data before filling gaps.
Choose a chart that answers the question 🔝
- Plotting overview — choose a chart type
- Bar charts — compare category totals
- Line charts — show change over time
- Histograms — inspect a numeric distribution
- Scatter plots — compare two numeric measures
- Pie charts — show shares for a small set of categories
Label the measure, units and population. A bar of revenue and a bar of order counts tell different stories. Build and verify the summary table before plotting it.
Prepare values and use custom rules when needed 🔝
- Data Cleaning hub — handle missing values, duplicates and string fields
- isnull() / isna() — identify missing values
- notnull() / notna() — identify available values
- fillna() — fill values using a justified rule
- astype() — convert validated values to the required dtype
- apply() — implement custom row or column calculations
- mask() — replace values where a condition is True
- where() — keep values where a condition is True
Built-in arithmetic and string methods are often clearer for simple rules. Preserve raw values and review uncertain records before assigning replacements.
Workflow: inspect a synthetic order extract 🔝
Each order has a date, product, status and amount. The sample includes a test order, an unparseable amount and a missing amount. These are practice records, not measured business performance.
import pandas as pd
raw = pd.DataFrame({
"order_id": [101, 102, 103, 104, 105, 106, 107, 108],
"date": ["2026-09-01", "2026-09-01", "2026-09-02", "2026-09-02", "2026-09-03", "2026-09-03", "2026-09-04", "2026-09-04"],
"product": ["Pen", "Book", "Pen", "Notebook", "Book", "Pen", "Notebook", "Book"],
"status": ["paid", "paid", "paid", "test", "paid", "paid", "paid", "paid"],
"amount": ["10", "30", "15", "0", "bad", None, "50", "20"]
})
print(raw.head())
print("Rows and columns:", raw.shape)
raw.info()
assert raw["order_id"].is_uniqueExpected output
order_id date product status amount
0 101 2026-09-01 Pen paid 10
1 102 2026-09-01 Book paid 30
2 103 2026-09-02 Pen paid 15
3 104 2026-09-02 Notebook test 0
4 105 2026-09-03 Book paid bad
Rows and columns: (8, 5)
<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 order_id 8 non-null int64
1 date 8 non-null str
2 product 8 non-null str
3 status 8 non-null str
4 amount 7 non-null str
dtypes: int64(1), str(4)
memory usage: 452.0 bytesDefine the population and keep excluded rows 🔝
Parse dates using the source format and amounts as numbers. Report paid orders with known non-negative amounts. A zero amount is allowed by this sample rule. Do not treat an unknown amount as zero.
cleaned = raw.copy()
cleaned["date"] = pd.to_datetime(raw["date"], format="%Y-%m-%d", errors="coerce")
cleaned["amount"] = pd.to_numeric(raw["amount"], errors="coerce").astype("Float64")
valid = cleaned["status"].eq("paid") & cleaned["date"].notna() & cleaned["amount"].ge(0).fillna(False)
analysis = cleaned.loc[valid].copy()
review = raw.loc[~valid].copy()
print("Analysis orders:")
print(analysis)
print("Excluded original records:")
print(review)
assert analysis["order_id"].tolist() == [101, 102, 103, 107, 108]
assert len(analysis) + len(review) == len(raw)Expected output
Analysis orders:
order_id date product status amount
0 101 2026-09-01 Pen paid 10.0
1 102 2026-09-01 Book paid 30.0
2 103 2026-09-02 Pen paid 15.0
6 107 2026-09-04 Notebook paid 50.0
7 108 2026-09-04 Book paid 20.0
Excluded original records:
order_id date product status amount
3 104 2026-09-02 Notebook test 0
4 105 2026-09-03 Book paid bad
5 106 2026-09-03 Pen paid NaNCalculate the overall summary 🔝
Select the amount measure rather than averaging every numeric field. The accepted sample has five orders and known revenue 125. Describe and a small named set of measures provide complementary views.
print(analysis["amount"].describe())
metrics = analysis["amount"].agg(["count", "sum", "mean", "median", "min", "max"])
print(metrics)
assert metrics["count"] == 5
assert metrics["sum"] == 125
assert metrics["mean"] == 25
assert metrics["median"] == 20Expected output
count 5.0
mean 25.0
std 15.811388
min 10.0
25% 15.0
50% 20.0
75% 30.0
max 50.0
Name: amount, dtype: Float64
count 5.0
sum 125.0
mean 25.0
median 20.0
min 10.0
max 50.0
Name: amount, dtype: float64Summarise products and reconcile the totals 🔝
Count orders with size and available amounts with count. They match here because every accepted order has a known amount. Check that grouped revenue sums to the same total as the analysis table.
product_summary = analysis.groupby("product", dropna=False).agg(orders=("order_id", "size"), known_amounts=("amount", "count"), revenue=("amount", "sum"), mean_order=("amount", "mean"))
product_summary = product_summary.sort_values("revenue", ascending=False)
print(product_summary)
print("Order frequency:")
print(analysis["product"].value_counts())
assert product_summary["orders"].sum() == 5
assert product_summary["revenue"].sum() == analysis["amount"].sum()Expected output
orders known_amounts revenue mean_order
product
Book 2 2 50.0 25.0
Notebook 1 1 50.0 50.0
Pen 2 2 25.0 12.5
Order frequency:
product
Pen 2
Book 2
Notebook 1
Name: count, dtype: int64Create a fixed band report with boundary checks 🔝
Use Low: [0, 20], Medium: (20, 40] and High: (40, 100]. The amount 20 belongs to Low. Retain empty defined categories with observed=False if the report requires a consistent set of bands.
analysis["amount_band"] = pd.cut(analysis["amount"].astype(float), [0, 20, 40, 100], labels=["Low", "Medium", "High"], include_lowest=True)
band_summary = analysis.groupby("amount_band", observed=False).agg(orders=("order_id", "size"), revenue=("amount", "sum"))
print(band_summary)
assert band_summary["orders"].tolist() == [3, 1, 1]
assert analysis.loc[analysis["order_id"].eq(108), "amount_band"].iloc[0] == "Low"
assert analysis["amount_band"].notna().all()Expected output
orders revenue
amount_band
Low 3 45.0
Medium 1 30.0
High 1 50.0Compare daily totals and moving averages 🔝
The accepted sample has observations on September 1, 2 and 4. The full sample confirms no accepted orders on September 3, so the daily accepted-order report uses zero for that day. In a real extract, confirm coverage before interpreting an empty day as zero. A two-day rolling mean then includes that zero.
daily = analysis.set_index("date")["amount"].sort_index().resample("D").sum(min_count=1)
daily_report = daily.fillna(0).rename("revenue").to_frame()
daily_report["two_day_mean"] = daily_report["revenue"].rolling(2, min_periods=2).mean()
print(daily_report)
assert daily_report["revenue"].tolist() == [40, 15, 0, 70]
assert daily_report["revenue"].sum() == 125
assert daily_report["two_day_mean"].iloc[-1] == 35Expected output
revenue two_day_mean
date
2026-09-01 40.0 NaN
2026-09-02 15.0 27.5
2026-09-03 0.0 7.5
2026-09-04 70.0 35.0Reshape the same accepted population 🔝
A pivot table aggregates product revenue by date. Because accepted values are known, an absent product-date combination is displayed as zero in this report. Verify that the reshaped total agrees with the source.
matrix = analysis.pivot_table(index="date", columns="product", values="amount", aggfunc="sum", fill_value=0, observed=True)
print(matrix)
assert matrix.to_numpy().sum() == 125Expected output
product Book Notebook Pen
date
2026-09-01 30.0 0.0 10.0
2026-09-02 0.0 0.0 15.0
2026-09-04 20.0 50.0 0.0Export a checked summary 🔝
Convert index labels to columns for a simple CSV. Recheck row counts and totals before saving. Use to_excel(), to_html(), to_json() or to_dict() when another output format is required.
export = product_summary.reset_index()
print(export.to_csv(index=False).rstrip())
assert export["orders"].sum() == len(analysis)
assert export["revenue"].sum() == 125
# Optional Colab export:
# export.to_csv("product_analysis.csv", index=False)Expected output
product,orders,known_amounts,revenue,mean_order
Book,2,2,50.0,25.0
Notebook,1,1,50.0,50.0
Pen,2,2,25.0,12.5Exercise: add an order and predict the report 🔝
Add a paid Pen order dated September 4 with amount 5. Predict the accepted order count, revenue, mean amount and Pen revenue before running the solution. Do not edit the existing raw table in place.
Exercise solution 🔝
The extra order changes the population, so recompute every dependent summary from the extended data.
extra = pd.DataFrame({"order_id": [109], "date": ["2026-09-04"], "product": ["Pen"], "status": ["paid"], "amount": ["5"]})
extended = pd.concat([raw, extra], ignore_index=True)
extended["date"] = pd.to_datetime(extended["date"], format="%Y-%m-%d", errors="coerce")
extended["amount"] = pd.to_numeric(extended["amount"], errors="coerce").astype("Float64")
keep = extended["status"].eq("paid") & extended["date"].notna() & extended["amount"].ge(0).fillna(False)
answer = extended.loc[keep].copy()
print("Accepted orders:", len(answer))
print("Revenue:", answer["amount"].sum())
print("Mean amount:", round(float(answer["amount"].mean()), 2))
print("Pen revenue:", answer.loc[answer["product"].eq("Pen"), "amount"].sum())
assert len(answer) == 6
assert answer["amount"].sum() == 130
assert answer.loc[answer["product"].eq("Pen"), "amount"].sum() == 30
assert len(raw) == 8Expected output
Accepted orders: 6
Revenue: 130.0
Mean amount: 21.67
Pen revenue: 30.0Check the interpretation before sharing results 🔝
Are counts and revenue interchangeable? No; report units explicitly. Should missing amounts become zero? Only with evidence that they mean zero; keep uncertain amounts in review. Why did totals change after a merge? Inspect repeated join keys and validate the expected cardinality. Why does a mean exclude some records? Many reductions skip missing values; report their available count. Can an outlier be deleted automatically? Investigate the source and define a rule before excluding it. Does a chart prove a relationship? It describes the selected data; causal conclusions require more evidence.
Additional DataFrame references 🔝
- DataFrame methods — arithmetic, label changes, aggregation, alignment, Boolean checks, concat() and function application with current Pandas
- tolist — From DataFrame to List
Combine row extracts and prepare report tables 🔝
Stack records when batches have the same meaning and schema. Match keys when adding fields from a lookup table. Check row counts, identifiers and totals after combining data.
- Add rows with concat() instead of append() — add lists, dictionaries, Series and DataFrames; filter incoming records and manage row indexes.
- merge() — match records using keys and validate join cardinality.
- reset_index() — prepare row labels for a report or another operation.
- drop() — remove selected row or column labels when preparing the final table; follow the Data Cleaning tutorial for removal rules.
concat() creates a combined table; it does not validate business identifiers or remove duplicate records. Collect incoming rows into a batch and concatenate once.
Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change the sample orders and reconcile the analysis summaries. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue to Pandas Data Cleaning or the Pandas tutorial index.
References: Pandas summary statistics and merging and reshaping data.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials