Decimal Number (Comma Separator)
representation.numeric.decimal_number_commasince finetype v0.5.1Number with comma as decimal separator and period as thousands separator (e.g., 1.234,56, 999,99, -42,5). The inverse of US format where period is decimal and comma is thousands. Silent corruption risk: "1.234" is either 1234 (comma-decimal thousands) or 1.234 (US decimal). Transforms to DOUBLE after swapping separators.
Operations on this type: Parse nested values
Try a value
Decimal Number (Comma Separator)'s rule accepts it.
Reads it as 1234.56
Use it
finetype infer -i '1.234,56' --mode column
# → representation.numeric.decimal_number_commaSELECT ft_infer('1.234,56');
-- → 'representation.numeric.decimal_number_comma'CAST(REPLACE(REPLACE({col}, '.', ''), ',', '.') AS DOUBLE)How finetype reads the samples
DuckDB 1.5.5 · finetype 0.6.66
| Value | As DOUBLE | fractional_part | integer_part |
|---|---|---|---|
| 1.234,56 | 1234.56 | 560000 | 1234 |
| 999,99 | 999.99 | 990000 | 999 |
| 1.000.000,00 | 1000000.0 | 0 | 1000000 |
| -42,5 | -42.5 | 500000 | -43 |
| 0,75 | 0.75 | 750000 | 0 |
| 12.345,678 | 12345.678 | 678000 | 12345 |
| 100,00 | 100.0 | 0 | 100 |
| -1.234,00 | -1234.0 | 0 | -1234 |
Pattern
^-?[0-9]{1,3}(\.[0-9]{3})*(,[0-9]+)?$|^-?[0-9]+,[0-9]+$| Value | Length | Shape |
|---|---|---|
| 1.234,56 | 8 | matches |
| 999,99 | 6 | matches |
| 1.000.000,00 | 12 | matches |
| -42,5 | 5 | matches |
| 0,75 | 4 | matches |
| 12.345,678 | 10 | matches |
| 100,00 | 6 | matches |
| -1.234,00 | 9 | matches |
| 1-234,56 | 8 | rejected: separator swapped |
Similar types
Going the other way, this type's own rule accepts 3 of 4 samples of Integer Number and 1 of 4 samples of Percentage.
These types are compared on one page: Decimal comma vs decimal point.
Column-level detection is important since single values like "1.234" are ambiguous between EU (=1234) and US (=1.234).
Background
Origin
Tables of logarithms prepared by John Napier in 1614 and 1619 used the period as the decimal separator.[1] Italy is among the countries that chose the comma to mark the decimal position.[1] English-speaking countries took the comma to separate groups of three digits instead.[1]
CGPM Resolution 10 says numbers may be divided into groups of three to make them easier to read.[2] CGPM Resolution 10 also says that neither dots nor commas are ever inserted in the spaces between groups.[2]
Unicode's CLDR data for the German locale gives a comma as the decimal symbol.[3] Unicode's CLDR data for the German locale gives a period as the grouping symbol.[3]
Use
By default, Microsoft Excel uses the system separators defined in the regional settings in Control Panel.[4] When a CSV file arrives with comma decimals, it is worth asking which regional settings the machine that exported it was using. (Meridian's view)
The pandas documentation suggests passing a comma as the decimal parameter for European data.[5] DuckDB's CSV reader has a decimal_separator option whose default is a period.[6] DuckDB's CSV reader has a thousands option, which must be a single character different from the decimal_separator.[6] Python's locale.atof converts a string to a number following the LC_NUMERIC settings.[7]
Decide the convention per column, not per value: 1.234 on its own could be one thousand two hundred and thirty-four or a little over one, and only its neighbours tell you which. (Meridian's view) Values with two separators, such as 1.234,56, settle the question, so look for them before trusting a column of short numbers. (Meridian's view) Strip the grouping periods before swapping the comma for a period, and never the other way round, or every value with a thousands group will be wrong. (Meridian's view) Keep the original text column until the converted totals have been checked against a known figure from the source. (Meridian's view)
Where you'll meet it
The German Federal Statistical Office's notes on its short-term indicator CSV files say decimal places in the values are separated by a comma.[8]
Sources
- [1]Decimal separatorsecondary source
- [2]Resolution 10 of the 22nd CGPM (2003)standard
- [3]cldr-json/cldr-numbers-full/main/de/numbers.jsonstandard
- [4]Change the character used to separate thousands or decimals | Microsoft Supportdocumentation
- [5]pandas.read_csv — pandas 3.0.6 documentationdocumentation
- [6]CSV Importdocumentation
- [7]locale — Internationalization servicesdocumentation
- [8]Notes on CSV files regarding short-term indicators - German Federal Statistical Officeorganisation
How it's detected
European decimal format uses period for thousands and comma for decimal — the exact inverse of US format. Transform removes period thousands separators, replaces comma with period, then casts to DOUBLE. Column-level detection is important since single values like "1.234" are ambiguous between EU (=1234) and US (=1.234). The Sense model's locale awareness may help disambiguation.
Schema
https://meridian.online/schemas/representation.numeric.decimal_number_comma · Frictionless type number
finetype taxonomy representation.numeric.decimal_number_comma -o json-schema