2

I have a df where some values are added to the same row like this fake df: 

    [['Apple, Kiwi, Clementine', np.nan , 'Cycling', 5], 
     ['Kiwi', 'Blue',  np.nan , 20], 
     ['Banana, Clementine',  np.nan , 'Hockey', 12], 
     ['Apple', 'Purple', 'Triathlon', 15], 
     ['Kiwi',  np.nan, 'Swimming', 8]]), 
                     columns=['fruit', 'colour', 'sport', 'wins'])

What I would like is to duplicate the rows with multiple fruits while splitting the first entry to contain only one fruit. enter image description here In the end I want to plot the average wins per fruit. So if there is a way of grouping where each fruit mentioned is grouped with the correct fruit so to speak that would also work.

I have tried some string manipulation but then it is simply split up and the values in the other columns not duplicated. It is quite frustrating since I know how to do it in r but am a beginner in python.

Mactilda
  • 393
  • 6
  • 18

1 Answers1

3

Use @Wen-Ben's solution from here:

s=pd.DataFrame([[x] + [z] for x, y in zip(df.index,df.fruit.str.split(',')) for z in y],
               columns=[0,'Fruit'])
df_new=s.merge(df,left_on=0,right_index=True).drop(0,1)
print(df_new)

         Fruit                    fruit  colour      sport  wins
0        Apple  Apple, Kiwi, Clementine     NaN    Cycling     5
1         Kiwi  Apple, Kiwi, Clementine     NaN    Cycling     5
2   Clementine  Apple, Kiwi, Clementine     NaN    Cycling     5
3         Kiwi                     Kiwi    Blue        NaN    20
4       Banana       Banana, Clementine     NaN     Hockey    12
5   Clementine       Banana, Clementine     NaN     Hockey    12
6        Apple                    Apple  Purple  Triathlon    15
7         Kiwi                     Kiwi     NaN   Swimming     8

Note You can chose to drop the fruit column if you want.

anky
  • 74,114
  • 11
  • 41
  • 70