Python Pandas - Filling Missing Data

---

---

Filling missing data is a process of replacing the missing (NaN) values with meaningful alternatives. Whether you want to replace missing values with a constant value, or propagate the values forward or backward, Pandas has built-in functions to achieve this.

In this tutorial, we'll learn different ways to fill missing data in Pandas including −

• Replacing missing values with a scalar.

• Forward and backward filling.

• Using a specified limit for filling.

• Replacing Data with the replace() method.

• Replacing values with regular expressions.

Filling Missing Data with Scalar Value

Thefillna() method in Pandas is used to fill missing values (NA or NaN) with a scalar value, such as any specific number.

Example

The following demonstrates how to fill the missing values NaN with a scalar value ("NaN" with "5") using thefillna() method.

import pandas as pdimport numpy as np# Create DataFrame with missing valuesdata = {"Col1": [3, np.nan, np.nan, 2], "Col2": [1.0, pd.NA, pd.NA, 2.0]}df = pd.DataFrame(data)# Display the original DataFrame with missing valuesprint("Original DataFrame:\n",df)# Fill missing values with 5df_filled = df.fillna(5)print("\nResultant DataFrame after NaN replaced with '5':\n", df_filled)

Itsoutput is as follows −

Original DataFrame:

	Col1	Col2
0	3.0	1.0
1	NaN	<NA>
2	NaN	<NA>
3	2.0	2.0

Resultant DataFrame after NaN replaced with '0':

	Col1	Col2
0	3.0	1.0
1	5.0	5.0
2	5.0	5.0
3	2.0	2.0

Filling Missing Values Forward or Backward

You can also propagate the last valid observation forward or backward to fill gaps using theffill() andbfill() methods respectively.

Example: Forward Fill

This example replaces the missing values with the forward fillffill() method.

import pandas as pdimport numpy as np# Create DataFrame with missing valuesdf = pd.DataFrame([[9, -3, -2], [-5, 1, 8], [6, 4, -8]], index=['a', 'c', 'd'], columns=['one', 'two', 'three'])df = df.reindex(['a', 'b', 'c', 'd', 'e']) # Display the original DataFrame with missing valuesprint("Original DataFrame:\n",df)# Forward Fill the missing valuesresult = df.ffill()print("\nResultant DataFrame after Forward fill:\n", result)

Itsoutput is as follows −

Original DataFrame:

	one	two	three
a	9.0	-3.0	-2.0
b	NaN	NaN	NaN
c	-5.0	1.0	8.0
d	6.0	4.0	-8.0
e	NaN	NaN	NaN

Resultant DataFrame after Forward fill:

	one	two	three
a	9.0	-3.0	-2.0
b	9.0	-3.0	-2.0
c	-5.0	1.0	8.0
d	6.0	4.0	-8.0
e	6.0	4.0	-8.0

Example: Backward Fill

This example replaces the missing values with backward fillbfill() method.

import pandas as pdimport numpy as np# Create DataFrame with missing valuesdf = pd.DataFrame([[9, -3, -2], [-5, 1, 8], [6, 4, -8]], index=['a', 'c', 'd'], columns=['one', 'two', 'three'])df = df.reindex(['a', 'b', 'c', 'd', 'e']) # Display the original DataFrame with missing valuesprint("Original DataFrame:\n",df)# Backward Fill the missing valuesresult = df.bfill()print("\nResultant DataFrame after Backward fill:\n", result)

Itsoutput is as follows −

Original DataFrame:

	one	two	three
a	9.0	-3.0	-2.0
b	NaN	NaN	NaN
c	-5.0	1.0	8.0
d	6.0	4.0	-8.0
e	NaN	NaN	NaN

Resultant DataFrame after Backward fill:

	one	two	three
a	9.0	-3.0	-2.0
b	-5.0	1.0	8.0
c	-5.0	1.0	8.0
d	6.0	4.0	-8.0
e	NaN	NaN	NaN

Limiting the Number of Fills

You can also control the limit of how many consecutive missing values are filled by specifying thelimit parameter.

Example

The following example demonstrates how to set limit for filling the missing values using theffill() method with thelimit parameter.

import pandas as pdimport numpy as np# Create DataFrame with missing valuesdf = pd.DataFrame([[9, -3, -2], [-5, 1, 8], [6, 4, -8]], index=['a', 'c', 'd'], columns=['one', 'two', 'three'])df = df.reindex(['a', 'b', 'd', 'e', 'f']) # Display the original DataFrame with missing valuesprint("Original DataFrame:\n",df)# Forward Fill the missing values with limitresult = df.ffill(limit=1)print("\nResultant DataFrame after Forward fill:\n", result)

Following is the output of the above code −

Original DataFrame:

	one	two	three
a	9.0	-3.0	-2.0
b	NaN	NaN	NaN
d	6.0	4.0	-8.0
e	NaN	NaN	NaN
f	NaN	NaN	NaN

Resultant DataFrame after Forward fill:

	one	two	three
a	9.0	-3.0	-2.0
b	9.0	-3.0	-2.0
d	6.0	4.0	-8.0
e	6.0	4.0	-8.0
f	NaN	NaN	NaN

Replacing Data with the replace() method

Many times, we have to replace a generic value with some specific value. We can achieve this by applying thereplace() method.

Replacing NA with a scalar value is equivalent behavior of thefillna() function.

Example

Here is the example of replacing the generic values using thereplace() method.

import pandas as pdimport numpy as np# Create DataFrame df = pd.DataFrame({'one':[10,20,30,40,50,2000], 'two':[1000,0,30,40,50,60]})# Replace the generic valuesprint(df.replace({1000:10,2000:60}))

Itsoutput is as follows −

	one	two
0	10	10
1	20	0
2	30	30
3	40	40
4	50	50
5	60	60

Replacing Missing Data Using Regular Expressions

You can also use regex patterns to replace the missing values in your data with thereplace() method.

Example

Here is the example of replacing the a specific data using the regular expression with thereplace() method.

import pandas as pdimport numpy as np# Create DataFrame with missing valuesdf = pd.DataFrame({"a": list(range(4)), "b": list("ab.."), "c": ["a", "b", np.nan, "d"]})# Display the original DataFrame with missing valuesprint("Original DataFrame:\n",df)# Replace the missing values with regular expresult = df.replace(r"\.", 10, regex=True)print("\nResultant DataFrame after filling the missing values using regex:\n", result)

Itsoutput is as follows −

Original DataFrame:

	a	b	c
0	0	a	a
1	1	b	b
2	2	.	NaN
3	3	.	d

Resultant DataFrame after filling the missing values using regex:

	a	b	c
0	0	a	a
1	1	b	b
2	2	10	NaN
3	3	10	d