removing lists from dataframes while adding data

Question

Starting with:

import pandas as pd

lis1= [['apples'],['bananas','oranges','cinnamon'],['pears','juice']]
lis2= [['john'],['stacy'],['ron']]

pd.DataFrame({'fruits':lis1,'users':lis2})

                         fruits    users
0                      [apples]   [john]
1  [bananas, oranges, cinnamon]  [stacy]
2                [pears, juice]    [ron]

I'd like to end with:

lis3= ['apples','bananas','oranges','cinnamon','pears','juice']
lis4= ['john','stacy','stacy','stacy','ron','ron']

pd.DataFrame({'fruits': lis3, 'users':lis4})

     fruits  users
0    apples   john
1   bananas  stacy
2   oranges  stacy
3  cinnamon  stacy
4     pears    ron
5     juice    ron

First, I need to create a new dataframe with each item sitting in its own row. Second, the name variable needs to repeat itself depending on the number of "fruits". So looking at the example, John has one fruit while Stacy has 5 fruits-- so under usernames Stacy has to be repeated 5 times.

Possible duplicate of "unstack" a pandas column containing lists into multiple rows — abcdaire
– abcdaire, Commented Sep 19, 2018 at 22:05

piRSquared · Accepted Answer · 2018-09-19 22:23:04Z

3

`itertools`

from itertools import chain, product, starmap

pd.DataFrame(
    [*chain(*starmap(product, zip(df.fruits, df.users)))],
    columns=df.columns
)

     fruits  users
0    apples   john
1   bananas  stacy
2   oranges  stacy
3  cinnamon  stacy
4     pears    ron
5     juice    ron

This also works if you have just 2 columns

pd.DataFrame(
    [*chain(*starmap(product, zip(*map(df.get, df))))],
    columns=df.columns
)

`generator`

def f(z):
  for A, B in z:
    for a in A:
      for b in B:
        yield (a, b)

pd.DataFrame([*f(zip(df.fruits, df.users))], columns=df.columns)

     fruits  users
0    apples   john
1   bananas  stacy
2   oranges  stacy
3  cinnamon  stacy
4     pears    ron
5     juice    ron

edited Sep 19, 2018 at 22:23

answered Sep 19, 2018 at 22:06

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

Comments

cpander · Accepted Answer · 2018-09-19 22:08:19Z

2

Assuming that lis1 and lis2 have the same number of elements, you can do this with a list comprehension after zipping the lists.

pd.DataFrame(
  [{'fruit':F, 'users':U} for (f, u) in zip(lis1, lis2) for F in f for U in u]
)

The below code produces the following output:

      fruit    users
0    apples     john
1   bananas    stacy
2   oranges    stacy
3  cinnamon    stacy
4     pears      ron
5     juice      ron

answered Sep 19, 2018 at 22:08

cpander

3742 silver badges10 bronze badges

1 Comment

D500 Over a year ago

This works only because I have access to lis1/lis2 in the example. For my dataset, I'm given a dataframe with a column variable "fruit" and "user". The rows are populated with lists like the above example. Would lis1 essentially be: df['fruit] ? -- which makes it a series, do they work like a list?

sacuL · Accepted Answer · 2018-09-19 22:03:52Z

1

Here is a solution with lots of stacking and unstacking:

Starting with:

>>> df
                         fruits    users
0                      [apples]   [john]
1  [bananas, oranges, cinnamon]  [stacy]
2                [pears, juice]    [ron]

Use:

final = (df.stack().apply(pd.Series)
         .stack(0).unstack(1)
         .ffill()
         .reset_index(drop=True))

>>> final
     fruits  users
0    apples   john
1   bananas  stacy
2   oranges  stacy
3  cinnamon  stacy
4     pears    ron
5     juice    ron

answered Sep 19, 2018 at 22:03

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Collectives™ on Stack Overflow

removing lists from dataframes while adding data

3 Answers 3

`itertools`

`generator`

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

itertools

generator

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`itertools`

`generator`