Guides / integer vs float columns

JSON to CSV: Distinguish Integer and Float Number Columns

When the same JSON field is the integer 42 in one record and the float 3.14 in the next, a naive CSV exporter writes both as bare text cells with no type label. A spreadsheet importing that CSV may coerce all values to floating-point, erasing the integer/float distinction from the source. An explicit numeric-type column policy records the kind, JSON type, value and original JSON serialization so a reader can tell integers from floats and reconstruct the exact source token.

Short answer

A field like quantity that is the JSON integer 42 in R01, the JSON float 3.14 in R02, and the JSON float 1.0 in R08 has three different JSON number representations that a naive CSV exporter collapses into text cells with no type metadata. Do not write all of them into one bare quantity column. Instead, expand the field into four columns: quantity_kind ("integer", "float_fractional", or "float_integral"), quantity_json_type (always "number" for JSON numbers), quantity_value (the textual representation), and quantity_json (the exact JSON serialization). This lets any CSV reader distinguish a JSON integer from a JSON float, identify floats whose values are integral (like 1.0), and reconstruct the source token exactly.

This guide demonstrates a measured eight-record fixture exported locally on 2026-11-05 with Python 3.13.14. The source has four integer records (42, 100, 7, 999), three fractional float records (3.14, 0.5, 2.718), and one integral float record (1.0 — a float whose value is a whole number). The explicit exporter writes eight CSV rows with five columns; every field was read back and matched exactly (40/40 checks). A naive exporter that writes a single quantity column produces identical-looking text cells, and a spreadsheet importing that CSV may convert all values to floating-point, making it impossible to know whether 42 was an integer or a float in the source.

The eight-record fixture

The source array contains eight records. The quantity field is the JSON integer 42 in R01, the JSON float 3.14 in R02, the JSON integer 100 in R03, the JSON float 0.5 in R04, the JSON integer 7 in R05, the JSON float 2.718 in R06, the JSON integer 999 in R07, and the JSON float 1.0 in R08. This alternation is the core problem: JSON integers and floats are both numbers but have different source tokens and different semantics.

[
  {"id": "R01", "quantity": 42},
  {"id": "R02", "quantity": 3.14},
  {"id": "R03", "quantity": 100},
  {"id": "R04", "quantity": 0.5},
  {"id": "R05", "quantity": 7},
  {"id": "R06", "quantity": 2.718},
  {"id": "R07", "quantity": 999},
  {"id": "R08", "quantity": 1.0}
]

Download the original JSON sample. Per RFC 8259 section 6, the JSON specification defines a number as an optional minus sign, an integer part, an optional fractional part, and an optional exponent part. A number with no fractional part and no exponent is an integer in source representation (42, 100, 7, 999). A number with a fractional part is a floating-point literal (3.14, 0.5, 2.718). The number 1.0 has a fractional part of zero — its value is integral, but its source token is a float literal because it contains a decimal point.

Python's json.loads (documented at docs.python.org) produces a Python int for integer literals and a Python float for floating-point literals. This means the type of the parsed value carries the source token distinction: 42 becomes int(42), while 1.0 becomes float(1.0). CSV cells are always text, so the integer 42 and the float 42.0 (had one appeared) would become text cells that a naive exporter cannot distinguish by type.

What a naive exporter gets wrong

A naive exporter writes a single quantity column. For the JSON integer 42 it writes the text 42. For the JSON float 3.14 it writes 3.14. For the JSON float 1.0 it writes 1.0. This creates three measurable problems:

Problem	What happens	Measured result
Integer vs float is invisible	The CSV cell `42` could come from integer `42` or float `42.0`	Reader cannot tell which records had integer source tokens
Integral floats are ambiguous	The CSV cell `1.0` looks like a float, but a spreadsheet may normalize it to `1`	The source distinction between `1` and `1.0` is lost
Spreadsheet coercion	A spreadsheet importing the CSV may convert all numeric cells to floating-point	All integers become floats in the spreadsheet, erasing the original type

The naive CSV output shows these problems concretely. R01 (integer 42) and R08 (float 1.0) both appear as plain text cells with no type indicator:

id,quantity
R01,42
R02,3.14
R03,100
R04,0.5
R05,7
R06,2.718
R07,999
R08,1.0

This is particularly dangerous in data pipelines where integers and floats have different semantics. For example, a count field that is the integer 42 may mean "there are exactly 42 items", while a measurement field that is the float 42.0 may mean "the measured value was 42.0 with one decimal place of precision". A downstream system that coerces all values to floating-point would treat the count 42 as 42.0, losing the information that it was a whole number. Similarly, the float 1.0 may carry semantic meaning — "the value was measured to one decimal place" — that is lost when a spreadsheet normalizes it to 1.

The explicit numeric-type column policy

Instead of one bare column, expand the field into four columns that together carry the full type information:

SCHEMA = ["id", "quantity_kind", "quantity_json_type", "quantity_value", "quantity_json"]

The quantity_kind column classifies the value into one of three kinds: "integer" (the source token has no fractional part, parsed as Python int), "float_fractional" (the source token has a non-zero fractional part, parsed as Python float), or "float_integral" (the source token has a fractional part of zero, parsed as Python float but whose value is a whole number, like 1.0). The quantity_json_type column records the JSON type name: always "number" for all numeric values, since RFC 8259 section 3 defines a single number type. The quantity_value column contains the textual representation (42, 3.14, 1.0). The quantity_json column stores the exact JSON serialization, which is identical to quantity_value for numbers but provides a consistent interface with the other guides in this series.

The quantity_kind column is the key differentiator. A downstream reader can filter quantity_kind=integer to get only records whose source token was an integer literal. Filtering quantity_kind=float_fractional returns only records with genuine fractional values. Filtering quantity_kind=float_integral returns records like R08 (1.0) whose source token was a float but whose value is a whole number — a case that a naive exporter and spreadsheet import would both erase. Python's csv module documentation describes the DictWriter used to write the output.

Measured export results

The exporter was run locally on 2026-11-05 with Python 3.13.14. Source SHA-256: caf69020…e6b1 (275 bytes). Output SHA-256: 2abbb8cd…d7a3 (324 bytes). The CSV was read back with Python's csv.DictReader and every field compared against the parsed source.

Row	ID	Source value	Python type	quantity_kind	quantity_json	Read-back
0	R01	`42`	int	integer	42	✓ all 5 fields
1	R02	`3.14`	float	float_fractional	3.14	✓ all 5 fields
2	R03	`100`	int	integer	100	✓ all 5 fields
3	R04	`0.5`	float	float_fractional	0.5	✓ all 5 fields
4	R05	`7`	int	integer	7	✓ all 5 fields
5	R06	`2.718`	float	float_fractional	2.718	✓ all 5 fields
6	R07	`999`	int	integer	999	✓ all 5 fields
7	R08	`1.0`	float	float_integral	1.0	✓ all 5 fields

All eight rows passed all five checks (40/40). The CSV contains 8 data rows and 5 columns. The quantity_kind column is the key differentiator: for R01 it is integer (Python type int, source token 42), for R02 it is float_fractional (Python type float, source token 3.14), and for R08 it is float_integral (Python type float, source token 1.0, value is whole number). The CSV writer does not need to quote numeric cells. A reader can parse the quantity_json column with json.loads to recover the exact value.

The exporter code

import json, csv, os, sys, hashlib
from pathlib import Path

SCHEMA = ["id", "quantity_kind", "quantity_json_type",
           "quantity_value", "quantity_json"]

def classify_number(val):
    """Classify a JSON number as integer or float based on its source token."""
    if isinstance(val, bool):
        kind = "boolean"
        json_type = "boolean"
    elif isinstance(val, int):
        kind = "integer"
        json_type = "number"
    elif isinstance(val, float):
        if val.is_integer():
            kind = "float_integral"
        else:
            kind = "float_fractional"
        json_type = "number"
    elif isinstance(val, str):
        kind = "string"
        json_type = "string"
    elif val is None:
        kind = "null"
        json_type = "null"
    else:
        kind = type(val).__name__
        json_type = type(val).__name__
    return kind, json_type

def export_explicit(source_path, csv_path, naive_path, evidence_path):
    with open(source_path, "r", encoding="utf-8") as f:
        raw_text = f.read()
    source_sha = hashlib.sha256(raw_text.encode("utf-8")).hexdigest()
    records = json.loads(raw_text)

    rows = []
    for rec in records:
        row = {col: "" for col in SCHEMA}
        row["id"] = rec["id"]
        val = rec["quantity"]
        kind, json_type = classify_number(val)
        row["quantity_kind"] = kind
        row["quantity_json_type"] = json_type
        row["quantity_value"] = json.dumps(val, ensure_ascii=False)
        row["quantity_json"] = json.dumps(val, ensure_ascii=False)
        rows.append(row)

    with open(csv_path, "x", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=SCHEMA)
        writer.writeheader()
        writer.writerows(rows)
    return rows, raw_text, records

The exporter classifies each value before writing. A JSON integer like 42 produces Python int, gets kind="integer" and json_type="number". A JSON float like 3.14 produces Python float, gets kind="float_fractional" and json_type="number". A JSON float like 1.0 produces Python float whose is_integer() method returns True, so it gets kind="float_integral". The quantity_json column stores json.dumps(val), which produces 42 for the integer, 3.14 for the fractional float, and 1.0 for the integral float. The exporter uses open(csv_path, "x") for exclusive file creation, refusing to overwrite an existing output. Python's isinstance(val, bool) check comes before isinstance(val, int) because bool is a subclass of int in Python; without this ordering, True would be misclassified as the integer 1.

Read-back verification

The verification step reads the CSV back with csv.DictReader and compares every column against the parsed source. For each row it checks that quantity_kind matches the expected kind, quantity_json_type matches the expected type, quantity_value matches the expected text, and quantity_json matches the JSON serialization. All eight rows passed all five checks (40/40).

def read_back_verify(csv_path, records):
    with open(csv_path, "r", encoding="utf-8", newline="") as f:
        reader = csv.DictReader(f)
        csv_rows = list(reader)

    for csv_row, src_rec in zip(csv_rows, records):
        val = src_rec["quantity"]
        exp_kind, exp_type = classify_number(val)
        exp_json = json.dumps(val, ensure_ascii=False)
        assert csv_row["quantity_kind"] == exp_kind
        assert csv_row["quantity_json_type"] == exp_type
        assert csv_row["quantity_value"] == exp_json
        assert csv_row["quantity_json"] == exp_json

Why JSON integers and floats are both "number" but differ in source token

RFC 8259 section 6 defines the JSON number grammar: an optional minus sign, an integer part, an optional fraction part, and an optional exponent part. The specification does not define separate "integer" and "float" types — both are numbers. However, the source token carries the distinction: 42 has no fraction part, while 3.14 and 1.0 both have fraction parts. Python's json.loads preserves this distinction by producing int for tokens without a fraction part and float for tokens with a fraction part.

The float_integral kind addresses a subtle case: the token 1.0 is a float literal (it has a fraction part of 0), but its numeric value is the whole number 1. Python's float.is_integer() method returns True for this value. Without the float_integral kind, a reader would have to guess whether 1.0 in the CSV came from a JSON float 1.0 or a JSON integer 1 that was coerced to a float during export. The kind column makes this explicit.

Distinct from the decimal token guide. The decimal token preservation guide preserves the original source spelling of number tokens using parse_float and parse_int hooks. That guide focuses on preserving digit sequences like 1.10 versus 1.1. This guide focuses on classifying whether a number was an integer or a float in the source, which is a different question: 42 and 42.0 have the same numeric value but different source token types.

Distinct from the falsy-value guide. The falsy-value guide distinguishes the number 0 from the boolean false and the string "0". This guide distinguishes integers from floats within the JSON number type. The falsy-value guide covers cross-type collisions; this guide covers within-type distinctions that matter for source-token preservation and downstream type semantics.

Reproduce the local example

These are independent Python examples, not measurements of the browser converter. Download the input, exporter, saved CSV, naive CSV and execution evidence into one folder. Use Python 3.12 or later and a new output filename.

Original JSON Python exporter Explicit CSV Naive CSV Execution evidence

python integer-float-to-csv.py integer-float-fixture.json new-output.csv new-naive.csv new-evidence.json

The exporter reads at most 1,000,000 bytes and rejects inputs over that demonstration policy. That limit is a code policy, not a measured browser limit. The output uses UTF-8 and CRLF record endings. Exclusive file creation prevents overwriting an existing file. Faithful text conversion does not neutralize spreadsheet formulas, so use a suitable text import workflow for untrusted data.

Question: Can I filter integers and floats in the CSV without the kind column?

You can filter quantity_value=42 in a single-column CSV, but that filter returns the cell regardless of whether the source was an integer 42 or a float 42.0. If you need to match only records where the quantity was a typed integer in the source JSON, you need the quantity_kind column set to integer. If you need to match only records where the quantity was a float, you need quantity_kind=float_fractional or quantity_kind=float_integral. Without the kind column, a filter for "integer values" cannot distinguish a source integer from a source float that happens to have a whole-number value, and a spreadsheet import may have already coerced all values to floats. The four-column policy provides the typed, directly comparable fields that a spreadsheet user can filter on without parsing JSON, while the quantity_json column carries the exact source value for reconstruction.