slicing pandas column using values from another column

user_5221163 0
Viewed 330

So I have a dataframe with some text in one column. I'm trying to find 2 strings within each row of the column, and then slice the row text between those two strings to get a substring. Something like this:

startinds = df[column].str.find("First Event = ")
endinds   = df[column].str.find("\nLast Event = ")

df["first_timestamp"] = df[column].str.slice(startinds,endinds)

Now this doesn't work because startinds and endinds are series, so I can't use them as indices for slicing the strings in column.

Anyone know a way I can access the values to do the substrings on each row?

Example Input:

    Data
0   "Blahblah
     First Event = 09/20/2017 12:00:00
     Last Event = 09/20/2017 13:00:00
     Blahblahblah"
1   "Blahblahblahblah
     Blahablahblah
     First Event = 09/20/2017 12:30:00
     Last Event = 09/20/2017 12:45:00
     Blahblahblah"

Output:

    first_timestamp
0   "First Event = 09/20/2017 12:00:00"
1   "First Event = 09/20/2017 12:30:00"
pandas python-2.7 substring python
2 Answers

To complete your slicing method you can use lambda i.e store the startinds and endinds in df and then slice the string based on columns using lambda across column i.e (note you need an escape character to get the \n)

df['startinds'] = df['Data'].str.find("First Event = ")
df['endinds']  = df['Data'].str.find("\\nLast Event = ")

df.apply(lambda x : str(x['Data'])[x['startinds']:x['endinds']],1 )

Output:

0    First Event = 09/20/2017 12:00:00
1    First Event = 09/20/2017 12:30:00
dtype: object
user_48006520

Not unlike the answer in the comments, this approach with Series.str.extract should work:

df['first_timestamp'] = df['Data'].str.extract('(First Event = .+)')

#                                                 Data  \
# 0  Blahblah\nFirst Event = 09/20/2017 12:00:00\nL...   
# 1  Blahblahblahblah\nFirst Event = 09/20/2017 12:...   
# 
#                      first_timestamp  
# 0  First Event = 09/20/2017 12:00:00  
# 1  First Event = 09/20/2017 12:30:00

The pattern '(First Event = .+)' captures a group (i.e. ()) with "First Event = " followed by one or more characters (i.e. .+), stopping at a newline (the . character matches anything except a newline).

user_52410320
Related