PERF: Fix performance regression in infer_dtype
State: closed

Fixes major performance regression in `infer_dtype` introduced in
aaaac86ee019675119cb0ae9c3fb7a2b7eef9959

- [x] closes #28814 
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Performance
description: Memory or execution speed performance

Comments:
Profiling the following function (before/after):
```python
def slow_infer_dtype():
    df = pd.DataFrame(np.ones((100000, 1000)))
    for col in df.columns:
        infer_dtype(df[col], skipna=True)
```
```
master:
300127297 function calls (300127289 primitive calls) in 67.103 seconds
infer_dtype_perf:
126297 function calls (126289 primitive calls) in 1.050 seconds
```

I seem to have some issues getting asv running.  ASV consistently fails to compile the cython extensions.
nice speedup!
Hello @groutr! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:



There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-12-17 00:16:18 UTC
@groutr one more linting issue:

```
black --version
black, version 19.10b0
Checking black formatting
would reformat /home/runner/work/pandas/pandas/asv_bench/benchmarks/dtypes.py
Oh no! 💥 💔 💥
1 file would be reformatted, 885 files would be left unchanged.
```
Were you able to get the benchmarks running? Roughly how long do your new ones take?

For testing you can do `asv dev -b InferDtypes`, which will reuse your same python / pandas install.
@TomAugspurger oh, sorry, I didn't know we ran black on asv_bench too.
I was eventually able to get asv running.
```
[  0.00%] ·· Benchmarking existing-py_home_grout_miniconda3_envs_pandas-dev_bin_python
[ 25.00%] ··· dtypes.InferDtypes.time_infer                                                                                                                        ok
[ 25.00%] ··· ============= =========
                  dtype
              ------------- ---------
                np-object    318±0μs
                py-object    335±0μs
                 np-null     342±0μs
                 py-null     329±0μs
                  np-int     323±0μs
               np-floating   342±0μs
                  empty      329±0μs
                  bytes      321±0μs
              ============= =========

[ 50.00%] ··· dtypes.InferDtypes.time_infer_skipna                                                                                                                 ok
[ 50.00%] ··· ============= =========
                  dtype
              ------------- ---------
                np-object    410±0μs
                py-object    431±0μs
                 np-null     418±0μs
                 py-null     418±0μs
                  np-int     413±0μs
               np-floating   414±0μs
                  empty      415±0μs
                  bytes      415±0μs
              ============= =========
```
Ah one more error linting error @groutr, sorry :) You might want to setup pre-commit: https://dev.pandas.io/docs/development/contributing.html#python-pep8-black

@datapythonista I don't recall, but before moving CI checks to GitHub actions, did we run all the checks and only report on failures at the end? So if there are both black and isort issues, you'd get both reported, instead of just the first failure? Sorry if that's a known issue.
I'm quite sure that when we moved to github actions all checks were running even if they were failures, but that stopped working. I reported it to github one or two weeks ago, but haven't heard from them yet. 
@groutr can you show the asv for master vs this branch.
~~@jreback~~
~~Running:~~
```
git checkout infer_dtype_perf
conda activate pandas-dev
cd asv_bench
asv continuous -f 1.1 master infer_dtype_perf -b dtypes.InferDtypes
```
~~Produces identical timings for both `master` and `infer_dtype_perf`.~~
~~I don't understand why, since my own timings show a major performance improvement.~~

EDIT: Please disregard this comment.  I had written the benchmarking code incorrectly.
```
(pandas-dev) ❯ asv continuous upstream/master infer_dtype_perf -b dtypes.InferDtypes
· Creating environments
· Discovering benchmarks
·· Uninstalling from conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
·· Installing 903abc2f <infer_dtype_perf> into conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
· Running 4 total benchmarks (2 commits * 1 environments * 2 benchmarks)
[  0.00%] · For pandas commit 37dfcc1a <master> (round 1/2):
[  0.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 12.50%] ··· Running (dtypes.InferDtypes.time_infer--)..
[ 25.00%] · For pandas commit 903abc2f <infer_dtype_perf> (round 1/2):
[ 25.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 25.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 37.50%] ··· Running (dtypes.InferDtypes.time_infer--)..
[ 50.00%] · For pandas commit 903abc2f <infer_dtype_perf> (round 2/2):
[ 50.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 62.50%] ··· dtypes.InferDtypes.time_infer                                                                                                                        ok
[ 62.50%] ··· ============= =============
                  dtype                  
              ------------- -------------
                np-object     1.95±0.2ms 
                py-object    4.51±0.04ms 
                 np-null      4.04±0.1μs 
                 py-null     4.49±0.04ms 
                  np-int      3.97±0.2μs 
               np-floating    3.96±0.4μs 
                  empty      9.32±0.06μs 
                  bytes      4.16±0.03ms 
              ============= =============

[ 75.00%] ··· dtypes.InferDtypes.time_infer_skipna                                                                                                                 ok
[ 75.00%] ··· ============= =============
                  dtype                  
              ------------- -------------
                np-object    7.09±0.06ms 
                py-object     9.66±0.1ms 
                 np-null      3.95±0.1μs 
                 py-null     6.92±0.06ms 
                  np-int     3.87±0.04μs 
               np-floating   3.94±0.03μs 
                  empty       14.3±0.2μs
                  bytes      9.53±0.07ms
              ============= =============

[ 75.00%] · For pandas commit 37dfcc1a <master> (round 2/2):
[ 75.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 75.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 87.50%] ··· dtypes.InferDtypes.time_infer                                                                                                                        ok
[ 87.50%] ··· ============= =============
                  dtype
              ------------- -------------
                np-object    1.92±0.05ms
                py-object    4.47±0.08ms
                 np-null      4.09±0.7μs
                 py-null      4.56±0.2ms
                  np-int      4.06±0.1μs
               np-floating    3.94±0.2μs
                  empty       9.34±0.3μs
                  bytes      4.22±0.06ms
              ============= =============

[100.00%] ··· dtypes.InferDtypes.time_infer_skipna                                                                                                                 ok
[100.00%] ··· ============= =============
                  dtype
              ------------- -------------
                np-object     7.24±0.3ms
                py-object     9.84±0.3ms
                 np-null      11.7±0.2ms
                 py-null     7.04±0.08ms
                  np-int      11.5±0.4ms
               np-floating    11.9±0.5ms
                  empty       14.3±0.2μs
                  bytes       9.62±0.1ms
              ============= =============

       before           after         ratio
     [37dfcc1a]       [903abc2f]
     <master>         <infer_dtype_perf>
-      11.7±0.2ms       3.95±0.1μs     0.00  dtypes.InferDtypes.time_infer_skipna('np-null')
-      11.5±0.4ms      3.87±0.04μs     0.00  dtypes.InferDtypes.time_infer_skipna('np-int')
-      11.9±0.5ms      3.94±0.03μs     0.00  dtypes.InferDtypes.time_infer_skipna('np-floating')

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
PERFORMANCE INCREASED.
```
@WillAyd, I already did in https://github.com/pandas-dev/pandas/pull/30202/commits/ea579f7e5069fb849b69e73f4246509b92d9a0e0
thanks