How To Compile Multiple Column Values Into One Under Corresponding Primary Key In Pandas?

Question

I have this dataset:

pd.DataFrame(data={'ID':['1','2','3'],'Genre':['Adventure|Children|Fantasy','Horror','Comedy|Drama']})

    ID  Genre
0   1   Adventure|Children|Fantasy
1   2   Horror
2   3   Comedy|Drama

I want it to look like this:

    ID  Genre
0   1   Adventure
1   1   Children
2   1   Fantasy
3   2   Horror
4   3   Comedy
5   3   Drama

How can I do it with Pandas?

Answer 1

With df.set_index + .explode() on splitted Genre:

df.set_index('ID')['Genre'].str.split('|').explode().reset_index()

---

  ID      Genre
0  1  Adventure
1  1   Children
2  1    Fantasy
3  2     Horror
4  3     Comedy
5  3      Drama

Answer 2

I would simply extractall the words and join them with the corresponding IDs :

out = df[["ID"]].join(df["Genre"].str.extractall(r"(?P<Genre>[^|]+)").droplevel(1))

​ Output :

print(out)

  ID      Genre
0  1  Adventure
0  1   Children
0  1    Fantasy
1  2     Horror
2  3     Comedy
2  3      Drama