How to duplicate rows in pandas, based on items in a list

Question

I have a pandas dataframe that looks like this:

COL     data
line1   [A,B,C]

where the items in the data column could either be a list or just comma separated elements. Is there an easy of way of getting:

COL     data
line1   A
line1   B
line1   C

I could iterate over the list and manually duplicate the rows via python, but is there some magic pandas trick for doing this? The key point is how to automatically duplicate the rows.

Thanks!

score 8 · Answer 1 · answered Apr 11 '13 at 16:40

8

You could write a simple cleaning function to make it a list (assuming it's not a list of commas, and you can't simply use ast.literal_eval):

def clean_string_to_list(s):
    return [c for c in s if c not in '[,]']  # you might need to catch errors

df['data'] = df['data'].apply(clean_string_to_list)

Iterating through the rows seems like a reasonable choice:

In [11]: pd.DataFrame([(row['COL'], d)
                       for d in row['data']
                       for _, row in df.iterrows()],
                       columns=df.columns)
Out[11]:
     COL data
0  line1    A
1  line1    B
2  line1    C

I'm afraid I don't think pandas caters specifically for this kind of manipulation.

answered Apr 11 '13 at 16:40

Andy Hayden

359,921
101
625
535

where does "row" and "row['COL']" come from? – Incerteza Mar 15 '15 at 09:54
@AlexanderSupertramp inside the list comprehension `for _, row in df.iterrows()`. The _ is the row's index, the row is the row of df as a Series. – Andy Hayden Mar 16 '15 at 05:18
5

This did not work for me as written; I needed to switch the two `for`statements: `[(row['COL'], d) for _, row in df.iterrows() for d in row['data']]`. – RafG Nov 25 '16 at 15:19
1

@AndyHayden, please correct your answer. For me also only RafG answer works. – Philipp Schwarz Jul 20 '17 at 10:52

score 3 · Accepted Answer · edited Oct 05 '20 at 15:46

3

You can use df.explode() option. Refer to the documentation. I believe this is exactly the functionality you need.

edited Oct 05 '20 at 15:46

Timus

10,974
5
14
28

answered Oct 04 '20 at 19:07

Sagar Simha

46
1

How to duplicate rows in pandas, based on items in a list

2 Answers2

Linked