pivot_table aggregate changes data type

[kieranholland]

IMHO the result should be integer type:

df = DataFrame({
    'x':('a', 'b'),
    'y':('A', 'B'),
    'i':(1, 2),
})
pivot_table(df, rows='x', cols='y')
y  A    B  
x          
a  1.0  NaN
b  NaN  2.0

Love pandas!

[adamklein]

There are a few places that this happens: see issue #622. Problem is representation of NaN in integer does not exist in pandas. for now.

[wesm]

if you replace the NaNs with some other value and cast to integer, that will work:

table.fillna(0).astype(int)

#622 is actually a separate issue re: richer handling of the NumPy type hierarchy

[kieranholland]

Except with mixed types:

df = dataframe({
    'x':('a', 'b'),
    'y':('a', 'b'),
    'i':(1, 2),
    'j':(1.0, 2.0),
})
pt = pivot_table(df, rows='x', cols='y', values=['i', 'j'])
print(pt)

   i         j       
   a    b    a    b  
x                    
a  1.0  nan  1.0  nan
b  nan  2.0  nan  2.0

print(pt.fillna(0).astype(int))

   i     j   
   a  b  a  b
x            
a  1  0  1  0
b  0  2  0  2

Desired:

   i         j   
   a    b    a    b
x                    
a  1    nan  1.0  nan
b  nan  2    nan  2.0

[wesm]

Unfortunately there's nothing that can be done until NA support in NumPy improves (not clear if that will ever happen)