I have a pandas dataframe. When I try to create a pivot, there is no data in the pivot (i.e. only the index, but no columns) when I do it like this. The data type of amount columns is object, not float.
pivot = table.pivot_table(index='Date', columns='Currency', values='Amount', aggfunc='sum')
However, when I use aggfunc count it works.
pivot = table.pivot_table(index='Date', columns='Currency', values='Amount', aggfunc=**'count'**)
Also, if I create a pivot through a workaround, it works
pivot = table.groupby(['Date', 'Currency'])['Amount'].sum().unstack()
When I change the data type of Amount column to float, the normal pivot works (the one shown first).
Do you know what causes this issue when data type is object? Then it sometimes works, sometimes not, and I would really like to understand the logic that makes it fail.
Source: Python Questions