How to add selft defined value to pivot?-CodePudding

I'm try to do a pivot to this dataframe,

 column1  column2
 A        Y
 A        N
 B        Y
 B        N
 C        Y

count coulmn2 by this code

pivot=pd.pivot_table(dataframe,index=['column1'],columns=['column2'],values=['column1'], aggfunc={'column1':'count'}, margins=False, fill_value=0)

And the answer will be

 count  Y   N
 A      1   1
 B      1   1
 C      1   0

, but if sometimes dataframe only contains one value 'Y',then the pivot will only have one column.

 count  Y
 A      1
 B      1
 C      1

Is there any method to use two default value['Y','N'] to pivot column2, and always shows the result like this ?

 count  Y   N
 A      1   0
 B      1   0
 C      1   0

CodePudding user response：

If dataframe only contains one value either 'Y' or 'N', then other value can be addable by simple if condition after pivoting... Something like this ,maybe

if "N" not in pivot.columns:
    df["N"] = 0
if "Y" not in pivot.columns:
    df["Y"] = 0

Hope this Helps...

CodePudding user response：

Create Categorical column first - if missing N values is added with 0:

dataframe['column2'] = pd.Categorical(dataframe['column2'], categories=['Y','N'])

pivot=pd.pivot_table(dataframe,
                     index='column1',
                     columns='column2',
                     values='column1', 
                     aggfunc={'column1':'count'}, 
                     fill_value=0)

print (pivot)
column2  Y  N
column1      
A        1  0
B        1  0
C        1  0

Or use DataFrame.reindex after pivot:

pivot=pd.pivot_table(dataframe,
                     index='column1',
                     columns='column2',
                     values='column1', 
                     aggfunc={'column1':'count'}, 
                     fill_value=0)

pivot= pivot.reindex(['Y','N'], axis=1, fill_value=0)
print (pivot)
column2  Y  N
column1      
A        1  0
B        1  0
C        1  0