Count Boolean values from Pivot table with pandas

I have a dataframe df defined like so:

    A   B   C   D   E   F
0   a   z   l   1   qqq True
1   a   z   l   2   qqq True
2   a   z   l   3   qqq False
3   a   z   r   1   www True
4   a   z   r   2   www False
5   a   z   r   2   www False
6   s   x   7   2   eee True
7   s   x   7   3   eee False
8   s   x   7   4   eee True
9   s   x   5   1   eee True
10  d   c   l   1   rrr True
11  d   c   l   2   rrr False
12  d   c   r   1   fff False
13  d   c   r   2   fff True
14  d   c   r   3   fff True

My goal is to create a table based on the unique values of columns A, B and C so that I am able to count the number of elements of column D and the unique number of elements in column C.

The output looks like this:

       D    E
A   B       
a   z   6   2
d   c   5   2
s   x   4   2

Where for example the 6 is how many elements are present in the column A having value a, and 2 indicates the number of unique elements in column E (qqq,wwww).

I was able to achgieve this goal by using the following lines of code:

# Define dataframe
df = pd.DataFrame({'A':['a','a','a','a','a','a','s','s','s','s','d','d','d','d','d'],
                   'B':   ['z','z','z','z','z','z','x','x','x','x','c','c','c','c','c'],
                   'C':  ['l','l','l','r','r','r','7','7','7','5','l','l','r','r','r'],
                   'D':    ['1','2','3','1','2','2','2','3','4','1','1','2','1','2','3'],
                   'E':    ['qqq','qqq','qqq','www','www','www','eee','eee','eee','eee','rrr','rrr','fff','fff','fff'],
                   'F':   [True,True,False,True,False,False,True,False,True,True,True,False,False,True,True]})

# My code so far
a = df.pivot_table(index=['A','B','C'], aggfunc={'E':'nunique', 'D':'count'}).sort_values(by='E')
a = a.pivot_table(index=['A','B'], aggfunc='sum').sort_values(by='E')

The Problem:

Now I would like also to count the number of True or False values present in the dataframe with the same criteria presented before so that the result looks like this:

        D   E   True    False
A   B               
a   z   6   2      3        3
d   c   5   2      3        2
s   x   4   2      3        1

As you can see the number of True values where A=a are 3 and False values are 3 as well.

What is a smart and elegant way to achieve my final goal?

2 answers

  • answered 2018-03-20 15:24 Wen

    You just need two steps

    pd.concat([df.groupby(['A','B','C']).agg({'E': 'nunique', 'D':'size'}).sum(level=[0,1])
    ,df.groupby(['A','B']).F.value_counts().unstack()],1)
    Out[702]: 
         E  D  False  True
    A B                   
    a z  2  6      3     3
    d c  2  5      2     3
    s x  2  4      1     3
    

    Using value_counts

    df.groupby(['A','B']).F.value_counts().unstack()
    

  • answered 2018-03-20 15:24 Scott Boston

    Using your code, you could extend like this:

    # My code so far
    a = df.pivot_table(index=['A','B','C'], aggfunc={'E':'nunique', 'D':'count','F':sum}).sort_values(by='E').rename(columns={'F':'F_True'})
    a = a.pivot_table(index=['A','B'], aggfunc='sum').sort_values(by='E').eval('F_False = D - F_True')
    

    OUtput:

         D  E  F_True  F_False
    A B                       
    a z  6  2     3.0      3.0
    d c  5  2     3.0      2.0
    s x  4  2     3.0      1.0