Data Wrangling with pandas and NumPy
Turn messy, scattered data into clean, analysis-ready tables: vectorised NumPy, pandas' index and dtypes, missing data, validated merges, reshaping and group operations.
Intermediate · Data Science · about 15 hours · 5 modules · certificate on completion
Taught by Davaadorjì Gantulàg, Tiàre Teriifaàtia, AI Learning Companions.
About this course
Real data rarely arrives ready to analyse. It comes in several files that don't quite agree, with gaps, codes typed three ways, values in the wrong units and rows that repeat. Data wrangling is the work of turning it into one clean, trustworthy table, and this course teaches it with the two libraries that do it in Python: NumPy and pandas. You'll start beneath pandas, with NumPy's arrays: computing a vegetation index for every pixel of a satellite tile at once, masking clouds, and using broadcasting, reshaping and views. Then you'll learn how pandas' Series and DataFrames really work: the index and the alignment it brings, read_csv options for awkward files, dtypes that save memory and keep data honest, and how Copy-on-Write in pandas 3 changes the way you modify data.
The heart of the course is messy data. You'll clean a reef monitoring export with missing-value markers, sentinels, duplicates and impossible values, combine livestock censuses with district data and satellite passes using merges you validate and audit, reshape between long and wide, and summarise two years of rangeland monitoring with groupby, transform, pivot tables and crosstabs. Every lab's data is made up for the course and comes with it, and every lab prints results you can check exactly. The capstone brings it all together: two overlapping exports from an air-quality network, cleaned, merged with station and weather data, gap-filled and summarised into an analysis-ready dataset, with a cleaning log and checks that would catch each problem if it returned.
What you'll learn
- Process arrays with vectorised NumPy operations, broadcasting and masks, and tell views from copies
- Build, load and index Series and DataFrames, using alignment and Copy-on-Write to your advantage
- Find and handle missing data, sentinels, duplicates and impossible values, and document every decision
- Merge, join and concatenate datasets with validated keys, and reshape between long and wide forms
- Summarise data with groupby, named aggregation, transform, filter, pivot tables and crosstabs
Skills you'll gain
NumPy, Vectorisation and broadcasting, pandas, Data cleaning, Missing data, Merging and joining, Reshaping data, groupby and aggregation, Pivot tables, Data validation
Tools
Python 3, NumPy, pandas, JupyterLab
Before you start
Python Foundations for Data Science, or the same Python: functions, loops, dictionaries and comprehensions. Data Analysis with Python and pandas helps but isn't required: this course goes further into NumPy, pandas' index and types, messy cleaning, validated merges, reshaping and group operations. You need Python 3 with NumPy and pandas 2.2 or later (pandas 3 differences are noted), and JupyterLab if you like notebooks. Every dataset comes with the labs.
Modules
Introduction to NumPy
Work with NumPy's n-dimensional arrays: how they store data, vectorised operations and universal functions, broadcasting between shapes, and indexing that gives views or copies, on a satellite image.
- The ndarray: shape, dtype and memory (12 min)
- Vectorised operations and universal functions (12 min)
- Broadcasting: arrays of different shapes (12 min)
- Indexing, slicing, views and copies (12 min)
- Hands-on lab: Process a satellite tile with NumPy
Pandas Data Structures
Understand pandas' Series and DataFrame: values with an index, the alignment the index brings, building and loading tables from dictionaries and messy files, and selecting data safely under Copy-on-Write.
- Series: values with an index (10 min)
- The index: alignment and fast look-ups (12 min)
- DataFrames: build, load and inspect (12 min)
- Selecting data safely: loc, iloc and Copy-on-Write (12 min)
- Hands-on lab: Build, align and load reef survey tables
Data Cleaning and Preparation
Find and handle missing data, sentinel values and impossible readings, fill gaps by group and over time, and transform messy text, units and categories into clean, typed columns you can trust.
- Missing data: NaN, NA and sentinels (12 min)
- Dropping and filling: dropna, fillna and interpolate (12 min)
- Transforming values: text, units and categories (12 min)
- Duplicates, impossible values and validation (10 min)
- Hands-on lab: Clean a reef monitoring export
Data Wrangling: Join, Combine, and Reshape
Combine tables with merges you validate and audit, stack and patch data with concat and combine_first, match readings in time with merge_asof, and reshape between long and wide with melt, pivot, stack and unstack.
- Merging on keys: relationships and validation (12 min)
- Concatenating, joining on the index and patching gaps (12 min)
- Reshaping: melt, pivot, stack and unstack (12 min)
- Hands-on lab: Combine and reshape livestock and pasture data
Data Aggregation and Group Operations
Master split-apply-combine with groupby: aggregate with built-in, named and custom functions, transform values within groups, filter whole groups, and summarise in two dimensions with pivot tables and crosstabs.
- Split-apply-combine with groupby (12 min)
- Aggregation: built-in, named and custom (12 min)
- transform, filter and apply (12 min)
- Pivot tables and crosstabs (10 min)
- Hands-on lab: Summarise two years of rangeland monitoring
Project: Real-World Dataset Cleaning Capstone: an air-quality network
A city's air-quality network (made-up data) has six stations measuring PM2.5, PM10 and NO2 every day. You have the first quarter of 2025 in two overlapping exports (readings_part1.csv and readings_part2.csv), the station list (stations.csv) and the daily weather (weather.csv). They're messy: station codes are typed several ways (aq01, AQ-01, ' AQ02'), dates are written 2025-01-14 or 14/01/2025, -999 means no reading, <5 means below the detection limit, a few rows are in mg/m3 instead of µg/m3, some station-days are absent and the overlap repeats rows. Build a pandas pipeline that turns them into one clean table with one row per station and day, then answer the city's questions. The rules: normalise the codes; parse each date format explicitly rather than relying on dayfirst; remove duplicates; treat -999 as missing and <5 as 2.5, half the limit; convert mg/m3 to µg/m3; validate the merges with the stations and the weather; reindex to every station and day from 1 January to 31 March; and fill gaps with interpolate(limit=2, limit_area='inside') within each station, flagging every filled value. Then report the mean PM2.5 by station and month, the days over the city's alert level of 25 µg/m3 (made up for this project) by station, the differences between roadside, industrial and background sites, and calm days (wind under 2 m/s) against windy ones. Check yourself: 553 rows read, 29 exact duplicates, 6 values below the detection limit, 9 rows in mg/m3, 16 of the 540 station-days absent, 1 PM2.5 value still missing after interpolation, and a January mean of 28.5 µg/m3 at AQ01.
Part of
The products this course names are trademarks of their owners, who haven't made or endorsed it.