{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Managing missing data with pandas\n", "\n", "Missing data often occurs in data analyses. 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*) for numerical data to represent missing data.\n", "\n", "Methods for handling `NA` objects:\n", "\n", "Argument | Description\n", ":------- | :----------\n", "`dropna` | filters axis labels based on whether values for each label have missing data, with different thresholds for the amount of missing data to tolerate\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\n", "\n", "This notebook introduces some ways to manage missing data using 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", "
\n", "\n", "**See also:**\n", "\n", "* [Dora](https://github.com/NathanEpstein/Dora)\n", "* [Badfish](https://github.com/harshnisar/badfish)\n", "
" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "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. Check the data\n", "\n", "In pandas, a convention borrowed from the R programming language was adopted and missing data was referred to 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 in data collection). When cleaning data for analysis, it is often important to analyse the missing data itself to identify problems in data collection or potential biases in the data due to the missing data. First we display the first 20 data records:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "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 we look at what data type the columns are:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "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": [ "We can also display the values and their frequency, for example for the column `note`:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "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": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.note.value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Remove all null values (including the indication `n/a`)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.1 …with `pandas.read_csv`\n", "\n", "[pandas.read_csv](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) usually already filters out many values that it recognises as `NA` or `NaN`. Further values can be specified with `na_values`." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "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`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Missing values can be deleted with [pandas.DataFrame.dropna](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.dropna.html).\n", "\n", "To analyse the extent of the deletions, we display the extent of the DataFrame before and after the deletion with [pandas.DataFrame.shape](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.shape.html):" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(146397, 7)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.shape" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(46116, 7)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df2 = df.dropna()\n", "\n", "df2.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "So we would lose more than 2/3 of the records with `pandas.DataFrame.dropna`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the next experiment, we want to analyse whether whole rows or columns contain no data. Here, `how='all'` removes rows or columns that do not contain values; `axis=1` says that empty rows should be removed." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(146397, 7)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.dropna(how=\"all\", axis=1).shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This, too, does not bring us any further." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.3 Find all columns where all data is present" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "complete_columns = list(df.columns)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['timestamp',\n", " 'username',\n", " 'temperature',\n", " 'heartrate',\n", " 'build',\n", " 'latest',\n", " 'note']" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "complete_columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.4 Find all columns where the most data is available" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "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` lashes a column." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.5 Find all columns where data is missing\n", "\n", "With [pandas.DataFrame.isnull](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isnull.html) we can find missing values and with [pandas.DataFrame.any](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.any.html) we find out if an element is valid, usually via a column." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "incomplete_columns = list(df.columns[df.isnull().any()])" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "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 output the number of missing values per column:" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "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].isnull() == True].shape[0]\n", " print(f\"number missing for column {col}: {num_missing}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also output these values as a percentage:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "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].isnull() == True].shape[0] / df.shape[0]\n", " print(f\"percent missing for column {col}: {percent_missing}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.6 Replace missing data\n", "\n", "To be able to check 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": {}, "outputs": [ { "data": { "text/plain": [ "latest\n", "0.0 75735\n", "1.0 38364\n", "Name: count, dtype: int64" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.latest.value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we replace the missing values in the column `latest` with `0` with [DataFrame.fillna](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.fillna.html):" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "df.latest = df.latest.fillna(0)" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "latest\n", "0.0 108033\n", "1.0 38364\n", "Name: count, dtype: int64" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.latest.value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.7 Replace missing data using `backfill`\n", "\n", "To make the records follow each other in their 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": 20, "metadata": {}, "outputs": [], "source": [ "df = df.set_index(\"timestamp\")" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "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-b5caa08f11e00.0NaN
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-ca69624f98240.0interval
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-9cb686c19d020.0NaN
2017-01-01T12:06:53gsutton29.080607c9f6e-2bdf-a606-6d16-3004c69584361.0update
2017-01-01T12:07:41jessica4822.08303e1a07b-3e14-412c-3a69-6b45bc79f81c0.0update
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-e6eec13503380.0interval
2017-01-01T12:10:23beckercharles12.061b1dacc73-c8b7-1d7d-ee02-578da781a71e0.0test
2017-01-01T12:10:57ipittman11.0691aef7db8-9a3e-7dc9-d7a5-781ec0efd2000.0user
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 0.0 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 0.0 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 0.0 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 0.0 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 0.0 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 0.0 user \n", "2017-01-01T12:11:34 8075d058-7dae-e2ec-d47e-58ec6d26899b 1.0 NaN " ] }, "execution_count": 21, "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 the missing data with the `backfill` method of [pandas.core.groupby.DataFrameGroupBy.fillna](https://pandas.pydata.org/docs/reference/api/pandas.core.groupby.DataFrameGroupBy.fillna.html). `limit` defines the maximum number of consecutive `NaN` values:" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [], "source": [ "df.temperature = df.groupby(\"username\").temperature.bfill(limit=3)" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "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: 0\n", "number missing for column note: 48704\n" ] } ], "source": [ "for col in incomplete_columns:\n", " num_missing = df[df[col].isnull() == True].shape[0]\n", " print(f\"number missing for column {col}: {num_missing}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Arguments of the function `fillna`:\n", "\n", "Argument | Description\n", ":------- | :----------\n", "`value` | Scalar value or dict-like object used to fill in missing values.\n", "Method | interpolation; by default `ffill` if the function is called without further arguments\n", "`axis` | Axis to be filled; default `axis=0`\n", "`inplace` | changes the calling object without creating a copy\n", "`limit` | for padding in forward and backward direction, maximum number of consecutive periods to pad" ] } ], "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 }