Transforming dataframe by making column using unique row values python pandas

Question

I have a following dataframe

Name      Activities
Eric      Soccer,Baseball,Swimming
Natasha   Soccer
Mike      Basketball,Baseball

I need to transform it into following dataframe

Activities     Name
Soccer          Eric,Natasha,Mike
Swimming        Eric
Baseball        Eric,Mike
Basketball      Mike

how should I do it?

Mike does not have Soccer as an activity, but you have Mike listed in the Soccer output. — andrew_reece
– andrew_reece, Commented Oct 10, 2018 at 3:39

user3483203 · Accepted Answer · 2018-10-10 03:46:09Z

2

Using `pd.get_dummies`

First, use get_dummies:

tmp = df.set_index('Name').Activities.str.get_dummies(sep=',')

Now using stack and agg:

tmp.mask(tmp.eq(0)).stack().reset_index('Name').groupby(level=0).agg(', '.join)

                     Name
Baseball       Eric, Mike
Basketball           Mike
Soccer      Eric, Natasha
Swimming             Eric

Using `str.split` and `melt`

(df.set_index('Name').Activities.str.split(',', expand=True)
    .reset_index().melt(id_vars='Name').groupby('value').Name.agg(', '.join))

answered Oct 10, 2018 at 3:46

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

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

Comments

andrew_reece · Accepted Answer · 2018-10-10 03:45:10Z

You can separate the Activities by performing a split and then converting the resulting list to a Series.
Then melt from wide to long format, and groupby the resulting value column (which is Activities).
In your grouped data frame, join the Name fields associated with each Activity.

Like this:

(df.Activities.str.split(",")
   .apply(pd.Series)
   .merge(df, right_index=True, left_index=True)
   .melt(id_vars="Name", value_vars=[0,1,2])
   .groupby("value")
   .agg({'Name': lambda x: ','.join(x)})
   .reset_index()
   .rename(columns={"value":"Activities"})
)

Output:

   Activities          Name
0    Baseball     Eric,Mike
1  Basketball          Mike
2      Soccer  Eric,Natasha
3    Swimming          Eric

Note: The reset_index() and rename() methods at the end of the chain are just cosmetic; the main operations are complete after the groupby aggregation.

Collectives™ on Stack Overflow

Transforming dataframe by making column using unique row values python pandas

2 Answers 2

Using `pd.get_dummies`

Using `str.split` and `melt`

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Using pd.get_dummies

Using str.split and melt

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Using `pd.get_dummies`

Using `str.split` and `melt`