Merge lists in columns in pandas dataframe

Question

I've got a dataframe with lists in two columns. It looks like this:

    column1    column2             column3
0   text       [cat1,cat2,cat3]    [1,2,3]
1   text2      [cat2,cat3,cat1]    [4,5,6]

The values in column3 belong to the categories in column2. How can I get a dataframe that looks like this?

    column1    cat1     cat2      cat3
0   text         1        2         3
1   text2        6        4         5

Thank you for your help!

sophocles · Accepted Answer · 2022-11-17 13:41:41Z

1

You could use explode to break the values in your lists into separate rows and use pivot_table:

df.explode(
    ['column2','column3']
    ).pivot_table(index='column1',columns='column2',values='column3',aggfunc='first').reset_index()

prints:

index     column1 cat1 cat2 cat3
0          text    1    2    3
1         text2    6    4    5

answered Nov 17, 2022 at 13:41

sophocles

13.9k3 gold badges18 silver badges37 bronze badges

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

1 Answer 1