The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

0
votes

I have a DataFrame which I am trying to pivot.

df

   col_1    col_2   col_3   col_4
    John    Method  4       White
     Tom    Method  29613   White
   Harry    Method  147     White
    John    Method  84      Blue
     Tom    Method  28      Blue
    John    Method  222085  Black
     Tom    Method  159459  Black
   Harry    Method  2204225 Black
    John    Method  600253  Green
     Tom    Method  3156210 Green
   Harry    Method  4343635 Green
   Harry    Method  4343635 Green

Expected result:

newDf

           Black    Blue    Green   White
Harry     2204225          8687270  147
John       222085   84     600253   4
 Tom       159459   28     3156210  29613

My code:

newDf = pd.pivot_table(df, values='col_3', index=['col_1'], columns=['col_4'], aggfunc={'col_3' : 'sum'})

Column types are as follows:

df.dtypes 

col_1     object
col_2     object
col_3     int64
col_4      object
dtype: object

Error:

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Can anyone please help me here? Thanks!

3
pythonpandasdataframe
Your error can not be reproducible , please show us more detail – BENY
Can you reproduce that error using the provided sample DF? What is your Pandas version? – MaxU

3 Answers

2
votes
In [54]: df.pivot_table(values='col_3', index='col_1', columns='col_4', aggfunc='sum', fill_value=0)
Out[54]:
col_4    Black  Blue    Green  White
col_1
Harry  2204225     0  8687270    147
John    222085    84   600253      4
Tom     159459    28  3156210  29613

If you want to replace NaN's with empty strings:

In [55]: df.pivot_table(values='col_3', index='col_1', columns='col_4', aggfunc='sum', fill_value='')
Out[55]:
col_4    Black Blue    Green  White
col_1
Harry  2204225       8687270    147
John    222085   84   600253      4
Tom     159459   28  3156210  29613

but as a result the columns containing such empty strings will be of string (object) dtype: 

In [56]: df.pivot_table(values='col_3', index='col_1', columns='col_4', aggfunc='sum', fill_value='').dtypes
Out[56]:
col_4
Black     int64
Blue     object
Green     int64
White     int64
dtype: object
2
votes

You already passed a dict to aggfunc, then you do not need point out the column to value

pd.pivot_table(df,index=['col_1'], columns=['col_4'], aggfunc={'col_3' : 'sum'})
Out[564]: 
           col_3                          
col_4      Black  Blue      Green    White
col_1                                     
Harry  2204225.0   NaN  8687270.0    147.0
John    222085.0  84.0   600253.0      4.0
Tom     159459.0  28.0  3156210.0  29613.0
1
votes

Prior to your pivot you need to handle missing values. df.fillna(0, inplace=True) will do the trick.