Pandas Split lists into multiple rows

Question

I have a Dataframe like this

pd.DataFrame([(1,'a','i',[1,2,3],['a','b','c']),(2,'b','i',[4,5],['d','e','f']),(3,'a','j',[7,8,9],['g','h'])])

Output:

    0   1   2   3           4
0   1   a   i   [1, 2, 3]   [a, b, c]
1   2   b   i   [4, 5]      [d, e, f]
2   3   a   j   [7, 8, 9]   [g, h]

I want to explode columns 3,4 matching their indices and preserving the rest of the columns like this. I go through this question but the answer is to create a new dataframe and defining all columns again which is memory inefficient (I have 18L rows and 19 columns)

    0  1  2  3   4
0   1  a  i  1   a
1   1  a  i  2   b
2   1  a  i  3   c
3   2  b  i  4   d
4   2  b  i  5   e
5   2  b  i  NaN f
6   3  c  j  7   g
7   3  c  j  8   h
8   3  c  j  9   NaN

Update: Forgot to mention for missing indices it should be NaN for other

Andrej Kesely · Accepted Answer · 2021-05-23T09:37:43.013

Another solution:

df_out = df.explode(3)
df_out[4] = df[4].explode()
print(df_out)

Prints:

   0  1  2  3  4
0  1  a  i  1  a
0  1  a  i  2  b
0  1  a  i  3  c
1  2  b  i  4  d
1  2  b  i  5  e
1  2  b  i  6  f
2  3  a  j  7  g
2  3  a  j  8  h

EDIT: To handle uneven cases:

df = pd.DataFrame(
    [
        (1, "a", "i", [1, 2, 3], ["a", "b", "c"]),
        (2, "b", "i", [4, 5], ["d", "e", "f"]),
        (3, "a", "j", [7, 8, 9], ["g", "h"]),
    ]
)


def fn(x):
    if len(x[3]) < len(x[4]):
        x[3].extend([np.nan] * (len(x[4]) - len(x[3])))
    elif len(x[3]) > len(x[4]):
        x[4].extend([np.nan] * (len(x[3]) - len(x[4])))
    return x


# "even-out" the lists:
df = df.apply(fn, axis=1)

# explode them:
df_out = df.explode(3)
df_out[4] = df[4].explode()
print(df_out)

Prints:

   0  1  2    3    4
0  1  a  i    1    a
0  1  a  i    2    b
0  1  a  i    3    c
1  2  b  i    4    d
1  2  b  i    5    e
1  2  b  i  NaN    f
2  3  a  j    7    g
2  3  a  j    8    h
2  3  a  j    9  NaN

Hey @Andrej thanks for the answer but a little update in question, forgot to mention how to handle uneven cases — Pawan Jain, May 23 '21 at 09:28

score 1 · Answer 2 · answered May 23 '21 at 09:13

1

You can use pd.Series.explode:

df = df.apply(pd.Series.explode).reset_index(drop=True)

output:

   0  1  2  3  4
0  1  a  i  1  a
1  1  a  i  2  b
2  1  a  i  3  c
3  2  b  i  4  d
4  2  b  i  5  e
5  2  b  i  6  f
6  3  a  j  7  g
7  3  a  j  8  h

answered May 23 '21 at 09:13

Nk03

14,699
2
8
22

Hey thanks for the answer but a little update in question, forgot to mention how to handle uneven cases – Pawan Jain May 23 '21 at 09:25

Pandas Split lists into multiple rows

2 Answers2