How to highlight the membership of a column of identifiers?

My input is this df :

df = pd.DataFrame({'group': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'C'], 'identifier': [1, 2, 1, 3, 1, 2, 5, 4]})

print(df)

  group  identifier
0     A           1
1     A           2
2     A           1
3     A           3
4     B           1
5     B           2
6     B           5
7     C           4

And my expected output is this :

   identifier      in    not_in
0           1  [A, B]       [C]
1           2  [A, B]       [C]
2           3     [A]    [B, C]
3           4     [C]    [A, B]
4           5     [B]    [A, C]

I made the code below :

result = df.drop_duplicates(subset=['group', 'identifier']) \
    .groupby('identifier')['group'].agg(list) \
    .reset_index() \
    .rename(columns={'group': 'in'}) \
    .assign(not_in=lambda x: set(df['group']).difference(set(x['in'])))
print(result)

But I got an error TypeError: unhashable type: 'list'

Can you guys help me fix the error ? I think it’s unfair that python throws that error.

>Solution :

This kind of operation is best done with sets. First groupby.agg to get the group per identifier, then subtract from the total population (obtained with set.union) to get those not in the set:

out = (df.groupby('identifier')['group']
         .agg(set).reset_index(name='in')
         .assign(not_in=lambda d: set.union(*d['in'])-d['in'])
      )

Output:

   identifier      in  not_in
0           1  {B, A}     {C}
1           2  {B, A}     {C}
2           3     {A}  {B, C}
3           4     {C}  {B, A}
4           5     {B}  {A, C}

If you want lists (which I don’t recommend, sets are more useful for set operations):

out = (df.groupby('identifier')['group']
         .agg(set).to_frame('in')
         .assign(not_in=lambda d: set.union(*d['in'])-d['in'])
         .map(list).reset_index()
      )

Output:

   identifier      in  not_in
0           1  [B, A]     [C]
1           2  [B, A]     [C]
2           3     [A]  [B, C]
3           4     [C]  [B, A]
4           5     [B]  [A, C]