How to make a binary cross validation table from a pivot table using Pandas

I have a dataset looking like below

before pivot

When I do pivot, then it should look like this

after pivot

But what I truely want is this

what I want pivot

I can do it with loops and stuffs in kinda-manual way which is not pretty and intuitive… like I could search values that are not None type then replace by True and ifnot False. But this is not, again, pretty and it could take some time if I execute it on a large dataset (which will be the case).

So I would like to know if I could do it with a single line or a couple of lines in a prettier and faster way.

Thanks!

Answer

From your DataFrame :

import pandas as pd
from io import StringIO

df = pd.read_csv(StringIO("""
ID,ISIN
AXAMIL,A
AXAMIL,B
AXAMIL,C
AXAEXL,D
UNPEVL,A
UNPEVL,E
UNPMSP,A
UNPMSP,B"""), sep=',')
df

We can use a groupby, then unstack the result and convert it as boolean to get the expected result :

>>> df.groupby(['ID', 'ISIN']).size().unstack(fill_value=0).astype(bool).rename_axis(None, axis=1).reset_index()
    ID      A       B       C       D       E
0   AXAEXL  False   False   False   True    False
1   AXAMIL  True    True    True    False   False
2   UNPEVL  True    False   False   False   True
3   UNPMSP  True    True    False   False   False