AI for data analysis

It is Monday morning and someone has emailed you a CSV: county-level GDP, inflation, and unemployment, pulled from a handful of KNBS releases. The county names are spelled three different ways, GDP is stored as text with thousands commas, inflation is a column of '7.1%' strings, and missing unemployment is coded as -99. A year ago you would have spent the morning in Excel by hand. AI can now do most of that cleaning in minutes — but it will also, with total confidence, hand you a wrong mean, a mislabelled axis, or a correlation it invented. This module is about getting from that messy file to insight fast, without being fooled.

Two ways AI touches your data — only one is safe

When you paste a table into a chat window, the model reads your numbers as text and pattern-matches over them. It never computes; it predicts a plausible-looking answer. Ask it for the mean of a column and it may transpose digits, skip rows, or simply make a number up that looks right. The safe mode is upload-and-run-code: tools such as ChatGPT's Advanced Data Analysis, Claude's analysis tool, Microsoft Excel Copilot, and Gemini in Google Sheets write actual code (usually pandas) and execute it on the real file. The numbers then come from computation you can read, question, and re-run. Rule of thumb: if you cannot see the code, do not trust the number.

Profile before you touch anything

Do not clean first. Profile first. Before any transformation you need to know the shape of the data (rows, columns), each column's type, how much is missing and how it is coded, the distinct values in every categorical column, and the range of every numeric one. This is also how you understand your variables before you model: is 'unemployment' a rate or a count, is 'gdp' nominal or real, is a year stored as 2019 or '2019/20'? Ask the AI to profile and report — and explicitly forbid it from changing anything yet. Real KNBS and World Bank exports are messy in predictable ways, and the profile is where you catch them.

You are a careful data analyst working in pandas. I have uploaded a CSV. Do NOT change anything yet — profile it first, and show me the code you run.

1. Report the number of rows, the number of columns, and each column's name and inferred dtype.
2. For every column, give the count and percentage of missing values, and show the 10 most frequent values, so I can spot sentinel codes like -99, 999, 'N/A', or blank strings that are really missing.
3. For any numeric-looking column that loaded as text, tell me WHY (thousands commas, currency symbols, percent signs, stray letters) — but do not convert it yet.
4. For each categorical column, list every distinct value and its count, so I can see inconsistent spellings and casing.
5. Flag any fully duplicate rows and any duplicate (county, year) combinations.

Do not clean, impute, or drop anything until I tell you to.

• Inconsistent categories: 'Nairobi', 'Nairobi City', and 'NAIROBI' are three labels for one county; 'Murang'a' and 'Muranga' differ by an apostrophe
• Missing values coded many ways: blank, -99, 999, 'n/a', '-', or a stray full stop, all in the same column
• Numbers stored as text: '1,204,915' with commas, 'KES 88bn', or '7.1%' — none of which arithmetic will touch until coerced
• Mixed types and formats: a year as 2019 in some rows and '2019/20' in others; dates as both 03/04/2020 and 2020-04-03
• Whitespace and casing: 'Nairobi ' with a trailing space is not equal to 'Nairobi'
• Duplicate county-year rows from stitching several releases together, sometimes with conflicting values

Cleaning — and diffing what changed

Now clean the data in these explicit steps. After EACH step print the row count before and after, plus a short change log of exactly what changed. Never drop or impute silently.

Step 1 — County names: standardise the 'county' column to Kenya's 47 official KNBS county names. Show me the full mapping table (raw value -> standard value) BEFORE applying it, and pause. Do not guess on ambiguous ones (e.g. 'Muranga' vs "Murang'a", 'Trans Nzoia' vs 'Trans-Nzoia') — flag them for me to decide.

Step 2 — Numerics: convert 'gdp' and 'unemployment' by stripping thousands commas and whitespace; convert 'inflation' by stripping the '%' sign. Report how many values converted cleanly and list every value that FAILED to convert.

Step 3 — Missing codes: treat -99, 999, 'n/a', 'N/A', '-', and empty strings as missing (NaN). Report the count set to missing per column. Do NOT impute.

Step 4 — Duplicates: show me every duplicate (county, year) row, then keep the most recent source and drop the rest. Report how many rows were dropped.

End with a summary table: rows in, rows out, cells converted, cells set missing, rows dropped.

Descriptive statistics, read critically

Now ask for the descriptives — count, mean, median, standard deviation, min, max, and quartiles per column, grouped by year where it helps. Two disciplines matter. First, spot-check: pick one statistic and recompute it on a slice by hand, because a leftover -99 sentinel will silently drag a mean down and a stray text row will make a column's mean vanish. Second, read the right statistic: county GDP and income are heavily right-skewed — Nairobi dwarfs Mandera — so the mean is misleading and the median plus the spread tell the honest story. If AI reports only the mean, ask for the median and the distribution too.

Charts, and the misleading ones AI makes

• Truncated axis: a y-axis starting at 6% instead of 0% turns a trivial inflation wobble into a dramatic-looking spike — always ask where the axis starts
• Dual axes: two series on separate y-scales can be slid until any two lines appear to move together, manufacturing a correlation that is not there
• Mislabelled units: KES plotted as if USD, or a level plotted where you asked for year-on-year growth — check what was actually computed, not just what the title claims
• Silent transforms: a log scale or a per-capita adjustment applied without telling you changes the whole message of the chart
• Wrong chart for the data: a pie chart of parts that do not sum to a meaningful whole, or a smoothed trend line hiding the scatter underneath

Regression, and telling real patterns from noise

AI is genuinely good at translating a regression table into plain English: it will tell you a coefficient's sign and size, whether it is statistically significant, and what the R-squared implies. That is a real time-saver. But watch three slips. It slides from correlation to causation, writing 'a rise in mobile-money density causes GDP growth' when your specification supports no such claim. It misreads functional form, describing a log-level coefficient as if it were a simple unit change. And it treats a tiny p-value as importance, when significance is not the same as economic magnitude. Give it the model context — what is regressed on what, and why — and make it hedge causal language.