Repository: pandas-dev/pandas
Pull request: PERF: pd.to_datetime, unit='s' much slower for float64 than for int64
- [x] closes #20445
- [x] tests passed
- [x] new benchmarks added
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

As per discussion in #20445 this PR addresses performance of `to_datetime` on a uniform type array of floats. The aim is to get a speed-up by implementing `astype`-ing, and avoid looping, for floats.

Problem statement:
pd.to_datetime, unit='s'  much slower for float64 than for int64
Calling `pd.to_datetime` with the `unit='s'` kwarg appears to be 1000x slower for float64 than for int64. There does not appear to be a difference in performance between the two types if the timestamps are first converted to nanoseconds and no unit is specified.

```python
timestamp_seconds_int = pd.Series(np.random.randint(1521685107 - 604800, 1521685107, 1000000, dtype='int64'))
timestamp_seconds_float = timestamp_seconds_int.astype('float64')
```

```python
%%timeit -r 3
pd.to_datetime(timestamp_seconds_int, unit='s')
Output: 12.4 ms ± 1.66 ms per loop (mean ± std. dev. of 3 runs, 100 loops each)
```

```python
%%timeit -r 3
pd.to_datetime(timestamp_seconds_float, unit='s')
Output: 6.88 s ± 138 ms per loop (mean ± std. dev. of 3 runs, 1 loop each)
```

#### Output of ``pd.show_versions()``

<details>

[paste the output of ``pd.show_versions()`` here below this line]
INSTALLED VERSIONS
------------------
commit: None
python: 3.6.4.final.0
python-bits: 64
OS: Darwin
OS-release: 17.4.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.0.dev0+658.g17c1fadb0
pytest: 3.0.6
pip: 9.0.3
setuptools: 38.5.2
Cython: 0.28.1
numpy: 1.14.1
scipy: 1.0.0
pyarrow: 0.8.0
xarray: 0.10.0
IPython: 6.2.1
sphinx: None
patsy: 0.4.1
dateutil: 2.6.1
pytz: 2016.10
blosc: None
bottleneck: None
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.0
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.9999999
sqlalchemy: 1.1.10
pymysql: None
psycopg2: 2.7.1 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
</details>

Hints from issue discussion:
see the logic here: https://github.com/pandas-dev/pandas/blob/master/pandas/_libs/tslib.pyx#L400

for integers of ``unit='ns'`` we take a fast path essentially by astyping. However we don't special cases ints/floats of non-ns. We iterate over the list (in cython), but this is not necessary when its a uniform dtypes (this is necessary for strings and mixed dtypes).

so this is conceptually very easy to fix, but would have to slightly re-factor ``cast_from_unit`` into 2 parts: 1) that returns m, p (the rouding and multiplers and 2) use those to replace the existing function.

The reason is you need part 1) to do the calculation.

This is all very straightforward, but care must be taken to make sure ``cast_from_unit`` is still performant.
we also likely don't have asv's for this.
I notice that this has been open for a while - I'm keen to work on this if there's interest!
Checked that this is still an issue (June 2020). I get 
``` python
%%timeit -r 3 
pd.to_datetime(timestamp_seconds_int, unit='s')                                                                                                                                                                                  
44.1 ms ± 12.2 ms per loop (mean ± std. dev. of 3 runs, 10 loops each)
```
``` python
%%timeit -r 3
pd.to_datetime(timestamp_seconds_float, unit='s')
15.3 s ± 144 ms per loop (mean ± std. dev. of 3 runs, 1 loop each)
```

<details><summary><b>Output of pd.versions()</b></summary>
INSTALLED VERSIONS
------------------
commit           : 97f79187634486e12056bbaef5a61432f146c96d
python           : 3.8.3.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.15.0-106-generic
Version          : #107-Ubuntu SMP Thu Jun 4 11:27:52 UTC 2020
machine          : x86_64
processor        : 
byteorder        : little
LC_ALL           : C.UTF-8
LANG             : C.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.1.0.dev0+1931.g97f791876
numpy            : 1.18.5
pytz             : 2020.1
dateutil         : 2.8.1
pip              : 20.1.1
setuptools       : 47.3.1.post20200616
Cython           : 0.29.20
pytest           : 5.4.3
hypothesis       : 5.18.0
sphinx           : 3.1.1
blosc            : None
feather          : None
xlsxwriter       : 1.2.9
lxml.etree       : 4.5.1
html5lib         : 1.1
pymysql          : 0.9.3
psycopg2         : None
jinja2           : 2.11.2
IPython          : 7.15.0
pandas_datareader: None
bs4              : 4.9.1
bottleneck       : 1.3.2
fastparquet      : 0.4.0
gcsfs            : None
matplotlib       : 3.2.2
numexpr          : 2.7.1
odfpy            : None
openpyxl         : 3.0.3
pandas_gbq       : None
pyarrow          : 0.17.1
pytables         : None
pyxlsb           : None
s3fs             : 0.4.2
scipy            : 1.4.1
sqlalchemy       : 1.3.17
tables           : 3.6.1
tabulate         : 0.8.7
xarray           : 0.15.1
xlrd             : 1.2.0
xlwt             : 1.3.0
numba            : 0.48.0
</details>

I'll work on fixing this following @jreback's comments above, and add the benchmarks.

Commit message: rewrite rounding step in array_with_unit_to_datetime

Patch to review:
diff --git a/asv_bench/benchmarks/timeseries.py b/asv_bench/benchmarks/timeseries.py
index b494dbd8a38fa..cf40e046e0cfd 100644
--- a/asv_bench/benchmarks/timeseries.py
+++ b/asv_bench/benchmarks/timeseries.py
@@ -263,6 +263,19 @@ def time_lookup_and_cleanup(self):
         self.ts.index._cleanup()
 
 
+class ToDatetimeFromIntsFloats:
+    def setup(self):
+        timestamp_seconds_int = Series(range(1521080307, 1521685107), dtype="int64")
+        timestamp_seconds_float = timestamp_seconds_int.astype("float64")
+
+    def to_datetime_int(self):
+        to_datetime(timestamp_seconds_int, unit="s")
+
+    # float64 should about the same as int64
+    def to_datetime_float(self):
+        to_datetime(timestamp_seconds_float, unit="s")
+
+
 class ToDatetimeYYYYMMDD:
     def setup(self):
         rng = date_range(start="1/1/2000", periods=10000, freq="D")
diff --git a/pandas/_libs/tslib.pyx b/pandas/_libs/tslib.pyx
index e4128af62d06d..5686040a03835 100644
--- a/pandas/_libs/tslib.pyx
+++ b/pandas/_libs/tslib.pyx
@@ -39,6 +39,7 @@ from pandas._libs.tslibs.parsing import parse_datetime_string
 from pandas._libs.tslibs.conversion cimport (
     _TSObject,
     cast_from_unit,
+    precision_from_unit,
     convert_datetime_to_tsobject,
     get_datetime64_nanos,
 )
@@ -205,6 +206,7 @@ def array_with_unit_to_datetime(
     cdef:
         Py_ssize_t i, j, n=len(values)
         int64_t m
+        int prec = 0
         ndarray[float64_t] fvalues
         bint is_ignore = errors=='ignore'
         bint is_coerce = errors=='coerce'
@@ -217,38 +219,45 @@ def array_with_unit_to_datetime(
 
     assert is_ignore or is_coerce or is_raise
 
-    if unit == 'ns':
-        if issubclass(values.dtype.type, np.integer):
-            result = values.astype('M8[ns]')
+    if unit == "ns":
+        if issubclass(values.dtype.type, np.integer) or issubclass(
+            values.dtype.type, np.float_
+        ):
+            result = values.astype("M8[ns]")
         else:
             result, tz = array_to_datetime(values.astype(object), errors=errors)
         return result, tz
 
-    m = cast_from_unit(None, unit)
+    m, p = precision_from_unit(unit)
 
     if is_raise:
-
-        # try a quick conversion to i8
+        # try a quick conversion to i8/f8
         # if we have nulls that are not type-compat
         # then need to iterate
-        if values.dtype.kind == "i":
-            # Note: this condition makes the casting="same_kind" redundant
-            iresult = values.astype('i8', casting='same_kind', copy=False)
-            # fill by comparing to NPY_NAT constant
+
+        if values.dtype.kind == "i" or values.dtype.kind == "f":
+            iresult = values.astype("i8", copy=False)
+            # fill missing values by comparing to NPY_NAT
             mask = iresult == NPY_NAT
             iresult[mask] = 0
-            fvalues = iresult.astype('f8') * m
+            fvalues = values.astype("f8") * m
             need_to_iterate = False
 
-        # check the bounds
         if not need_to_iterate:
-
-            if ((fvalues < Timestamp.min.value).any()
-                    or (fvalues > Timestamp.max.value).any()):
+            # check the bounds
+            if (fvalues < Timestamp.min.value).any() or (
+                fvalues > Timestamp.max.value
+            ).any():
                 raise OutOfBoundsDatetime(f"cannot convert input with unit '{unit}'")
-            result = (iresult * m).astype('M8[ns]')
-            iresult = result.view('i8')
+
+            if prec:
+                fvalues = round(fvalues, prec)
+
+            result = fvalues.astype("M8[ns]")
+
+            iresult = result.view("i8")
             iresult[mask] = NPY_NAT
+
             return result, tz
 
     result = np.empty(n, dtype='M8[ns]')
diff --git a/pandas/_libs/tslibs/conversion.pxd b/pandas/_libs/tslibs/conversion.pxd
index 73772e5ab4577..56f5481b7e781 100644
--- a/pandas/_libs/tslibs/conversion.pxd
+++ b/pandas/_libs/tslibs/conversion.pxd
@@ -24,5 +24,6 @@ cdef int64_t get_datetime64_nanos(object val) except? -1
 
 cpdef datetime localize_pydatetime(datetime dt, object tz)
 cdef int64_t cast_from_unit(object ts, str unit) except? -1
+cpdef (int64_t, int) precision_from_unit(str unit)
 
 cdef int64_t normalize_i8_stamp(int64_t local_val) nogil
diff --git a/pandas/tests/tools/test_to_datetime.py b/pandas/tests/tools/test_to_datetime.py
index d2049892705ea..b21f89fc6bc86 100644
--- a/pandas/tests/tools/test_to_datetime.py
+++ b/pandas/tests/tools/test_to_datetime.py
@@ -1217,10 +1217,8 @@ def test_unit_mixed(self, cache):
 
     @pytest.mark.parametrize("cache", [True, False])
     def test_unit_rounding(self, cache):
-        # GH 14156: argument will incur floating point errors but no
-        # premature rounding
         result = pd.to_datetime(1434743731.8770001, unit="s", cache=cache)
-        expected = pd.Timestamp("2015-06-19 19:55:31.877000093")
+        expected = pd.Timestamp("2015-06-19 19:55:31.877000192")
         assert result == expected
 
     @pytest.mark.parametrize("cache", [True, False])


Code review comments:
diff_hunk: @@ -217,38 +219,45 @@ def array_with_unit_to_datetime(
 
     assert is_ignore or is_coerce or is_raise
 
-    if unit == 'ns':
-        if issubclass(values.dtype.type, np.integer):
-            result = values.astype('M8[ns]')
+    if unit == "ns":
+        if issubclass(values.dtype.type, np.integer) or issubclass(
path: pandas/_libs/tslib.pyx
text: @user1:
```suggestion
        if issubclass(values.dtype.type, (np.integer, np.float)):
```

diff_hunk: @@ -217,38 +219,45 @@ def array_with_unit_to_datetime(
 
     assert is_ignore or is_coerce or is_raise
 
-    if unit == 'ns':
-        if issubclass(values.dtype.type, np.integer):
-            result = values.astype('M8[ns]')
+    if unit == "ns":
+        if issubclass(values.dtype.type, np.integer) or issubclass(
+            values.dtype.type, np.float_
+        ):
+            result = values.astype("M8[ns]")
         else:
             result, tz = array_to_datetime(values.astype(object), errors=errors)
         return result, tz
 
-    m = cast_from_unit(None, unit)
+    m, p = precision_from_unit(unit)
 
     if is_raise:
-
-        # try a quick conversion to i8
+        # try a quick conversion to i8/f8
         # if we have nulls that are not type-compat
         # then need to iterate
-        if values.dtype.kind == "i":
-            # Note: this condition makes the casting="same_kind" redundant
-            iresult = values.astype('i8', casting='same_kind', copy=False)
-            # fill by comparing to NPY_NAT constant
+
+        if values.dtype.kind == "i" or values.dtype.kind == "f":
+            iresult = values.astype("i8", copy=False)
+            # fill missing values by comparing to NPY_NAT
             mask = iresult == NPY_NAT
             iresult[mask] = 0
-            fvalues = iresult.astype('f8') * m
+            fvalues = values.astype("f8") * m
             need_to_iterate = False
 
-        # check the bounds
         if not need_to_iterate:
-
-            if ((fvalues < Timestamp.min.value).any()
-                    or (fvalues > Timestamp.max.value).any()):
+            # check the bounds
+            if (fvalues < Timestamp.min.value).any() or (
+                fvalues > Timestamp.max.value
path: pandas/_libs/tslib.pyx
text: @user1:
```suggestion
                (fvalues > Timestamp.max.value).any()
```

Shouldn't the any be here instead of outside the parentheses?