Data Quality#
Before performing any analysis, it is essential to assess and improve the quality of the data. Real-world sensor data often contains missing values, gaps and duplicates.
This chapter covers common techniques for detecting and handling these issues. Implausible values (outliers) are covered in the recipe Outlier Detection.
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
url = "https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatTempHum.csv"
df = pd.read_csv(url, sep=";")
df["time"] = pd.to_datetime(df["time"])
df = df.set_index("time")
df.head()
| FlatA_Hum | FlatA_Temp | FlatB_Hum | FlatB_Temp | FlatC_Hum | FlatC_Temp | FlatD_Hum | FlatD_Temp | |
|---|---|---|---|---|---|---|---|---|
| time | ||||||||
| 2018-10-03 00:00:00 | 53.0 | 24.43 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.43 |
| 2018-10-03 01:00:00 | 53.0 | 24.40 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.40 |
| 2018-10-03 02:00:00 | 53.0 | 24.40 | 39.3 | 22.40 | 44.7 | 24.5 | 48.3 | 24.38 |
| 2018-10-03 03:00:00 | 53.0 | 24.40 | 40.3 | 22.40 | 45.0 | 24.5 | 48.0 | 24.33 |
| 2018-10-03 04:00:00 | 53.3 | 24.40 | 41.0 | 22.37 | 45.2 | 24.5 | 47.7 | 24.30 |
Missing Values#
Missing values (NaN) are common in sensor data due to communication failures, sensor outages, or maintenance periods.
# Count missing values per column
df.isna().sum()
FlatA_Hum 697
FlatA_Temp 697
FlatB_Hum 711
FlatB_Temp 711
FlatC_Hum 684
FlatC_Temp 684
FlatD_Hum 827
FlatD_Temp 827
dtype: int64
# Percentage of missing values
missing_pct = (df.isna().sum() / len(df)) * 100
missing_pct.round(2)
FlatA_Hum 4.06
FlatA_Temp 4.06
FlatB_Hum 4.14
FlatB_Temp 4.14
FlatC_Hum 3.98
FlatC_Temp 3.98
FlatD_Hum 4.81
FlatD_Temp 4.81
dtype: float64
# Detailed info including non-null counts
df.info()
<class 'pandas.DataFrame'>
DatetimeIndex: 17181 entries, 2018-10-03 00:00:00 to 2020-09-17 20:00:00
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 FlatA_Hum 16484 non-null float64
1 FlatA_Temp 16484 non-null float64
2 FlatB_Hum 16470 non-null float64
3 FlatB_Temp 16470 non-null float64
4 FlatC_Hum 16497 non-null float64
5 FlatC_Temp 16497 non-null float64
6 FlatD_Hum 16354 non-null float64
7 FlatD_Temp 16354 non-null float64
dtypes: float64(8)
memory usage: 1.2 MB
Visualizing Missing Data#
A heatmap of missing values helps identify patterns — for example, whether all sensors fail at the same time.
# Resample to daily resolution for a cleaner visualization
missing_daily = df.isna().resample("D").mean() * 100
fig = px.imshow(
missing_daily.T,
labels=dict(x="Date", y="Column", color="% Missing"),
title="Missing Data Pattern (% per day)",
color_continuous_scale="Reds",
aspect="auto"
)
fig.show()
Detecting Gaps in Time Series#
A gap shows up in one of two ways: as missing timestamps (no row at all), or as rows whose values are NaN. Check both.
# 1) Missing timestamps: expected vs. actual number of hourly rows
expected = pd.date_range(df.index.min(), df.index.max(), freq="h")
print(f"Expected timestamps: {len(expected)}")
print(f"Actual timestamps: {len(df)}")
Expected timestamps: 17181
Actual timestamps: 17181
# 2) Gaps as NaN rows: length of each run of missing values
s = df["FlatA_Temp"]
gap_len = s.isna().groupby(s.notna().cumsum()).sum() # NaN rows after each value
gap_len = gap_len[gap_len > 0]
print(f"FlatA_Temp: {len(gap_len)} gaps, longest {gap_len.max()} hours, "
f"{(gap_len > 6).sum()} longer than 6 hours")
FlatA_Temp: 171 gaps, longest 47 hours, 23 longer than 6 hours
Duplicates#
Duplicate rows can occur when data is merged from multiple sources or exported incorrectly.
# df.duplicated() compares the values only, not the timestamp
dup_rows = df.duplicated()
empty_rows = df.isna().all(axis=1)
print(f"Rows equal to an earlier row: {dup_rows.sum()}")
print(f"... of which completely empty (all sensors NaN): {(dup_rows & empty_rows).sum()}")
Rows equal to an earlier row: 544
... of which completely empty (all sensors NaN): 538
# Duplicate timestamps are what matters for time series
print(f"Number of duplicate timestamps: {df.index.duplicated().sum()}")
Number of duplicate timestamps: 0
# Remove duplicates (if any)
df_clean = df[~df.index.duplicated(keep="first")]
print(f"Rows before: {len(df)}, after: {len(df_clean)}")
Rows before: 17181, after: 17181
Tip
Use keep="first" to keep the first occurrence, keep="last" to keep the last, or keep=False to remove all duplicates.
Interpolation and Filling Missing Values#
Once missing values are identified, they can be filled using various strategies.
Linear Interpolation#
col = "FlatA_Temp"
# Original data with NaN values
print(f"NaN values before: {df[col].isna().sum()}")
# Interpolate linearly, only between two valid values (limit_area="inside")
df_interp = df.copy()
df_interp[col] = df_interp[col].interpolate(method="linear", limit_area="inside")
# The NaN values left are at the end of the series, where nothing can be interpolated
print(f"NaN values after: {df_interp[col].isna().sum()}")
NaN values before: 697
NaN values after: 47
Interpolation also bridges long gaps with a straight line. To fill only short gaps, see Gap filling strategies in Time Series.
# Visualize a section with interpolated values
# Find a region with NaN values
nan_mask = df[col].isna()
if nan_mask.any():
first_nan = df[nan_mask].index[0]
start = first_nan - pd.Timedelta(days=2)
end = first_nan + pd.Timedelta(days=2)
fig = go.Figure()
fig.add_trace(go.Scatter(
x=df.loc[start:end].index, y=df.loc[start:end, col],
mode="lines+markers", name="Original", line=dict(color="steelblue")
))
fig.add_trace(go.Scatter(
x=df_interp.loc[start:end].index, y=df_interp.loc[start:end, col],
mode="lines", name="Interpolated", line=dict(color="orange", dash="dash")
))
fig.update_layout(title="Linear Interpolation of Missing Values", xaxis_title="Time", yaxis_title="Temperature [°C]")
fig.show()
Forward and Backward Fill#
# Forward fill: carry the last valid observation forward
df_ffill = df.copy()
df_ffill[col] = df_ffill[col].ffill()
# Backward fill: carry the next valid observation backward
df_bfill = df.copy()
df_bfill[col] = df_bfill[col].bfill()
print(f"Forward fill NaN remaining: {df_ffill[col].isna().sum()}")
print(f"Backward fill NaN remaining: {df_bfill[col].isna().sum()}")
Forward fill NaN remaining: 0
Backward fill NaN remaining: 47
Fill with a Constant#
# Fill with the column mean
df_mean_fill = df.copy()
df_mean_fill[col] = df_mean_fill[col].fillna(df[col].mean())
print(f"NaN remaining: {df_mean_fill[col].isna().sum()}")
NaN remaining: 0
Note
Choose the imputation method carefully based on the nature of the data. Linear interpolation works well for slowly changing sensor data. Forward fill is appropriate when the last known value is the best estimate. Filling with the mean is generally not recommended for time series data.
Summary Statistics for Quality Assessment#
A quick quality overview can be compiled by combining several checks.
quality_summary = pd.DataFrame({
"total_rows": len(df),
"non_null": df.count(),
"null_count": df.isna().sum(),
"null_pct": (df.isna().sum() / len(df) * 100).round(2),
"min": df.min(),
"max": df.max(),
"mean": df.mean().round(2),
"std": df.std().round(2)
})
quality_summary
| total_rows | non_null | null_count | null_pct | min | max | mean | std | |
|---|---|---|---|---|---|---|---|---|
| FlatA_Hum | 17181 | 16484 | 697 | 4.06 | 23.50 | 76.50 | 54.34 | 6.30 |
| FlatA_Temp | 17181 | 16484 | 697 | 4.06 | 19.10 | 28.88 | 23.79 | 1.27 |
| FlatB_Hum | 17181 | 16470 | 711 | 4.14 | 25.00 | 73.30 | 49.67 | 6.90 |
| FlatB_Temp | 17181 | 16470 | 711 | 4.14 | 20.53 | 29.12 | 23.40 | 1.47 |
| FlatC_Hum | 17181 | 16497 | 684 | 3.98 | 22.50 | 69.80 | 48.90 | 6.49 |
| FlatC_Temp | 17181 | 16497 | 684 | 3.98 | 21.38 | 28.80 | 23.96 | 1.36 |
| FlatD_Hum | 17181 | 16354 | 827 | 4.81 | 20.50 | 69.80 | 49.30 | 6.52 |
| FlatD_Temp | 17181 | 16354 | 827 | 4.81 | 18.12 | 28.03 | 24.13 | 1.31 |
# Visualize data completeness
completeness = (1 - df.isna().mean()) * 100
fig = px.bar(
x=completeness.index,
y=completeness.values,
labels={"x": "Column", "y": "Completeness (%)"},
title="Data Completeness per Column"
)
fig.update_layout(yaxis_range=[0, 105])
fig.show()