How to create a DataFrame column based on multiple columns values

Question

I've a DataFrame that looks like this

Name	Result_cat_1	Result1	Result_cat_2	Result_2	Result_cat_3	Result_3
Andrew	Rock	12 plays	Paper	4 plays	Scissors	45 plays
John	Paper	8 plays	Scissors	15 plays	Rock	76 plays
Ronald	Scissors	6 plays	Rock	3 plays	Paper	23 plays

The end result should be like this:

Name	Rock	Paper	Scissors
Andrew	12 plays	4 plays	45 plays
John	76 plays	8 plays	15 plays
Ronald	3 plays	23 plays	6 plays

I've tried

df['Rock'] = df[Result1].where(df[Result_cat_1] == 'Rock')
df['Rock'] = df[Result2].where(df[Result_cat_2] == 'Rock')
df['Rock'] = df[Result3].where(df[Result_cat_3] == 'Rock')

but the column contains only the df[Result_cat_3] overwriting the previous 2

user7864386 · Accepted Answer · 2022-03-12 05:00:23Z

1

We could stack the "Results_cat" columns and "Results" columns separately; build a DataFrame with the stacked Series; then pivot:

df = df.set_index('Name')
out = (df.filter(like='Result_cat')
       .stack()
       .droplevel(1)
       .reset_index(name='result')
       .assign(play=df[['Result1', 'Result_2', 'Result_3']]
               .stack().to_numpy())
       .pivot('Name', 'result', 'play')
       .rename_axis(index=[None], columns=[None]))

Output:

           Paper      Rock  Scissors
Andrew   4 plays  12 plays  45 plays
John     8 plays  76 plays  15 plays
Ronald  23 plays   3 plays   6 plays

answered Mar 12, 2022 at 5:00

user7864386

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

Collectives™ on Stack Overflow

How to create a DataFrame column based on multiple columns values

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