Dataframe with a column which every row is a list of values

Question

I have a pandas dataframe like this:

         name     favourite_fruits                     votes
0         A          banana                       [5, 0, 5, 0, 5]
1         B          orange                          [5, 0, 5]
2         C          peach                              [5]

I need to convert "votes" column in:

         name     favourite_fruits                     vote1 vote2 vote3 vote4 vote5
0         A          banana                              5     0     5     0     5
1         B          orange                              5     0     5
2         C          peach                               5

How i can do that? Thanks in advance for your answers.

Hi, so you are trying to make a column into 5 columns, depending on the max length of the list? — , May 24 '22 at 16:33
Does this answer your question? [Pandas: split column of lists of unequal length into multiple columns](https://stackoverflow.com/questions/44663903/pandas-split-column-of-lists-of-unequal-length-into-multiple-columns) — Rabinzel, May 24 '22 at 16:36

score 1 · Answer 1 · answered May 24 '22 at 16:43

Try:

#explode to split list into individual rows
df = df.explode("votes")

#groupby and cumcount to get the total votes per index
df["column"] = df.groupby(level=0).cumcount().add(1)

#pivot to get the expected output
output = df.pivot(["name","favourite_fruits"],"column","votes").add_prefix("vote").rename_axis(None,axis=1).reset_index()

>>> output
  name favourite_fruits vote1 vote2 vote3 vote4 vote5
0    A           banana     5     0     5     0     5
1    B           orange     5     0     5   NaN   NaN
2    C            peach     5   NaN   NaN   NaN   NaN

Timus · Accepted Answer · 2022-05-25T11:12:28.130

1

Here's an alternative approach:

result = pd.concat(
    [df[["name", "favourite_fruits"]],
     pd.DataFrame(lst for lst in df["votes"]).rename(columns=lambda n: f"vote{n + 1}")],
    axis=1
)

edited May 25 '22 at 11:12

answered May 24 '22 at 21:45

Timus

10,974
5
14
28

1

I really enjoy your one code line solution but i would add some fix. I've added .astype('Int64) in order to have integers. But i need to have columns which starts from 1 like (vote1, vote2, etc) and not (vote0, vote1, vote2, etc.) – Mario Maria Mario May 25 '22 at 11:03
@MarioMariaMario Thanks for the feedback! You're right, I've missed that in the last edit (I've switched to `.add_prefix()` without realizing that it changes the count). I'll rollback to the old version. – Timus May 25 '22 at 11:11

Dataframe with a column which every row is a list of values

2 Answers2