Prompt
How do I compare two datasets for completeness and missing values?
Latest observation
To compare two datasets for completeness and missing values, you usually want to check both:
- How much data is missing in each dataset
- Where the missingness occurs
- Whether the same rows/columns are missing in both datasets
- Whether one dataset is more complete than the other
1) Basic completeness checks
For each dataset, calculate:
- Total rows
- Total columns
- Count of missing values per column
- Count of missing values per row
- Overall missing percentage
Example metrics
- Column completeness =
1 - (missing values in column / total rows) - Dataset completeness =
1 - (total missing cells / total cells)
2) Compare missing values column by column
If the datasets have the same schema, compare each column:
- Missing count in Dataset A vs Dataset B
- Missing percentage in Dataset A vs Dataset B
- Difference in completeness
Useful table
| Column | Missing A | Missing B | % Missing A | % Missing B | Difference |
|---|---|---|---|---|---|
| age | 10 | 25 | 1% | 2.5% | +1.5% |
This helps identify which fields are less reliable in one dataset.
3) Compare missingness row by row
If both datasets represent the same entities and have a common key, compare whether the same records are missing values:
- Join on a unique ID
- Identify rows where one dataset has missing values and the other does not
- Check if missingness patterns are consistent
This is especially useful when one dataset is a transformed or updated version of the other.
4) Check patterns of missingness
Look for:
- Completely empty columns
- Rows with many missing values
- Blocks of missingness in certain time periods or groups
- Columns missing only in one dataset
This can reveal data collection or extraction issues.
5) Use visualizations
A few good plots:
- Missingness matrix/heatmap
- Bar chart of missing values by column
- Side-by-side comparison charts
- Heatmap of differences in missingness
These make it easy to spot where one dataset is weaker.
6) Example in Python (pandas)
import pandas as pd
# Load datasets
df1 = pd.read_csv("dataset1.csv")
df2 = pd.read_csv("dataset2.csv")
# Missing values per column
missing1 = df1.isna().sum()
missing2 = df2.isna().sum()
# Missing percentages per column
pct1 = df1.isna().mean() * 100
pct2 = df2.isna().mean() * 100
# Compare
comparison = pd.DataFrame({
"missing_a": missing1,
"missing_b": missing2,
"pct_missing_a": pct1,
"pct_missing_b": pct2
})
comparison["diff_pct"] = comparison["pct_missing_a"] - comparison["pct_missing_b"]
print(comparison.sort_values("diff_pct", ascending=False))
7) If the datasets have different sizes
Use percentages, not just counts, because raw missing counts can be misleading if one dataset is much larger.
8) If you want an overall completeness score
You can define:
completeness = 100 - overall_missing_percentage
Then compare the two scores directly.
9) Things to watch out for
- Empty strings (
"") may not be recognized as missing unless converted toNaN - Different column names or types may need mapping before comparison
- Duplicates can distort completeness if not handled
- Different row counts mean row-by-row comparison may not be valid without alignment
If you want, I can also give you:
- a SQL version
- a Python function to compare two datasets automatically
- or a template report for missingness comparison