pivot_table changes data type of unrelated columns

[albint]

When using the pivot_table command, the data type of a float64 column changes to object when I include a boolean column in the pivot table. This is unrelated to issue #713.

I'm using

python: 2.7.3.final.0
pandas: 0.13.1

For example, I've created the table below with two float64 columns a,b and one boolean column c

import pandas as pd
import numpy as np

np.random.seed(2)
x = pd.DataFrame(np.random.random((10,2)), columns=list('ab'))
x['pivot_column'] = x.a > 0.5
x['c'] = x.b > 0.5

I wish to create a pivot table, splitting on the 'pivot_column'. When using the columns a and b (both float64), the resulting table is as expected:

p2 = x.reset_index().pivot_table(rows='index',
                                     cols='pivot_column',
                                     values=list('ab'))
p2.info()

with output

Int64Index: 10 entries, 0 to 9
Data columns (total 4 columns):
(a, False)    6 non-null float64
(a, True)     4 non-null float64
(b, False)    6 non-null float64
(b, True)     4 non-null float64
dtypes: float64(4)<class 'pandas.core.frame.DataFrame'>

But, when I use the columns a,b,c the data type of a and b changes from float64 to object

p1 = x.reset_index().pivot_table(rows='index',
                                     cols='pivot_column',
                                     values=list('abc'))
p1.info()

with output

Int64Index: 10 entries, 0 to 9
Data columns (total 6 columns):
(a, False)    6 non-null object
(a, True)     4 non-null object
(b, False)    6 non-null object
(b, True)     4 non-null object
(c, False)    6 non-null object
(c, True)     4 non-null object
dtypes: object(6)<class 'pandas.core.frame.DataFrame'>

Is this a bug or is there some internal logic I don't know about (or both)?

Using unstack works as expected.

P.s. Pandas is great. Thanks for creating it! D.s.

[jreback]

In [8]: p1 = x.reset_index().pivot_table(rows='index',
   ...:                                      cols='pivot_column',
   ...:                                      values=list('abc'))

In [9]: p1
Out[9]: 
                      a                      b                  c       
pivot_column      False      True        False       True   False  True 
index                                                                   
0             0.4359949        NaN  0.02592623         NaN  False    NaN
1                   NaN  0.5496625         NaN   0.4353224    NaN  False
2             0.4203678        NaN   0.3303348         NaN  False    NaN
3             0.2046486        NaN    0.619271         NaN   True    NaN
4             0.2996547        NaN   0.2668273         NaN  False    NaN
5                   NaN  0.6211338         NaN   0.5291421    NaN   True
6             0.1345799        NaN   0.5135781         NaN   True    NaN
7             0.1844399        NaN   0.7853351         NaN   True    NaN
8                   NaN  0.8539753         NaN   0.4942368    NaN  False
9                   NaN  0.8465615         NaN  0.07964548    NaN  False

In [10]: p1.dtypes
Out[10]: 
   pivot_column
a  False           object
   True            object
b  False           object
   True            object
c  False           object
   True            object
dtype: object

In [11]: p1.convert_objects()
Out[11]: 
                     a                   b                c       
pivot_column     False     True      False     True   False  True 
index                                                             
0             0.435995       NaN  0.025926       NaN  False    NaN
1                  NaN  0.549662       NaN  0.435322    NaN  False
2             0.420368       NaN  0.330335       NaN  False    NaN
3             0.204649       NaN  0.619271       NaN   True    NaN
4             0.299655       NaN  0.266827       NaN  False    NaN
5                  NaN  0.621134       NaN  0.529142    NaN   True
6             0.134580       NaN  0.513578       NaN   True    NaN
7             0.184440       NaN  0.785335       NaN   True    NaN
8                  NaN  0.853975       NaN  0.494237    NaN  False
9                  NaN  0.846561       NaN  0.079645    NaN  False

In [12]: p1.convert_objects().dtypes
Out[12]: 
   pivot_column
a  False           float64
   True            float64
b  False           float64
   True            float64
c  False            object
   True             object
dtype: object

Simple enough to do a .convert_objects() to convert what can be (and that maybe the soln; haven't really looked internally). When you pivot the frame it is mixed to begin with because you are including float and boolean columns; that's why its object, but I agree, should be coerced back.

Note that the 'c' columns WILL remain as bool because they have nan (you can always .fillna(False) if you wanted).

[dsm054]

This seems to behave as of 0.19.2. Add test and close?

[jreback]

yep