How to write and debug pandas code with Claude: merge, groupby and NaN checks

2 hours ago

9 min read

Cover for a guide on writing and debugging pandas code with Claude, with merge, groupby and NaN checks. Data Studios original cover

Claude can write pandas code from a plain-English description of a data task and, with code execution turned on, run that code on the CSV or Excel files you upload. The code usually runs on the first try. The risk sits in the code that runs and returns a plausible number that is wrong: a merge on a key that is duplicated in the lookup table, a groupby that silently drops rows with a missing category, or an amount column that pandas reads as text.

Three checks catch most of these errors before a figure reaches a report. Compare the row count before and after every merge, ask pandas itself to validate the join type with the validate argument, and reconcile every grouped total against a control total taken from the raw data. The worked example below shows a two-file revenue task where a typical first draft overstates revenue by $705 on a true total of $6,985, and the corrected code that returns the right answer.

The requirements and limits quoted here come from Anthropic's help center and the official pandas documentation, both checked on November 12, 2026. The pandas behavior described applies to pandas 3.x, the current major version.

··········

PANDAS TASKS WHERE CLAUDE HELPS AND WHERE IT SLIPS.

Claude is fast at the routine parts of pandas work: reading files with the right parser options, reshaping wide tables into long ones, building groupby aggregations with named outputs, writing date filters and producing a chart from the result. It is also useful as a debugger. Pasting a traceback together with the offending cell and a description of the input columns usually produces a correct diagnosis, because most pandas errors (a KeyError on a misspelled column, a dtype mismatch in a merge key, a SettingWithCopyWarning in older code) follow well-known patterns.

The weak spots are the assumptions the code makes about your data rather than the syntax. A model writing pandas code cannot know that a customer appears twice in your CRM export unless it inspects the file or you tell it. The same applies to blank categories, currency symbols stored inside numbers and dates in mixed formats. The same failure appears in SQL written by AI, covered in our guide on how to write SQL with Claude and catch fan-out and date errors, and the defense is the same: make the code prove its assumptions with explicit checks.

··········

CLAUDE REQUIREMENTS FOR RUNNING PANDAS CODE ON YOUR FILES.

Claude can write pandas code in any chat, but running it on your data requires the feature Anthropic calls code execution and file creation. According to Anthropic's help center, it is available on every Claude plan and gives Claude a private computing environment where it can write and run code such as Python.

........

Item	As documented by Anthropic (checked Nov 12, 2026)
Plans	Free, Pro, Max, Ultra, Team and Enterprise
Where	Claude web, Claude Desktop and mobile apps
Default state	On for Free, Pro, Max, Ultra and Team; on for new Enterprise organizations
Network access	Enabled on Free, Pro, Max and Ultra; package managers only on Team; off on new Enterprise organizations
Setting	Settings > Capabilities, "Code execution and file creation"
File size in the code environment	30MB per file for uploads and downloads
Chat uploads	Up to 500MB per file and 20 files per chat; CSV and XLSX supported
XLSX uploads	Require code execution and file creation to be enabled

........

The two size figures come from two different help articles. The upload article lists 500MB per file for chat uploads, while the file creation article gives 30MB per file as the maximum for uploads and downloads in the code environment. For a CSV you want Claude to process with pandas, plan around 30MB. Anthropic also notes that file creation consumes more of a plan's usage limits than normal chat.

The help article does not list the Python libraries installed in the environment; it says Claude uses common code packages. The first line of any pandas session should therefore be a request to print pandas.__version__, because the answer decides which behavior applies to chained assignment and string columns. Our overview of how Claude handles Excel and CSV uploads covers the file side in more detail.

··········

PANDAS 3.0 CHANGES THAT BREAK OLDER AI-WRITTEN CODE.

Much of the pandas code published online, and therefore much of what any model has learned from, predates pandas 3.0. The pandas project lists two changes that matter most for AI-written code. Copy-on-Write is now the default and only mode, and the project states that chained assignment will no longer work. String columns are now inferred as the dedicated str dtype instead of the NumPy object dtype.

The chained assignment change is the dangerous one, because the old pattern does not fail loudly. In a local run on pandas 3.0.5, the statement below emitted a ChainedAssignmentError warning and left the DataFrame unchanged, so any later calculation used the old values.

# Old pattern: does not modify df in pandas 3.x
df["amount"][df["customer_id"] == "C06"] = 0

# pandas 3.x pattern: one step with .loc
df.loc[df["customer_id"] == "C06", "amount"] = 0

The pandas project also says the SettingWithCopyWarning has been removed and that defensive .copy() calls written to silence it are no longer needed. For upgrades it recommends moving to pandas 2.3 first and making the code run without warnings before installing 3.0. When you ask Claude for code that will run on your own machine, state your pandas version in the prompt.

··········

WORKED EXAMPLE: REVENUE BY SEGMENT FROM TWO CSV FILES.

The task is common in sales reporting: total October revenue by customer segment, where orders come from one export and the segment comes from a CRM export. The data below is illustrative and deliberately contains three traps.

Illustrative orders.csv:

........

order_id	customer_id	order_date	amount
1001	C01	2026-10-02	$1,200.00
1002	C02	2026-10-03	$450.00
1003	C03	2026-10-05	$980.00
1004	C01	2026-10-09	$300.00
1005	C04	2026-10-11	$2,150.00
1006	C05	2026-10-14	$620.00
1007	C02	2026-10-20	$875.00
1008	C06	2026-10-27	$410.00

........

Illustrative customers.csv:

........

customer_id	segment	updated_at
C01	Enterprise	2026-01-15
C02	SMB	2026-02-01
C02	Mid-market	2026-09-12
C03	Mid-market	2026-03-08
C04	Enterprise	2026-04-22
C05	(blank)	2026-05-30
C06	SMB	2026-06-18

........

The traps are the amount column stored as text with dollar signs and thousands separators, customer C02 listed twice after moving from SMB to Mid-market, and customer C05 with no segment. The control total of the eight orders is $6,985.00. A prompt that names the business rules gets better code than one that only names the output:

I uploaded orders.csv and customers.csv. Write pandas code that returns
October revenue and order count by customer segment.
Rules:
- amount is text like "$1,200.00"; convert it to a number.
- customers.csv can list a customer more than once; use the row with
  the latest updated_at.
- Keep customers with a blank segment as their own group.
- Use validate= on the merge and assert that the grouped revenue equals
  the total of orders.csv. Print pandas.__version__ first.

··········

THE PANDAS BUGS IN A TYPICAL FIRST DRAFT.

When the prompt only asks for "revenue by segment", the natural first draft is short and runs without an error. This version reproduces the pattern:

import pandas as pd

orders = pd.read_csv("orders.csv")
customers = pd.read_csv("customers.csv")
orders["amount"] = (
    orders["amount"].str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False).astype(float)
)
merged = orders.merge(customers, on="customer_id", how="left")
by_segment = merged.groupby("segment")["amount"].sum()

Two errors offset each other partly, which makes the result look reasonable. The merge turns 8 orders into 10 rows, because both C02 orders match both C02 customer rows. The groupby then drops the C05 order, because groupby excludes missing keys by default (dropna=True in the pandas documentation).

........

Segment	First draft	Correct
Enterprise	$3,650.00	$3,650.00
Mid-market	$2,305.00	$2,305.00
SMB	$1,735.00	$410.00
No segment	(dropped)	$620.00
Total	$7,690.00	$6,985.00
Rows after merge	10	8

........

The draft double counts $1,325.00 of C02 orders and loses $620.00 from C05, a net overstatement of $705.00. Neither problem raises an error, and the draft total is close enough to the real one that a quick glance would not flag it. The same row-count logic is the core of our guide on how to clean and deduplicate a messy CSV with ChatGPT.

··········

CORRECTED PANDAS CODE WITH MERGE AND GROUPBY CHECKS.

The corrected version deduplicates the lookup table explicitly, asks pandas to enforce the join type and checks the totals with assertions that stop the script if an assumption fails.

import pandas as pd

orders = pd.read_csv("orders.csv")
customers = pd.read_csv("customers.csv")

orders["amount"] = pd.to_numeric(
    orders["amount"].str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

latest = (
    customers.sort_values("updated_at")
    .drop_duplicates("customer_id", keep="last")
)

merged = orders.merge(
    latest[["customer_id", "segment"]],
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)
assert len(merged) == len(orders)
assert (merged["_merge"] == "both").all()

by_segment = (
    merged.groupby("segment", dropna=False)["amount"]
    .agg(orders="count", revenue="sum")
    .reset_index()
)
assert by_segment["revenue"].sum() == orders["amount"].sum()

Each line protects against one trap. With validate="many_to_one", pandas checks that the merge keys are unique in the right-hand table; run against the original customers table, the merge stops with "MergeError: Merge keys are not unique in right dataset; not a many-to-one merge" instead of silently duplicating rows. The indicator=True argument adds a _merge column whose values are left_only, right_only or both, which exposes orders with no matching customer. Sorting on updated_at works here because the dates are ISO strings; with other date formats, convert the column with pd.to_datetime first. The pd.to_numeric call raises an error on any value it cannot parse, where a silent coercion would turn bad values into missing ones.

The expected result, computed on the illustrative data:

........

segment	orders	revenue
Enterprise	3	3650.0
Mid-market	3	2305.0
SMB	1	410.0
NaN	1	620.0

........

If Claude's output differs from this table on the same input, one of the traps has slipped back into the code.

··········

DEBUGGING PANDAS ERRORS WITH CLAUDE: PROMPTS THAT WORK.

For a traceback, give Claude the full error text, the cell that raised it, the pandas version and the output of df.dtypes and df.head() for every DataFrame involved. The dtypes matter more than the rows: a merge key read as int64 in one file and str in the other makes the merge fail with a ValueError, and an ID stored with leading zeros in one file and without them in the other returns no matches at all. Neither problem is visible from the code alone.

For a result that looks wrong without an error, the most productive request asks Claude to write checks rather than to rewrite the analysis:

This pandas code runs but the total looks too high. Do not rewrite it.
Add checks after each step that print the row count, the number of
duplicate keys in each table before every merge, the number of NaN
values in each groupby key, and the running sum of amount. Then tell me
at which step the total first changes.

This approach keeps the original logic visible, so you can see exactly which step went wrong and judge the fix yourself. Ask for the checks to stay in the final code as assertions; they cost almost nothing to run and they fail loudly the next time the export changes shape. Our wider overview of how to use Claude for data analysis covers other analysis tasks.

··········

DATA PRIVACY AND ADMIN SETTINGS FOR CLAUDE CODE EXECUTION.

Anthropic's help center warns that a bad actor can hide instructions inside external files or websites to make Claude run untrusted code or leak data, and recommends monitoring Claude while it works and stopping it if it accesses data unexpectedly. Listed mitigations include sandboxes that are never shared between users and a prompt injection classifier.

On Team and Enterprise plans, owners can enable or disable the feature in Organization settings > Capabilities and choose the network setting: off, package managers only, package managers plus specific domains, or all domains, which Anthropic calls the riskiest option. With network access off, Claude works with pre-installed packages only. Anthropic notes that MCP connections can still communicate externally regardless of the egress setting.

For consumer plans (Free, Pro and Max), Anthropic's privacy center says chats and coding sessions are used to improve Claude when the user allows it through the Model Improvement option in Privacy Settings, or when conversations are flagged for safety review; incognito chats are excluded. Commercial products are covered by a separate policy. Customer-level or personal data should go through an organization account whose settings your data owner has approved.

··········

PANDAS WITH CLAUDE: WHAT TO VERIFY BEFORE TRUSTING THE NUMBERS.

The documented facts are stable and checkable. Code execution is available on every Claude plan, the code environment accepts files up to 30MB, Team and Enterprise owners control network access, and pandas 3.x has removed chained assignment and changed the default string dtype. What Anthropic does not document is the exact set of libraries and the pandas version in the environment at any given moment, which is why printing the version belongs at the start of every session.

On the pandas side, three habits remove most silent errors from AI-written code: validate= on every merge, dropna=False on every groupby whose key can be blank, and an assertion that ties the final totals back to a control total from the raw file. In the worked example, those three lines are the difference between $7,690 and the correct $6,985. Business rules such as which CRM record wins or how blank segments are reported stay with the analyst, written into the prompt and checked in the output.

··········

·····

DATA STUDIOS

·····

datastudios.org