Restructure a dataframe using pandas

Question

I have the following dataframe.

a1 = [1,2,3]
a2 = [[[11,'a'],[12, 'b'],[13,'c']], [[21,'a'],[22, 'a']], [[31, 'b']]]

df = pd.DataFrame({'col1': a1,
                   'col2': a2})

   col1                         col2
0     1  [[11, a], [12, b], [13, c]]
1     2           [[21, a], [22, a]]
2     3                    [[31, b]]

And I want to convert it to this dataframe

c1   c2    c3
1    11    a
1    12    b
1    13    c
2    21    a
2    22    a
3    31    b

Could anyone show me how to do that. Many thanks.

jezrael · Accepted Answer · 2020-09-17 07:22:49Z

1

Use DataFrame.explode for convert nested lists to Series and then create new columns by DataFrame cosntructor:

df = df.explode('col2').rename(columns={'col1':'c1'}).reset_index(drop=True)
df[['c2','c3']] = pd.DataFrame(df.pop('col2').tolist(), index=df.index)
print (df)
   c1  c2 c3
0   1  11  a
1   1  12  b
2   1  13  c
3   2  21  a
4   2  22  a
5   3  31  b

Or use list comprehension with flatten nested lists for tuples:

L = [(v['col1'], x[0], x[1]) for k, v in df.to_dict('index').items() for x in v['col2']]

df = pd.DataFrame(L, columns=['c1','c2','c3'])
print (df)
   c1  c2 c3
0   1  11  a
1   1  12  b
2   1  13  c
3   2  21  a
4   2  22  a
5   3  31  b

edited Sep 17, 2020 at 7:22

answered Sep 17, 2020 at 7:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Restructure a dataframe using pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related