Python - Regex split data in Dataframe

Question

I have a column containing values. I want to split it based on a regex. If the regex matches, the original value will be replaced with the left-side of the split. A new column will contain the right-side of a split.

Below is some sample code. I feel I am close but it isn't quite working.

import pandas as pd
import re

df = pd.DataFrame({ 'A' : ["test123","foo"]})

// Regex example to split it if it ends in numbers
r = r"^(.+?)(\d*)$"

df['A'], df['B'] = zip(*df['A'].apply(lambda x: x.split(r, 1)))
print(df)

In the example above I would expect the following output

         A        B
0     test      123
1     foo

I am fairly new to Python and assumed this would be the way to go. However, it appears that I haven't quite hit the mark. Is anyone able to help me correct this example?

BENY · Accepted Answer · 2018-01-05 19:43:18Z

3

Just base on your own regex

df.A.str.split(r,expand=True).replace('',np.nan).dropna(thresh=1,axis=1).fillna('')
Out[158]: 
      1    2
0  test  123
1   foo     


df[['A','B']]=df.A.str.split(r,expand=True).replace('',np.nan).dropna(thresh=1,axis=1).fillna('')
df
Out[160]: 
      A    B
0  test  123
1   foo

edited Jan 5, 2018 at 19:43

answered Jan 5, 2018 at 19:23

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

ekjcfn3902039 Over a year ago

Maybe I'm missing something but how do I add this to column A and column B? I see the example output you provided is column 1 and 2. If I do df['A'], df['B'] = yourCode I get values of 1 and 2 instead of what's expected

BENY Over a year ago

@ekjcfn3902039 updated ...You just need to assign it back

Vaishali · Accepted Answer · 2018-01-05 19:34:02Z

2

Your regex is working just fine, use it with str.extract

df = pd.DataFrame({ 'A' : ["test123","foo", "12test3"]})
df[['A', 'B']] = df['A'].str.extract("^(.+?)(\d*)$", expand = True)


    A       B
0   test    123
1   foo 
2   12test  3

answered Jan 5, 2018 at 19:34

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Comments

aiven · Accepted Answer · 2018-01-05 19:26:29Z

0

def bar(x):
    els = re.findall(r'^(.+?)(\d*)$', x)[0]
    if len(els):
        return els
    else:
        return x, None


def foo():
    df = pd.DataFrame({'A': ["test123", "foo"]})
    df['A'], df['B'] = zip(*df['A'].apply(bar))
    print(df)

result:

      A    B
0  test  123
1   foo

answered Jan 5, 2018 at 19:26

aiven

4,3624 gold badges35 silver badges60 bronze badges

Collectives™ on Stack Overflow

Python - Regex split data in Dataframe

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related