I Requested ChatGPT to Analyze 3 Datasets. It Made the Similar Errors Each Time

We ran an experiment: three small datasets, one AI mannequin, and the questions a enterprise staff asks in a traditional week — what’s our common supply time, which area is our greatest performer, what number of athletes are on this file.

Then we added a evaluate cross. We handed the mannequin its personal reply again and instructed it the numbers have been going into an exec deck, so confirm every little thing.

One evaluate cross caught a improper row depend and put a checkmark subsequent to a conclusion that was backwards. The opposite invented a correction and turned a proper reply right into a improper one.

Every part beneath is reproducible. We used GPT-5.6 Terra for the quick first cross and GPT-5.6 Luna for a separate set of unhurried runs on the identical information. The code runs on Pandas and SciPy.

The Information

First, we use the shipment_tracking datatable, which is used on this interview query.

shipment_tracking is one row per order. 40 orders from 40 totally different prospects, all positioned between January 1 and 21, 2024. Each row carries three dates that fill in because the order progresses: ordered_date from the beginning, shipped_date as soon as the parcel leaves the warehouse, and delivered_date as soon as it arrives.

Take a look at that final row: ordered, by no means shipped, by no means delivered. There are 18 prefer it out of 40 within the dataset.

The second file we’re coping with on this article is regional_sales, used on this interview query. regional_sales is supposed to be one row per area per yr: 59 rows, 8 areas, years from 2007 to 2025, and a single gross sales determine for every mixture.

That grain is damaged in two methods, and neither one is seen within the column names. Six region-year mixtures have multiple row. Three of the additional rows are precise duplicates, all in us_west, and 4 mixtures maintain conflicting figures: apac 2015 seems as each 173.46 and 126.78, and us_west 2012 seems 4 instances with three totally different values. There isn’t a single us_west 2012 quantity to report. Protection is uneven too, operating from apac with 15 years of historical past right down to latam with 1.

The third file is olympics_athletes_events, used on this interview query.

olympics_athletes_events is one row per athlete per occasion — which is the element that issues later. Its 352 rows cowl 336 athletes throughout 15 Video games and 167 occasions, so 11 athletes seem greater than as soon as and one seems 6 instances. The medal column is crammed for 120 rows, and a clean signifies that athlete didn’t win a medal in that occasion.

Mistake 1: Measuring Ship-To-Door When We Requested Order-To-Door

Engaged on shipment_tracking, we requested for the typical supply time, and that is the calculation that got here again:

df['delivery_days'] = (df['delivered_date'] - df['shipped_date']).dt.days
print(f"Avg supply: {df['delivery_days'].imply():.1f} days")

Output

Avg supply: 2.6 days

The reply led with “Common supply time: 2.6 days.”

A buyer ready for a package deal experiences ordered-to-door, and that clock begins at checkout.

order_to_door = (df['delivered_date'] - df['ordered_date']).dt.days
print(spherical(order_to_door.imply(), 2))

Output

6.09

The query we requested has one reply — 6.09 days — and the reply gave a quantity 2.4 instances smaller.

That is the category of mistake to observe hardest, as a result of there isn’t any bug to seek out. The code runs, it’s legitimate pandas, and it computes precisely what it claims to compute. The error lives within the selection of columns, so no take a look at, no exception, and no sort test will ever flag it. You catch it by studying the query after which studying the column names within the calculation, and by nothing else.

Each metrics are actual and so they measure various things: ship-to-door tells you the way the warehouse is performing, and order-to-door tells you the way lengthy prospects wait. We requested the second query and acquired the primary quantity, and the one-line abstract that individuals learn earlier than a gathering provides no signal of the swap.

The right way to Catch the Error

Learn the query, then learn the column names within the calculation beneath it. That’s the solely test that works right here, as a result of the code runs clear and no take a look at will ever flag a sound subtraction between the improper two dates.

Mistake 2: Writing Numbers That No Code Ever Computed

This one confirmed up in two totally different information. The identical shipment_tracking reply closed with a caveat that reads like good apply:

Heads up: Solely 22 of fifty orders have supply dates but (28 nonetheless in transit/pending).

The file has 40 rows.

print(len(df), df['delivered_date'].notna().sum(), df['delivered_date'].isna().sum())

Output

40 22 18

The 22 is true. The 50 and the 28 got here from nowhere: no code in that session computed both determine or printed both determine.

What makes the sentence harmful is that fifty minus 22 is 28, so it’s internally constant and externally false. A reader doing the arithmetic of their head finds nothing improper.

The regional_sales run failed the identical manner with extra harm. Requested which area performs greatest, it reported APAC at “$3.68M complete gross sales (32% of all regional income)” and signed off with “the info is obvious.”

print(spherical(df.groupby('region_name')['sales'].sum()['apac'], 2))

Output

3675.49

The full is 3675.49 in no matter unit the file makes use of, and APAC’s share is 30.4%. The reply inflated the magnitude roughly a thousandfold, connected a foreign money image to a column that carries no items, and rounded a share that was by no means computed. Studying the session log defined how: that run executed no code in any respect. It printed a pandas snippet and wrote numbers beneath it.

Working code will not be ample safety both. One unhurried Luna mannequin run did execute its queries and nonetheless wrote that APAC was “greater than 60% above US West and US East mixed,” when these two areas sum to 3575.70 towards APAC’s 3675.49 — a spot of two.8%.

Its different comparability in the identical paragraph, 32% forward of europe_north, was appropriate at 32.2%. One determine was measured and one was invented, facet by facet in a single sentence.

That’s the factor to carry on to about summaries. The numbers inside a code block are computed; the numbers within the paragraph round it are written. Nothing forces the 2 to agree.

The right way to Catch the Error

Ask whether or not the code really ran, and test that each quantity within the prose seems someplace within the output, as a result of two of our runs offered code they by no means executed. In our instance, test the grain earlier than accepting any rating: three duplicate rows inflate us_west by 30.3%, and the areas carry between 1 and 15 years of historical past, so dividing by years of information places us_west first at 290.9 towards APAC’s 245.0 and reverses the headline.

Mistake 3: Studying a Development From Orders That Have Not Arrived

Again on shipment_tracking, we requested whether or not transport was getting quicker or slower. The quick cross mentioned quicker, and cited week 1 at 3.2 days towards week 3 at 1.0.

Each numbers are actual. The conclusion is backwards.

df['week'] = df['ordered_date'].dt.isocalendar().week
print(df.groupby('week').agg(
    orders=('order_id', 'dimension'),
    delivered=('delivered_date', 'depend'),
    avg_days=('delivery_days', 'imply')).spherical(2))

Output

The file ends on January 21. Week 3 orders have had about 3 days to finish; week 1 orders had 17. Of week 3’s 10 orders, 7 haven’t any supply date. The one week 3 orders with a supply time are those that occurred to be quick, as a result of the gradual ones have not arrived to be measured.

Later weeks look faster as a result of extra of their proof is lacking. The typical falls from 3.17 to 1.00 whereas unresolved orders climb from 20% to 70%.

Given the identical file and no time stress, the Luna mannequin caught this unprompted and opened with a warning that the advance was an phantasm. Similar lure, similar knowledge, reverse final result.

The right way to Catch the Error

Ask what a clean means earlier than an mixture drops it for you. The absent supply dates belonged to the most recent and slowest orders, so dropping them manufactured a speedup. The giveaway is that unresolved orders climb from 20% to 70% throughout the identical three weeks.

Mistake 4: Dropping 226 Clean Heights With out Saying So

On olympics_athletes_events we requested whether or not peak helps an athlete win a medal. The quick cross in contrast the 2 teams and stopped there.

medalists = df[df['medal'].notna()]['height']
others = df[df['medal'].isna()]['height']
print(spherical(medalists.imply(), 1), spherical(others.imply(), 1))

Output

176.5 176.2

Its verdict: “Peak barely issues — medalists are solely 0.3cm taller, so tall doesn’t equal higher at profitable.”

The arithmetic is true and the conclusion doesn’t comply with. That comparability ran on 126 of the file’s 352 rows, as a result of peak is clean for the opposite 226, and pandas dropped each a kind of rows with out saying so. The imply of a column ignores its empty cells, so the pattern quietly shrank by 64% between the query and the reply, and the reply by no means mentions it.

The second drawback is what these blanks grow to be.

print(spherical(df[df['height'].notna()]['medal'].notna().imply() * 100, 1))
print(spherical(df[df['height'].isna()]['medal'].notna().imply() * 100, 1))

Output

54.0
23.0

Athletes with a recorded peak gained a medal 54% of the time, and athletes with out one gained 23% of the time. A chi-square take a look at on that relationship returns p = 9e-09, which implies whether or not the worth exists predicts the result much better than the worth itself does.

The explanation sits within the years. Of the 302 rows from earlier than 2016, solely 76 carry a peak, and their medal fee is 26.5%. All 50 rows from 2016 onward carry a peak, and their medal fee is 80%. On this file, having a recorded peak, being current, and profitable a medal are near the identical truth, so the 126 rows the mannequin examined lean closely towards the one yr the place nearly everybody medaled.

The helpful reply to “does peak assist” is that this file can not assist one, and a stakeholder is healthier served by listening to that than by a 0.3cm distinction. Each run we did dropped the blanks and analyzed what was left.

The right way to Catch the Error

Verify what number of rows survived the calculation, as a result of this comparability ran on 126 of 352 and by no means mentioned so. Then ask whether or not the blanks are random: these belonged principally to the earliest Video games, and NULL means “not but delivered” in a single column and “didn’t win a medal” in one other.

What Occurred When We Requested It to Verify Its Personal Work

For every first-pass reply we opened a clear session, pasted that reply in full, connected the identical file and sandbox, and requested it to confirm each quantity for an exec deck.

On the shipment_tracking reply, the evaluate reported “ONE ERROR within the Heads up part.” It fastened 50 to 40 and 28 to 18, which was the appropriate correction. It ran code to do it, and it counted the 18 undelivered orders appropriately. Then it wrote this:

All three major metrics are appropriate:

• Q1: 2.6 days
• Q2: 45.5%
• Q3: Getting quicker (3.2 to 1.0 days)

Q2 — the on-time fee towards a 5-day goal — was genuinely appropriate.

Q1 is mistake 1 and Q3 is mistake 3. So the evaluate accepted a supply time that answered a special query, and accepted a development created by the identical 18 undelivered orders it had simply completed counting. It had the quantity that explains the phantasm on display screen and by no means linked it to the declare two strains beneath.

Its corrected reply was an identical to the unique aside from these two digits. It repaired the fabricated determine from mistake 2, left errors 1 and three standing, and the reply went out carrying a verification stamp.

The evaluate of olympics_athletes_events went additional within the improper path. It opened with an actual catch on a separate error — appropriately recognizing that the medal share had been computed per file when the query was about athletes, which is the grain drawback from the info part — and it fastened that determine to 35.4%.

Then it reached the peak comparability from mistake 4. It by no means talked about the 226 clean heights, which was the defect in that reply. As an alternative it reported that the true means have been 176.4cm for medalists and 175.5cm for non-medalists, labeled the unique 176.2 a “Main” error off by 0.7cm, and rewrote the conclusion to say that “being taller does seem to correlate with profitable medals.”

No constant grouping of this file produces 175.5. The 176.4 determine is roughly the medalist imply after duplicate rows are eliminated, so the evaluate mixed two incompatible groupings into one comparability and produced a distinction that no single evaluation yields. It then used that distinction to reverse a verdict — shifting from “peak barely issues,” which the 126 usable rows do assist, to a declare of correlation that those self same rows reject at p = 0.87. That session additionally executed no code.

Line up the 4 errors towards what the evaluate did with them. It fastened the fabricated numbers in mistake 2. It accepted errors 1 and three with out remark. On mistake 4 it missed the defect totally, invented a substitute, and made the reply worse than the one it was reviewing. Each a kind of verdicts arrived in the identical assured tone, and nothing within the wording separated the right ones from the improper ones.

Conclusion

The mechanical work was robust all through. The mannequin parsed dates, wrote legitimate SQL and pandas, and within the unhurried runs produced evaluation sharper than many analysts would write — together with the censoring analysis in mistake 3.

The 4 errors have one factor in widespread. Every of them turned on one thing that was not on the display screen: the query sitting behind the metric in mistake 1, the code that was by no means run in mistake 2, the orders that had not arrived but in mistake 3, and the 226 heights no person ever recorded in mistake 4. The mannequin learn the file it was given, and in all 4 instances the appropriate reply relied on what the file disregarded. Realizing what a quantity is for remains to be the half you can’t hand over.

Run the second cross for the arithmetic. Then work by way of these 4 checks your self, as a result of the evaluate will inform you the numbers are appropriate both manner.

Nate Rosidi is an information scientist and in product technique. He is additionally an adjunct professor instructing analytics, and is the founding father of StrataScratch, a platform serving to knowledge scientists put together for his or her interviews with actual interview questions from prime firms. Nate writes on the newest tendencies within the profession market, provides interview recommendation, shares knowledge science initiatives, and covers every little thing SQL.