# Data-quality issues in the AI usage survey The table has 300 rows but only 288 distinct `User_ID`s, and most numeric measures are stored as text. The queries behind this are the profiling queries I ran across steps 1–7. ## 1. Mixed units and formats (numbers stored as text) - **Age** (VARCHAR) mixes `"28 yrs"` style values with bare numbers like `"97"` (2 rows). 23 rows are blank. - **Monthly_Income** mixes plain `0` with formatted values such as `$1,884`. 10 rows are blank. - **Monthly_AI_Cost** carries a `$` sign and trailing whitespace (`"$25 "`). 12 rows are blank. - **AI_Usage_Hours_Per_Day** has an `hrs` suffix (`"1.7 hrs"`). 7 rows are blank. - **Time_Saved_Hours_Per_Week** has the same `hrs` suffix (`"4.1 hrs"`). 10 rows are blank. - **Work_or_Study_Hours_Per_Day** is also VARCHAR. 9 rows are blank. - All of these need stripping and casting before analysis. Blanks are empty strings, not true NULLs. ## 2. Inconsistent category labels - **Education_Level** has about 17 spellings for roughly 6 real levels. For example, bachelor's appears as `Bachelor's`, `Bachelors`, `BA/BSc` and `bachelor's degree`. Master's appears as `Master's`, `Masters`, `MA/MSc` and `master's degree`. High school appears as `High School`, `high school` and `HS`. Undergraduate appears as `Undergraduate` and `undergrad`. PhD appears as `PhD` and `Ph.D.`. 13 rows are blank. - **Gender** has 12 variants: `Male`, `male`, `MALE`, `M`, `Female`, `female`, `FEMALE`, `F`, `Other`, `other`, `Non-binary`, and blank (10 rows). Case and abbreviation differences would split any group-by. - Other blanks: **AI_Purpose** 13, **AI_Tool** 11, **Would_Recommend** 5. ## 3. Impossible or suspicious values - **Age** ranges from 16 to 97. The 97 values are bare numbers (2 rows) and look like likely entry errors. - **Monthly_Income** has a maximum of 250,000 against a median of about 521.5, so it is heavily skewed with extreme outliers. It also has 34 zeros (33 plain `0` plus `$0`), which may be students with no income or may be missing data coded as zero. - **Monthly_AI_Cost** has a maximum of 100, while the other values I saw were $0–$50. - **AI_Usage_Hours_Per_Day** reaches 23. In 24 rows, daily AI usage exceeds daily work or study hours, which is not plausible. - **Work_or_Study_Hours_Per_Day** reaches 20, and 2 rows exceed 16 hours. - **Time_Saved_Hours_Per_Week** reaches 19.4. In 1 row the time saved exceeds weekly AI usage (daily usage × 7). ## 4. Missing values in typed scores - **Productivity_Score** has 16 NULLs, **Accuracy_Rating** 15 and **Satisfaction_Score** 7. - The ranges themselves are valid: productivity 1.6–10, accuracy 2–5, satisfaction 3–10. - Accuracy takes only about 4 distinct values, so it is a coarse scale. ## 5. Duplicate identifiers - 300 rows against 288 distinct `User_ID`s means 12 rows share an ID with another row. I did not check whether they are exact copies or conflicting records, so they should be deduplicated or reviewed before counting respondents. ## 6. Lineage columns carry no information - `_ingestion_timestamp`, `_batch_id`, `_source_file` and `_source_system` each hold a single value across all rows. They give no per-record provenance. ## Suggested cleanup 1. Normalise the text numerics: strip `$`, `,`, `yrs` and `hrs`, trim whitespace, and treat empty strings as NULL. 2. Map education and gender variants to canonical labels. 3. Dedupe on `User_ID`. 4. Flag or cap implausible rows: age above about 90, daily usage above work hours, work hours above 16, and income or cost outliers. 5. Decide whether a zero income means "no income" or "missing" before computing income averages.