Getting No numeric types to aggregate error when using pivot_table

Question

I know this should be really easy but I keep getting errors when trying to pivot. I'm getting my data from psycopg2

cur.execute("select * from table")
arr=cur.fetchall()
pdres=pandas.DataFrame(arr, columns=['pricedate',hour', 'node','dart'])
pdres=pdres.set_index(['pricedate','hour','node'])

Here is where stuff stops working...

pdres.pivot(index=['pricedate','hour'],columns='node',values='dart')

results in Wrong number of items passed 720, placement implies 2

That brought me to this question which led me to doing this

pandas.pivot_table(pdres, index=['pricedate','hour'], columns='node', values='dart')

This tells me 'No numeric types to aggregate

That led me to http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.convert_objects.html but I'm not sure the right syntax of it (when to use it), and I also don't even know if I'm on the right track.

You don't need the line `pdres=pdres.set_index(['pricedate','hour','node'])`, as `pivot` works on the columns. — joris, Jun 10 '15 at 08:32
And BTW, you can also use `pd.read_sql_query("select * from table", con)` for the first three lines — joris, Jun 10 '15 at 08:33
@joris thanks to your suggestion and even more googling I found the problem. — Dean MacGregor, Jun 10 '15 at 15:04

score 0 · Accepted Answer · edited Jul 29 '16 at 17:55

There are two changes to make it work:

First don't set_index
Second, don't use convert_objects. Do use astype

Specifically, the following works:

cur.execute("select * from table")
arr = cur.fetchall()
pdres = pandas.DataFrame(arr, columns=['pricedate', 'hour', 'node', 'dart'])
pdres['dart'] = pdres['dart'].astype(float)
pdres = pandas.pivot_table(pdres, index=['pricedate','hour'], columns='node', values='dart')

Getting No numeric types to aggregate error when using pivot_table

1 Answers1