Repository: pandas-dev/pandas
Pull request: PERF: GH28635 Add ASV benchmark for resample after groupby
- [x] closes #28635 (Replace xxxx with the GitHub issue number)
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Problem statement:
Resample on MultiIndex level takes much more time than on normal column
#### Code Sample

```python
import pandas as pd
import numpy as np

n=80000
g=5

index = pd.MultiIndex.from_product([
    np.arange(g),
    pd.to_timedelta(np.arange(n), unit='s')
])
data = pd.DataFrame(
    np.random.randint(0,1000,size=(len(index))),
    index=index
)

%timeit data.groupby(level=0).resample('10s',level=1).mean()

# 3.93 s ± 295 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit data.reset_index(1).groupby(level=0).resample('10s',on='level_1').mean()

# 157 ms ± 3.33 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
```
#### Problem description

``resample`` seem to take much more time when resampling on a level of MultiIndex instead of normal data column. The second, faster approach is more convoluted and is not what first comes to mind. 

#### Expected Output

Both operations should around the same amount of time with second possibly slightly more, because of additional reset_index operation. If the difference is expected than first operation should show warning hinting on optimal solution. 

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.4.final.0
python-bits      : 64
OS               : Windows
OS-release       : 10
machine          : AMD64
processor        : Intel64 Family 6 Model 94 Stepping 3, GenuineIntel
byteorder        : little
LC_ALL           : None
LANG             : None
LOCALE           : None.None

pandas           : 0.25.1
numpy            : 1.16.4
pytz             : 2019.2
dateutil         : 2.8.0
pip              : 19.2.2
setuptools       : 41.0.1
Cython           : 0.29.13
pytest           : 5.0.1
hypothesis       : None
sphinx           : 2.1.2
blosc            : None
feather          : None
xlsxwriter       : 1.1.8
lxml.etree       : 4.4.1
html5lib         : 1.0.1
pymysql          : None
psycopg2         : None
jinja2           : 2.10.1
IPython          : 7.8.0
pandas_datareader: None
bs4              : 4.8.0
bottleneck       : 1.2.1
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.4.1
matplotlib       : 3.1.1
numexpr          : 2.7.0
odfpy            : None
openpyxl         : 2.6.2
pandas_gbq       : None
pyarrow          : None
pytables         : None
s3fs             : None
scipy            : 1.3.1
sqlalchemy       : 1.3.7
tables           : 3.5.2
xarray           : None
xlrd             : 1.2.0
xlwt             : 1.3.0
xlsxwriter       : 1.1.8
</details>

Hints from issue discussion:
Performance looks comparable now. Could use an ASV

```
In [9]: import pandas as pd
   ...: import numpy as np
   ...:
   ...: n=80000
   ...: g=5
   ...:
   ...: index = pd.MultiIndex.from_product([
   ...:     np.arange(g),
   ...:     pd.to_timedelta(np.arange(n), unit='s')
   ...: ])
   ...: data = pd.DataFrame(
   ...:     np.random.randint(0,1000,size=(len(index))),
   ...:     index=index
   ...: )
   ...:
   ...: %timeit data.groupby(level=0).resample('10s',level=1).mean()
116 ms ± 7.01 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [10]: %timeit data.reset_index(1).groupby(level=0).resample('10s',on='level_1').mean()
111 ms ± 7.26 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
```
Hey, @mroeschke I'm also getting similar performance for both of them. Is there anything else to do here? I'm happy to work on it if there is.
```python
import pandas as pd
import numpy as np

n=80000
g=5

index = pd.MultiIndex.from_product([
    np.arange(g),
    pd.to_timedelta(np.arange(n), unit='s')
])
data = pd.DataFrame(
    np.random.randint(0,1000,size=(len(index))),
    index=index
)

%timeit data.groupby(level=0).resample('10s',level=1).mean()

%timeit data.reset_index(1).groupby(level=0).resample('10s',on='level_1').mean()
```
```
104 ms ± 4.3 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
105 ms ± 2.45 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
```
> Is there anything else to do here?

Yes, to track performance regressions an ASV benchmark needs to be added in `asv_bench/benchmarks/groupby.py`
take

Commit message: GH28635 Add ASV benchmark for resample after groupby

Patch to review:
diff --git a/asv_bench/benchmarks/groupby.py b/asv_bench/benchmarks/groupby.py
index 97f48a3a6f69f..37b6500d97c28 100644
--- a/asv_bench/benchmarks/groupby.py
+++ b/asv_bench/benchmarks/groupby.py
@@ -14,6 +14,7 @@
     Timestamp,
     date_range,
     period_range,
+    to_timedelta,
 )
 
 from .pandas_vb_common import tm
@@ -987,4 +988,26 @@ def time_sample_weights(self):
         self.df.groupby(self.groups).sample(n=1, weights=self.weights)
 
 
+class Resample:
+    # GH 28635
+    def setup(self):
+        num_timedeltas = 100_000
+        num_groups = 5
+
+        index = MultiIndex.from_product(
+            [
+                np.arange(num_groups),
+                to_timedelta(np.arange(num_timedeltas), unit="s"),
+            ]
+        )
+
+        self.df = DataFrame(np.random.randint(0, 1000, size=(len(index))), index=index)
+
+    def time_resample(self):
+        self.df.reset_index(1).groupby(level=0).resample("10s", on="level_1").mean()
+
+    def time_resample_multiindex(self):
+        self.df.groupby(level=0).resample("10s", level=1).mean()
+
+
 from .pandas_vb_common import setup  # noqa: F401 isort:skip


Code review comments:
diff_hunk: @@ -987,4 +988,26 @@ def time_sample_weights(self):
         self.df.groupby(self.groups).sample(n=1, weights=self.weights)
 
 
+class Resample:
+    # GH 28635
+    def setup(self):
+        num_timedeltas = 100_000
+        num_groups = 5
+
+        index = MultiIndex.from_product(
+            [
+                np.arange(num_groups),
+                to_timedelta(np.arange(num_timedeltas), unit="s"),
+            ]
+        )
+
+        self.df = DataFrame(np.random.randint(0, 1000, size=(len(index))), index=index)
+
+    def time_resample(self):
+        self.df.reset_index(1).groupby(level=0).resample("10s", on="level_1").mean()
path: asv_bench/benchmarks/groupby.py
text: @user1:
Could you prepare df as needed in setup so that the time_* function only calls the function we want to measure?

@author:
Ah yes, that makes sense, I did not consider that. I moved the reset_index() into the setup method. I also added names for the index levels, because the whole level=0, level=1 felt extra confusing after that change.

@user1:
You could also move the groupby call I think

@author:
Oh I see, I thought since this is in file groupby.py we are measuring the perfomance of the combination of the groupby and resample operation? The class just before my addition "Sample" also has the groupby inside the time_ methods

@user1:
Depends a bit, you could do it both ways. If you specifically would want to test the resample operation, it would be better to move groupby to setup, to reduce possible noise. If you want to measure both, then the current setup is preferable

@author:
I personally think that measuring both reflects the spirit of the initial performance issue more accurately