Count unique values within groups and then pivot two into categories

user_6143848 0
Viewed 40

I have the following dataset


    Package Document    bool
0   Pkg1    DocumentA   True
1   Pkg1    DocumentA   True
2   Pkg1    DocumentB   True
3   Pkg1    DocumentC   True
4   Pkg2    DocumentA   False
5   Pkg2    DocumentB   True
6   Pkg2    DocumentB   True
7   Pkg2    DocumentC   True
8   Pkg3    DocumentA   False
9   Pkg3    DocumentB   True
10  Pkg3    DocumentD   False
11  Pkg3    DocumentD   True
12  Pkg4    DocumentB   True
13  Pkg4    DocumentC   True
14  Pkg5    DocumentB   False
15  Pkg5    DocumentC   False

I need to count the number of packages a document was identified. and then count their respective True False, based on the condition that if even there is one false in that package category it will only be False. such that DocumentA is in 3 packages, out of which it has been True once and False Twice. Hope this helps

Document Count True False
DocumentA 3 1 2
DocumentB 5 4 1
DocumentC 4 3 1
DocumentD 1 0 1

I was able to do the group based count but unable to get to those True False columns by using

df.groupby("Document")["Package"].nunique()

which gives me

Document
DocumentA    3
DocumentB    5
DocumentC    4
DocumentD    1

But I also need those additional columns

pandas-groupby pandas dataframe python
1 Answers

First use GroupBy.transform with GroupBy.all and then pivot by DataFrame.pivot_table, last add count column by DataFrame.insert:

print (df.dtypes)
Package     object
Document    object
bool          bool
dtype: object

df["bool"] = df.groupby(["Document",'Package'])["bool"].transform('all')
print (df)
   Package   Document   bool
0     Pkg1  DocumentA   True
1     Pkg1  DocumentA   True
2     Pkg1  DocumentB   True
3     Pkg1  DocumentC   True
4     Pkg2  DocumentA  False
5     Pkg2  DocumentB   True
6     Pkg2  DocumentB   True
7     Pkg2  DocumentC   True
8     Pkg3  DocumentA  False
9     Pkg3  DocumentB   True
10    Pkg3  DocumentD  False
11    Pkg3  DocumentD  False
12    Pkg4  DocumentB   True
13    Pkg4  DocumentC   True
14    Pkg5  DocumentB  False
15    Pkg5  DocumentC  False

df= df.pivot_table(index='Document', 
                   columns='bool', 
                   values='Package', 
                   aggfunc='nunique', 
                   fill_value=0)
df.insert(0, 'count', df.sum(axis=1))
print (df)
bool       count  False  True
Document                     
DocumentA      3      2     1
DocumentB      5      1     4
DocumentC      4      1     3
DocumentD      1      1     0
user_29010020
Related