Split pandas string column based on varying length separator

user_4123749 0
Viewed 2588

I have a df.LOCATION column which contains text strings. Each string includes text reflecting origin and destination addresses. My goal is to split origin and destinations so I can create two separate columns.

Most of the times, the origin and destination text are separated by a string pattern "to" e.g. "1234 A St. to 9876 B St.". I have used 

map(lambda x: re.split(' to ', x), df.LOCATION)

This works great, except in some rows I have more "to" in the text e.g. "From 1234 A St. need to pick up truck to 9876 B St." - in a case like this, I would still like to split into two strings for origin and destination but my code above will return three lists because of the extra ' to ' string I highlighted in bold.

So to address this, I have implemented 

map(lambda x: re.split(' to \d+', x), dfJobs.LOCATION))

This correctly searches for strings where I have not only the text 'to' but also any digits which indicates there is a new address following the it i.e. the destination. This is working, except it will actually remove those initial digits in the destination address as well, but I would like to keep them.

In other words, I would like to detect patterns such as the above, and when they are found, split based only on the 'to' portion of the pattern.

pandas python-3.x python split regex
3 Answers
df = pd.DataFrame(
    {'Location' : ['1234 A St. to 9876 B St.', 
                   'From 1234 A St. to pick up truck to 9876 B St.']})    
df
                                         Location
0                        1234 A St. to 9876 B St.
1  From 1234 A St. to pick up truck to 9876 B St.

You can start matching from "to" upto the first digit using r' to\D+'. To vectorise, use str.split.

v = df.Location.str.split(r' to\D+', expand=True)
v.columns = ['source', 'destination'] 

df.join(v)
                                         Location           source destination
0                        1234 A St. to 9876 B St.       1234 A St.  9876 B St.
1  From 1234 A St. to pick up truck to 9876 B St.  From 1234 A St.  9876 B St.
user_49090870

You can use rsplit and use n = 1 to return only one split

df[['source', 'destination']] = df.Location.str.rsplit('to', 1, expand = True)

    Location                                        source                              destination
0   1234 A St. to 9876 B St.                        1234 A St.                          9876 B St.
1   From 1234 A St. to pick up truck to 9876 B St.  From 1234 A St. to pick up truck    9876 B St.
user_62873080

This sounds like a problem for lookahead. Lookahead allows you to match subsequent characters, but not consume them.

>>> re.split(' to (?=\d)', 'From 1234 A St. need to pick up truck to 9876 B St')
['From 1234 A St. need to pick up truck', '9876 B St']
user_10764800
Related