Repository: pandas-dev/pandas
Pull request: PERF: to speed up rendering of styler
see https://github.com/pandas-dev/pandas/issues/19917#issuecomment-646209895

- [x] closes #19917
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
Styler extremely slow
#### Code Sample

```python
def highlighter(col):
    '''
    Highlights rows in `highlight_map` keys where df[`highlight map` values]==1
    '''

    highlight_map = {
        'Service Type': 'ServiceType_Added',
        'Store #': 'Store_Added',
    }
    if col.name in highlight_map:
        return ['background-color: yellow' if final[highlight_map[col.name]][v] else '' for v in col.index.tolist()]
    else:
        return [''] * len(col)
    
styled = final.style.apply(highlighter)
styled.to_excel('highlights.xlsx', engine='openpyxl') 

```
#### Problem description

Here I have some conditional highlighting on a df with 18k rows. The issue is that despite preceding complex operations on the df (such as conditional merges and `df.apply` by row) taking ~300ms at the most, the `Styler.apply` part takes over two minutes. I realize this feature is in development but I am wondering if there is a way to make it faster or if this is a known issue.

Hints from issue discussion:
Is it `Styler.apply` that's slow, or your function, or the writer? Have you profiled it to get a sense for where the time being spent?

Note that `Styler` builds up a list of tasks lazily. To actually run it you'll have to call `_compute`, so you'll want to profile something like

```python
styled = final.style.apply(highlighter)._compute()
```
Good point.
Here are some times:

Function scanning the df and writing new values `final.apply(highlighter)`  1.42s

The Styler by itself `final.style.apply(highlighter)._compute()`   1m 28.3s

The Styler and  openpyxl writer `final.style.apply(highlighter).to_excel('highlights.xlsx', engine='openpyxl') `   
 2m 31s

> final.style.apply(highlighter)._compute()

If you want, you can break that down into how much time is spent in pandas' `Styler._apply` vs. your function. I suspect the majority will be in your function, in which case there's nothing pandas can do to hep ;)

For the `to_excel` part, I'd be curious to see a breakdown between pandas and whatever engine is doing the writing. I'm not familiar with that bit of the code though I'd guess most of the time will be spent in the writer engine.
As stated above, unless I misunderstand, calling the function independently of the styler using `df.apply(highlight)` takes 1.42s as opposed to with the styler `df.style.apply(highlight)` which takes 1m 28s. 

Both calls have the same amount of iteration over the original dataframe, the only difference being the latter creates a `Style` object.
Therefore I don't believe it is my function that is slowing the process.
>  Therefore I don't believe it is my function that is slowing the process.

Could you do some line profiling then? Or post a reproducible example? `_update_context` could be to blame, but it's hard to say for sure.
@N2ITN find any time to profile this?
Closing, let us know if you're able to profile things.
I'm experiencing the same issue and did some profiling.

I have a very small DataFrame:

```python
In [4]: df.shape
Out[4]: (78, 4)
```

Just adding a `text-align: left` property takes almost 1 second:

```
In [5]: %%time
    ...: s = df.style.set_properties(**{'text-align': 'left'})
    ...: s.render();
    ...: 
CPU times: user 844 ms, sys: 68 ms, total: 912 ms
Wall time: 833 ms
```

I ran the line profiler and pasted the output [here](https://pastebin.com/raw/ShfAcVGx). As suspected most time is spent in `_update_ctx()`.

@TomAugspurger Could you please reopen the issue? I'm happy to provide more info, just let me know what is helpful!
Profiling would be helpful.

On Sat, Mar 30, 2019 at 5:52 AM Simon Gurcke <notifications@github.com>
wrote:

> I'm experiencing the same issue and did some profiling.
>
> I have a very small DataFrame:
>
> In [4]: df.shape
> Out[4]: (78, 4)
>
> Just adding a text-align: left property takes almost 1 second:
>
> In [5]: %%time
>     ...: s = df.style.set_properties(**{'text-align': 'left'})
>     ...: s.render();
>     ...:
> CPU times: user 844 ms, sys: 68 ms, total: 912 ms
> Wall time: 833 ms
>
> I ran the line profiler and pasted the output here
> <https://pastebin.com/raw/ShfAcVGx>. As suspected most time is spent in
> _update_ctx().
>
> @TomAugspurger <https://github.com/TomAugspurger> Could you please reopen
> the issue? I'm happy to provide more info, just let me know what is helpful!
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/issues/19917#issuecomment-478234444>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/ABQHIpgucxErZ7xaqt2nSz2arz6AAxpRks5vb0H_gaJpZM4ST6vu>
> .
>

Ah, I missed that you linked to some. Does set_properties update every cell
in the table? It'd be nice to avoid the
calls to get_indexer in those cases.

```

   484       312    1351736.0   4332.5     86.9                  i =
self.index.get_indexer([row_label])[0]
   485       312     170846.0    547.6     11.0                  j =
self.columns.get_indexer([col_label])[0]

```


On Sat, Mar 30, 2019 at 6:33 AM Tom Augspurger <tom.augspurger88@gmail.com>
wrote:

> Profiling would be helpful.
>
> On Sat, Mar 30, 2019 at 5:52 AM Simon Gurcke <notifications@github.com>
> wrote:
>
>> I'm experiencing the same issue and did some profiling.
>>
>> I have a very small DataFrame:
>>
>> In [4]: df.shape
>> Out[4]: (78, 4)
>>
>> Just adding a text-align: left property takes almost 1 second:
>>
>> In [5]: %%time
>>     ...: s = df.style.set_properties(**{'text-align': 'left'})
>>     ...: s.render();
>>     ...:
>> CPU times: user 844 ms, sys: 68 ms, total: 912 ms
>> Wall time: 833 ms
>>
>> I ran the line profiler and pasted the output here
>> <https://pastebin.com/raw/ShfAcVGx>. As suspected most time is spent in
>> _update_ctx().
>>
>> @TomAugspurger <https://github.com/TomAugspurger> Could you please
>> reopen the issue? I'm happy to provide more info, just let me know what is
>> helpful!
>>
>> —
>> You are receiving this because you were mentioned.
>> Reply to this email directly, view it on GitHub
>> <https://github.com/pandas-dev/pandas/issues/19917#issuecomment-478234444>,
>> or mute the thread
>> <https://github.com/notifications/unsubscribe-auth/ABQHIpgucxErZ7xaqt2nSz2arz6AAxpRks5vb0H_gaJpZM4ST6vu>
>> .
>>
>

Commit message: PERF: speed up rendering of styler (#19917)

see https://github.com/pandas-dev/pandas/issues/19917

Patch to review:
diff --git a/asv_bench/asv.conf.json b/asv_bench/asv.conf.json
index 7c10a2d17775a..4583fac85b776 100644
--- a/asv_bench/asv.conf.json
+++ b/asv_bench/asv.conf.json
@@ -53,6 +53,7 @@
         "xlwt": [],
         "odfpy": [],
         "pytest": [],
+        "jinja2": [],
         // If using Windows with python 2.7 and want to build using the
         // mingw toolchain (rather than MSVC), uncomment the following line.
         // "libpython": [],
diff --git a/asv_bench/benchmarks/io/style.py b/asv_bench/benchmarks/io/style.py
new file mode 100644
index 0000000000000..4fc07bbabda06
--- /dev/null
+++ b/asv_bench/benchmarks/io/style.py
@@ -0,0 +1,34 @@
+import numpy as np
+
+from pandas import DataFrame
+
+
+class RenderApply:
+
+    params = [[12, 24, 36], [12, 120]]
+    param_names = ["cols", "rows"]
+
+    def setup(self, cols, rows):
+        self.df = DataFrame(
+            np.random.randn(rows, cols),
+            columns=[f"float_{i+1}" for i in range(cols)],
+            index=[f"row_{i+1}" for i in range(rows)],
+        )
+        self._style_apply()
+
+    def time_render(self, cols, rows):
+        self.st.render()
+
+    def peakmem_apply(self, cols, rows):
+        self._style_apply()
+
+    def peakmem_render(self, cols, rows):
+        self.st.render()
+
+    def _style_apply(self):
+        def _apply_func(s):
+            return [
+                "background-color: lightcyan" if s.name == "row_1" else "" for v in s
+            ]
+
+        self.st = self.df.style.apply(_apply_func, axis=1)
diff --git a/doc/source/whatsnew/v1.1.0.rst b/doc/source/whatsnew/v1.1.0.rst
index 14d1e1b49a726..8b8c17689e136 100644
--- a/doc/source/whatsnew/v1.1.0.rst
+++ b/doc/source/whatsnew/v1.1.0.rst
@@ -806,6 +806,7 @@ Performance improvements
 - Performance improvement in :class:`pandas.core.groupby.RollingGroupby` (:issue:`34052`)
 - Performance improvement in arithmetic operations (sub, add, mul, div) for MultiIndex (:issue:`34297`)
 - Performance improvement in `DataFrame[bool_indexer]` when `bool_indexer` is a list (:issue:`33924`)
+- Significant performance improvement of :meth:`io.formats.style.Styler.render` with styles added with various ways such as :meth:`io.formats.style.Styler.apply`, :meth:`io.formats.style.Styler.applymap` or :meth:`io.formats.style.Styler.bar` (:issue:`19917`)
 
 .. ---------------------------------------------------------------------------
 
diff --git a/pandas/io/formats/style.py b/pandas/io/formats/style.py
index f7ba4750bc2ad..1abe64735dc03 100644
--- a/pandas/io/formats/style.py
+++ b/pandas/io/formats/style.py
@@ -561,11 +561,15 @@ def _update_ctx(self, attrs: DataFrame) -> None:
             Whitespace shouldn't matter and the final trailing ';' shouldn't
             matter.
         """
-        for row_label, v in attrs.iterrows():
-            for col_label, col in v.items():
-                i = self.index.get_indexer([row_label])[0]
-                j = self.columns.get_indexer([col_label])[0]
-                for pair in col.rstrip(";").split(";"):
+        rows = [(row_label, v) for row_label, v in attrs.iterrows()]
+        row_idx = self.index.get_indexer([x[0] for x in rows])
+        for ii, row in enumerate(rows):
+            i = row_idx[ii]
+            cols = [(col_label, col) for col_label, col in row[1].items() if col]
+            col_idx = self.columns.get_indexer([x[0] for x in cols])
+            for jj, itm in enumerate(cols):
+                j = col_idx[jj]
+                for pair in itm[1].rstrip(";").split(";"):
                     self.ctx[(i, j)].append(pair)
 
     def _copy(self, deepcopy: bool = False) -> "Styler":
diff --git a/pandas/tests/io/formats/test_style.py b/pandas/tests/io/formats/test_style.py
index ec4614538004c..9c6910637fa7e 100644
--- a/pandas/tests/io/formats/test_style.py
+++ b/pandas/tests/io/formats/test_style.py
@@ -405,9 +405,10 @@ def f(x):
 
         result = self.df.style.where(f, style1)._compute().ctx
         expected = {
-            (r, c): [style1 if f(self.df.loc[row, col]) else ""]
+            (r, c): [style1]
             for r, row in enumerate(self.df.index)
             for c, col in enumerate(self.df.columns)
+            if f(self.df.loc[row, col])
         }
         assert result == expected
 
@@ -966,7 +967,6 @@ def test_bar_align_mid_nans(self):
                 "transparent 25.0%, #d65f5f 25.0%, "
                 "#d65f5f 50.0%, transparent 50.0%)",
             ],
-            (1, 0): [""],
             (0, 1): [
                 "width: 10em",
                 " height: 80%",
@@ -994,7 +994,6 @@ def test_bar_align_zero_nans(self):
                 "transparent 50.0%, #d65f5f 50.0%, "
                 "#d65f5f 75.0%, transparent 75.0%)",
             ],
-            (1, 0): [""],
             (0, 1): [
                 "width: 10em",
                 " height: 80%",
@@ -1091,7 +1090,7 @@ def test_format_with_bad_na_rep(self):
     def test_highlight_null(self, null_color="red"):
         df = pd.DataFrame({"A": [0, np.nan]})
         result = df.style.highlight_null()._compute().ctx
-        expected = {(0, 0): [""], (1, 0): ["background-color: red"]}
+        expected = {(1, 0): ["background-color: red"]}
         assert result == expected
 
     def test_highlight_null_subset(self):
@@ -1104,9 +1103,7 @@ def test_highlight_null_subset(self):
             .ctx
         )
         expected = {
-            (0, 0): [""],
             (1, 0): ["background-color: red"],
-            (0, 1): [""],
             (1, 1): ["background-color: green"],
         }
         assert result == expected
@@ -1219,8 +1216,6 @@ def test_highlight_max(self):
             expected = {
                 (1, 0): ["background-color: yellow"],
                 (1, 1): ["background-color: yellow"],
-                (0, 1): [""],
-                (0, 0): [""],
             }
             assert result == expected
 
@@ -1228,8 +1223,6 @@ def test_highlight_max(self):
             expected = {
                 (0, 1): ["background-color: yellow"],
                 (1, 1): ["background-color: yellow"],
-                (0, 0): [""],
-                (1, 0): [""],
             }
             assert result == expected
 


Code review comments:
diff_hunk: @@ -561,11 +561,15 @@ def _update_ctx(self, attrs: DataFrame) -> None:
             Whitespace shouldn't matter and the final trailing ';' shouldn't
             matter.
         """
-        for row_label, v in attrs.iterrows():
-            for col_label, col in v.items():
-                i = self.index.get_indexer([row_label])[0]
-                j = self.columns.get_indexer([col_label])[0]
-                for pair in col.rstrip(";").split(";"):
+        rows = [(row_label, v) for row_label, v in attrs.iterrows()]
path: pandas/io/formats/style.py
text: @user1:
you should use itertuples here (its actually much faster)

@author:
@user2 - that's a good thing to know and I tried it but could not figure out doing the same thing with itertuples.
To get the col_label within the inner loop, I need to use ._fields(), getattr(), list slicing, etc to separate index, ... basically many extra steps. I am not sure how much we can save here.

However, it seems that .get_indexer is the one that caused much delay. So real solution should be something that will eliminate get_indexer entirely or some acceleration effort done on get_indexer.

I can think of one way to avoid get_indexer -- simply taking index & columns as list and use it to get integer index # of given label. However, I was not sure if I could do that safely because I am not sure all the labels given in attrs always matches that of self.index and self.columns. probably not. 


@user1:
ahh i see, you are doing get indexer once for the rows, you can do the same once for the columns. you can throw this in a dict {label -> int}. This will vastly speed up things.

@author:
So you mean, each row will have same columns ..?
how about 4x4 table that has some attr assignments on column A, B on row 1, 2 but on column C, D only on row 3, 4 ..?
It would be great if the original author of this function could answer this -- or, are you may be?
I admit that I did the patch much relying on guesses based on common sense ( or my version of common sense :) )


@author:
If it is certain that we really do not have to use get_indexer method, probably something this should work, outside the loops:
rowmap = { label: i for i, label in enumerate(self.index) }
colmap = { label: i for i, label in enumerate(self.columns) }

@author:
This is the exact code that worked for my app:
```
    def _update_ctx(self, attrs: DataFrame) -> None:
        coli = {k: i for i, k in enumerate(self.columns)}
        rowi = {k: i for i, k in enumerate(self.index)}
        for jj in range(len(attrs.columns)):
            cn = attrs.columns[jj]
            j = coli[cn]
            for rn, c in attrs[[cn]].itertuples():
                if not c: continue
                c = c.rstrip(";")
                if not c: continue
                i = rowi[rn]
                for pair in c.split(";"):
                    self.ctx[(i, j)].append(pair)
```
However, it outperform the current patch only slightly with benchmark. 
for 1200 case, it took 3.02s where it took 3.13 with the current patch. 
Plus we're not sure if this is okay with all occasions.

@user1:
can you avoid the append? I think if this was a comprehension (or at least the last append) would be much better

@author:
I don't see how how it can be better.
Let's say we have a defaultdict out of comprehension with new pairs to be added to existing ctx. What should happen afterward is basically same things as this one, I would assume.

@user1:
ok i actually like your code above a little better, its very idiomatic and easy to understand. push it up and ping on green.

@author:
I really don't feel safe with this code. It might break someone's code.
What if the attrs's column is specified different way other than plain column label?