python - 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?

标签： pythonpandas

Answer 1

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))