Pandas pivot with duplicates

Question

The sample data looks like this:

d = pd.DataFrame({'name': ['Adam', 'Adam', 'Bob', 'Bob', 'Craig'],
              'number': [111, 222, 333, 444, 555], 
              'type': ['phone', 'fax', 'phone', 'phone', 'fax']})

name    number  type
------   ------  -----
Adam    111     phone
Adam    222     fax
Bob     333     phone
Bob     444     phone
Craig   555     fax

I am trying to convert the numbers (phone and fax) to a wide format, the ideal output:

name    fax     phone
----    -----   -----
Adam    222.0   111.0
Bob     NaN     333.0
Bob     NaN     444.0
Craig   555.0   NaN

When I tried to use the pivot method and run the following code:

p = d.pivot(index='name', columns = 'type', values='number').reset_index()

I received the error ValueError: Index contains duplicate entries, cannot reshape due to the fact that Bob has two phone numbers.

Is there a workaround for this?

Answer 1

Here you go with cumcount create the additional key

d['key']=d.groupby(['name','type']).cumcount()
p = d.pivot_table(index=['key','name'], columns = 'type', values='number',aggfunc='sum').reset_index()
p
Out[71]: 
type  key   name    fax  phone
0       0   Adam  222.0  111.0
1       0    Bob    NaN  333.0
2       0  Craig  555.0    NaN
3       1    Bob    NaN  444.0