Reshaping pandas dataframe with a column containing lists

Question

Let's say I have a dataframe that looks like this:

import pandas as pd

data = [{"Name" : "Project A", "Feedback" : ['we should do x', 'went well']},
            {"Name" : "Project B", "Feedback" : ['eat pop tarts', 'boo']},
            {"Name" : "Project C", "Feedback" : ['bar', 'baz']}
            ]

df = pd.DataFrame(data)
df = df[['Name','Feedback']]
df

    Name        Feedback
0   Project A   ['we should do x', 'went well']
1   Project B   ['eat pop tarts', 'boo']
2   Project C   ['bar', 'baz']

What I would like to do is reshape the dataframe, such that Name is the key and each element in the list of the Feedback column is a value like so:

        Name          Feedback
0       Project A    'we should do x'   
1       Project A    'went well'
2       Project B    'eat pop tarts'
3       Project B    'boo'
4       Project C    'bar'
5       Project C    'baz'

What would be an efficient way to do this?

This could be a good read as well: https://stackoverflow.com/questions/27263805/pandas-when-cell-contents-are-lists-create-a-row-for-each-element-in-the-list — Vinícius Figueiredo, Jul 06 '17 at 20:01

score 4 · Accepted Answer · answered Jul 06 '17 at 19:49

4

One option is to reconstruct the data frame by flattening column Feedback and repeat column Name:

pd.DataFrame({
        'Name': df.Name.repeat(df.Feedback.str.len()),
        'Feedback': [x for s in df.Feedback for x in s]
    })

#         Feedback       Name
#0  we should do x  Project A
#0       went well  Project A
#1   eat pop tarts  Project B
#1             boo  Project B
#2             bar  Project C
#2             baz  Project C

answered Jul 06 '17 at 19:49

Psidom

209,562
33
339
356

ah! Thanks @Psidom...didn't think about list comprehensions..my initial approach of trying to use stack/unstack was erroring out. – Jason Jul 06 '17 at 19:59
Your *Feedback* column is of list type which is kind of *irregular*. So the *stack/unstack* approach won't help much here. – Psidom Jul 06 '17 at 20:03

score 1 · Answer 2 · answered Jul 06 '17 at 21:03

Here's another method:

# Separate out values (NOTE- this assumes you'll always have two strings in list)
df['pos_0'] = df['Feedback'].str[0]
df['pos_1'] = df['Feedback'].str[1]

df
        Name                     Feedback           pos_0      pos_1
0  Project A  [we should do x, went well]  we should do x  went well
1  Project B         [eat pop tarts, boo]   eat pop tarts        boo
2  Project C                   [bar, baz]             bar        baz

Desired output:

pd.melt(df, 'Name', ['pos_0', 'pos_1'], 'Feedback').drop('Feedback', axis=1)
        Name           value
0  Project A  we should do x
1  Project B   eat pop tarts
2  Project C             bar
3  Project A       went well
4  Project B             boo
5  Project C             baz

Reshaping pandas dataframe with a column containing lists

2 Answers2