How to Covert multiple list columns in data frame into given one?

Question

I have Dataframe like this

     Number      String            Aut
  0 [12, 13]    [hi are, ho to]    ppppp
  1   34         How               qqqqq
  2   35         are               wwwwwww

i want to convert this into this

         Number      String            Aut
  0   12          hi are            ppppp
  1   13          ho to             ppppp
  2   34          How               qqqqq
  3   35          are               wwwwwww

i tried this but not working ref

res = df.set_index(['Aut'])['Number', 'String'].apply(pd.Series).stack()

Help will be appreciated .

score 0 · Accepted Answer · answered Jan 07 '19 at 08:34

There is mix list with scalars, so first need some pre processing and then create DataFrame by chain with repeat:

n = [x if isinstance(x, list) else [x] for x in df['Number']]
s = [x if isinstance(x, list) else [x] for x in df['String']]
lens = [len(x) for x in n]

from itertools import chain

df = pd.DataFrame({
    'Number' : list(chain.from_iterable(n)), 
    'String' : list(chain.from_iterable(s)), 
    'Aut' : df['Aut'].values.repeat(lens)
})
print (df)
   Number  String      Aut
0      12  hi are    ppppp
1      13   ho to    ppppp
2      34     How    qqqqq
3      35     are  wwwwwww

score 0 · Answer 2 · answered Jan 07 '19 at 08:38

This can be done in two steps:

step1_df=df.set_index('Aut').String.apply(pd.Series).stack().reset_index(level=0).rename(columns={0:'String'}) 

step2_df=df.set_index('Aut').Number.apply(pd.Series).stack().reset_index(level=0).rename(columns={0:'Number'})

Merge two df:

 final=pd.merge(step1_df,step2_df,on="Aut")

How to Covert multiple list columns in data frame into given one?

2 Answers2