1
# Dataset Profiler
2
3
## 0. Do not clean anything yet
4
5
Profiling and cleaning are separate steps and mixing them loses information. First find
6
out what is there. Cleaning decisions come after, and each one gets written down.
7
8
## 1. Shape and provenance
9
10
- Row count, column count, file size.
11
- Where did this come from, and when? An export has a timestamp and a filter behind it.
12
- Is this the whole population or a sample? If a sample, sampled how? This determines
13
  what you are allowed to conclude.
14
- One row is one what? State it explicitly. Half of all data confusion is an unclear
15
  grain.
16
17
## 2. Per column: what is actually in there
18
19
For every column, regardless of its declared type:
20
21
- Declared type versus actual content. A numeric column read as text usually has one
22
  bad value, and finding it is the whole task.
23
- Null count and null rate. Also count the nulls in disguise: empty string, `"NA"`,
24
  `"null"`, `"-"`, `0` where zero is impossible, `1970-01-01`, `9999`.
25
- Distinct count. Equal to the row count means it is an identifier. Very low means it is
26
  a category, whatever its type.
27
- For numerics: min, max, mean, median, and the 1st and 99th percentiles. A mean far
28
  from the median means skew or outliers.
29
- For text: length range, and the 10 most frequent values. Look for the same category
30
  spelled several ways.
31
- For dates: min, max, and whether any fall in the future.
32
33
## 3. Missingness is data
34
35
Do not just count nulls, ask whether they are random.
36
37
- Group the null rate by other columns. Nulls concentrated in one segment, one date
38
  range, or one source system are a structural fact, not noise.
39
- A column that is null before a certain date means the field was added then. Any trend
40
  across that boundary is an artifact.
41
- Never fill a null without saying why. Filling with the mean invents data and shrinks
42
  variance. It is sometimes right, and always a decision to record.
43
44
## 4. Duplicates
45
46
- Exact duplicate rows: count them.
47
- Duplicates on what should be the key: these are the dangerous ones, and they silently
48
  double every join.
49
- Near-duplicates: same entity, different spelling or whitespace or case.
50
51
Before removing any, work out why they exist. An export run twice and a genuine repeated
52
event look identical and mean opposite things.
53
54
## 5. Outliers and impossibilities
55
56
Separate these two. An outlier is surprising but possible. An impossibility is a bug.
57
58
- Impossible: negative ages, future birthdates, percentages above 100, end before
59
  start, a total that is less than one of its parts.
60
- Outliers: values beyond the 1st or 99th percentile. Look at the actual rows. Do not
61
  remove them because they are inconvenient.
62
63
Impossible values mean the pipeline is broken and everything derived from that column
64
is suspect.
65
66
## 6. Report what the data cannot answer
67
68
The most useful section. Be specific:
69
70
- Questions that need a column that is absent.
71
- Questions that need a grain finer than one row.
72
- Questions ruled out by the sampling, the date range, or a systematic gap.
73
- Comparisons broken by a definition change partway through.
74
75
If a `target` question was given, answer explicitly whether this dataset can answer it,
76
and say what would be needed if not.
77
78
## Output
79
80
Report the shape, a per-column table, the missingness findings, the duplicates, the
81
impossibilities, and the limits. Then list every cleaning decision you propose with its
82
reason, and apply none of them until that list is agreed.
83