Black Lives Matter. Please consider donating to Black Girls Code today.

Show and Tell - Dash Pivottable

Update: Dash Pivottable is now an official library, published on PyPi and contributed to CRAN! You can read the announcement for Python v0.0.1 here, and the release post for R v0.0.2 here.

I found out about pivottable.js last summer, and I really liked the idea of a library for interactive data exploration in JS, along with built-in aggregations (sums, average, count) and displays (table, scatter plots, bar charts), all done through an UI component.

I haven’t had the chance to try it out, so today I decided to get started by making it into a Dash component. Thus, I made this dash wrapping of the react-pivottable library (maintained by Plotly, and created by Nicholas who also made pivottable.js; awesome work!).

The github link: https://github.com/xhlulu/dash_pivottable

Here’s what it looks like when you run the usage.py file in the project:

Honestly all the heavy-lifting was already done in the react component, and the library is really easy to learn. Go check it out: https://github.com/plotly/react-pivottable/

Shoutout to Nicolas for making this awesome library (2 years late haha)!

15 Likes

:heart_eyes::heart_eyes::heart_eyes:

I really like this! I tried it out and it works great for data under 100,000 rows; any suggestions for working with much larger data?

1 Like

Thank you! This is based on react-pivottable, so I would recommend taking a look at how it optimizing rendering for very large datasets. Otherwise, using Pandas and Numpy to preprocess your dataset so that the table transmitted at the end is smaller could be helpful too!

Took a look - turns out that all the pivoting happens client-side, and server-side integration is not great; this makes sense from a React-ive point of view but definitely not from a large-data-workability viewpoint. Sadly the design just doesn’t work for me.

My data is between 15M and 125M records, held in a pretty powerful server, accessed through a Dash app for business reporting. Right now it’s possible to interactively pivot server-side by generating a pivot table and rendering it with table.to_html() and making pivot charts with matplotlib before rendering them as images in the Dash. It’s not optimal though.

It could be interesting to hack out a component that mimics react-pivottable's pivoting and visualization logic on the server-side in order to make the larger data realistic.

1 Like

Is there a way to refresh the data of the pivot table?

i love this looks really useful guys

1 Like

Very cool, thanks a lot. I am attempting to alter styling by using an external stylesheet (Dash(…, external_stylesheets=‘my.css’)) but it seems as though many css classes are defined downstream, and I cannot override them. I have had limited success altering fontsizes and padding of various elements.

Any tips? And thanks again!

EDIT: I was able to get this working using the css classes outlined in https://github.com/plotly/react-pivottable/blob/master/src/pivottable.css edited to my liking and added to the layout by html.Link like here: Serve_locally option with additional scripts and style sheets

This is fantastic, thanks very much for developing. I know this isn’t an official dash add-on yet - are there any plans for it to get the full dash treatment?

In the meantime (and this isn’t a begging request for someone to completely overhaul the code…more a question of the underlying pivottable.js functionality), is it possible to generate sub-totals per row, e.g. turn the following:

…into something like a ‘normal’ excel pivot table, with sub-totals displayed/expandable:

image

Thanks in advance for any pointers.

This is great thank you!

What if I want to sum/count/Average not just 1 column, but two or more? For example, for each gender I want to see sum/count/average of Total Bills and also of Tips.

Is that possible? If not - any suggestions how I could add myself?

Regards

Hi Bugatank,

Any lucky to solve this? I also met the same issue. I want to sum/count for aggfuc, not sure how to fix it. If you know how to fix it, could you share it with me? Thanks a lot.

Hello

I am a newbie to Dash and one of my primary requirement is to incorporate a pivot table. I refereed to the github link, but could not figure out how to include it in my project so I would use it in my dash application. Any guidance is appreciated.

I was in the same situation not so long ago, and I manage to integrate it in my project by downloading the dash_pivottable folder and copying it in my project folder. After you just have to import it at the beginning of your python file. You should have a look at the usage.py in the GitHub to understand how does it work.

Hope this helps.

HI, i used this library but i notice that the data callback is not updating the data in the dash-pivottable. Can someone help me with this?

Maybe provide a example on how this can happen.

Thank You

Hi, like kirk77, I am struggling to understand how to install and use the package. I tried to follow easy_panda’s advice, but still have not succeeded. is anyone able to point me in the right direction for how to ‘Make sure to clone this project, create a venv and install requirements:’?

I am using Windows 7,

Thanks

Hi. Have you tried the instructions in Getting Started section of the readme? The only line you would change is how you activate the venv. instead of:

$ source venv/bin/activate

You would write

>>> venv\Scripts\activate
1 Like

Hi
@xhlu

I’m having difficulty to be able to pull the “rows” values from my pivot table (yellow).

As seen from the picture, I’m making pivot_id(red) change every time when a new filtered dataframe is passed in as an input because the pivot-table will not update automatically with a constant id value.

Since I now can’t pass in the pivot-table’s id directly into my RSV_Data Table callback , I’m storing my “testa” (blue) in a hidden html.Div separately then try to use it instead in the Input’s id; and this is not working.

Do you have a solution for this?

Thanks for the help!

I don’t see the full code, but I’m assuming here the component “testa” is a html.Div. In this case it’s not possible to update its rows property since it does not exist. You will need to update the pivot_id directly.

Ofc, this will throw an error if it happens. To bypass it, you can use pattern matching to find all instances of a class of components. For example, you can assign your table id={'type': 'pivottable', 'index': 0}, and later in a callback, you can pattern match

@app.callback(
  Output(...),
  [Input({'type': 'pivottable', 'index': ALL}, 'rows')]
)
def update_rows(tables):
  if len(tables) == 0:
    # No table exists yet
    return ...
  else:
    # There's at least one pivottable now
   ...

Thank you, i’ve finally got it installed - i also didn’t have git in my PATH, but have fixed that

1 Like

This is incredibly powerful and useful, thank you.

I’m having trouble formatting my data to be read into the template script.

I see that the data file tips.Rdata is a list in R - I assume that i turn my table into a dict for this to work, but pandas.DataFrame.to_dict is throwing up a memory error for me.

can i just turn my data into a list in r and save down like the tips.Rdata file?

any advice on this would be greatly appreciated. (i’m much more comfortable in R than Python)

thanks