pandas for Spreadsheet Analysts: Clean, Join, Reconcile

What's inside

1. Level 1: Your First Notebook: Series and DataFramesFree
   Set up a Jupyter or VS Code notebook, import pandas and check its version, then build a small Series and DataFrame by hand and see how they map to a spreadsheet's columns, rows and row labels.
2. Level 2: Loading CSV and Excel FilesFree
   Read the provided CSV and Excel files into DataFrames with read_csv and read_excel, using the options that matter for real exports: separators, header rows, sheet names, skipped rows and encodings.
3. Level 3: Inspecting What Loaded
   Profile a newly loaded DataFrame with shape, head, tail, info, describe and value_counts, so you catch a wrong header, a misread column or an unexpected category before you analyse anything.
4. Level 4: Selecting Rows and Columns with loc and iloc
   Pick out exactly the rows and columns you need, by label with loc and by position with iloc, and see why copy-on-write in pandas 3.0 makes chained assignment like df[...][...] = value stop working.
5. Level 5: Filtering and Sorting
   Replace spreadsheet AutoFilter and sorting with boolean masks, isin, between, query and sort_values, combining conditions correctly with &, | and ~.
6. Level 6: Handling Missing Values
   Find, count and deal with missing data using isna, fillna and dropna, and decide case by case whether a blank means zero, unknown or a row that should be excluded.
7. Level 7: Data Types and Conversions
   Fix columns that loaded with the wrong type using astype, to_numeric and category, and learn how the default str dtype in pandas 3.0 replaces object for text columns.
8. Level 8: Cleaning Text with .str Methods
   Standardise messy text columns such as names, codes and currency strings with the vectorised .str methods, so that grouping and joining work on clean keys.
9. Level 9: Dates and Time Zones
   Parse text and Excel dates with to_datetime, pull out parts of a date with the .dt accessor, and handle time zones with tz_localize and tz_convert.
10. Level 10: Joining Tables with merge and concat
    Replace VLOOKUP and XLOOKUP with merge, stack tables with concat, and prove that each join did what you meant using validate and indicator.
11. Level 11: Grouping and Aggregation with groupby
    Summarise data the way SUMIFS and subtotals do, but at scale, with groupby, agg using named aggregations, and transform.
12. Level 12: Pivot Tables and Reshaping with melt
    Build spreadsheet-style pivot tables with pivot_table and crosstab, and turn wide month-per-column layouts into tidy long data with melt and back again with pivot.
13. Level 13: Rolling and Window Calculations
    Calculate running totals, moving averages, period-over-period change and month-level summaries with cumsum, shift, pct_change, rolling, expanding and resample.
14. Level 14: Plotting with pandas and matplotlib
    Make line, bar and histogram charts straight from a DataFrame with .plot, then finish them with matplotlib for titles, labels and saved image files.
15. Level 15: Writing Results to Excel and CSV
    Export clean results with to_csv and to_excel, write several sheets to one workbook with ExcelWriter, and avoid common problems with indexes, encodings and date formats.
16. Level 16: Reconciling Totals to Control Figures
    Prove that your analysis is correct by checking row counts, totals and key sums against known control figures at each step, and by tracing any difference back to the rows that cause it.
17. Level 17: Repeatable Analysis, Performance and Scaling Up
    Turn a notebook into a repeatable, parameterised analysis made of functions, then learn the performance basics in pandas and when Polars or DuckDB would be the better tool.
18. Level 18: Writing pandas with an AI Assistant, and Checking It
    Teaches how to get useful pandas code from an AI assistant and how to prove it is right before trusting its numbers.
19. Level 19: Final Challenge: End-to-End Reconciled Analysis Exam
    Combine the whole course in one repeatable analysis: load, inspect, clean, join, summarise, chart and export a messy multi-file dataset, using an AI assistant to draft code that you then verify, and reconcile every output to the given control figures.

Access

The first 2 levels are free with a free account. Every level, the podcast edition and the AI tutor come with All Access at £4.99/month or any Creator plan — see pricing.

Start free →