Python pandas.pivot_table方法代码示例

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
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) 

# 需要导入模块: import pandas [as 别名]
# 或者: from pandas import pivot_table [as 别名]
def test_pivot_table(self):
        index = ['A', 'B']
        columns = 'C'
        table = pivot_table(self.data, values='D',
                            index=index, columns=columns)

        table2 = self.data.pivot_table(
            values='D', index=index, columns=columns)
        tm.assert_frame_equal(table, table2)

        # this works
        pivot_table(self.data, values='D', index=index)

        if len(index) > 1:
            assert table.index.names == tuple(index)
        else:
            assert table.index.name == index[0]

        if len(columns) > 1:
            assert table.columns.names == columns
        else:
            assert table.columns.name == columns[0]

        expected = self.data.groupby(
            index + [columns])['D'].agg(np.mean).unstack()
        tm.assert_frame_equal(table, expected)