Python Pandas - Missing Data

Pandas - Handling Missing Data

Missing data is a ubiquitous challenge in real-world datasets. It can arise from various sources: data entry errors, equipment malfunctions, non-responses in surveys, or data merging issues. Effectively handling missing values is crucial for accurate data analysis, visualization, and machine learning model training.

Pandas represents missing data primarily using the special floating-point value NaN (Not a Number). For object-type data (like strings), Python’s None object might also be used, and for datetime types, NaT (Not a Time) represents missing values. Pandas provides tools designed to work consistently across these representations.

Let’s create a DataFrame with some missing values using .reindex:

# Import libraries
import pandas as pd
import numpy as np

# Create a base DataFrame
df_base = pd.DataFrame(np.random.randn(5, 3),
                     index=['a', 'c', 'e', 'f', 'h'],
                     columns=['one', 'two', 'three'])

# Reindex to introduce missing rows ('b', 'd', 'g')
df_missing = df_base.reindex(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'])

print("DataFrame with Missing Values (NaN):")
print(df_missing)

Output (random values will differ):

DataFrame with Missing Values (NaN):
        one       two     three
a  0.077988  0.476149  0.965836
b       NaN       NaN       NaN
c -0.390208 -0.551605 -2.301950
d       NaN       NaN       NaN
e -2.000303 -0.788201  1.510072
f -0.930230 -0.670473  1.146615
g       NaN       NaN       NaN
h  0.085100  0.532791  0.887415

Rows ‘b’, ‘d’, and ‘g’ were not in the original index, so reindexing filled them with NaN.

Detecting Missing Values

Pandas provides methods to easily detect missing values. These return boolean objects (same shape as the input) indicating True where data is missing.

• .isna(): Preferred method, returns True for NaN, None, or NaT.
• .isnull(): An alias for .isna().
• .notna(): The inverse of .isna(), returns True for non-missing values.
• .notnull(): An alias for .notna().

Example: Using .isna()

# (Using df_missing from the previous step)

# Check for missing values in column 'one'
print("Missing values in column 'one' (.isna()):")
print(df_missing['one'].isna())

# Check for non-missing values in the entire DataFrame
print("\nNon-missing values in DataFrame (.notna()):")
print(df_missing.notna())

Output:

Missing values in column 'one' (.isna()):
a    False
b     True
c    False
d     True
e    False
f    False
g     True
h    False
Name: one, dtype: bool

Non-missing values in DataFrame (.notna()):
     one    two  three
a   True   True   True
b  False  False  False
c   True   True   True
d  False  False  False
e   True   True   True
f   True   True   True
g  False  False  False
h   True   True   True

Calculations with Missing Data

By default, most descriptive statistics and calculations in Pandas (like .sum(), .mean(), .std()) automatically exclude missing values (skipna=True is the default).

# (Using df_missing from the previous step)

# Calculate the sum of column 'one' (NaNs are skipped)
sum_one = df_missing['one'].sum()
print(f"Sum of column 'one' (skipping NaN): {sum_one:.4f}")

# Calculate the mean of column 'two'
mean_two = df_missing['two'].mean()
print(f"Mean of column 'two' (skipping NaN): {mean_two:.4f}")

# Create a column containing only NaNs
df_missing['all_nan'] = np.nan
sum_all_nan = df_missing['all_nan'].sum()
print(f"Sum of a column with only NaNs: {sum_all_nan}")

# Calculate sum including NaNs (treats NaN as 0 if possible, else result is NaN)
# sum_one_with_nan = df_missing['one'].sum(skipna=False)
# print(f"Sum of column 'one' (skipna=False): {sum_one_with_nan}") # This will result in NaN

Output (sum/mean values depend on the initial random data):

Sum of column 'one' (skipping NaN): -2.9674
Mean of column 'two' (skipping NaN): -0.1991
Sum of a column with only NaNs: 0.0

Note: If all data points for an operation (excluding NaNs) are missing, the result is typically NaN (e.g., mean of an all-NaN column). However, sum() of an all-NaN column results in 0. Setting skipna=False forces inclusion of NaNs, which usually propagates the NaN value through the calculation.

Filling Missing Data (.fillna())

Instead of just ignoring missing values, you often want to replace them with a specific value or an estimated value. The .fillna() method is the primary tool for this.

Replace NaN with a Scalar Value

Replace all missing values in a Series or DataFrame with a single constant value (e.g., 0, -1, or the mean/median of the column).

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randn(4, 3), index=['a', 'c', 'e', 'g'], columns=['one', 'two', 'three'])
df = df.reindex(['a', 'b', 'c', 'd', 'e', 'f', 'g'])

print("Original DataFrame with NaNs:")
print(df)

# Fill all NaNs with 0
df_filled_zero = df.fillna(0)
print("\nDataFrame with NaNs replaced by 0:")
print(df_filled_zero)

# Fill NaNs in column 'one' with the mean of that column
mean_one = df['one'].mean()
df_filled_mean = df.copy() # Work on a copy to keep original df
df_filled_mean['one'] = df_filled_mean['one'].fillna(mean_one)
print(f"\nFilling column 'one' NaNs with its mean ({mean_one:.2f}):")
print(df_filled_mean)

Output (random values will differ):

Original DataFrame with NaNs:
        one       two     three
a -0.576991 -0.741695  0.553172
b       NaN       NaN       NaN
c  0.744328 -1.735166  1.749580
d       NaN       NaN       NaN
e -1.234567  0.987654 -0.123456
f       NaN       NaN       NaN
g  0.456789 -0.321098  1.112233

DataFrame with NaNs replaced by 0:
        one       two     three
a -0.576991 -0.741695  0.553172
b  0.000000  0.000000  0.000000
c  0.744328 -1.735166  1.749580
d  0.000000  0.000000  0.000000
e -1.234567  0.987654 -0.123456
f  0.000000  0.000000  0.000000
g  0.456789 -0.321098  1.112233

Filling column 'one' NaNs with its mean (-0.15):
        one       two     three
a -0.576991 -0.741695  0.553172
b -0.152610       NaN       NaN
c  0.744328 -1.735166  1.749580
d -0.152610       NaN       NaN
e -1.234567  0.987654 -0.123456
f -0.152610       NaN       NaN
g  0.456789 -0.321098  1.112233

Fill NA Forward (ffill) and Backward (bfill)

These methods propagate non-missing values to fill gaps. This is particularly common in time series data.

Method	Action
method='ffill' or .ffill()	Forward Fill: Propagates the last valid observation forward.
method='bfill' or .bfill()	Backward Fill: Propagates the next valid observation backward.

# (Using df from the previous example)

# Forward fill NaNs
df_ffill = df.fillna(method='ffill') # or df.ffill()
print("\nDataFrame after forward fill (ffill):")
print(df_ffill)

# Backward fill NaNs
df_bfill = df.fillna(method='bfill') # or df.bfill()
print("\nDataFrame after backward fill (bfill):")
print(df_bfill)

Output:

DataFrame after forward fill (ffill):
        one       two     three
a -0.576991 -0.741695  0.553172
b -0.576991 -0.741695  0.553172 # Filled from 'a'
c  0.744328 -1.735166  1.749580
d  0.744328 -1.735166  1.749580 # Filled from 'c'
e -1.234567  0.987654 -0.123456
f -1.234567  0.987654 -0.123456 # Filled from 'e'
g  0.456789 -0.321098  1.112233

DataFrame after backward fill (bfill):
        one       two     three
a -0.576991 -0.741695  0.553172
b  0.744328 -1.735166  1.749580 # Filled from 'c'
c  0.744328 -1.735166  1.749580
d -1.234567  0.987654 -0.123456 # Filled from 'e'
e -1.234567  0.987654 -0.123456
f  0.456789 -0.321098  1.112233 # Filled from 'g'
g  0.456789 -0.321098  1.112233

Dropping Missing Values (.dropna())

If filling missing values isn’t appropriate, you might choose to remove rows or columns containing them using .dropna().

Key parameters for dropna():

• axis: 0 or 'index' to drop rows (default), 1 or 'columns' to drop columns.
• how: 'any' (default) drops if at least one NA is present, 'all' drops only if all values are NA.
• thresh: Integer value specifying the minimum number of non-NA values required to keep the row/column.
• subset: List of column/index labels to consider when looking for NaNs.

Example: Dropping Rows with Any NaNs

# (Using df_missing created earlier)
print("Original DataFrame with NaNs:")
print(df_missing)

# Drop rows containing any NaN values (default behavior)
df_dropped_rows = df_missing.dropna() # axis=0, how='any' are defaults

print("\nDataFrame after dropping rows with any NaNs:")
print(df_dropped_rows)

Output:

Original DataFrame with NaNs:
        one       two     three  all_nan
a  0.077988  0.476149  0.965836      NaN
b       NaN       NaN       NaN      NaN
c -0.390208 -0.551605 -2.301950      NaN
d       NaN       NaN       NaN      NaN
e -2.000303 -0.788201  1.510072      NaN
f -0.930230 -0.670473  1.146615      NaN
g       NaN       NaN       NaN      NaN
h  0.085100  0.532791  0.887415      NaN

DataFrame after dropping rows with any NaNs:
Empty DataFrame
Columns: [one, two, three, all_nan]
Index: []

Since every row in df_missing contained at least one NaN (especially in the all_nan column we added), dropna() with default settings removed all rows.

Example: Dropping Columns with Any NaNs

# (Using df_missing created earlier)

# Drop columns containing any NaN values
df_dropped_cols = df_missing.dropna(axis=1, how='any')

print("\nDataFrame after dropping columns with any NaNs:")
print(df_dropped_cols)

Output:

DataFrame after dropping columns with any NaNs:
Empty DataFrame
Columns: []
Index: [a, b, c, d, e, f, g, h]

Again, since all columns contained at least one NaN, all columns were dropped.

Example: Using thresh

# Use the original df_missing before adding 'all_nan'
df_original_missing = df_base.reindex(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'])
print("Original df_missing (before adding all_nan column):")
print(df_original_missing)

# Keep rows that have at least 2 non-NaN values
df_thresh = df_original_missing.dropna(thresh=2)
print("\nDataFrame keeping rows with at least 2 non-NaN values:")
print(df_thresh)

Output:

Original df_missing (before adding all_nan column):
        one       two     three
a  0.077988  0.476149  0.965836
b       NaN       NaN       NaN
c -0.390208 -0.551605 -2.301950
d       NaN       NaN       NaN
e -2.000303 -0.788201  1.510072
f -0.930230 -0.670473  1.146615
g       NaN       NaN       NaN
h  0.085100  0.532791  0.887415

DataFrame keeping rows with at least 2 non-NaN values:
        one       two     three
a  0.077988  0.476149  0.965836
c -0.390208 -0.551605 -2.301950
e -2.000303 -0.788201  1.510072
f -0.930230 -0.670473  1.146615
h  0.085100  0.532791  0.887415

Rows ‘b’, ‘d’, ‘g’ were dropped because they had 0 non-NaN values, which is less than the threshold of 2.

Replacing Generic Values (.replace())

The .replace() method is more general than fillna(). It allows replacing any specified value (not just NaN) with another value. It’s useful for correcting data entry errors, mapping codes, or replacing specific placeholders.

Replacing NaN with a scalar using replace(np.nan, value) is similar to fillna(value).

Example: Replacing Specific Values

import pandas as pd
import numpy as np

df = pd.DataFrame({'one': [10, 20, 30, 40, 50, 999], # 999 might be a placeholder for missing
                   'two': [1000, 0, 30, 40, 50, 60]})

print("Original DataFrame:")
print(df)

# Replace the placeholder 999 with NaN
df_cleaned = df.replace({999: np.nan})

# Replace 1000 with 10, and 40 with 400 across the DataFrame
df_replaced = df_cleaned.replace({1000: 10, 40: 400})

print("\nDataFrame after replacing 999 with NaN:")
print(df_cleaned)

print("\nDataFrame after replacing 1000->10 and 40->400:")
print(df_replaced)

Output:

Original DataFrame:
   one   two
0   10  1000
1   20     0
2   30    30
3   40    40
4   50    50
5  999    60

DataFrame after replacing 999 with NaN:
    one   two
0  10.0  1000
1  20.0     0
2  30.0    30
3  40.0    40
4  50.0    50
5   NaN    60

DataFrame after replacing 1000->10 and 40->400:
    one   two
0  10.0    10 # 1000 replaced
1  20.0     0
2  30.0    30
3 400.0   400 # 40 replaced in both columns
4  50.0    50
5   NaN    60

You can provide dictionaries to replace multiple values at once, or use more complex arguments to replace values within specific columns only. Refer to the .replace() documentation for advanced usage.