\( \newcommand{\vect}[1]{\boldsymbol{\mathbf{#1}}} \newcommand{\mat}[1]{\boldsymbol{\mathbf{#1}}} \newcommand{\R}{\mathbb{R}} \newcommand{\E}{\mathbb{E}} \newcommand{\Prob}{\mathbb{P}} \newcommand{\T}{^{\top}} \newcommand{\norm}[1]{\lVert #1 \rVert} \newcommand{\abs}[1]{\lvert #1 \rvert} \newcommand{\ihat}{\mkern1mu\hat{\boldsymbol{\imath}}\mkern2mu} \newcommand{\jhat}{\mkern1mu\hat{\boldsymbol{\jmath}}\mkern2mu} \newcommand{\pd}[2]{\frac{\partial #1}{\partial #2}} \newcommand{\argmax}{\operatorname*{arg\,max}} \newcommand{\argmin}{\operatorname*{arg\,min}} \newcommand{\cdata}[1]{\class{c-data}{#1}} \newcommand{\cmodel}[1]{\class{c-model}{#1}} \newcommand{\ctruth}[1]{\class{c-truth}{#1}} \newcommand{\cgrad}[1]{\class{c-grad}{#1}} \newcommand{\closs}[1]{\class{c-loss}{#1}} \newcommand{\cparam}[1]{\class{c-param}{#1}} \newcommand{\csecond}[1]{\class{c-second}{#1}} \renewcommand{\,}{\mkern3mu} \)

P02.5 · Cleaning, reshaping and joining

A free, illustrated module of 4 lessons and one interactive in P02 Scientific Python: NumPy, pandas and Plotting: Reading a file and fixing its types; Missing values and duplicates; Wide to long and back: melt and pivot; Joining tables on a key: merge.

P02.5 L1 · Core 4 lessons · about 100 min Live · in review

Part of P02 Scientific Python: NumPy, pandas and Plotting in the Programming and Data track.

Real data arrive as files with typing slips, holes and repeats, in a shape chosen for reading rather than for computing, and spread over several tables. These four lessons read a logger’s CSV file and repair a column that one entry turned into text, answer missing values and duplicate rows, reshape the wide table of temperatures into the long table of readings and back, and join every reading to its station’s details on a key.

What you need first. Reading CSV files by hand (P01.7.4), dictionaries and sets (P01.6.3), NumPy arrays and their dtypes (P02.1), and DataFrames and Series with loc, masks and alignment (P02.4).

Lessons

1. Reading a file and fixing its types P02.5.1 25 min

   Read a weather logger’s CSV file with pd.read_csv, check what arrived with dtypes, info and a count of missing values, then repair a column that one entry such as ‘27 C’ has turned into text, without losing a reading on the way.

2. Missing values and duplicates P02.5.2 25 min

   NaN is a float that is not equal to itself: see how it spreads through NumPy and how pandas skips it, count it with isna, answer it with dropna, fillna or ffill, and find the rows that two overlapping files repeat with duplicated and drop_duplicates.

3. Wide to long and back: melt and pivot P02.5.3 25 min

   Tidy data puts each variable in a column and each observation in a row. Turn the wide table of 4 days by 3 stations into the 12 rows of readings with two loops and then with melt, find where every cell lands, and go back with pivot, which sorts the days and refuses a repeated pair.

4. Joining tables on a key: merge P02.5.4 25 min

   Attach each reading’s station details by matching rows on the key station: first by hand with a dict and one pass, then with pd.merge. Predict the rows of inner, left, right and outer joins, read the _merge indicator, and catch the repeated key that multiplies rows.

The interactive

P02.5-X1 · Join matcher. A table of readings and a table of stations, joined on the key station. Choose inner, left, right or outer, step through the match and watch the result grow row by row; change a key to see unmatched rows turn into NaN, or list a station twice and watch the rows multiply. It lives in Joining tables on a key: merge.