pandas - create new column based on duplicates

Question

I would like to combine records that have an identical id number. For example, suppose I have this DataFrame:

df=pd.DataFrame({'id': [1, 2, 2, 4], 'animal': ['dog', 'cat', 'bear', 'mouse']})

# just rearranging the order a bit
df=df[['id', 'animal', 'name']]

  id animal name
  1   dog   john
  2   cat   mary
  2   bear  mary
  4   mouse joe

What I'd like to end up with is a way to get the following:

id  name  animal  more_animals
 1  john   dog      NaN
 2  mary   cat      bear
 4  joe    mouse    NaN

I could use df[df.duplicated('id', 'last')] to find the duplicated rows, then loop through each duplicated id and add details to the new column but wondered if there was something a bit more elegant.

Anything come to mind?

Answer 1

You could do:

df.groupby('id')['animal'].apply(lambda x: pd.Series(list(x))).unstack()

Which gives you:

        0     1
id             
1     dog  None
2     cat  bear
4   mouse  None

Answer 2

Try using cumcount create the new key then pivot

newdf=df.assign(Newid=df.groupby('id').cumcount())
newdf.pivot('id','Newid','animal')
Out[448]: 
Newid      0     1
id                
1        dog  None
2        cat  bear
4      mouse  None

Answer 3

import pandas as pd
import numpy as np
from collections import defaultdict
from itertools import count

d = defaultdict(count)

i, r = pd.factorize([*zip(df.id, df.name)])
j = np.array([next(d[x]) for x in i])

n, m = len(r), j.max() + 1

b = np.empty((n, m), dtype=np.object)
b[i, j] = df.animal

d1 = pd.DataFrame(r.tolist(), columns=['id', 'name'])
d2 = pd.DataFrame(b, columns=['animal', 'more_animal'])
d1.join(d2)

   id  name animal more_animal
0   1  john    dog        None
1   2  mary    cat        bear
2   4   joe  mouse        None