Pivot table with Pandas float and int values

Question

Goodmorning everyone

Python 2.7.3.final.0

Pandas 0.15.1

I must pivot multi values .... float and int ... but it doesn't work . Here are some examples

import pandas as pd
import decimal
import numpy as np

data = [[decimal.Decimal('696.00'), decimal.Decimal('1876.53'), decimal.Decimal('2578.53'), 518L, 2008, 'Ala'], [decimal.Decimal('705.52'), decimal.Decimal('4534.33'), decimal.Decimal('5246.33'), 525L, 2007, 'Ala']]
headers = ['A', 'B', 'C', 'D', 'E', 'F']
df = pd.DataFrame(data, columns=headers)

columns = ['E']
rows = ['F']
value = ['A', 'B'] # A float, B float

pivot_df = df.pivot_table(columns=columns,
                                  index=rows,
                                  values=value,
                                  margins=True,
                                  aggfunc=np.sum)

Result

          A                         B                  
E      2007    2008      All     2007     2008      All
F                                                      
Ala  705.52  696.00  1401.52  4534.33  1876.53  6410.86
All  705.52  696.00  1401.52  4534.33  1876.53  6410.86

All rights !

value = ['A', 'D'] # A float, D int

pivot_df = df.pivot_table(columns=columns,
                                  index=rows,
                                  values=value,
                                  margins=True,
                                  aggfunc=np.sum)

Result

       D           
E   2007 2008   All
F                  
Ala  525  518  1043
All  525  518  1043

Dosen't work .....

And ideas ?

Beat regards.

Manuel

JohnE · Answer 1 · 2015-04-14T12:45:50.790

This may be a workaround or explanation more so than an answer, but FWIW.

I suspect you don't really need to store anything as a decimal, just floats (in fact you refer to the decimal types as floats in your question, but they aren't the same thing, try df.info()). I would suggest starting with floats in the first place, or convert them:

value=['A','D']

df[value] = df[value].astype(float)

Decimal types get stored as objects. Unless you really need to do this, floats will be faster and easier to work with. If you are doing this because you want to seee two decimal places, just use formats on the floats, although pandas default format choices will often be what you want anyway. If you use ipython, check out the %precision magic which lets you specify the default formatting of floats.

Now, it works fine:

df.pivot_table(columns=columns, index=rows,       values=value,
               margins=True,    aggfunc=np.sum)

          A                  D           
E      2007 2008      All 2007 2008   All
F                                        
Ala  705.52  696  1401.52  525  518  1043
All  705.52  696  1401.52  525  518  1043

Now, all of that said, I don't see any problem with what you did, and it may be a bug in pandas. At the same time, I would stick with doing it the simpler way (floats instead of decimals) unless you really have a reason to use the decimal type, and then the problem doesn't appear.

The variable DATA is the result of a query performed with django - sql -explorer ( https://github.com/epantry/django-sql-explorer ) . To put an end to the matter I'll do the type cast as proposed. I need a procedure that cycling the seriers of data frame and convert only decimal series to float (there are string series, int series etc). Can you help me? thanks — Manuel Voltolini, Apr 14 '15 at 08:04
Sorry, I'm not sure. I would say use ```convert_objects()``` as discussed here: http://stackoverflow.com/questions/19864028/python-convert-numerical-data-in-pandas-dataframe-to-floats-in-the-presence-of but I just checked and it doesn't seem to work on decimals. I don't know anything about django-sql-explorer, but I would expect it has some option to output as float, no? Otherwise, I would just post a new question here asking how to automatically convert decimal objects to floats (probably involves writing a short function though). — JohnE, Apr 14 '15 at 12:37
I do see a slightly better way to do it, though. I'll edit above. In short, you are specifying values with ```value```, so you could just just convert that to float before doing the pivot. Those are the only columns that need to be converted and are presumably of a numeric type even if not held as int or float. — JohnE, Apr 14 '15 at 12:43

Pivot table with Pandas float and int values

1 Answers1