Pandas binary table from column based on index

Question

How can I binarize a dataset according to the index? E.g.

                   A          B          C
idUser                                 
3                  1          1          1
2                  0          1          0
4                  1          0          0

I have tried using pd.get_dummies but the result is almost what I need.

dictio = {'idUser': [3, 3, 3, 2, 4], 'artist': ['A', 'B', 'C', 'B', 'A']}
df = pd.DataFrame(dictio)
df = df.set_index('idUser')
df_binary = pd.get_dummies(df, columns=['artist'])
print(df_binary)

                   A          B          C
idUser                                 
3                  1          0          0
3                  0          1          0
3                  0          0          1
2                  0          1          0
4                  1          0          0

Which dataframe is your example and which your expected output? Does df_binary.groupby('idUser', sort=False).max() solve your question? — Michael Szczesny
– Michael Szczesny, Commented Dec 20, 2021 at 14:29
It does! Thank you. It also keeps other columns in the dataset (e.g. country and sex) that I needed. — Caio Rocha
– Caio Rocha, Commented Dec 20, 2021 at 14:45

Asish M. · Accepted Answer · 2021-12-20 14:28:50Z

1

In [27]: df_binary.groupby(level=0).any().astype(int)
Out[27]:
        artist_A  artist_B  artist_C
idUser
2              0         1         0
3              1         1         1
4              1         0         0

alternatively starting from your df before the .set_index()

In [33]: df.pivot_table(index='idUser', columns='artist', aggfunc='size', fill_value=0).rename_axis(columns=None)
Out[33]:
        A  B  C
idUser
2       0  1  0
3       1  1  1
4       1  0  0

answered Dec 20, 2021 at 14:28

Asish M.

2,6571 gold badge19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas binary table from column based on index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related