2

I'd like to apply the pd.pivot_table() to get the number of each categorical value for column 'categories'.

Here, the basic info of the dataset is as following:

df.info()
Data columns (total 3 columns):
location                                   2270 non-null object
time                               2270 non-null object
categories    2270 non-null object
dtypes: object(3)

My code: 

table=pd.pivot_table(df,values=['categories'],
                     index=['location','time'],
                     columns=['categories'],
                     aggfunc='count',fill_value=0)
table.head()

Expected result is: 

location    time    Cat1 Cat2
L1      Jan-2020    5   1
L1      Feb-2020    2   1
L2      Jan-2019    4   3
L2      Feb-2020    5   0

But my result is: 

    location      time  
    L1      Jan-2020    
    L1      Feb-2020    
    L2      Jan-2019    
    L2      Feb-2020

I tried to create the simple dataframe by input the data as the example below, then I could get my expected result. But if I import my whole dataframe from csv file, it failed.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html

Thanks for all the suggestions and help in advance.

Elaine
  • 35
  • 3

1 Answers1

1

I think you need GroupBy.size function for count and also values parameter should be omit:

table=pd.pivot_table(df,
                     index=['location','time'],
                     columns=['categories'],
                     aggfunc='size',fill_value=0)
print (table.head())
categories         Cat1  Cat2
location time                
L1       Feb-2020     2     1
         Jan-2020     5     1
L2       Feb-2020     5     0
         Jan-2019     4     3
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • but I am wondering why the example from official guide works, while my solution doesn't work? – Elaine May 06 '20 at 15:10
  • @Elaine - Problem is with number of columns, here are only 3 columns. I think it is bug, similar problem is with `groupby` and `count` like [this](https://stackoverflow.com/a/32307259/2901002) – jezrael May 06 '20 at 15:12
  • 1
    very clear explanation. Thanks again for your answer – Elaine May 06 '20 at 15:19