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

The fillna() 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 the fillna() method.

 import pandas as pd import numpy as np # Create DataFrame with missing values data = {"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 values print("Original DataFrame:\n",df) # Fill missing values with 5 df_filled = df.fillna(5) print("\nResultant DataFrame after NaN replaced with '5':\n", df_filled) 

Its output 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 the ffill() and bfill() methods respectively.

Example: Forward Fill

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

 import pandas as pd import numpy as np # Create DataFrame with missing values df = 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 values print("Original DataFrame:\n",df) # Forward Fill the missing values result = df.ffill() print("\nResultant DataFrame after Forward fill:\n", result) 

Its output 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 fill bfill() method.

 import pandas as pd import numpy as np # Create DataFrame with missing values df = 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 values print("Original DataFrame:\n",df) # Backward Fill the missing values result = df.bfill() print("\nResultant DataFrame after Backward fill:\n", result) 

Its output 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 the limit parameter.

Example

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

 import pandas as pd import numpy as np # Create DataFrame with missing values df = 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 values print("Original DataFrame:\n",df) # Forward Fill the missing values with limit result = 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 the replace() method.

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

Example

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

 import pandas as pd import 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 values print(df.replace({1000:10,2000:60})) 

Its output 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 the replace() method.

Example

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

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

Its output 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