Sticky header and index for large data frames in Jupyter

[ghost]

Problem description

When displaying a large data frame in Jupyter the number of columns will be limited by max_cols set in the default setting, and all the rows will be displayed.

I would like to add an option in the default settings so that large data frames will be displayed with a sticky header and index and then be able to scroll though the data frame.

Proof of concept solution

Following the solution for html tables found at Stackoverflow: Table with fixed header and fixed column on pure css with the solution shown in action here HTML and CSS Solution
I came up with the following solution (which follows the same way of <style scoped> as the _repr_html_ method):

import numpy as np
import pandas as pd
from IPython.display import HTML

# Dummy dataframe
columns = [chr(i) for i in range(ord('a'),ord('z')+1)]
data = np.random.rand(len(columns),len(columns))
df = pd.DataFrame(data, columns=columns)

# Solution
# Getting default html as string
df_html = df.to_html() 
# CSS styling 
style = """
<style scoped>
    .dataframe-div {
      max-height: 300px;
      overflow: auto;
      position: relative;
    }

    .dataframe thead th {
      position: -webkit-sticky; /* for Safari */
      position: sticky;
      top: 0;
      background: black;
      color: white;
    }

    .dataframe thead th:first-child {
      left: 0;
      z-index: 1;
    }

    .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

    .dataframe tbody tr th {
      position: -webkit-sticky; /* for Safari */
      position: sticky;
      left: 0;
      background: black;
      color: white;
      vertical-align: top;
    }
</style>
"""
# Concatenating to single string
df_html = style+'<div class="dataframe-div">'+df_html+"\n</div>"

# Displaying df with sticky header and index
HTML(df_html)

I would therefore like to know if others also would like to have this feature in pandas?
Otherwise I guess I would make it to an independent module that wraps the _repr_html_ method.
I know that it is not just a matter of adding the new styling above for the general case, but the above solution is a minimal working solution.
A related issues is #28091

[alimcmaster1]

Have you tried using qgrid or beakerx?

https://github.com/quantopian/qgrid
https://github.com/twosigma/beakerx

[ghost]

I tried qgrid but I haven't tried beakerx.
They both look great for displaying the dataframe, when working in the notebook. But as fare as I can see they are both based on ipywidget which can't be converted into a static HTML file. I did not mention that in my original post but exporting to HTML was one of my use cases.

I would also prefer the sticky header and index as the default for displaying the dataframe since I often want to inspect the data, fx. after loading a CSV file. Using Jupyter Lab and a dataframe with many rows would result in a very long notebook. In the classical notebook the output can be made into horizontal scroll, but without have a sticky header and index.

[attack68]

This is already possible if you know the CSS solution:

pd.DataFrame(np.random.randn(200, 50)).style.set_table_styles([
    {'selector': 'thead th', 'props': 'position: sticky; top:0; background-color:red;'},
    {'selector': 'tbody th', 'props': 'position: sticky; left:0; background-color:green;'}  
])

I dont think this is worth API but a mention in docs. Ill leave open until then

[dsjstc]

[updated with solution]
@attack68 , your code snippet gives me a ValueError: not enough values to unpack (expected 2, got 1) at render time, with pandas 1.2.2.

Looks like set_table_styles doesn't accept that kind of string anymore. It worked for me with ...

.set_table_styles(
    [{'selector': 'thead th','props': [
          ('position', 'sticky'),('top', '0'),('background-color', 'red'),
               ]}]
)

[attack68]

@dsjstc sorry I gave you the new 1.3.0 input format for release June 2021, which is more CSS friendly.
Yes you need what you did for 1.2.2.

[xielmonx]

I tried qgrid but I haven't tried beakerx.
They both look great for displaying the dataframe, when working in the notebook. But as fare as I can see they are both based on ipywidget which can't be converted into a static HTML file. I did not mention that in my original post but exporting to HTML was one of my use cases.

I would also prefer the sticky header and index as the default for displaying the dataframe since I often want to inspect the data, fx. after loading a CSV file. Using Jupyter Lab and a dataframe with many rows would result in a very long notebook. In the classical notebook the output can be made into horizontal scroll, but without have a sticky header and index.

how about if I have multi-index?

[attack68]

then you need to define the sticky positions for each index level so they dont overlap e.g:

 for i, level in enumerate(sorted(index)):
                self.set_table_styles(
                    [
                        {
                            "selector": f"tbody th.level{level}",
                            "props": f"position: sticky; "
                            f"left: {i * index_width}px; "
                            f"min-width: {index_width}px; "
                            f"max-width: {index_width}px; "
                            f"background-color: white;",
                        }
                    ],
                    overwrite=False,
                )

[alxfed]

@attack68 , I still can not understand how to set the size of a frame that will have scrolls... Can it be done in 1.3.2 that I'm using?

[attack68]

set sticky uses css that works for the table within the frame or container you place it in. The css is quite simple, build a simple dataframe, set it sticky and observe the rules. you cannot control the size of the frame/container from pandas

[TSSFL]

How do I set a custom Pandas dataframe width for a specific column, say the column named Description within the sticky header template or using CSS in general?