Incubator-superset: Pivot table not working - unhashable type: 'dict'

[chgangaraju]

Superset version

Superset 0.23.0dev

Expected results

Pivot table should display the table with results.

Actual results

unhashable type: 'dict'

Steps to reproduce

• Install superset, load sample data as given in the installation guide
• Create pivot table with any table and choose any metric except Count(*) . Because its working only for Count(*) metric.
• Run Query

PS: The same query working for other chart types with same parameters.

Stacktrace:

2018-05-11 17:17:36,253:DEBUG:root:[stats_logger] (incr) explore_json
2018-05-11 17:17:36,375:DEBUG:parsedatetime:eval now with context - False, False
2018-05-11 17:17:36,376:INFO:root:Cache key: 0ee55f4b95926a7373cfbf36300e1d71
2018-05-11 17:17:36,380:INFO:root:Database.get_sqla_engine(). Masked URL: sqlite:////home/gancha/.superset/superset.db
2018-05-11 17:17:36,388:INFO:root:SELECT gender AS gender, SUM(num) AS "SUM(num)" 
FROM birth_names 
WHERE ds >= '1918-05-11 00:00:00.000000' AND ds <= '2018-05-11 17:17:36.000000' GROUP BY gender ORDER BY "SUM(num)" DESC
 LIMIT 10000 OFFSET 0
2018-05-11 17:17:36,405:INFO:root:Database.get_sqla_engine(). Masked URL: sqlite:////home/gancha/.superset/superset.db
2018-05-11 17:17:36,464:DEBUG:root:[stats_logger] (incr) loaded_from_source
2018-05-11 17:17:40,465:ERROR:root:unhashable type: 'dict'
Traceback (most recent call last):
  File "/home/gancha/superset/superset/views/core.py", line 1107, in generate_json
    payload = viz_obj.get_payload()
  File "/home/gancha/superset/superset/viz.py", line 371, in get_payload
    payload['data'] = self.get_data(df)
  File "/home/gancha/superset/superset/viz.py", line 670, in get_data
    margins=self.form_data.get('pivot_margins'),
  File "/home/gancha/superset/venv/lib/python2.7/site-packages/pandas/core/frame.py", line 4468, in pivot_table
    margins_name=margins_name)
  File "/home/gancha/superset/venv/lib/python2.7/site-packages/pandas/core/reshape/pivot.py", line 57, in pivot_table
    if i not in data:
  File "/home/gancha/superset/venv/lib/python2.7/site-packages/pandas/core/generic.py", line 1075, in __contains__
    return key in self._info_axis
  File "/home/gancha/superset/venv/lib/python2.7/site-packages/pandas/core/indexes/base.py", line 1694, in __contains__
    hash(key)
TypeError: unhashable type: 'dict'

[ToonoW]

I have the same problem.

[ToonoW]

I read souce code, find the reason.

The reason is superset change the data struct of metric. So in some situation it is work, but some not.

Before change, the type of metric is str, but after change the type of metric is dict.

For example.
Before

'avg__2004'

After

{'expressionType': 'SIMPLE', 'column': {'column_name': '2004', 'verbose_name': None, 'description': None, 'expression': '', 'filterable': False, 'groupby': False, 'is_dttm': False, 'type': 'BIGINT', 'optionName': '_col_2004'}, 'aggregate': 'AVG', 'sqlExpression': None, 'hasCustomLabel': False, 'fromFormData': True, 'label': 'AVG(2004)', 'optionName': 'metric_681b3cp6kvh_sa14ez05lx'}

Solution

Change the file viz.py get metric code of per get_data function. We should make metric is a str.

Like this.

class CountryMapViz(BaseViz):

    """A country centric"""

    viz_type = 'country_map'
    verbose_name = _('Country Map')
    is_timeseries = False
    credits = 'From bl.ocks.org By john-guerra'

    def query_obj(self):
        qry = super(CountryMapViz, self).query_obj()
        qry['metrics'] = [
            self.form_data['metric']]
        qry['groupby'] = [self.form_data['entity']]
        return qry

    def get_data(self, df):
        fd = self.form_data
        cols = [fd.get('entity')]
        metric = fd.get('metric')
        if isinstance(metric, dict):
            metric = metric['label']
        cols += [metric]
        ndf = df[cols]
        df = ndf
        df.columns = ['country_id', 'metric']
        d = df.to_dict(orient='records')
        return d

Then CountryMapViz is OK.

[chgangaraju]

Thanks @ToonoW , Its working for me after making the following changes for get_data method in PivotTableViz

def get_data(self, df):
    if (self.form_data.get('granularity') == 'all' and DTTM_ALIAS in df):
        del df[DTTM_ALIAS]
    metrics = self.form_data.get('metrics')
    metric_str = []
    if isinstance(metrics, list):
        for metric in metrics:
            if isinstance(metric, dict):
                metric_str.append(metric['label'])
            else:
                metric_str.append(metric)

    df = df.pivot_table(
        index=self.form_data.get('groupby'),
        columns=self.form_data.get('columns'),
        values=metric_str,
        aggfunc=self.form_data.get('pandas_aggfunc'),
        margins=self.form_data.get('pivot_margins'),
    )
    # Display metrics side by side with each column
    if self.form_data.get('combine_metric'):
        df = df.stack(0).unstack()
    return dict(
        columns=list(df.columns),
        html=df.to_html(
            na_rep='',
            classes=(
                'dataframe table table-striped table-bordered '
                'table-condensed table-hover').split(' ')),
  )

[xrmx]

If you have a fix available please open a PR!

[mistercrunch]

I think this fixes it https://github.com/apache/incubator-superset/pull/4914

[gangh]

I think this fixes it #4914

the bug in pivot table have not been fixed in #4914
the viz still not work, so I put a new PR https://github.com/apache/incubator-superset/pull/5025 ,thx @mistercrunch @xrmx

[bgriffen]

Did your original query work when doing heatmaps? I have a table which has row and column and a value at that row and column (all ints) and when I do X=row, Y=col, metric=value, I get unhashable dict error just as above. If this is unrelated, I can open another issue but it is basically the exact same but for histogram charts.

[gangh]

Did your original query work when doing heatmaps? I have a table which has row and column and a value at that row and column (all ints) and when I do X=row, Y=col, metric=value, I get unhashable dict error just as above. If this is unrelated, I can open another issue but it is basically the exact same but for histogram charts.

which version?
Can you paste a screen shot of this chart

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.