How to extract comma separated values to individual rows

Question

This is my dataframe (where the values in the authors column are comma separated strings):

authors            book
Jim, Charles       The Greatest Book in the World
Jim                An OK book
Charlotte          A book about books
Charlotte, Jim     The last book

How do I transform it to a long format, like this:

authors            book
Jim                The Greatest Book in the World
Jim                An OK book
Jim                The last book
Charles            The Greatest Book in the World
Charlotte          A book about books
Charlotte          The last book

I've tried extracting the individual authors to a list, authors = list(df['authors'].str.split(',')), flatten that list, matched every author to every book, and construct a new list of dicts with every match. But that doesn't seem very pythonic to me, and I'm guessing pandas has a cleaner way to do this.

Answer 1

You can split the authors column by column after setting the index to the book which will get you almost all the way there. Rename and sort columns to finish.

df.set_index('book').authors.str.split(',', expand=True).stack().reset_index('book')

                             book          0
0  The Greatest Book in the World        Jim
1  The Greatest Book in the World    Charles
0                      An OK book        Jim
0              A book about books  Charlotte
0                   The last book  Charlotte
1                   The last book        Jim

And to get you all the way home

df.set_index('book')\
  .authors.str.split(',', expand=True)\
  .stack()\
  .reset_index('book')\
  .rename(columns={0:'authors'})\
  .sort_values('authors')[['authors', 'book']]\
  .reset_index(drop=True)