Python pandas.pivot_table() Examples

def test_pivot_table(self):
        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='C')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='C')
        tm.assert_frame_equal(res_sparse, res_dense)

        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='E')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='E')
        tm.assert_frame_equal(res_sparse, res_dense)

        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='E', aggfunc='mean')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='E', aggfunc='mean')
        tm.assert_frame_equal(res_sparse, res_dense)

        # ToDo: sum doesn't handle nan properly
        # res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
        #                             values='E', aggfunc='sum')
        # res_dense = pd.pivot_table(self.dense, index='A', columns='B',
        #                            values='E', aggfunc='sum')
        # tm.assert_frame_equal(res_sparse, res_dense) 

def test_pivot_table_with_iterator_values(self):
        # GH 12017
        aggs = {'D': 'sum', 'E': 'mean'}

        pivot_values_list = pd.pivot_table(
            self.data, index=['A'], values=list(aggs.keys()), aggfunc=aggs,
        )

        pivot_values_keys = pd.pivot_table(
            self.data, index=['A'], values=aggs.keys(), aggfunc=aggs,
        )
        tm.assert_frame_equal(pivot_values_keys, pivot_values_list)

        agg_values_gen = (value for value in aggs.keys())
        pivot_values_gen = pd.pivot_table(
            self.data, index=['A'], values=agg_values_gen, aggfunc=aggs,
        )
        tm.assert_frame_equal(pivot_values_gen, pivot_values_list) 

def test_margins_dtype(self):
        # GH 17013

        df = self.data.copy()
        df[['D', 'E', 'F']] = np.arange(len(df) * 3).reshape(len(df), 3)

        mi_val = list(product(['bar', 'foo'], ['one', 'two'])) + [('All', '')]
        mi = MultiIndex.from_tuples(mi_val, names=('A', 'B'))
        expected = DataFrame({'dull': [12, 21, 3, 9, 45],
                              'shiny': [33, 0, 36, 51, 120]},
                             index=mi).rename_axis('C', axis=1)
        expected['All'] = expected['dull'] + expected['shiny']

        result = df.pivot_table(values='D', index=['A', 'B'],
                                       columns='C', margins=True,
                                       aggfunc=np.sum, fill_value=0)

        tm.assert_frame_equal(expected, result) 

def test_daily(self):
        rng = date_range('1/1/2000', '12/31/2004', freq='D')
        ts = Series(np.random.randn(len(rng)), index=rng)

        annual = pivot_table(DataFrame(ts), index=ts.index.year,
                             columns=ts.index.dayofyear)
        annual.columns = annual.columns.droplevel(0)

        doy = np.asarray(ts.index.dayofyear)

        for i in range(1, 367):
            subset = ts[doy == i]
            subset.index = subset.index.year

            result = annual[i].dropna()
            tm.assert_series_equal(result, subset, check_names=False)
            assert result.name == i 

def test_pivot_table_margins_name_with_aggfunc_list(self):
        # GH 13354
        margins_name = 'Weekly'
        costs = pd.DataFrame(
            {'item': ['bacon', 'cheese', 'bacon', 'cheese'],
             'cost': [2.5, 4.5, 3.2, 3.3],
             'day': ['M', 'M', 'T', 'T']}
        )
        table = costs.pivot_table(
            index="item", columns="day", margins=True,
            margins_name=margins_name, aggfunc=[np.mean, max]
        )
        ix = pd.Index(
            ['bacon', 'cheese', margins_name], dtype='object', name='item'
        )
        tups = [('mean', 'cost', 'M'), ('mean', 'cost', 'T'),
                ('mean', 'cost', margins_name), ('max', 'cost', 'M'),
                ('max', 'cost', 'T'), ('max', 'cost', margins_name)]
        cols = pd.MultiIndex.from_tuples(tups, names=[None, None, 'day'])
        expected = pd.DataFrame(table.values, index=ix, columns=cols)
        tm.assert_frame_equal(table, expected) 

def test_pivot_table_with_margins_set_margin_name(self, margin_name):
        # see gh-3335
        msg = (r'Conflicting name "{}" in margins|'
               "margins_name argument must be a string").format(margin_name)
        with pytest.raises(ValueError, match=msg):
            # multi-index index
            pivot_table(self.data, values='D', index=['A', 'B'],
                        columns=['C'], margins=True,
                        margins_name=margin_name)
        with pytest.raises(ValueError, match=msg):
            # multi-index column
            pivot_table(self.data, values='D', index=['C'],
                        columns=['A', 'B'], margins=True,
                        margins_name=margin_name)
        with pytest.raises(ValueError, match=msg):
            # non-multi-index index/column
            pivot_table(self.data, values='D', index=['A'],
                        columns=['B'], margins=True,
                        margins_name=margin_name) 

def test_pivot_multi_functions(self):
        f = lambda func: pivot_table(self.data, values=['D', 'E'],
                                     index=['A', 'B'], columns='C',
                                     aggfunc=func)
        result = f([np.mean, np.std])
        means = f(np.mean)
        stds = f(np.std)
        expected = concat([means, stds], keys=['mean', 'std'], axis=1)
        tm.assert_frame_equal(result, expected)

        # margins not supported??
        f = lambda func: pivot_table(self.data, values=['D', 'E'],
                                     index=['A', 'B'], columns='C',
                                     aggfunc=func, margins=True)
        result = f([np.mean, np.std])
        means = f(np.mean)
        stds = f(np.std)
        expected = concat([means, stds], keys=['mean', 'std'], axis=1)
        tm.assert_frame_equal(result, expected) 

def test_categorical_aggfunc(self, observed):
        # GH 9534
        df = pd.DataFrame({"C1": ["A", "B", "C", "C"],
                           "C2": ["a", "a", "b", "b"],
                           "V": [1, 2, 3, 4]})
        df["C1"] = df["C1"].astype("category")
        result = df.pivot_table("V", index="C1", columns="C2",
                                dropna=observed, aggfunc="count")

        expected_index = pd.CategoricalIndex(['A', 'B', 'C'],
                                             categories=['A', 'B', 'C'],
                                             ordered=False,
                                             name='C1')
        expected_columns = pd.Index(['a', 'b'], name='C2')
        expected_data = np.array([[1., np.nan],
                                  [1., np.nan],
                                  [np.nan, 2.]])
        expected = pd.DataFrame(expected_data,
                                index=expected_index,
                                columns=expected_columns)
        tm.assert_frame_equal(result, expected) 

def test_pivot_table_with_margins_set_margin_name(self):
        # see gh-3335
        for margin_name in ['foo', 'one', 666, None, ['a', 'b']]:
            with pytest.raises(ValueError):
                # multi-index index
                pivot_table(self.data, values='D', index=['A', 'B'],
                            columns=['C'], margins=True,
                            margins_name=margin_name)
            with pytest.raises(ValueError):
                # multi-index column
                pivot_table(self.data, values='D', index=['C'],
                            columns=['A', 'B'], margins=True,
                            margins_name=margin_name)
            with pytest.raises(ValueError):
                # non-multi-index index/column
                pivot_table(self.data, values='D', index=['A'],
                            columns=['B'], margins=True,
                            margins_name=margin_name) 

def test_pivot_table_with_iterator_values(self):
        # GH 12017
        aggs = {'D': 'sum', 'E': 'mean'}

        pivot_values_list = pd.pivot_table(
            self.data, index=['A'], values=list(aggs.keys()), aggfunc=aggs,
        )

        pivot_values_keys = pd.pivot_table(
            self.data, index=['A'], values=aggs.keys(), aggfunc=aggs,
        )
        tm.assert_frame_equal(pivot_values_keys, pivot_values_list)

        agg_values_gen = (value for value in aggs.keys())
        pivot_values_gen = pd.pivot_table(
            self.data, index=['A'], values=agg_values_gen, aggfunc=aggs,
        )
        tm.assert_frame_equal(pivot_values_gen, pivot_values_list) 

def test_pivot_table_margins_name_with_aggfunc_list(self):
        # GH 13354
        margins_name = 'Weekly'
        costs = pd.DataFrame(
            {'item': ['bacon', 'cheese', 'bacon', 'cheese'],
             'cost': [2.5, 4.5, 3.2, 3.3],
             'day': ['M', 'M', 'T', 'T']}
        )
        table = costs.pivot_table(
            index="item", columns="day", margins=True,
            margins_name=margins_name, aggfunc=[np.mean, max]
        )
        ix = pd.Index(
            ['bacon', 'cheese', margins_name], dtype='object', name='item'
        )
        tups = [('mean', 'cost', 'M'), ('mean', 'cost', 'T'),
                ('mean', 'cost', margins_name), ('max', 'cost', 'M'),
                ('max', 'cost', 'T'), ('max', 'cost', margins_name)]
        cols = pd.MultiIndex.from_tuples(tups, names=[None, None, 'day'])
        expected = pd.DataFrame(table.values, index=ix, columns=cols)
        tm.assert_frame_equal(table, expected) 

def test_pivot_multi_functions(self):
        f = lambda func: pivot_table(self.data, values=['D', 'E'],
                                     index=['A', 'B'], columns='C',
                                     aggfunc=func)
        result = f([np.mean, np.std])
        means = f(np.mean)
        stds = f(np.std)
        expected = concat([means, stds], keys=['mean', 'std'], axis=1)
        tm.assert_frame_equal(result, expected)

        # margins not supported??
        f = lambda func: pivot_table(self.data, values=['D', 'E'],
                                     index=['A', 'B'], columns='C',
                                     aggfunc=func, margins=True)
        result = f([np.mean, np.std])
        means = f(np.mean)
        stds = f(np.std)
        expected = concat([means, stds], keys=['mean', 'std'], axis=1)
        tm.assert_frame_equal(result, expected) 

def spread(verb):
    key = verb.key
    value = verb.value

    if isinstance(key, str) or not np.iterable(key):
        key = [key]

    if isinstance(value, str) or not np.iterable(key):
        value = [value]

    key_value = pd.Index(list(chain(key, value))).drop_duplicates()
    index = verb.data.columns.difference(key_value).tolist()
    data = pd.pivot_table(
        verb.data,
        values=value,
        index=index,
        columns=key,
        aggfunc=identity,
    )

    clean_indices(data, verb.sep, inplace=True)
    data = data.infer_objects()
    return data 

def test_pivot_with_non_observable_dropna(self, dropna):
        # gh-21133
        df = pd.DataFrame(
            {'A': pd.Categorical([np.nan, 'low', 'high', 'low', 'high'],
                                 categories=['low', 'high'],
                                 ordered=True),
             'B': range(5)})

        result = df.pivot_table(index='A', values='B', dropna=dropna)
        expected = pd.DataFrame(
            {'B': [2, 3]},
            index=pd.Index(
                pd.Categorical.from_codes([0, 1],
                                          categories=['low', 'high'],
                                          ordered=True),
                name='A'))

        tm.assert_frame_equal(result, expected) 

def test_pivot_table(self):
        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='C')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='C')
        tm.assert_frame_equal(res_sparse, res_dense)

        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='E')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='E')
        tm.assert_frame_equal(res_sparse, res_dense)

        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='E', aggfunc='mean')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='E', aggfunc='mean')
        tm.assert_frame_equal(res_sparse, res_dense)

        # ToDo: sum doesn't handle nan properly
        # res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
        #                             values='E', aggfunc='sum')
        # res_dense = pd.pivot_table(self.dense, index='A', columns='B',
        #                            values='E', aggfunc='sum')
        # tm.assert_frame_equal(res_sparse, res_dense) 

def test_pivot_table_dropna(self):
        df = DataFrame({'amount': {0: 60000, 1: 100000, 2: 50000, 3: 30000},
                        'customer': {0: 'A', 1: 'A', 2: 'B', 3: 'C'},
                        'month': {0: 201307, 1: 201309, 2: 201308, 3: 201310},
                        'product': {0: 'a', 1: 'b', 2: 'c', 3: 'd'},
                        'quantity': {0: 2000000, 1: 500000,
                                     2: 1000000, 3: 1000000}})
        pv_col = df.pivot_table('quantity', 'month', [
                                'customer', 'product'], dropna=False)
        pv_ind = df.pivot_table(
            'quantity', ['customer', 'product'], 'month', dropna=False)

        m = MultiIndex.from_tuples([('A', 'a'), ('A', 'b'), ('A', 'c'),
                                    ('A', 'd'), ('B', 'a'), ('B', 'b'),
                                    ('B', 'c'), ('B', 'd'), ('C', 'a'),
                                    ('C', 'b'), ('C', 'c'), ('C', 'd')],
                                   names=['customer', 'product'])
        tm.assert_index_equal(pv_col.columns, m)
        tm.assert_index_equal(pv_ind.index, m) 

def test_pivot_table(self):
        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='C')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='C')
        tm.assert_frame_equal(res_sparse, res_dense)

        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='E')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='E')
        tm.assert_frame_equal(res_sparse, res_dense)

        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values='E', aggfunc='mean')
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values='E', aggfunc='mean')
        tm.assert_frame_equal(res_sparse, res_dense)

        # ToDo: sum doesn't handle nan properly
        # res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
        #                             values='E', aggfunc='sum')
        # res_dense = pd.pivot_table(self.dense, index='A', columns='B',
        #                            values='E', aggfunc='sum')
        # tm.assert_frame_equal(res_sparse, res_dense) 

def test_pivot_table_dropna(self):
        df = DataFrame({'amount': {0: 60000, 1: 100000, 2: 50000, 3: 30000},
                        'customer': {0: 'A', 1: 'A', 2: 'B', 3: 'C'},
                        'month': {0: 201307, 1: 201309, 2: 201308, 3: 201310},
                        'product': {0: 'a', 1: 'b', 2: 'c', 3: 'd'},
                        'quantity': {0: 2000000, 1: 500000,
                                     2: 1000000, 3: 1000000}})
        pv_col = df.pivot_table('quantity', 'month', [
                                'customer', 'product'], dropna=False)
        pv_ind = df.pivot_table(
            'quantity', ['customer', 'product'], 'month', dropna=False)

        m = MultiIndex.from_tuples([('A', 'a'), ('A', 'b'), ('A', 'c'),
                                    ('A', 'd'), ('B', 'a'), ('B', 'b'),
                                    ('B', 'c'), ('B', 'd'), ('C', 'a'),
                                    ('C', 'b'), ('C', 'c'), ('C', 'd')],
                                   names=['customer', 'product'])
        tm.assert_index_equal(pv_col.columns, m)
        tm.assert_index_equal(pv_ind.index, m) 

def _long_to_wide_rm(data, dv=None, within=None, subject=None):
    """Convert long-format dataframe to wide-format.
    This internal function is used in pingouin.epsilon and pingouin.sphericity.
    """
    # Check arguments
    assert isinstance(dv, str), 'dv must be a string.'
    assert isinstance(subject, str), 'subject must be a string.'
    assert isinstance(within, (str, list)), 'within must be a string or list.'
    # Check that all columns are present
    assert dv in data.columns, '%s not in data' % dv
    assert data[dv].dtype.kind in 'bfiu', '%s must be numeric' % dv
    assert subject in data.columns, '%s not in data' % subject
    assert not data[subject].isnull().any(), 'Cannot have NaN in %s' % subject
    if isinstance(within, str):
        within = [within]  # within = ['fac1'] or ['fac1', 'fac2']
    for w in within:
        assert w in data.columns, '%s not in data' % w
    # Keep all relevant columns and reset index
    data = data[_fl([subject, within, dv])]
    # Convert to wide-format + collapse to the mean
    data = pd.pivot_table(data, index=subject, values=dv, columns=within,
                          aggfunc='mean', dropna=True)
    return data 

def test_pivot_table_not_series(self):
        # GH 4386
        # pivot_table always returns a DataFrame
        # when values is not list like and columns is None
        # and aggfunc is not instance of list
        df = DataFrame({'col1': [3, 4, 5],
                        'col2': ['C', 'D', 'E'],
                        'col3': [1, 3, 9]})

        result = df.pivot_table('col1', index=['col3', 'col2'], aggfunc=np.sum)
        m = MultiIndex.from_arrays([[1, 3, 9],
                                    ['C', 'D', 'E']],
                                   names=['col3', 'col2'])
        expected = DataFrame([3, 4, 5],
                             index=m, columns=['col1'])

        tm.assert_frame_equal(result, expected)

        result = df.pivot_table(
            'col1', index='col3', columns='col2', aggfunc=np.sum
        )
        expected = DataFrame([[3, np.NaN, np.NaN],
                              [np.NaN, 4, np.NaN],
                              [np.NaN, np.NaN, 5]],
                             index=Index([1, 3, 9], name='col3'),
                             columns=Index(['C', 'D', 'E'], name='col2'))

        tm.assert_frame_equal(result, expected)

        result = df.pivot_table('col1', index='col3', aggfunc=[np.sum])
        m = MultiIndex.from_arrays([['sum'],
                                    ['col1']])
        expected = DataFrame([3, 4, 5],
                             index=Index([1, 3, 9], name='col3'),
                             columns=m)

        tm.assert_frame_equal(result, expected) 

def test_pass_function(self):
        result = self.data.pivot_table('D', index=lambda x: x // 5,
                                       columns=self.data.C)
        expected = self.data.pivot_table('D', index=self.data.index // 5,
                                         columns='C')
        tm.assert_frame_equal(result, expected) 

def test_categorical_pivot_index_ordering(self, observed):
        # GH 8731
        df = pd.DataFrame({'Sales': [100, 120, 220],
                           'Month': ['January', 'January', 'January'],
                           'Year': [2013, 2014, 2013]})
        months = ['January', 'February', 'March', 'April', 'May', 'June',
                  'July', 'August', 'September', 'October', 'November',
                  'December']
        df['Month'] = df['Month'].astype('category').cat.set_categories(months)
        result = df.pivot_table(values='Sales',
                                index='Month',
                                columns='Year',
                                dropna=observed,
                                aggfunc='sum')
        expected_columns = pd.Int64Index([2013, 2014], name='Year')
        expected_index = pd.CategoricalIndex(['January'],
                                             categories=months,
                                             ordered=False,
                                             name='Month')
        expected = pd.DataFrame([[320, 120]],
                                index=expected_index,
                                columns=expected_columns)
        if not observed:
            result = result.dropna().astype(np.int64)

        tm.assert_frame_equal(result, expected) 

def test_pivot_table_nocols(self):
        df = DataFrame({'rows': ['a', 'b', 'c'],
                        'cols': ['x', 'y', 'z'],
                        'values': [1, 2, 3]})
        rs = df.pivot_table(columns='cols', aggfunc=np.sum)
        xp = df.pivot_table(index='cols', aggfunc=np.sum).T
        tm.assert_frame_equal(rs, xp)

        rs = df.pivot_table(columns='cols', aggfunc={'values': 'mean'})
        xp = df.pivot_table(index='cols', aggfunc={'values': 'mean'}).T
        tm.assert_frame_equal(rs, xp) 

def test_pivot_table_multi(self):
        res_sparse = pd.pivot_table(self.sparse, index='A', columns='B',
                                    values=['D', 'E'])
        res_dense = pd.pivot_table(self.dense, index='A', columns='B',
                                   values=['D', 'E'])
        res_dense = res_dense.apply(lambda x: x.astype("Sparse[float64]"))
        tm.assert_frame_equal(res_sparse, res_dense) 

def test_pivot_multi_values(self):
        result = pivot_table(self.data, values=['D', 'E'],
                             index='A', columns=['B', 'C'], fill_value=0)
        expected = pivot_table(self.data.drop(['F'], axis=1),
                               index='A', columns=['B', 'C'], fill_value=0)
        tm.assert_frame_equal(result, expected) 

def test_pivot_no_values(self):
        # GH 14380
        idx = pd.DatetimeIndex(['2011-01-01', '2011-02-01', '2011-01-02',
                                '2011-01-01', '2011-01-02'])
        df = pd.DataFrame({'A': [1, 2, 3, 4, 5]},
                          index=idx)
        res = df.pivot_table(index=df.index.month, columns=df.index.day)

        exp_columns = pd.MultiIndex.from_tuples([('A', 1), ('A', 2)])
        exp = pd.DataFrame([[2.5, 4.0], [2.0, np.nan]],
                           index=[1, 2], columns=exp_columns)
        tm.assert_frame_equal(res, exp)

        df = pd.DataFrame({'A': [1, 2, 3, 4, 5],
                           'dt': pd.date_range('2011-01-01', freq='D',
                                               periods=5)},
                          index=idx)
        res = df.pivot_table(index=df.index.month,
                             columns=pd.Grouper(key='dt', freq='M'))
        exp_columns = pd.MultiIndex.from_tuples([('A',
                                                  pd.Timestamp('2011-01-31'))])
        exp_columns.names = [None, 'dt']
        exp = pd.DataFrame([3.25, 2.0],
                           index=[1, 2], columns=exp_columns)
        tm.assert_frame_equal(res, exp)

        res = df.pivot_table(index=pd.Grouper(freq='A'),
                             columns=pd.Grouper(key='dt', freq='M'))
        exp = pd.DataFrame([3],
                           index=pd.DatetimeIndex(['2011-12-31']),
                           columns=exp_columns)
        tm.assert_frame_equal(res, exp) 

def test_pivot_preserve_dtypes(self, columns, values):
        # GH 7142 regression test
        v = np.arange(5, dtype=np.float64)
        df = DataFrame({'float1': v, 'float2': v + 2.0,
                        'bool1': v <= 2, 'bool2': v <= 3})

        df_res = df.reset_index().pivot_table(
            index='index', columns=columns, values=values)

        result = dict(df_res.dtypes)
        expected = {col: np.dtype('O') if col[0].startswith('b')
                    else np.dtype('float64') for col in df_res}
        assert result == expected 

def test_pivot_table_dropna_categoricals(self, dropna):
        # GH 15193
        categories = ['a', 'b', 'c', 'd']

        df = DataFrame({'A': ['a', 'a', 'a', 'b', 'b', 'b', 'c', 'c', 'c'],
                        'B': [1, 2, 3, 1, 2, 3, 1, 2, 3],
                        'C': range(0, 9)})

        df['A'] = df['A'].astype(CDT(categories, ordered=False))
        result = df.pivot_table(index='B', columns='A', values='C',
                                dropna=dropna)
        expected_columns = Series(['a', 'b', 'c'], name='A')
        expected_columns = expected_columns.astype(
            CDT(categories, ordered=False))
        expected_index = Series([1, 2, 3], name='B')
        expected = DataFrame([[0, 3, 6],
                              [1, 4, 7],
                              [2, 5, 8]],
                             index=expected_index,
                             columns=expected_columns,)
        if not dropna:
            # add back the non observed to compare
            expected = expected.reindex(
                columns=Categorical(categories)).astype('float')

        tm.assert_frame_equal(result, expected) 

def test_pivot_table_multiple(self):
        index = ['A', 'B']
        columns = 'C'
        table = pivot_table(self.data, index=index, columns=columns)
        expected = self.data.groupby(index + [columns]).agg(np.mean).unstack()
        tm.assert_frame_equal(table, expected) 

def test_pass_array(self):
        result = self.data.pivot_table(
            'D', index=self.data.A, columns=self.data.C)
        expected = self.data.pivot_table('D', index='A', columns='C')
        tm.assert_frame_equal(result, expected)