Pandas, Pivot error - cannot label index with null key

Question

I have a data set which looks like below.

ID     Product   date
1        A     01.01.2015  
1        B     01.01.2015  
1        C     01.03.2015  

A person can take more than one product on the same day, I want to transpose it by ID,date and get product as values.

ID date col1 col2

1      01.01.2015   A      B
1      01.03.2015   C

In SAS I do it like below:

proc transpose data = data;
  by ID Date;
  var product
run;

In pandas I used following code.

data_b = data_a.pivot(index = ['patnum','day'], values = ['drug']).add_prefix('p')

This is giving following error.

ValueError: cannot label index with a null key

Why I'm getting above error? How can I avoid it?

I'm using latest version in Pandas.

Answer 1

use groupby

for list objects

df.groupby(['ID', 'date'])['Product'].apply(list)

ID  date      
1   01.01.2015    [A, B]
    01.03.2015       [C]
Name: Product, dtype: object

for a dataframe

df.groupby(['ID', 'date'])['Product'].apply(list).apply(pd.Series)

               0    1
ID date              
1  01.01.2015  A    B
   01.03.2015  C  NaN

Answer 2

You need to create another column to identify rows within each date to help you pivot:

df.assign(Count = df.groupby('date').cumcount()).pivot("date", "Count", "Product")

#    Count  0      1
#date       
#01.01.2015 A      B
#01.03.2015 C   None