<commit_before># -*- coding: utf-8 -*-

from datetime import datetime, date, timedelta

import pytest


import numpy as np

from collections import OrderedDict
import pandas as pd
from pandas import (DataFrame, Series, Index, MultiIndex,
                    Grouper, date_range, concat, Categorical)
from pandas.core.reshape.pivot import pivot_table, crosstab
from pandas.compat import range, product
import pandas.util.testing as tm
from pandas.api.types import CategoricalDtype as CDT


@pytest.fixture(params=[True, False])
def dropna(request):
    return request.param


class TestPivotTable(object):

    def setup_method(self, method):
        self.data = DataFrame({'A': ['foo', 'foo', 'foo', 'foo',
                                     'bar', 'bar', 'bar', 'bar',
                                     'foo', 'foo', 'foo'],
                               'B': ['one', 'one', 'one', 'two',
                                     'one', 'one', 'one', 'two',
                                     'two', 'two', 'one'],
                               'C': ['dull', 'dull', 'shiny', 'dull',
                                     'dull', 'shiny', 'shiny', 'dull',
                                     'shiny', 'shiny', 'shiny'],
                               'D': np.random.randn(11),
                               'E': np.random.randn(11),
                               'F': np.random.randn(11)})

    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)

    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_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_categorical(self):

        cat1 = Categorical(["a", "a", "b", "b"],
                           categories=["a", "b", "z"], ordered=True)
        cat2 = Categorical(["c", "d", "c", "d"],
                           categories=["c", "d", "y"], ordered=True)
        df = DataFrame({"A": cat1, "B": cat2, "values": [1, 2, 3, 4]})
        result = pd.pivot_table(df, values='values', index=['A', 'B'],
                                dropna=True)

        exp_index = pd.MultiIndex.from_arrays(
            [cat1, cat2],
            names=['A', 'B'])
        expected = DataFrame(
            {'values': [1, 2, 3, 4]},
            index=exp_index)
        tm.assert_frame_equal(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_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_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)

    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_pivot_table_multiple(self):
        index = ['A', 'B']
        columns = 'C'<commit_msg>TST: adding test cases for verifying correct values shown by pivot_table() #21378 (#21393)<commit_after># -*- coding: utf-8 -*-

from datetime import datetime, date, timedelta

import pytest


import numpy as np

from collections import OrderedDict
import pandas as pd
from pandas import (DataFrame, Series, Index, MultiIndex,
                    Grouper, date_range, concat, Categorical)
from pandas.core.reshape.pivot import pivot_table, crosstab
from pandas.compat import range, product
import pandas.util.testing as tm
from pandas.api.types import CategoricalDtype as CDT


@pytest.fixture(params=[True, False])
def dropna(request):
    return request.param


class TestPivotTable(object):

    def setup_method(self, method):
        self.data = DataFrame({'A': ['foo', 'foo', 'foo', 'foo',
                                     'bar', 'bar', 'bar', 'bar',
                                     'foo', 'foo', 'foo'],
                               'B': ['one', 'one', 'one', 'two',
                                     'one', 'one', 'one', 'two',
                                     'two', 'two', 'one'],
                               'C': ['dull', 'dull', 'shiny', 'dull',
                                     'dull', 'shiny', 'shiny', 'dull',
                                     'shiny', 'shiny', 'shiny'],
                               'D': np.random.randn(11),
                               'E': np.random.randn(11),
                               'F': np.random.randn(11)})

    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)

    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_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_categorical(self):

        cat1 = Categorical(["a", "a", "b", "b"],
                           categories=["a", "b", "z"], ordered=True)
        cat2 = Categorical(["c", "d", "c", "d"],
                           categories=["c", "d", "y"], ordered=True)
        df = DataFrame({"A": cat1, "B": cat2, "values": [1, 2, 3, 4]})
        result = pd.pivot_table(df, values='values', index=['A', 'B'],
                                dropna=True)

        exp_index = pd.MultiIndex.from_arrays(
            [cat1, cat2],
            names=['A', 'B'])
        expected = DataFrame(
            {'values': [1, 2, 3, 4]},
            index=exp_index)
        tm.assert_frame_equal(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_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)

        # gh-21378
        df = pd.DataFrame(
            {'A': pd.Categorical(['left', 'low', 'high', 'low', 'high'],
                                 categories=['low', 'high', 'left'],
                                 ordered=True),
             'B': range(5)})

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

        tm.assert_frame_equal(result, 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)

    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_pivot_table_multiple(self):
        index = ['A', 'B']
        columns = 'C'
{'fraction_non_alphanumeric': 0.16296100811506234, 'fraction_numerical': 0.022827736359437883, 'mean_word_length': 1.5919972640218878, 'pattern_counts': {'":': 6, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 50, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1989518', 'n_tokens_mistral': 4915, 'n_tokens_neox': 4498, 'n_words': 1025}