Repository: pandas-dev/pandas
Pull request: BUG: read_csv not recognizing numbers appropriately when decimal is set
- [x] closes #31920
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

The old regex was shorter but pretty buggy.

Let's assume ``decimal=","`` and ``thousands="."``

For example something like ``1a.2,3`` was interpreted as numeric by the regex and converted to ``1a2.3``, because the ``.`` was not escaped in the regex.
Also something like ``1,2,3`` was interpreted as numeric by the regex and converted to ``1.2.3``. 

This one is not quite finished. We have to define a few thousand separator related things:

How strict would we want to be? Should ``1.2,3`` be interpreted as numeric and be converted to ``12.3`` or is only something like ``1.234,5`` relevant as thousands separator? C Engine validation is not strict
Additionally should ``,2`` be the number ``0.2``? -> Currently it is, because the C engine behaves the same

Problem statement:
pd.read_csv does not recognize scientific notation if 'decimal' attribute is set with engine=python
Contents of 'abc.tsv':
```
a	b
1,2	3,5E-1
1,2e2	3,5
```
Code:
```python
>>> import pandas as pd
>>> pd.read_csv('abc.tsv', '\t', decimal=',')
       a     b
0    1.2  0.35
1  120.0  3.50
>>> pd.read_csv('abc.tsv', '\t', decimal=',', engine='python')
       a       b
0    1.2  3,5E-1
1  1,2e2     3.5
```
#### Problem description
The outputs of the two read_csv commands above should be the same. In this case an obvious "solution" is to just use the C-engine, however I have to process a file where I need to use a regular expression as the delimiter.

#### Expected Output
```python
>>> pd.read_csv('abc.tsv', '\t', decimal=',', engine='python')
       a     b
0    1.2  0.35
1  120.0  3.50
```

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.6.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 19.2.0
machine          : x86_64
processor        : i386
byteorder        : little
LC_ALL           : en_US.UTF-8
LANG             : None
LOCALE           : en_US.UTF-8

pandas           : 1.0.1
numpy            : 1.18.1
pytz             : 2019.3
dateutil         : 2.8.1
pip              : 20.0.2
setuptools       : 45.2.0.post20200210
Cython           : 0.29.15
pytest           : 5.3.5
hypothesis       : 5.4.1
sphinx           : 2.4.0
blosc            : None
feather          : None
xlsxwriter       : 1.2.7
lxml.etree       : 4.5.0
html5lib         : 1.0.1
pymysql          : None
psycopg2         : None
jinja2           : 2.11.1
IPython          : 7.12.0
pandas_datareader: None
bs4              : 4.8.2
bottleneck       : 1.3.1
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.5.0
matplotlib       : 3.1.3
numexpr          : 2.7.1
odfpy            : None
openpyxl         : 3.0.3
pandas_gbq       : None
pyarrow          : None
pytables         : None
pytest           : 5.3.5
pyxlsb           : None
s3fs             : None
scipy            : 1.4.1
sqlalchemy       : 1.3.13
tables           : 3.6.1
tabulate         : None
xarray           : None
xlrd             : 1.2.0
xlwt             : 1.3.0
xlsxwriter       : 1.2.7
numba            : 0.48.0

</details>

Hints from issue discussion:

Commit message: Merge branch '31920' of https://github.com/phofl/pandas into 31920

 Conflicts:
	doc/source/whatsnew/v1.3.0.rst

Patch to review:
diff --git a/doc/source/whatsnew/v1.3.0.rst b/doc/source/whatsnew/v1.3.0.rst
index 7671962018144..ab34d70097d22 100644
--- a/doc/source/whatsnew/v1.3.0.rst
+++ b/doc/source/whatsnew/v1.3.0.rst
@@ -237,6 +237,7 @@ MultiIndex
 I/O
 ^^^
 
+- Bug in :func:`read_csv` not recognizing scientific notation if decimal is set (:issue:`31920`)
 - Bug in :func:`read_csv` interpreting ``NA`` value as comment, when ``NA`` does contain the comment string fixed for ``engine="python"`` (:issue:`34002`)
 - Bug in :func:`read_csv` raising ``IndexError`` with multiple header columns and ``index_col`` specified when file has no data rows (:issue:`38292`)
 - Bug in :func:`read_csv` not accepting ``usecols`` with different length than ``names`` for ``engine="python"`` (:issue:`16469`)
diff --git a/pandas/io/parsers.py b/pandas/io/parsers.py
index d670821c98520..22b7ba2a60f8d 100644
--- a/pandas/io/parsers.py
+++ b/pandas/io/parsers.py
@@ -2344,10 +2344,16 @@ def __init__(self, f: Union[FilePathOrBuffer, List], **kwds):
         if len(self.decimal) != 1:
             raise ValueError("Only length-1 decimal markers supported")
 
+        decimal = re.escape(self.decimal)
         if self.thousands is None:
-            self.nonnum = re.compile(fr"[^-^0-9^{self.decimal}]+")
+            regex = fr"^\-?[0-9]*({decimal}[0-9]*)?([0-9](E|e)\-?[0-9]*)?$"
         else:
-            self.nonnum = re.compile(fr"[^-^0-9^{self.thousands}^{self.decimal}]+")
+            thousands = re.escape(self.thousands)
+            regex = (
+                fr"^\-?([0-9]+{thousands}|[0-9])*({decimal}[0-9]*)?"
+                fr"([0-9](E|e)\-?[0-9]*)?$"
+            )
+        self.num = re.compile(regex)
 
     def _set_no_thousands_columns(self):
         # Create a set of column ids that are not to be stripped of thousands
@@ -3039,7 +3045,7 @@ def _search_replace_num_columns(self, lines, search, replace):
                     not isinstance(x, str)
                     or search not in x
                     or (self._no_thousands_columns and i in self._no_thousands_columns)
-                    or self.nonnum.search(x.strip())
+                    or not self.num.search(x.strip())
                 ):
                     rl.append(x)
                 else:
diff --git a/pandas/tests/io/parser/test_python_parser_only.py b/pandas/tests/io/parser/test_python_parser_only.py
index 016fae4f4a6f5..a15a0c6292be3 100644
--- a/pandas/tests/io/parser/test_python_parser_only.py
+++ b/pandas/tests/io/parser/test_python_parser_only.py
@@ -314,3 +314,51 @@ def test_malformed_skipfooter(python_parser_only):
     msg = "Expected 3 fields in line 4, saw 5"
     with pytest.raises(ParserError, match=msg):
         parser.read_csv(StringIO(data), header=1, comment="#", skipfooter=1)
+
+
+@pytest.mark.parametrize("thousands", [None, "."])
+@pytest.mark.parametrize(
+    "value, result_value",
+    [
+        ("1,2", 1.2),
+        ("1,2e-1", 0.12),
+        ("1,2E-1", 0.12),
+        ("1,2e-10", 0.0000000012),
+        ("1,2e1", 12.0),
+        ("1,2E1", 12.0),
+        ("-1,2e-1", -0.12),
+        ("0,2", 0.2),
+        (",2", 0.2),
+    ],
+)
+def test_decimal_and_exponential(python_parser_only, thousands, value, result_value):
+    # GH#31920
+    data = StringIO(
+        f"""a	b
+    1,1	{value}
+    """
+    )
+    result = python_parser_only.read_csv(
+        data, "\t", decimal=",", engine="python", thousands=thousands
+    )
+    expected = DataFrame({"a": [1.1], "b": [result_value]})
+    tm.assert_frame_equal(result, expected)
+
+
+@pytest.mark.parametrize("thousands", [None, "."])
+@pytest.mark.parametrize(
+    "value",
+    ["e11,2", "1e11,2", "1,2,2", "1,2.1", "1,2e-10e1", "--1,2", "1a.2,1", "1..2,3"],
+)
+def test_decimal_and_exponential_erroneous(python_parser_only, thousands, value):
+    # GH#31920
+    data = StringIO(
+        f"""a	b
+    1,1	{value}
+    """
+    )
+    result = python_parser_only.read_csv(
+        data, "\t", decimal=",", engine="python", thousands=thousands
+    )
+    expected = DataFrame({"a": [1.1], "b": [value]})
+    tm.assert_frame_equal(result, expected)


Code review comments:
diff_hunk: @@ -314,3 +314,51 @@ def test_malformed_skipfooter(python_parser_only):
     msg = "Expected 3 fields in line 4, saw 5"
     with pytest.raises(ParserError, match=msg):
         parser.read_csv(StringIO(data), header=1, comment="#", skipfooter=1)
+
+
+@pytest.mark.parametrize("thousands", [None, "."])
+@pytest.mark.parametrize(
+    "value, result_value",
+    [
+        ("1,2", 1.2),
+        ("1,2e-1", 0.12),
+        ("1,2E-1", 0.12),
+        ("1,2e-10", 0.0000000012),
+        ("1,2e1", 12.0),
+        ("1,2E1", 12.0),
+        ("-1,2e-1", -0.12),
+        ("0,2", 0.2),
+        (",2", 0.2),
+    ],
+)
+def test_decimal_and_exponential(python_parser_only, thousands, value, result_value):
+    # GH#31920
+    data = StringIO(
+        f"""a	b
+    1,1	{value}
+    """
+    )
+    result = python_parser_only.read_csv(
+        data, "\t", decimal=",", engine="python", thousands=thousands
+    )
+    expected = DataFrame({"a": [1.1], "b": [result_value]})
+    tm.assert_frame_equal(result, expected)
+
+
+@pytest.mark.parametrize("thousands", [None, "."])
+@pytest.mark.parametrize(
+    "value",
+    ["e11,2", "1e11,2", "1,2,2", "1,2.1", "1,2e-10e1", "--1,2", "1a.2,1", "1..2,3"],
+)
+def test_decimal_and_exponential_erroneous(python_parser_only, thousands, value):
+    # GH#31920
+    data = StringIO(
+        f"""a	b
+    1,1	{value}
+    """
+    )
+    result = python_parser_only.read_csv(
+        data, "\t", decimal=",", engine="python", thousands=thousands
path: pandas/tests/io/parser/test_python_parser_only.py
text: @user1:
```suggestion
        data, "\t", decimal=",", thousands=thousands
```

How well supported is this in the `c` engine?