Explode column of lists into multiple columns

Question

I have a pandas series that contains an array for each element, like so:

0            [0, 0]
1          [12, 15]
2          [43, 45]
3           [9, 10]
4            [0, 0]
5            [3, 3]
6            [0, 0]
7            [0, 0]
8            [0, 0]
9            [3, 3]
10           [2, 2]

I want to extract all the first elements, put them in another Series or list and do the same for the second element. I've tried doing regular expression:

mySeries.str.extract(r'\[(\d+), (\d+)\]', expand=True)

and also splitting:

mySeries.str.split(', ').tolist())

both give nan values. What am I doing wrong?

cs95 · Accepted Answer · 2018-05-10 03:07:45Z

4

Case 1
Column of lists
You will need to .tolist that column and load it into a DataFrame.

pd.DataFrame(df['col'].tolist())

df
         col
0     [0, 0]
1   [12, 15]
2   [43, 15]
3    [9, 10]
4     [0, 0]
5     [3, 3]
6     [0, 0]
7     [0, 0]
8     [0, 0]
9     [3, 3]
10    [2, 2]

pd.DataFrame(df['col'].tolist())

     0   1
0    0   0
1   12  15
2   43  15
3    9  10
4    0   0
5    3   3
6    0   0
7    0   0
8    0   0
9    3   3
10   2   2

Note: If your data has NaNs, I'd recommend dropping them first: df = df.dropna() and then proceed as shown above.

Case 2
Column of strings represented as lists

If you have < 100 rows, use:

df['col'] = pd.eval(df['col'])

And then implement case 1. Otherwise, use ast:

import ast
df['col'] = df['col'].apply(ast.literal_eval)

And proceed as before.

edited May 10, 2018 at 3:07

answered May 10, 2018 at 2:43

cs95

406k106 gold badges744 silver badges797 bronze badges

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

Comments

piRSquared · Accepted Answer · 2018-05-10 03:38:53Z

3

zip the elements of df.col

df.assign(**dict(zip('AB', zip(*df.col))))

         col   A   B
0     [0, 0]   0   0
1   [12, 15]  12  15
2   [43, 45]  43  45
3    [9, 10]   9  10
4     [0, 0]   0   0
5     [3, 3]   3   3
6     [0, 0]   0   0
7     [0, 0]   0   0
8     [0, 0]   0   0
9     [3, 3]   3   3
10    [2, 2]   2   2

Or

df['A'], df['B'] = zip(*df.col)
df

         col   A   B
0     [0, 0]   0   0
1   [12, 15]  12  15
2   [43, 45]  43  45
3    [9, 10]   9  10
4     [0, 0]   0   0
5     [3, 3]   3   3
6     [0, 0]   0   0
7     [0, 0]   0   0
8     [0, 0]   0   0
9     [3, 3]   3   3
10    [2, 2]   2   2

edited May 10, 2018 at 3:38

answered May 10, 2018 at 3:32

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

jpp · Accepted Answer · 2018-05-10 02:42:57Z

2

One solution is to use pd.DataFrame.apply with pd.Series. This assumes you have a series of lists, as implied in your question, rather than of strings.

Your logic will not work with a series of lists as these are not represented as strings in pandas.

df = pd.DataFrame({'A': [[1, 2], [3, 4], [5, 6]]})

df[['B', 'C']] = df['A'].apply(pd.Series)

print(df)

        A  B  C
0  [1, 2]  1  2
1  [3, 4]  3  4
2  [5, 6]  5  6

answered May 10, 2018 at 2:42

jpp

166k37 gold badges301 silver badges363 bronze badges

1 Comment

cs95 Over a year ago

One should note that apply + Series is a match made in hell in terms of performance. OP's headache, less skin off my back :p

Collectives™ on Stack Overflow

Explode column of lists into multiple columns

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related