pandas: When cell contents are lists/ NaN/ string, create a row for each element

Question

Hi I have a df which similar below

index a  b  c  d
0     xx aa av NaN
1     pp as ka [1,2,3,4]
2     pa aj q  1234
3     xq aq aq NaN
4     pn an kn [10,20,30,40]
5     px ax kx "00012"

I would like to convert into some like below

index a  b  c  d              d-separated
0     xx aa av NaN            NaN
1     pp as ka [1,2,3,4]      1
2     pp as ka [1,2,3,4]      2
3     pp as ka [1,2,3,4]      3
4     pp as ka [1,2,3,4]      4
5     pa aj q  1234           1234
6     xq aq aq NaN            NaN
7     pn an kn [10,20,30,40]  10
8     pn an kn [10,20,30,40]  20
9     pn an kn [10,20,30,40]  30
10    pn an kn [10,20,30,40]  40
11    px ax kx "00012"        "00012"

I have take reference from

pandas: When cell contents are lists, create a row for each element in the list and

Split (explode) pandas dataframe string entry to separate rows

However, as my case is different from them. The solution won't work in my example. Thank you for your help

user3483203 · Answer 1 · 2018-07-19T05:07:31.520

Setup

df = pd.DataFrame({'a': ['xx', 'pp', 'pa', 'xq', 'pn', 'px'], 'b': ['aa', 'as', 'aj', 'aq', 'an', 'ax'], 'c': ['av', 'ka', 'q', 'aq', 'kn', 'kx'], 'd': [np.nan, [1,2,3,4], 1234, np.nan, [10, 20, 30, 40], '00012']})

This was a tricky one, mostly because of the NaN's, so I replaced them with a filler value first, then changed them back at the end:

(df.join(df.fillna(-999)
    .d.apply(pd.Series))
    .drop('d', 1).set_index(['a', 'b', 'c'])
    .stack().reset_index()
    .drop('level_3',1)
    .replace(-999, np.nan).rename(columns={0: 'd-separated'})
)

     a   b   c d-separated
0   xx  aa  av         NaN
1   pp  as  ka           1
2   pp  as  ka           2
3   pp  as  ka           3
4   pp  as  ka           4
5   pa  aj   q        1234
6   xq  aq  aq         NaN
7   pn  an  kn          10
8   pn  an  kn          20
9   pn  an  kn          30
10  pn  an  kn          40
11  px  ax  kx       00012

This does however lose the original d column, since it contains unhashable types so it cannot be set as a level of the index.

jezrael · Answer 2 · 2018-07-19T05:27:14.500

It is possible, but not trivial - for column for index id necessary convert lists to tuples for hashable types and for DataFrame from constructor scalar to one element lists:

df = pd.DataFrame({'a': ['xx', 'pp', 'pa', 'xq', 'pn', 'px'], 
                   'b': ['aa', 'as', 'aj', 'aq', 'an', 'ax'], 
                   'c': ['av', 'ka', 'q', 'aq', 'kn', 'kx'], 
                   'd': [np.nan, [1,2,3,4], '1234', np.nan, [10, 20, 30, 40], '00012']})


s = (df.assign(d1=df['d'].fillna('NANval').apply(lambda x: x if isinstance(x, list) else [x]),
               d = df['d'].apply(lambda x: tuple(x) if isinstance(x, list) else x))
       .set_index(['a','b','c','d'])['d1']
       )
print (s)
a   b   c   d               
xx  aa  av  NaN                         [NANval]
pp  as  ka  (1, 2, 3, 4)            [1, 2, 3, 4]
pa  aj  q   1234                          [1234]
xq  aq  aq  NaN                         [NANval]
pn  an  kn  (10, 20, 30, 40)    [10, 20, 30, 40]
px  ax  kx  00012                        [00012]
Name: d1, dtype: object

df = (pd.DataFrame(s.values.tolist(), index=s.index)
        .stack()
        .reset_index(4, drop=True)
        .reset_index(name='d-separated')
        .replace('NANval', np.nan)
        )

Last if necessary convert tuples to lists:

df['d'] = df['d'].apply(lambda x: list(x) if isinstance(x, tuple) else x)
print (df)

     a   b   c                 d d-separated
0   xx  aa  av               NaN         NaN
1   pp  as  ka      [1, 2, 3, 4]           1
2   pp  as  ka      [1, 2, 3, 4]           2
3   pp  as  ka      [1, 2, 3, 4]           3
4   pp  as  ka      [1, 2, 3, 4]           4
5   pa  aj   q              1234        1234
6   xq  aq  aq               NaN         NaN
7   pn  an  kn  [10, 20, 30, 40]          10
8   pn  an  kn  [10, 20, 30, 40]          20
9   pn  an  kn  [10, 20, 30, 40]          30
10  pn  an  kn  [10, 20, 30, 40]          40
11  px  ax  kx             00012       00012

score 0 · Answer 3 · answered Jul 19 '18 at 06:58

First Expand the dataframe to the required size, repeating each row as necessary:

df1 = df.loc[df.index.repeat([len(x) if isinstance(x,list) else 1 for x in df.d])]

Now unlist the column d and concatenate it with the above df

d_sep= pd.DataFrame({'d_Sep':sum([x if isinstance(x,list) else [x] for x in df.d],[])})

df2 = pd.concat([df1.reset_index(drop=True),d_sep],axis=1)

   a   b   c                 d  d_Sep
0   xx  aa  av               NaN    NaN
1   pp  as  ka      [1, 2, 3, 4]      1
2   pp  as  ka      [1, 2, 3, 4]      2
3   pp  as  ka      [1, 2, 3, 4]      3
4   pp  as  ka      [1, 2, 3, 4]      4
5   pa  aj   q              1234   1234
6   xq  aq  aq               NaN    NaN
7   pn  an  kn  [10, 20, 30, 40]     10
8   pn  an  kn  [10, 20, 30, 40]     20
9   pn  an  kn  [10, 20, 30, 40]     30
10  pn  an  kn  [10, 20, 30, 40]     40
11  px  ax  kx             00012  00012

sorry, but it shows ValueError: operands could not be broadcast together with shape (768329,) (2,) — Platalea Minor, Jul 19 '18 at 07:38

pandas: When cell contents are lists/ NaN/ string, create a row for each element

3 Answers3