How best to display Pandas PivotTable in a Dash DataTable?

Dash Python
1

Hi

Is it possible to display a pandas pivot table in a Dash datatable?

The code for my table callback function (Output(‘table2’, ‘data’)) currently looks like this:

pt = df.pivot_table(index=['field1', 'field2', 'field3'], columns='col1')`
data = pt.to_dict()
return data

But I’m getting this error:

The callback for property data
of component table2 returned a value
which is not JSON serializable.

The same method worked for a standard data frame i.e. data = df.to_dict()

Thanks

Chris

2

Good question. You’ll have to somehow get the data formatted as a list of dicts like:

[
     {'column-1': 3, 'column-2': 5},
     {'column-1': 1, 'column-2': 3},
]

So, for pivot_table, try:

pt.reset_index().to_dict('rows')

Full example:

import pandas as pd
import numpy as np
>>> df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
...                          "bar", "bar", "bar", "bar"],
...                    "B": ["one", "one", "one", "two", "two",
...                          "one", "one", "two", "two"],
...                    "C": ["small", "large", "large", "small",
...                          "small", "large", "small", "small",
...                          "large"],
...                    "D": [1, 2, 2, 3, 3, 4, 5, 6, 7]})
>>> table = pd.pivot_table(df, values='D', index=['A', 'B'],
...                     columns=['C'], aggfunc=np.sum)
>>> table.reset_index().to_dict('rows')
[{'A': 'bar', 'B': 'one', 'large': 4.0, 'small': 5.0},
 {'A': 'bar', 'B': 'two', 'large': 7.0, 'small': 6.0},
 {'A': 'foo', 'B': 'one', 'large': 4.0, 'small': 1.0},
 {'A': 'foo', 'B': 'two', 'large': nan, 'small': 6.0}]

This is a good reference: http://www.jeannicholashould.com/tidy-data-in-python.html

1 Like
3

Amazing thank you - will give this a go :slight_smile:

4

Hello ,

thanks for sharing this information. I am having a pivot table that has multiple values , so the columns are then a combination of a value plus the column. The dictionary results in something like this using table.reset_index().to_dict(‘rows’):

[{('index, ‘’): ‘value’,
(‘column-1’, ‘column-a’): value,
(‘column-1’, ‘column-b’): value,
(‘column-1’, ‘column-c’): value}]

Since the keys are a tuple , i get the the callback for property data
of component table returned a value
which is not JSON serializable error.

Is there a way to solve this issue?

Best!

5

Did you resolve the issue? I am having the same problem

6

Hello,

Any solution to this problem i.e. displaying Pandas Pivot Table in a Dash DataTable wherein Column Headings is in the form of Col 1, Col 2, (Item 1, Item 2), (Item 3, Item 4).

Error shown is: “TypeError: keys must be str, int, float, bool or None, not tuple”

How best to resolve it?

7

There may be a better way, but I just converted the tuples to a string:

Cols = [str(c) for c in columns]

Then set merge_duplicate_headers=True on the dataTable.

8

Thanks.

However, based on

i used

df.rename(columns='_'.join, inplace=True)