Guides / numeric token sign and notation

JSON to CSV: Distinguish Positive/Negative and Scientific Notation Tokens

When the same JSON field is 42 in one record, -17 in the next, and 1e3 in a third, a naive CSV exporter writes each value through Python's float serialization, changing 1e3 to 1000.0 and 6.022e23 to 6.022e+23. The source token — the original spelling of the number — is lost. An explicit numeric-token column policy records the sign, notation, source token and JSON serialization so a reader can filter positive from negative or scientific from decimal without re-parsing the original JSON.

Short answer

A field like value that is the JSON number 42 in R01, -17 in R02, 1e3 in R05, and 6.022e23 in R07 has different source token spellings that a naive CSV exporter collapses through json.dumps(). Do not write all of them into one bare value column. Instead, expand the field into four columns: value_sign ("positive" or "negative"), value_notation ("decimal" or "scientific"), value_token (the exact source spelling, e.g. 1e3), and value_json (the JSON serialization of the parsed value, e.g. 1000.0). This lets any CSV reader distinguish a positive decimal from a negative scientific-notation number, identify the original spelling of the source token, and reconstruct the parsed value exactly.

This guide demonstrates a measured ten-record fixture exported locally on 2026-11-06 with Python 3.13.14. The source has four positive decimal records (42, 3.14, 1000000, 1.23456789), two negative decimal records (-17, -0.5), and four scientific-notation records (1e3, -2.5e2, 6.022e23, -1.6e-19). The explicit exporter writes ten CSV rows with five columns; every field was read back and matched exactly (50/50 checks). A naive exporter that writes a single value column re-serializes each number through Python's json.dumps(), changing 1e3 to 1000.0, -2.5e2 to -250.0, and 6.022e23 to 6.022e+23 (Python adds a + sign to positive exponents).

The ten-record fixture

The source array contains ten records. The value field alternates between positive and negative numbers and between decimal and scientific notation:

[
  {"id": "R01", "value": 42},
  {"id": "R02", "value": -17},
  {"id": "R03", "value": 3.14},
  {"id": "R04", "value": -0.5},
  {"id": "R05", "value": 1e3},
  {"id": "R06", "value": -2.5e2},
  {"id": "R07", "value": 6.022e23},
  {"id": "R08", "value": -1.6e-19},
  {"id": "R09", "value": 1000000},
  {"id": "R10", "value": 1.23456789}
]

Download the original JSON sample. Per RFC 8259 section 6, the JSON number grammar defines an optional minus sign, an integer part, an optional fraction part, and an optional exponent part introduced by e or E. A number like 1e3 uses the exponent part; a number like 42 does not. Python's json.loads (documented at docs.python.org) parses both as Python int or float, but the source token spelling is lost after parsing.

What a naive exporter gets wrong

A naive exporter writes a single value column using json.dumps(val). For the JSON integer 42 it writes 42. For the JSON number 1e3 it writes 1000.0 — Python's json.dumps re-serializes the parsed float 1000.0, not the original token 1e3. This creates three measurable problems:

Problem	What happens	Measured result
Source token lost	`1e3` becomes `1000.0`	Reader cannot tell the source used scientific notation
Sign is implicit	`-17` and `17` both appear as bare text	Filtering positive vs negative requires parsing each cell's leading character
Python re-serialization	`6.022e23` becomes `6.022e+23`	Python adds `+` to positive exponents, changing the source spelling

The naive CSV output shows these problems concretely. R05 (1e3 in source) appears as 1000.0. R07 (6.022e23 in source) appears as 6.022e+23:

id,value
R01,42
R02,-17
R03,3.14
R04,-0.5
R05,1000.0
R06,-250.0
R07,6.022e+23
R08,-1.6e-19
R09,1000000
R10,1.23456789

This is particularly dangerous in scientific and engineering data pipelines where the notation carries semantic meaning. For example, Avogadro's number 6.022e23 is a measurement written in scientific notation; the fact that the source used e23 rather than writing out 23 zeros is part of the data's provenance. A downstream system that receives 6.022e+23 cannot tell whether the source used 6.022e23, 6.022E23, or 6.022e+23 — three different token spellings for the same numeric value. Similarly, a negative sign buried in a text cell requires string parsing to filter, while a dedicated value_sign column enables direct filtering.

The explicit numeric-token column policy

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

SCHEMA = ["id", "value_sign", "value_notation", "value_token", "value_json"]

The value_sign column classifies the value as "positive" or "negative" based on whether the parsed number is less than zero. The value_notation column classifies the source token as "decimal" (no e or E in the source spelling) or "scientific" (the source token contains e or E). The value_token column stores the exact source spelling (1e3, -2.5e2, 6.022e23), preserving the original notation. The value_json column stores json.dumps(val), which is the canonical JSON serialization of the parsed Python value (1000.0, -250.0, 6.022e+23) — this shows how Python re-serializes the number, which may differ from the source token.

The value_token column is the key differentiator. A downstream reader can filter value_notation=scientific to get only records whose source token used exponential notation. Filtering value_sign=negative returns only records with negative values. Comparing value_token and value_json reveals where Python's serialization changed the source spelling: R05 has value_token=1e3 but value_json=1000.0, a visible transformation. Python's csv module documentation describes the DictWriter used to write the output.

Measured export results

The exporter was run locally on 2026-11-06 with Python 3.13.14. Source SHA-256: 4b886a83…246a (338 bytes). Output SHA-256: 8a36ac37…7dd7 (419 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	value_sign	value_notation	value_token	value_json	Read-back
0	R01	`42`	int	positive	decimal	42	42	✓ all 5 fields
1	R02	`-17`	int	negative	decimal	-17	-17	✓ all 5 fields
2	R03	`3.14`	float	positive	decimal	3.14	3.14	✓ all 5 fields
3	R04	`-0.5`	float	negative	decimal	-0.5	-0.5	✓ all 5 fields
4	R05	`1e3`	float	positive	scientific	1e3	1000.0	✓ all 5 fields
5	R06	`-2.5e2`	float	negative	scientific	-2.5e2	-250.0	✓ all 5 fields
6	R07	`6.022e23`	float	positive	scientific	6.022e23	6.022e+23	✓ all 5 fields
7	R08	`-1.6e-19`	float	negative	scientific	-1.6e-19	-1.6e-19	✓ all 5 fields
8	R09	`1000000`	int	positive	decimal	1000000	1000000	✓ all 5 fields
9	R10	`1.23456789`	float	positive	decimal	1.23456789	1.23456789	✓ all 5 fields

All ten rows passed all five checks (50/50). The CSV contains 10 data rows and 5 columns. The value_token column preserves the exact source spelling: for R05 it is 1e3 (not 1000.0), for R07 it is 6.022e23 (not 6.022e+23). The value_json column shows where Python's json.dumps changed the spelling: R05's value_json is 1000.0 (Python serialized the parsed float), R07's is 6.022e+23 (Python added + to the exponent). R08's value_token and value_json are both -1.6e-19 because Python preserves negative exponents in json.dumps.

The exporter code

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

SCHEMA = ["id", "value_sign", "value_notation", "value_token", "value_json"]
MAX_BYTES = 1_000_000

def classify_number_token(raw_text, val):
    """Classify a JSON number from its source token."""
    if isinstance(val, bool):
        return "boolean", "boolean", str(val), json.dumps(val)
    if isinstance(val, int):
        sign = "negative" if val < 0 else "positive"
        notation = "decimal"
        token = raw_text
        return sign, notation, token, json.dumps(val)
    if isinstance(val, float):
        sign = "negative" if val < 0 else "positive"
        notation = "scientific" if re.search(r'[eE]', raw_text) else "decimal"
        token = raw_text
        return sign, notation, token, json.dumps(val)
    raise ValueError(f"Expected number, got {type(val).__name__}")

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)

    # Extract raw value tokens from source text
    token_pattern = re.compile(r'"value"\s*:\s*([^\s,}\]]+)')
    raw_tokens = token_pattern.findall(raw_text)

    rows = []
    for i, rec in enumerate(records):
        row = {col: "" for col in SCHEMA}
        row["id"] = rec["id"]
        val = rec["value"]
        raw_token = raw_tokens[i]
        sign, notation, token, json_repr = classify_number_token(raw_token, val)
        row["value_sign"] = sign
        row["value_notation"] = notation
        row["value_token"] = token
        row["value_json"] = json_repr
        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 extracts the raw value tokens from the source text using a regex pattern, then classifies each value before writing. A JSON integer like 42 produces Python int, gets sign="positive" and notation="decimal". A JSON number like 1e3 produces Python float, gets sign="positive" and notation="scientific" (because the source token contains e). The value_token column stores the exact source spelling from the regex match, while value_json stores json.dumps(val). The exporter uses open(csv_path, "x") for exclusive file creation, refusing to overwrite an existing output.

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 value_sign matches the expected sign, value_notation matches the expected notation, value_token matches the source spelling, and value_json matches the JSON serialization. All ten rows passed all five checks (50/50).

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

    for csv_row, src_rec, raw_token in zip(csv_rows, records, raw_tokens):
        val = src_rec["value"]
        exp_sign, exp_notation, exp_token, exp_json = classify_number_token(raw_token, val)
        assert csv_row["value_sign"] == exp_sign
        assert csv_row["value_notation"] == exp_notation
        assert csv_row["value_token"] == exp_token
        assert csv_row["value_json"] == exp_json

Why Python changes scientific notation tokens

When Python's json.loads parses 1e3, it produces the Python float 1000.0. When json.dumps serializes that float back, it produces "1000.0", not "1e3". Similarly, json.loads("6.022e23") produces 6.022e+23, and json.dumps serializes it as "6.022e+23" — Python adds a + sign to positive exponents. The original token spelling is permanently lost after parsing. The value_token column recovers it by extracting the raw text from the source JSON before parsing, preserving the exact spelling that the author wrote.

This distinction matters because different JSON producers may use different token spellings for the same numeric value: 1e3, 1E3, 1000, and 1000.0 are four different source tokens that all represent the same number. Without value_token, a CSV reader cannot tell which spelling was used. With value_token, the original spelling is preserved as a text column alongside the parsed value.

Distinct from the integer-vs-float guide. The integer-vs-float guide distinguishes JSON integers (42) from JSON floats (3.14, 1.0) based on whether the source token has a fractional part. This guide distinguishes positive from negative and decimal from scientific notation — a different axis. A number like -2.5e2 is negative and scientific; the integer-vs-float guide would classify it as a float, but would not record its sign or notation.

Distinct from the decimal token guide. The decimal token preservation guide preserves the original digit sequence of number tokens using parse_float and parse_int hooks, focusing on cases like 1.10 versus 1.1. This guide focuses on sign and notation classification — whether the number was positive or negative, and whether it used decimal or scientific notation — which is a different question about the source token's form.

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 numeric-token-to-csv.py

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 positive and negative numbers without the sign column?

You can filter value&lit;- in a single-column CSV using string operations, but that approach is fragile: it requires parsing each cell's leading character, it does not distinguish the sign of zero (Python's json.dumps(-0.0) produces -0.0, but json.dumps(0) produces 0), and it does not tell you whether the source used scientific notation. If you need to filter "all negative values" or "all scientific-notation values", you need the value_sign and value_notation columns. Without them, a filter for negative numbers requires inspecting every cell for a leading minus sign, and a filter for scientific-notation values requires searching every cell for e or E. The four-column policy provides the typed, directly comparable fields that a spreadsheet user can filter on without parsing JSON, while the value_token column carries the exact source spelling for provenance.