Detecting a Column's Data Type Automatically

Type detection looks like a series of regex tests and is really a series of decisions about precedence. The tests are easy; getting the order right, and knowing which real values defeat each stage, is the part that makes the difference between an ingestion pipeline that works and one that silently mangles a column.

The cascade, and why the order is fixed

Every implementation of this — pandas’ CSV reader, a database import wizard, a BI tool’s connector — runs essentially the same cascade, testing the most restrictive type first and falling through:

for each column:
  1. strip and normalise nulls        →  all null? type = unknown
  2. boolean?                         →  {true/false, yes/no, 0/1, t/f}
  3. integer?                         →  optional sign, digits only
  4. decimal?                         →  digits with one separator, or exponent
  5. date or datetime?                →  parses under a *declared* format
  6. categorical?                     →  distinct count low vs row count
  7. otherwise                        →  free text

The order is not arbitrary and reversing any two steps produces a specific bug. Boolean must precede integer, or a column of 0 and 1 becomes an integer that somebody later takes the mean of and calls a rate — which is sometimes what you want and should never be an accident. Integer must precede decimal, or every whole number becomes a float and identifiers lose their exactness past 2⁵³. Date must come after both numeric tests, or an eight-digit product code like 20250131 is read as a date. And categorical must come last among the structured types, because a column of five distinct integers is both a valid integer column and a valid categorical one, and only the numeric interpretation is recoverable later.

Stage zero: what counts as missing

Almost every mis-detection traces back to this step. A column is perfectly numeric except for 40 rows containing N/A, and the entire column falls through to free text. The fix is a null vocabulary applied before any type test, and it needs to be wider than people expect: the empty string, NA, N/A, n/a, NULL, null, None, -, --, ?, #N/A (the Excel error literal), NaN, and domain sentinels such as -1, 999, -9999 and 9999-12-31.

The numeric sentinels are the dangerous half, because they do not break detection — they pass it. A survey column where -1 means “declined to answer” will be typed as a perfectly good integer and will drag every mean downward for the life of the model. No type detector can find these, because they are type-correct and semantically wrong. The only detector is a histogram and somebody looking at it, which is why this belongs alongside data quality checks rather than inside the cascade.

Numeric, and the identifiers it swallows

The naive numeric test is ^-?\d+$ for integers, and it is wrong in both directions. Values it wrongly rejects, all of which are real numbers as written by real systems:

• Thousands separators. 1,234,567 in a US-formatted export, and 1.234.567 in a German one where the comma is the decimal mark. A single file can contain both if it was concatenated from two regions, and the two conventions are mutually ambiguous: 1.234 is one-point-two-three-four or one-thousand-two-hundred-and-thirty-four depending entirely on a locale nobody recorded.
• Accounting negatives. (1,234.50) means −1234.50 in every finance export ever produced.
• Units and currency. $1,234, 45%, 12kg, 1.2e6. The percent case is worse than it looks: stripping the sign gives 45, but the value the analyst means is 0.45, and which one you produce changes every downstream number.

And the values it wrongly accepts, which is the more expensive direction: zip codes (02134 becomes 2134 and Boston moves), phone numbers, account and order numbers, national identifiers, and any code with meaningful leading zeros. The signal that a numeric column is really an identifier is available and worth testing for — near-100% distinct values, a constant string length, leading zeros anywhere in the column, or a value range far outside anything arithmetic would produce. A column that is 99.98% unique across 400,000 rows is an id, whatever the digits say.

Dates, where the locale lives

Date detection is the stage most likely to succeed and be wrong. 03/04/2026 parses cleanly under both %m/%d/%Y and %d/%m/%Y, and produces two different dates five weeks apart. No amount of examining that single value resolves it.

What resolves it is the column, not the value. Scan every non-null entry and collect the first and second numeric components. If any row has a first component above 12, the format is day-first for the whole column. If any row has a second component above 12, it is month-first. If both occur, the column contains two formats and is not a date column — it is a data-quality incident. If neither occurs across the whole column, the file is genuinely ambiguous and the correct behaviour is to require a declared format rather than to guess, which is why every mature loader asks you for one.

Three further traps are worth naming. Two-digit years require a pivot rule and there is no correct one, only a documented one. Epoch timestamps are integers and will have been caught at stage three, so detecting them needs a plausibility range on the magnitude — roughly 10⁹ for seconds and 10¹² for milliseconds — and even then 1700000000 could be a transaction id. And a datetime with no timezone is not a moment in time; assuming UTC is a choice that must be recorded in the schema, because the alternative is a silent one-day error at every month boundary for half your users.

Categorical versus free text

The usual rule is a ratio: if distinct values divided by non-null values is below some threshold, call it categorical. The threshold is where the rule breaks, because the ratio is not scale-free. Fifty distinct values in 200 rows is a ratio of 0.25 and is obviously categorical; fifty distinct values in 500,000 rows is a ratio of 0.0001 and is even more obviously categorical. The same threshold cannot serve both, so a usable rule combines an absolute cap on the distinct count with the ratio, and applies the ratio only above some minimum row count.

Two structural signals beat the ratio entirely. String length variance is close to zero for categories and high for free text — country codes are all two characters, customer comments are not. And the shape of the frequency distribution differs: categories follow a concentrated distribution where the top few values cover most rows, while free text is close to all-singletons. A column where the top ten values cover 95% of rows is categorical no matter how many distinct values sit in the tail, and that tail is exactly the rare-category problem discussed in categorical encoding methods.

Report a distribution, not a type

The design mistake underneath most bad ingestion is returning a single type per column. Return the evidence instead: the fraction of values parseable as each candidate type, the count and examples of the ones that are not, and the confidence. A column that is 99.6% integers and 0.4% the string pending is a different object from one that is 60/40, and collapsing both to object throws away the information a human needs to decide.

This also makes the failure loud rather than quiet. Coercing with errors ignored turns unparseable values into nulls and the column then looks clean, so the bug surfaces months later as a model that underperforms for no visible reason. Coerce with errors surfaced, count them, and put anything above a threshold in front of a person. For the residue that genuinely needs judgement rather than a rule — is this numeric column an amount or an account number — see inferring a schema from a CSV with an LLM.