Category and Dataset Median Imputation

How do you handle missing data in a dataset?

Using the provided observation, category, and source data, produce a cleaned result. Treat scores outside the range 0 through 100 as missing, retain every observation, and apply category-level then dataset-level fallbacks for missing scores. Replace missing or blank text values with the appropriate category or generic fallback.

Output

1. One row per observation, ordered by entity_id, observed_on, and observation_id
2. Return observation_id, entity_id, observed_on, category_name, cleaned_score, score_was_missing, cleaned_status, source_name, and entity_sequence
3. Use Unclassified, Unknown, or 0 only when no more specific fallback exists

Output details

• Order entity_id, observed_on, and observation_id ascending, using observation_id to break ties.
• Compute category and dataset medians using continuous percentile interpolation, excluding missing scores and preserving fractional values without rounding.
• Mark score_was_missing true for NULL or out-of-range scores, and false for scores from 0 through 100 inclusive.
• For cleaned_score, use the category median, then dataset median, then 0 when no valid score exists.
• Trim status whitespace before treating it as blank, then use the category default status, or Unknown.
• Use Unclassified for missing category names and Unknown for missing source names; number entity_sequence from 1 per entity in ascending observation order.