python pandas extract column of array to rows and colums

Question

Hello I have a dataframe with the following structure:

Index   A   B   C   D   E
3    foo    bar 0   1   [{'A': 1, 'B': 'text', 'C': 3}, {'A': 2, 'B': 'other', 'C': 2}]
4    foo    bar 0   1   [{'A': 3, 'B': 'foo', 'C': 4}, {'A': 6, 'B': 'bar', 'C': 8}]

With loc I get the Index and the E column

df2 = df.loc[:, ['E']]

Index   E
3   [{'A': 1, 'B': 'text', 'C': 3}, {'A': 2, 'B': 'other', 'C': 2}]
4   [{'A': 3, 'B': 'foo', 'C': 4}, {'A': 6, 'B': 'bar, 'C': 8}]

But what I need is this structure

Index   A   B   C
3   1   text    3
3   2   other   2
4   3   foo 4
4   6   bar 8

I think that iterating over the rows, extracting the array and creating another df for each will work but I hope that more efficient solution can be found.

Thanks in advance.

Do you need to preserve the index?

Corralien
– Corralien

2022-02-19 20:15:10 +00:00
Commented Feb 19, 2022 at 20:15 — Corralien
– Corralien, Commented Feb 19, 2022 at 20:15

Corralien · Accepted Answer · 2022-02-19 20:18:32Z

1

You can use explode to flatten your column then create a dataframe:

out = pd.DataFrame(df['E'].explode().tolist())
print(out)

# Output
   A      B  C
0  1   text  3
1  2  other  2
2  3    foo  4
3  6    bar  8

To preserve the index:

out = df['E'].explode()
out = pd.DataFrame(out.tolist(), index=out.index)
print(out)

# Output
   A      B  C
3  1   text  3
3  2  other  2
4  3    foo  4
4  6    bar  8

edited Feb 19, 2022 at 20:18

answered Feb 19, 2022 at 20:12

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

2 Comments

Victor Bayona Over a year ago

It works! At least pandas 0.25.0 is needed to work . Once updated it works fine. Thank you!

Victor Bayona Over a year ago

When you need to preserve the index, thats my case, after explode(), the null values need to be removed. If not, the Dataframe creation crash.

Collectives™ on Stack Overflow

python pandas extract column of array to rows and colums

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related