Quick answer: pandas read_csv can interpret the text NA as a missing-value marker. Setting a string dtype alone does not preserve that token. When NA is a valid business code, define the missing-value rules explicitly and verify both the code and genuinely empty fields after import.
A valid region code disappears from an order export
Our fictional source uses NA for its North America reporting region and EU for its Europe reporting region, including the UK. These are internal business labels, not claims about political membership or standardized country codes. Four orders come from the US, UK and Germany. All amounts are already in USD, one row represents one order, and every amount is known.
The correct reconciliation is $100 assigned to NA, $140 to EU and $40 with no supplied region. Total order value is $280. We must preserve the unresolved region rather than infer it from the country: the source may assign accounts using a different commercial rule.
Reproduce the default import
from io import StringIO
import pandas as pd
source = """id,market,region,amount_usd
U1,US,NA,100
K1,UK,EU,80
D1,DE,EU,60
U2,US,,40
"""
wrong = pd.read_csv(
StringIO(source), dtype={"region": "string"}
)
print(wrong[["id", "region"]])
print(wrong["region"].isna().sum()) # 2
The NA token on U1 and the empty field on U2 both become missing in the region column. The input rows still exist and their amounts still total $280. However, a default groupby on region omits the missing keys, leaving only the EU subtotal of $140. A regional report can therefore lose assigned value without losing source rows.
Declare which fields mean missing
clean = pd.read_csv(
StringIO(source),
dtype={"region": "string"},
keep_default_na=False,
na_values={"region": [""]},
)
assert clean.loc[0, "region"] == "NA"
assert clean["region"].isna().sum() == 1
print(clean.groupby("region")["amount_usd"].sum())
# EU 140
# NA 100
This parser contract preserves NA as a literal value and treats an empty region field as missing. Disabling the defaults affects the entire import, so define missing markers for other columns too when the real file requires them. This fixture has no missing amounts; do not assume that will remain true in production.
The official pandas read_csv reference explains how keep_default_na combines with na_values. With the defaults disabled, only explicitly supplied markers are recognized. Setting na_filter=False would disable missing-marker detection altogether and ignore these options, so it is not a substitute for this contract.
Reconcile the unresolved group
by_region = clean.groupby(
"region", dropna=False
)["amount_usd"].sum()
print(by_region)
assert by_region.sum() == 280
assert clean.loc[
clean["region"].isna(), "amount_usd"
].sum() == 40
The output includes EU at $140, NA at $100 and a missing-key group at $40. That final group communicates unresolved classification. Keeping it in reconciliation does not turn it into a known region. Report the missing share separately if a dashboard requires a regional completeness measure.
Can you repair the already imported column?
Do not fill every missing region with NA: that would also relabel U2, whose source field was empty. Once different source tokens have collapsed to the same missing value, the parsed column alone cannot distinguish them. Re-read the original export using the corrected rules, then compare records by a stable order ID.
Preserve the source file or an appropriate raw-field copy under your normal retention policy. Test additional tokens such as N/A, NULL, whitespace-only fields and unexpected region codes against the source contract. A nonmissing token can still be invalid; parser preservation and business validation are separate checks.
Verification and next checks
Six checks passed with pandas 2.3.3: default parsing produced two missing regions; explicit parsing produced one; U1 retained NA; default regional subtotals summed to $140; corrected known regions were EU $140 and NA $100; including the unresolved group reconciled to $280. This is a synthetic example, not customer data. The installed test version is stated so you can repeat the checks when upgrading.
Also compare the imported row count, unique order IDs and total amount with the export. Check the original text before applying a mapping: a later mapping cannot recover a code the parser already discarded. Continue with separating unmapped codes from missing inputs, missing group keys and revenue totals, and preserving leading-zero identifiers.