Search code examples
pythonpandas

Pandas Add Unique Values from Groupby to Column

I have a dataframe that lists a users codes

UserID     Code
   123        A
   123        B
   123        A
   456        C
   456        D

How do I add a column that shows all of the users unique codes?

UserID     Code     UniqueCodes
   123        A          [A, B]
   123        B          [A, B]
   123        A          [A, B]
   456        C          [C, D]
   456        D          [C, D]

I tried doing df.groupby(by='UserID')['Code'].agg(['unique']) but that did not work.

I also tried to do df.groupby(by='UserID')['Code'].transform('unique') but I got an error:

'unique' is not a valid function name for transform(name)

Solution

  • You almost did it, the last step was to merge initial dataframe with the grouped-by one:

    df.merge(df.groupby(by='UserID')['Code'].agg(['unique']).reset_index(), on = 'UserID')


UserID  Code    unique
0   123 A   [A, B]
1   123 B   [A, B]
2   123 A   [A, B]
3   456 C   [C, D]
4   456 D   [C, D]