unnest (explode) multiple list 2.0

Question

Background

The following code is modified from here Efficient way to unnest (explode) multiple list columns in a pandas DataFrame

I create a dataframe

import pandas as pd
df = pd.DataFrame({'App': ['x1','x2','x3', 'x4'], 'Ban':['v1','v2','v3','v4'], 'C':[['c2'],['c3','c4'],['c5','c6'],['c7','c8']],'D':[['d1','d2'],['d3','d4'],['d5','d6'],['d7','d8']], 'E':[['e1','e2'],['e3','e4'],['e5','e6'],['e7','e8']]})
df

which looks like the following

   App Ban    C             D         E
0   x1  v1  [c2]        [d1, d2]    [e1, e2]
1   x2  v2  [c3, c4]    [d3, d4]    [e3, e4]
2   x3  v3  [c5, c6]    [d5, d6]    [e5, e6]
3   x4  v4  [c7, c8]    [d7, d8]    [e7, e8]

I then use the following code

(df.set_index('Ban')
              .apply(lambda x: x.apply(pd.Series).stack())
              .reset_index()
              .drop('level_1', 1))

Which creates the following

    Ban App C   D   E
0   v1  x1  c2  d1  e1
1   v1  NaN NaN d2  e2
2   v2  x2  c3  d3  e3
3   v2  NaN c4  d4  e4
4   v3  x3  c5  d5  e5
5   v3  NaN c6  d6  e6
6   v4  x4  c7  d7  e7
7   v4  NaN c8  d8  e8

Desired Output

This is close to what I want. But my desired output is this (no NaN but instead having the appropriate App and C name:

    Ban App C   D   E
0   v1  x1  c2  d1  e1
1   v1  x1  c2  d2  e2
2   v2  x2  c3  d3  e3
3   v2  x2  c4  d4  e4
4   v3  x3  c5  d5  e5
5   v3  x3  c6  d6  e6
6   v4  x4  c7  d7  e7
7   v4  x4  c8  d8  e8

Question

How do I get my desired output?

score 0 · Accepted Answer · answered Jun 01 '19 at 22:21

0

Just fix your output by adding ffill

df.set_index('Ban').apply(lambda x: x.apply(pd.Series).stack()).groupby(level=0).ffill().reset_index(drop=True)
Out[794]: 
  Ban App   C   D   E
0  v1  x1  c2  d1  e1
1  v1  x1  c2  d2  e2
2  v2  x2  c3  d3  e3
3  v2  x2  c4  d4  e4
4  v3  x3  c5  d5  e5
5  v3  x3  c6  d6  e6
6  v4  x4  c7  d7  e7
7  v4  x4  c8  d8  e8

answered Jun 01 '19 at 22:21

BENY

317,841
20
164
234

using your code I don't get your output. I get your output except for the column `Ban` – SFC Jun 01 '19 at 22:32
@ER_18 did you assign it back ? – BENY Jun 01 '19 at 22:45
what do you mean by "assign it back?" I'm not sure. I just copied and paste your code – SFC Jun 01 '19 at 22:51
@ER_18 df=df.set_index('Ban').apply(lambda x: x.apply(pd.Series).stack()).groupby(level=0).ffill().reset_index(drop=True) – BENY Jun 01 '19 at 22:54
I copy and pasted your code and I still don't get `ban` column – SFC Jun 02 '19 at 00:21
@ER_18 then just reset_index without drop=True – BENY Jun 02 '19 at 00:28
@ER_18 like `df.set_index('Ban').apply(lambda x: x.apply(pd.Series).stack()).ffill().reset_index().drop('level_1', 1) ` – BENY Jun 02 '19 at 00:36

unnest (explode) multiple list 2.0

1 Answers1

Linked