Pandas split into columns with regex

Question

I have something like this I have a column in DataFrame like this

Column1
message1 message2 notmessage
message1 message2 
message1 message2 message3 notmessage

I want a data frame, like:

Column1                                 |  A        | b        | c
message1 message2 notmessage            |  message1 | message2 | null
message1 message2                       |  message1 | message2 | null
message1 message2 message3 notmessage   |  message1 | message2 | message3

There is no problem getting first value from the Column1 using

df['A'] = df['Column1'].str.extract('(my_regex)',expand=True)

But how Can I obtain 3 new Columns? I was trying using this: https://stackoverflow.com/a/39358924 it worked for me when I was using split method in other files, but it doesnt work for me with regex splitting like below:

df.join(df['Column1'].str.extract('(my_regex)',expand=True).rename(columns={0:'A', 1:'B', 2:'C'}))

Please help :)

score 1 · Accepted Answer · answered Jun 19 '20 at 10:36

1

I believe you need Series.str.extractall with select first column 0 and reshape by Series.unstack:

d = {0:'A', 1:'B', 2:'C'}
df = df.join(df['Column1'].str.extractall('(my_regex)')[0].unstack().rename(columns=d))

answered Jun 19 '20 at 10:36

jezrael

822,522
95
1,334
1,252

score 0 · Answer 2 · answered Jun 19 '20 at 10:46

Get all 3 columns:

import pandas as pd

df = pd.DataFrame(["message1 message2 notmessage",
                   "message1 message2",
                   "message1 message2 message3 notmessage"
                   ],
                  columns=["Column1"]
                  )

df['A'] = df['Column1'].str.extract('(^\w+)',
                                    expand=True
                                    )

df['b'] = df['Column1'].str.extract('(?<=\s)(\w+).*',
                                    expand=True
                                    )

df['c'] = df['Column1'].str.extract('(\w+3).*',
                                    expand=True
                                    )

print(df)

Result:

                                 Column1         A         b         c
0           message1 message2 notmessage  message1  message2       NaN
1                      message1 message2  message1  message2       NaN
2  message1 message2 message3 notmessage  message1  message2  message3

Pandas split into columns with regex

2 Answers2