When using some machine learning methods, replacing NaN values in a data frame is required. In my previous projects I utilized different methods like dropping the rows or columns with NaN .dropna(), or replacing them with zero or .mean().
While working a new project, those methods did not make sense when applied to the data, and I discovered using interpolation, .interpolate(), to approximate the missing values.
Here is a simplified example using a data frame that contains NaNs:
Original DataFrame:
A B C Text
0 1.0 10.0 100.0 apple
1 3.0 NaN 200.0 banana
2 NaN NaN 300.0 cherry
3 7.0 45.0 400.0 date
4 10.0 50.0 NaN elderberry
Replacing values in each column
Since the example has text and numeric values, I first separated each so I could apply the interpolation on the numeric columns only.
numeric_columns = df.select_dtypes(include=[np.number])
text_column = df['Text']
numeric_columns_interpolated = numeric_columns.interpolate(method='linear')
df_interpolated = pd.concat([numeric_columns_interpolated, text_column], axis=1)
print("\nDataFrame after interpolation:")
print(df_interpolated)
DataFrame after interpolation:
A B C Text
0 1.0 10.000000 100.0 apple
1 3.0 21.666667 200.0 banana
2 5.0 33.333333 300.0 cherry
3 7.0 45.000000 400.0 date
4 10.0 50.000000 400.0 elderberry
Replacing values across rows
In my project I needed to replace the values across rows. To do this change the axis=1 parameter in the interpolation line.
numeric_columns_interpolated = numeric_columns.interpolate(method='linear', axis=1)
df_interpolated = pd.concat([numeric_columns_interpolated, text_column], axis=1)
print("\nDataFrame after interpolation:")
print(df_interpolated)
DataFrame after interpolation:
A B C Text
0 1.0 10.0 100.0 apple
1 3.0 101.5 200.0 banana
2 NaN NaN 300.0 cherry
3 7.0 45.0 400.0 date
4 10.0 50.0 50.0 elderberry
Notice there are still NaNs because the first column 'A' has NaN. This requires values in the first column to work. Replace the NaNs in the first column with zeros.
df['A'] = df['A'].fillna(0 )
df
DataFrame :
A B C Text
0 1.0 10.0 100.0 apple
1 3.0 NaN 200.0 banana
2 0.0 NaN 300.0 cherry
3 7.0 45.0 400.0 date
4 10.0 50.0 NaN elderberry
Interpolate again
numeric_columns = df.select_dtypes(include=[np.number])
text_column = df['Text']
numeric_columns_interpolated = numeric_columns.interpolate(method='linear', axis=1)
df_interpolated = pd.concat([numeric_columns_interpolated, text_column], axis=1)
print("\nDataFrame after interpolation:")
print(df_interpolated)
DataFrame after interpolation:
A B C Text
0 1.0 10.0 100.0 apple
1 3.0 101.5 200.0 banana
2 0.0 150.0 300.0 cherry
3 7.0 45.0 400.0 date
4 10.0 50.0 50.0 elderberry
Now I have a clean data frame I can use in my project.
