{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Managing missing data with pandas\n", "\n", "Missing data often occurs in data analysis. pandas simplifies working with missing data as much as possible. For example, all [descriptive statistics](../workspace/pandas/descriptive-statistics.ipynb) of pandas objects exclude missing data by default. pandas uses the floating point value `NaN` (*Not a Number*) to represent missing data for numerical data.\n", "\n", "pandas has adopted a convention borrowed from the R programming language and refers to missing data as `NA`, which stands for not available. In statistical applications, `NA` data can be either data that does not exist or data that exists but has not been observed (for example, due to problems with data collection). Python’s [None](https://docs.python.org/3/c-api/none.html) object is also treated as `NA` in non-numeric arrays.\n", "\n", "Methods for handling `NA` objects:\n", "\n", "Argument | Description\n", ":------- | :----------\n", "`dropna` | filters axis labels based on whether values for individual labels have missing data, applying different thresholds for the amount of missing data to be tolerated.\n", "`fillna` | fills missing data with a value or with an interpolation method such as `ffill` or `bfill`.\n", "`isna` | returns Boolean values indicating which values are missing/`NA`.\n", "`notna` | negates `isna` and returns `True` for non-`NA` values and `False` for `NA` values." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This notebook presents some ways to manage missing data with pandas DataFrames. For more information, see the Pandas documentation: [Working with missing data](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html) and [Missing data cookbook](https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html#cookbook-missing-data).\n", "\n", "> **See also:**\n", "> \n", "> * [Dora](https://github.com/NathanEpstein/Dora)\n", "> * [Badfish](https://github.com/harshnisar/badfish)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:28.177257Z", "iopub.status.busy": "2026-05-18T17:52:28.177020Z", "iopub.status.idle": "2026-05-18T17:52:28.391575Z", "shell.execute_reply": "2026-05-18T17:52:28.391252Z", "shell.execute_reply.started": "2026-05-18T17:52:28.177239Z" } }, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:28.392097Z", "iopub.status.busy": "2026-05-18T17:52:28.391955Z", "iopub.status.idle": "2026-05-18T17:52:30.206909Z", "shell.execute_reply": "2026-05-18T17:52:30.206435Z", "shell.execute_reply.started": "2026-05-18T17:52:28.392083Z" } }, "outputs": [], "source": [ "df = pd.read_csv(\n", " \"https://raw.githubusercontent.com/kjam/data-cleaning-101/master/data/iot_example_with_nulls.csv\",\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Checking the data\n", "\n", "When cleaning data for analysis, it is often important to analyse the missing data itself in order to identify problems with data collection or potential biases in the data due to the missing data. First, let’s display the first 20 data records:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:30.207473Z", "iopub.status.busy": "2026-05-18T17:52:30.207381Z", "iopub.status.idle": "2026-05-18T17:52:30.217602Z", "shell.execute_reply": "2026-05-18T17:52:30.217352Z", "shell.execute_reply.started": "2026-05-18T17:52:30.207465Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
timestampusernametemperatureheartratebuildlatestnote
02017-01-01T12:00:23michaelsmith12.0674e6a7805-8faa-2768-6ef6-eb3198b483ac0.0interval
12017-01-01T12:01:09kharrison6.0787256b7b0-e502-f576-62ec-ed73533c9c840.0wake
22017-01-01T12:01:34smithadam5.0899226c94b-bb4b-a6c8-8e02-cb42b53e9c900.0NaN
32017-01-01T12:02:09eddierodriguez28.076NaN0.0update
42017-01-01T12:02:36kenneth9429.062122f1c6a-403c-2221-6ed1-b5caa08f11e0NaNNaN
52017-01-01T12:03:04bryanttodd13.0860897dbe5-9c5b-71ca-73a1-7586959ca1980.0interval
62017-01-01T12:03:51andrea9817.0811c07ab9b-5f66-137d-a74f-921a41001f4e1.0NaN
72017-01-01T12:04:35scott2816.0767a60219f-6621-e548-180e-ca69624f9824NaNinterval
82017-01-01T12:05:05hillpamela5.082a8b87754-a162-da28-2527-4bce4b3d41911.0NaN
92017-01-01T12:05:41moorejeffrey25.063585f1a3c-0679-0ffe-9132-508933c703430.0wake
102017-01-01T12:06:21njohnsonNaN63e09b6001-125d-51cf-9c3f-9cb686c19d02NaNNaN
112017-01-01T12:06:53gsutton29.080607c9f6e-2bdf-a606-6d16-3004c69584361.0update
122017-01-01T12:07:41jessica4822.08303e1a07b-3e14-412c-3a69-6b45bc79f81cNaNupdate
132017-01-01T12:08:08hornjohn16.073NaN0.0interval
142017-01-01T12:08:35gramirez24.073NaN0.0wake
152017-01-01T12:09:05schmidtsamuelNaN78b9890c1e-79d5-8979-63ae-6c08a4cd476a0.0NaN
162017-01-01T12:09:48derrick47NaN63b60bd7de-4057-8a85-f806-e6eec1350338NaNinterval
172017-01-01T12:10:23beckercharles12.061b1dacc73-c8b7-1d7d-ee02-578da781a71e0.0test
182017-01-01T12:10:57ipittman11.0691aef7db8-9a3e-7dc9-d7a5-781ec0efd200NaNuser
192017-01-01T12:11:34sabrina6522.0828075d058-7dae-e2ec-d47e-58ec6d26899b1.0NaN
\n", "
" ], "text/plain": [ " timestamp username temperature heartrate \\\n", "0 2017-01-01T12:00:23 michaelsmith 12.0 67 \n", "1 2017-01-01T12:01:09 kharrison 6.0 78 \n", "2 2017-01-01T12:01:34 smithadam 5.0 89 \n", "3 2017-01-01T12:02:09 eddierodriguez 28.0 76 \n", "4 2017-01-01T12:02:36 kenneth94 29.0 62 \n", "5 2017-01-01T12:03:04 bryanttodd 13.0 86 \n", "6 2017-01-01T12:03:51 andrea98 17.0 81 \n", "7 2017-01-01T12:04:35 scott28 16.0 76 \n", "8 2017-01-01T12:05:05 hillpamela 5.0 82 \n", "9 2017-01-01T12:05:41 moorejeffrey 25.0 63 \n", "10 2017-01-01T12:06:21 njohnson NaN 63 \n", "11 2017-01-01T12:06:53 gsutton 29.0 80 \n", "12 2017-01-01T12:07:41 jessica48 22.0 83 \n", "13 2017-01-01T12:08:08 hornjohn 16.0 73 \n", "14 2017-01-01T12:08:35 gramirez 24.0 73 \n", "15 2017-01-01T12:09:05 schmidtsamuel NaN 78 \n", "16 2017-01-01T12:09:48 derrick47 NaN 63 \n", "17 2017-01-01T12:10:23 beckercharles 12.0 61 \n", "18 2017-01-01T12:10:57 ipittman 11.0 69 \n", "19 2017-01-01T12:11:34 sabrina65 22.0 82 \n", "\n", " build latest note \n", "0 4e6a7805-8faa-2768-6ef6-eb3198b483ac 0.0 interval \n", "1 7256b7b0-e502-f576-62ec-ed73533c9c84 0.0 wake \n", "2 9226c94b-bb4b-a6c8-8e02-cb42b53e9c90 0.0 NaN \n", "3 NaN 0.0 update \n", "4 122f1c6a-403c-2221-6ed1-b5caa08f11e0 NaN NaN \n", "5 0897dbe5-9c5b-71ca-73a1-7586959ca198 0.0 interval \n", "6 1c07ab9b-5f66-137d-a74f-921a41001f4e 1.0 NaN \n", "7 7a60219f-6621-e548-180e-ca69624f9824 NaN interval \n", "8 a8b87754-a162-da28-2527-4bce4b3d4191 1.0 NaN \n", "9 585f1a3c-0679-0ffe-9132-508933c70343 0.0 wake \n", "10 e09b6001-125d-51cf-9c3f-9cb686c19d02 NaN NaN \n", "11 607c9f6e-2bdf-a606-6d16-3004c6958436 1.0 update \n", "12 03e1a07b-3e14-412c-3a69-6b45bc79f81c NaN update \n", "13 NaN 0.0 interval \n", "14 NaN 0.0 wake \n", "15 b9890c1e-79d5-8979-63ae-6c08a4cd476a 0.0 NaN \n", "16 b60bd7de-4057-8a85-f806-e6eec1350338 NaN interval \n", "17 b1dacc73-c8b7-1d7d-ee02-578da781a71e 0.0 test \n", "18 1aef7db8-9a3e-7dc9-d7a5-781ec0efd200 NaN user \n", "19 8075d058-7dae-e2ec-d47e-58ec6d26899b 1.0 NaN " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head(20)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Then let's take a look at what data type the columns are:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:30.218496Z", "iopub.status.busy": "2026-05-18T17:52:30.218351Z", "iopub.status.idle": "2026-05-18T17:52:30.221038Z", "shell.execute_reply": "2026-05-18T17:52:30.220788Z", "shell.execute_reply.started": "2026-05-18T17:52:30.218488Z" } }, "outputs": [ { "data": { "text/plain": [ "timestamp object\n", "username object\n", "temperature float64\n", "heartrate int64\n", "build object\n", "latest float64\n", "note object\n", "dtype: object" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "With `pandas.api.types`, we can also automatically check whether the data types meet our expectations:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:30.221633Z", "iopub.status.busy": "2026-05-18T17:52:30.221560Z", "iopub.status.idle": "2026-05-18T17:52:30.321934Z", "shell.execute_reply": "2026-05-18T17:52:30.320683Z", "shell.execute_reply.started": "2026-05-18T17:52:30.221626Z" } }, "outputs": [ { "ename": "ValueError", "evalue": "Expected datetimes in ['timestamp'].", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[5], line 13\u001b[0m\n\u001b[1;32m 9\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28mall\u001b[39m(\n\u001b[1;32m 10\u001b[0m ptypes\u001b[38;5;241m.\u001b[39mis_datetime64_any_dtype(df[col]) \u001b[38;5;28;01mfor\u001b[39;00m col \u001b[38;5;129;01min\u001b[39;00m expected_datetimes\n\u001b[1;32m 11\u001b[0m ):\n\u001b[1;32m 12\u001b[0m msg \u001b[38;5;241m=\u001b[39m \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mExpected datetimes in \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mexpected_datetimes\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m.\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m---> 13\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(msg)\n\u001b[1;32m 14\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28mall\u001b[39m(ptypes\u001b[38;5;241m.\u001b[39mis_object_dtype(df[col]) \u001b[38;5;28;01mfor\u001b[39;00m col \u001b[38;5;129;01min\u001b[39;00m expected_objects):\n\u001b[1;32m 15\u001b[0m msg \u001b[38;5;241m=\u001b[39m \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mExpected objects in \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mexpected_objects\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m.\u001b[39m\u001b[38;5;124m\"\u001b[39m\n", "\u001b[0;31mValueError\u001b[0m: Expected datetimes in ['timestamp']." ] } ], "source": [ "import pandas.api.types as ptypes\n", "\n", "\n", "expected_datetimes = [\"timestamp\"]\n", "expected_objects = [\"username\", \"build\", \"note\"]\n", "expected_floats = [\"temperature\", \"latest\"]\n", "expected_ints = [\"heartrate\"]\n", "\n", "if not all(\n", " ptypes.is_datetime64_any_dtype(df[col]) for col in expected_datetimes\n", "):\n", " msg = f\"Expected datetimes in {expected_datetimes}.\"\n", " raise ValueError(msg)\n", "if not all(ptypes.is_object_dtype(df[col]) for col in expected_objects):\n", " msg = f\"Expected objects in {expected_objects}.\"\n", " raise ValueError(msg)\n", "if not all(ptypes.is_float_dtype(df[col]) for col in expected_floats):\n", " msg = f\"Expected floats in {expected_floats}\"\n", " raise ValueError(msg)\n", "if not all(ptypes.is_int64_dtype(df[col]) for col in expected_ints):\n", " msg = f\"Expected ints in {expected_ints}\"\n", " raise ValueError(msg)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can convert the `timestamp` column to the appropriate type, see also [Converting dtype](../workspace/pandas/convert-dtypes.ipynb):" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:39.915755Z", "iopub.status.busy": "2026-05-18T17:52:39.915178Z", "iopub.status.idle": "2026-05-18T17:52:39.961009Z", "shell.execute_reply": "2026-05-18T17:52:39.960581Z", "shell.execute_reply.started": "2026-05-18T17:52:39.915713Z" } }, "outputs": [], "source": [ "df[\"timestamp\"] = df[\"timestamp\"].astype(\"datetime64[ns]\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also display the values and their frequency, for example for the column `note`:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:40.677030Z", "iopub.status.busy": "2026-05-18T17:52:40.676749Z", "iopub.status.idle": "2026-05-18T17:52:40.693630Z", "shell.execute_reply": "2026-05-18T17:52:40.693187Z", "shell.execute_reply.started": "2026-05-18T17:52:40.677012Z" } }, "outputs": [ { "data": { "text/plain": [ "note\n", "wake 16496\n", "user 16416\n", "interval 16274\n", "sleep 16226\n", "update 16213\n", "test 16068\n", "Name: count, dtype: int64" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.note.value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Removing all zero values (including `n/a`)\n", "\n", "### 2.1 … with `pandas.read_csv`\n", "\n", "[pandas.read_csv](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) normally filters out many values that it recognises as `NA` or `NaN`. Additional values can be specified with `na_values`." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:41.366354Z", "iopub.status.busy": "2026-05-18T17:52:41.365922Z", "iopub.status.idle": "2026-05-18T17:52:43.177637Z", "shell.execute_reply": "2026-05-18T17:52:43.177308Z", "shell.execute_reply.started": "2026-05-18T17:52:41.366326Z" } }, "outputs": [], "source": [ "df = pd.read_csv(\n", " \"https://raw.githubusercontent.com/kjam/data-cleaning-101/master/data/iot_example_with_nulls.csv\",\n", " na_values=[\"n/a\"],\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.2 … with `pandas.DataFrame.dropna`\n", "\n", "Missing values can be deleted using [pandas.DataFrame.dropna](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.dropna.html)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To analyse the scope of the deletions, we display the scope of the DataFrame before and after deletion using [pandas.DataFrame.shape](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.shape.html):" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:43.178348Z", "iopub.status.busy": "2026-05-18T17:52:43.178268Z", "iopub.status.idle": "2026-05-18T17:52:43.180805Z", "shell.execute_reply": "2026-05-18T17:52:43.180580Z", "shell.execute_reply.started": "2026-05-18T17:52:43.178338Z" } }, "outputs": [ { "data": { "text/plain": [ "(146397, 7)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.shape" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:43.181297Z", "iopub.status.busy": "2026-05-18T17:52:43.181126Z", "iopub.status.idle": "2026-05-18T17:52:43.197792Z", "shell.execute_reply": "2026-05-18T17:52:43.197445Z", "shell.execute_reply.started": "2026-05-18T17:52:43.181286Z" } }, "outputs": [ { "data": { "text/plain": [ "(46116, 7)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.dropna().shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Using `pandas.DataFrame.dropna` would therefore result in the loss of more than two-thirds of the data records." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the next attempt, we want to analyse whether entire rows or columns contain no data. Using `how=\"all\"`, rows or columns that contain no values are removed; `axis=1` specifies that empty rows should be removed." ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:43.410324Z", "iopub.status.busy": "2026-05-18T17:52:43.409937Z", "iopub.status.idle": "2026-05-18T17:52:43.440111Z", "shell.execute_reply": "2026-05-18T17:52:43.439696Z", "shell.execute_reply.started": "2026-05-18T17:52:43.410288Z" } }, "outputs": [ { "data": { "text/plain": [ "(146397, 7)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.dropna(how=\"all\", axis=1).shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This does not get us any further either." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.3 Find all columns containing the most data" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:44.296259Z", "iopub.status.busy": "2026-05-18T17:52:44.295917Z", "iopub.status.idle": "2026-05-18T17:52:44.329711Z", "shell.execute_reply": "2026-05-18T17:52:44.329462Z", "shell.execute_reply.started": "2026-05-18T17:52:44.296235Z" } }, "outputs": [ { "data": { "text/plain": [ "['timestamp', 'username', 'heartrate']" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "list(df.dropna(thresh=int(df.shape[0] * 0.9), axis=1).columns)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`thresh` requires a certain number of NA values, in our case 90%, before `axis=1` hides a column." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.4 Find all columns with missing data\n", "\n", "With [pandas.DataFrame.isna](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isna.html), we can find missing values, and with [pandas.DataFrame.any](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.any.html), we can find out whether an element is valid, usually across a column." ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:45.153049Z", "iopub.status.busy": "2026-05-18T17:52:45.152355Z", "iopub.status.idle": "2026-05-18T17:52:45.182317Z", "shell.execute_reply": "2026-05-18T17:52:45.181889Z", "shell.execute_reply.started": "2026-05-18T17:52:45.153016Z" } }, "outputs": [], "source": [ "incomplete_columns = list(df.columns[df.isna().any()])" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:45.452936Z", "iopub.status.busy": "2026-05-18T17:52:45.452650Z", "iopub.status.idle": "2026-05-18T17:52:45.457627Z", "shell.execute_reply": "2026-05-18T17:52:45.456736Z", "shell.execute_reply.started": "2026-05-18T17:52:45.452917Z" } }, "outputs": [ { "data": { "text/plain": [ "['temperature', 'build', 'latest', 'note']" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "incomplete_columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "With `num_missing`, we can now display the number of missing values per column:" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:46.064913Z", "iopub.status.busy": "2026-05-18T17:52:46.064604Z", "iopub.status.idle": "2026-05-18T17:52:46.096237Z", "shell.execute_reply": "2026-05-18T17:52:46.095863Z", "shell.execute_reply.started": "2026-05-18T17:52:46.064894Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "number missing for column temperature: 32357\n", "number missing for column build: 32350\n", "number missing for column latest: 32298\n", "number missing for column note: 48704\n" ] } ], "source": [ "for col in incomplete_columns:\n", " num_missing = df[df[col].isna()].shape[0]\n", " print(f\"number missing for column {col}: {num_missing}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also display these values as percentages:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:46.952113Z", "iopub.status.busy": "2026-05-18T17:52:46.951841Z", "iopub.status.idle": "2026-05-18T17:52:46.985386Z", "shell.execute_reply": "2026-05-18T17:52:46.985109Z", "shell.execute_reply.started": "2026-05-18T17:52:46.952095Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "percent missing for column temperature: 0.22102228870810195\n", "percent missing for column build: 0.22097447352063226\n", "percent missing for column latest: 0.22061927498514314\n", "percent missing for column note: 0.332684412931959\n" ] } ], "source": [ "for col in incomplete_columns:\n", " percent_missing = df[df[col].isna()].shape[0] / df.shape[0]\n", " print(f\"percent missing for column {col}: {percent_missing}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.5 Replacing missing data\n", "\n", "To verify our changes in the `latest` column, we use [pandas.Series.value_counts](https://pandas.pydata.org/docs/reference/api/pandas.Series.value_counts.html). The method returns a series containing the number of unique values:" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:47.692445Z", "iopub.status.busy": "2026-05-18T17:52:47.692129Z", "iopub.status.idle": "2026-05-18T17:52:47.699170Z", "shell.execute_reply": "2026-05-18T17:52:47.698761Z", "shell.execute_reply.started": "2026-05-18T17:52:47.692425Z" } }, "outputs": [ { "data": { "text/plain": [ "temperature\n", "29.0 4688\n", "26.0 4674\n", "16.0 4656\n", "28.0 4648\n", "10.0 4632\n", "13.0 4629\n", "7.0 4624\n", "27.0 4621\n", "21.0 4585\n", "9.0 4576\n", "23.0 4571\n", "5.0 4568\n", "6.0 4563\n", "19.0 4561\n", "18.0 4557\n", "17.0 4556\n", "11.0 4529\n", "15.0 4525\n", "8.0 4486\n", "12.0 4484\n", "20.0 4473\n", "25.0 4469\n", "14.0 4464\n", "22.0 4455\n", "24.0 4446\n", "Name: count, dtype: int64" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.temperature.value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we replace the missing values in the `temperature` column with the mean value rounded to one decimal place using [DataFrame.fillna](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.fillna.html):" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:48.586284Z", "iopub.status.busy": "2026-05-18T17:52:48.585740Z", "iopub.status.idle": "2026-05-18T17:52:48.597784Z", "shell.execute_reply": "2026-05-18T17:52:48.597068Z", "shell.execute_reply.started": "2026-05-18T17:52:48.586243Z" } }, "outputs": [ { "data": { "text/plain": [ "temperature\n", "17.0 36913\n", "29.0 4688\n", "26.0 4674\n", "16.0 4656\n", "28.0 4648\n", "10.0 4632\n", "13.0 4629\n", "7.0 4624\n", "27.0 4621\n", "21.0 4585\n", "9.0 4576\n", "23.0 4571\n", "5.0 4568\n", "6.0 4563\n", "19.0 4561\n", "18.0 4557\n", "11.0 4529\n", "15.0 4525\n", "8.0 4486\n", "12.0 4484\n", "20.0 4473\n", "25.0 4469\n", "14.0 4464\n", "22.0 4455\n", "24.0 4446\n", "Name: count, dtype: int64" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "temp_mean = round(df.temperature.mean(), 1)\n", "fill_mean = df.temperature.fillna(temp_mean)\n", "fill_mean.value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.6 Replacing missing data with `backfill`\n", "\n", "To ensure that the data records follow each other in chronological order, we first set the index for `timestamp` with [set_index](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.set_index.html):" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:49.492973Z", "iopub.status.busy": "2026-05-18T17:52:49.492661Z", "iopub.status.idle": "2026-05-18T17:52:49.502589Z", "shell.execute_reply": "2026-05-18T17:52:49.502102Z", "shell.execute_reply.started": "2026-05-18T17:52:49.492955Z" } }, "outputs": [], "source": [ "df = df.set_index(\"timestamp\")" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:49.893277Z", "iopub.status.busy": "2026-05-18T17:52:49.892907Z", "iopub.status.idle": "2026-05-18T17:52:49.909865Z", "shell.execute_reply": "2026-05-18T17:52:49.909377Z", "shell.execute_reply.started": "2026-05-18T17:52:49.893236Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
usernametemperatureheartratebuildlatestnote
timestamp
2017-01-01T12:00:23michaelsmith12.0674e6a7805-8faa-2768-6ef6-eb3198b483ac0.0interval
2017-01-01T12:01:09kharrison6.0787256b7b0-e502-f576-62ec-ed73533c9c840.0wake
2017-01-01T12:01:34smithadam5.0899226c94b-bb4b-a6c8-8e02-cb42b53e9c900.0NaN
2017-01-01T12:02:09eddierodriguez28.076NaN0.0update
2017-01-01T12:02:36kenneth9429.062122f1c6a-403c-2221-6ed1-b5caa08f11e0NaNNaN
2017-01-01T12:03:04bryanttodd13.0860897dbe5-9c5b-71ca-73a1-7586959ca1980.0interval
2017-01-01T12:03:51andrea9817.0811c07ab9b-5f66-137d-a74f-921a41001f4e1.0NaN
2017-01-01T12:04:35scott2816.0767a60219f-6621-e548-180e-ca69624f9824NaNinterval
2017-01-01T12:05:05hillpamela5.082a8b87754-a162-da28-2527-4bce4b3d41911.0NaN
2017-01-01T12:05:41moorejeffrey25.063585f1a3c-0679-0ffe-9132-508933c703430.0wake
2017-01-01T12:06:21njohnsonNaN63e09b6001-125d-51cf-9c3f-9cb686c19d02NaNNaN
2017-01-01T12:06:53gsutton29.080607c9f6e-2bdf-a606-6d16-3004c69584361.0update
2017-01-01T12:07:41jessica4822.08303e1a07b-3e14-412c-3a69-6b45bc79f81cNaNupdate
2017-01-01T12:08:08hornjohn16.073NaN0.0interval
2017-01-01T12:08:35gramirez24.073NaN0.0wake
2017-01-01T12:09:05schmidtsamuelNaN78b9890c1e-79d5-8979-63ae-6c08a4cd476a0.0NaN
2017-01-01T12:09:48derrick47NaN63b60bd7de-4057-8a85-f806-e6eec1350338NaNinterval
2017-01-01T12:10:23beckercharles12.061b1dacc73-c8b7-1d7d-ee02-578da781a71e0.0test
2017-01-01T12:10:57ipittman11.0691aef7db8-9a3e-7dc9-d7a5-781ec0efd200NaNuser
2017-01-01T12:11:34sabrina6522.0828075d058-7dae-e2ec-d47e-58ec6d26899b1.0NaN
\n", "
" ], "text/plain": [ " username temperature heartrate \\\n", "timestamp \n", "2017-01-01T12:00:23 michaelsmith 12.0 67 \n", "2017-01-01T12:01:09 kharrison 6.0 78 \n", "2017-01-01T12:01:34 smithadam 5.0 89 \n", "2017-01-01T12:02:09 eddierodriguez 28.0 76 \n", "2017-01-01T12:02:36 kenneth94 29.0 62 \n", "2017-01-01T12:03:04 bryanttodd 13.0 86 \n", "2017-01-01T12:03:51 andrea98 17.0 81 \n", "2017-01-01T12:04:35 scott28 16.0 76 \n", "2017-01-01T12:05:05 hillpamela 5.0 82 \n", "2017-01-01T12:05:41 moorejeffrey 25.0 63 \n", "2017-01-01T12:06:21 njohnson NaN 63 \n", "2017-01-01T12:06:53 gsutton 29.0 80 \n", "2017-01-01T12:07:41 jessica48 22.0 83 \n", "2017-01-01T12:08:08 hornjohn 16.0 73 \n", "2017-01-01T12:08:35 gramirez 24.0 73 \n", "2017-01-01T12:09:05 schmidtsamuel NaN 78 \n", "2017-01-01T12:09:48 derrick47 NaN 63 \n", "2017-01-01T12:10:23 beckercharles 12.0 61 \n", "2017-01-01T12:10:57 ipittman 11.0 69 \n", "2017-01-01T12:11:34 sabrina65 22.0 82 \n", "\n", " build latest note \n", "timestamp \n", "2017-01-01T12:00:23 4e6a7805-8faa-2768-6ef6-eb3198b483ac 0.0 interval \n", "2017-01-01T12:01:09 7256b7b0-e502-f576-62ec-ed73533c9c84 0.0 wake \n", "2017-01-01T12:01:34 9226c94b-bb4b-a6c8-8e02-cb42b53e9c90 0.0 NaN \n", "2017-01-01T12:02:09 NaN 0.0 update \n", "2017-01-01T12:02:36 122f1c6a-403c-2221-6ed1-b5caa08f11e0 NaN NaN \n", "2017-01-01T12:03:04 0897dbe5-9c5b-71ca-73a1-7586959ca198 0.0 interval \n", "2017-01-01T12:03:51 1c07ab9b-5f66-137d-a74f-921a41001f4e 1.0 NaN \n", "2017-01-01T12:04:35 7a60219f-6621-e548-180e-ca69624f9824 NaN interval \n", "2017-01-01T12:05:05 a8b87754-a162-da28-2527-4bce4b3d4191 1.0 NaN \n", "2017-01-01T12:05:41 585f1a3c-0679-0ffe-9132-508933c70343 0.0 wake \n", "2017-01-01T12:06:21 e09b6001-125d-51cf-9c3f-9cb686c19d02 NaN NaN \n", "2017-01-01T12:06:53 607c9f6e-2bdf-a606-6d16-3004c6958436 1.0 update \n", "2017-01-01T12:07:41 03e1a07b-3e14-412c-3a69-6b45bc79f81c NaN update \n", "2017-01-01T12:08:08 NaN 0.0 interval \n", "2017-01-01T12:08:35 NaN 0.0 wake \n", "2017-01-01T12:09:05 b9890c1e-79d5-8979-63ae-6c08a4cd476a 0.0 NaN \n", "2017-01-01T12:09:48 b60bd7de-4057-8a85-f806-e6eec1350338 NaN interval \n", "2017-01-01T12:10:23 b1dacc73-c8b7-1d7d-ee02-578da781a71e 0.0 test \n", "2017-01-01T12:10:57 1aef7db8-9a3e-7dc9-d7a5-781ec0efd200 NaN user \n", "2017-01-01T12:11:34 8075d058-7dae-e2ec-d47e-58ec6d26899b 1.0 NaN " ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head(20)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We then use [pandas.DataFrame.groupby](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html) to group the records by `username` and then fill in the missing data using the backfill method from [pandas.api.typing.DataFrameGroupBy.bfill](https://pandas.pydata.org/docs/reference/api/pandas.api.typing.DataFrameGroupBy.bfill.html). `limit` defines the maximum number of consecutive `NaN` values:" ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:51.105692Z", "iopub.status.busy": "2026-05-18T17:52:51.105352Z", "iopub.status.idle": "2026-05-18T17:52:51.196201Z", "shell.execute_reply": "2026-05-18T17:52:51.195810Z", "shell.execute_reply.started": "2026-05-18T17:52:51.105674Z" } }, "outputs": [], "source": [ "df.temperature = df.groupby(\"username\").temperature.bfill(limit=3)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "execution": { "iopub.execute_input": "2026-05-18T17:52:51.564221Z", "iopub.status.busy": "2026-05-18T17:52:51.563945Z", "iopub.status.idle": "2026-05-18T17:52:51.606618Z", "shell.execute_reply": "2026-05-18T17:52:51.606327Z", "shell.execute_reply.started": "2026-05-18T17:52:51.564203Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "number missing for column temperature: 22633\n", "number missing for column build: 32350\n", "number missing for column latest: 32298\n", "number missing for column note: 48704\n" ] } ], "source": [ "for col in incomplete_columns:\n", " num_missing = df[df[col].isna()].shape[0]\n", " print(f\"number missing for column {col}: {num_missing}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Arguments of the `fillna` function:\n", "\n", "Argument | Description\n", ":------- | :----------\n", "`value` | Scalar value or dict-like object used to fill in missing values\n", "Methode | Interpolation; default is `ffill` when the function is called without any other arguments\n", "`axis` | Axis to fill; default is `axis=0`\n", "`inplace` | Modifies the calling object without creating a copy\n", "`limit` | For filling in forward and backward directions, maximum number of consecutive periods to fill" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.13 Kernel", "language": "python", "name": "python313" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.0" }, "latex_envs": { "LaTeX_envs_menu_present": true, "autoclose": false, "autocomplete": true, "bibliofile": "biblio.bib", "cite_by": "apalike", "current_citInitial": 1, "eqLabelWithNumbers": true, "eqNumInitial": 1, "hotkeys": { "equation": "Ctrl-E", "itemize": "Ctrl-I" }, "labels_anchors": false, "latex_user_defs": false, "report_style_numbering": false, "user_envs_cfg": false }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 4 }