The data is as follows:

COL1    COL2

 12    :402:agsh,hhjd,:45:hghgh,gruru,:12:fgh,ghgh,:22:hhhh
 57    :42:agshhhjd,:57:hghgh,gruru,:120:fghghgh,:12:hhhhhh

I am creating a third column field_info like:

 COL1  COL2                                                    field_info

 12   :402:agsh,hhjd,:45:hghghgruru,:12:fgh,ghgh,:22:hhhh      fgh,ghg
 57   :42:agshhhjd :57:hghgh,gruru:120:fghghgh :12:hhhhhh    hghgh,g

I am using a regex function as follows:

df.loc[:,'field_info']=df.col2.replace(regex=r'.*'+ df.col1.astype('str') +':(.{15}).*',value="\\1")

I have 2 columns col1 & col2. col1 has some value which I am searching in col2 dynamically and extracting the next 15 characters from that. However, it's taking a lot of time. Can anyone suggest a faster way of doing this?

  • $\begingroup$ Would you have a python notebook or google collab so we can play with your code ? $\endgroup$ Commented May 29, 2019 at 8:09
  • $\begingroup$ notebook or .python file $\endgroup$
    – sayan sen
    Commented May 29, 2019 at 8:11
  • $\begingroup$ As you prefer as long as it makes it possible to reproduce your code $\endgroup$ Commented May 29, 2019 at 8:12
  • $\begingroup$ Actually my code is stuck at this part for over 45 mins while i am working with just 150000 records. Any faster way of improving the regex such that it takes less time? $\endgroup$
    – sayan sen
    Commented May 29, 2019 at 8:13
  • $\begingroup$ You should try it with less records (10 - 100) for example. The points I would check are: - does it make sense to have a dataframe column in the regex. - could you precompile your regex $\endgroup$ Commented May 29, 2019 at 8:16

2 Answers 2

string = ':402:agsh,hhjd,:45:hghghgruru,:12:fgh,ghgh,:22:hhhh'
place = string.find('12')
def extract_substring(string, num):
    starting_point = place + len('12')
    return string[starting_point:(starting_point + 15)]
df.apply(lambda row:extract_substring(row['col2'], row['col1']), axis=1)
%timeit df.loc[:,'field_data']=df.col2.replace(regex=r'.*'+ df.col1.astype('str') +':(.{15}).*',value="\\1")

Should work as well, and doesn't use regexp


Based on your sample data, I replicated it 50000 times and the result is as follows-

>>> df = pd.DataFrame({'COL1':[12 ,57],'COL2': [':402:agsh,hhjd,:45:hghgh,gruru,:12:fgh,ghgh,:22:hhhh',':42:agshhhjd,:57:hghgh,gruru,:120:fghghgh,:12:hhhhhh']})

>>> for _ in range(50000):
        df = df.append({'COL1':12,'COL2': ':402:agsh,hhjd,:45:hghgh,gruru,:12:fgh,ghgh,:22:hhhh'}, ignore_index = True)
        df = df.append({'COL1':57,'COL2': ':42:agshhhjd,:57:hghgh,gruru,:120:fghghgh,:12:hhhhhh'}, ignore_index = True)

>>> df.shape 
(100002, 2)

Then I defined a custom function and applied to the columns-

>>> def somefunc(x,y):
        res = []
        for i in range(len(x)):
            ix = y[i].find(x[i]) + len(x[i])
        return res

>>> df['col3'] = somefunc(df['COL1'].astype(str),df['COL2'])
>>> df.head()
    COL1                                   COL2             col3 
0    12  :402:agsh,hhjd,:45:hghgh,gruru,:12:fgh,ghgh,:2...  fgh,ghg
1    57  :42:agshhhjd,:57:hghgh,gruru,:120:fghghgh,:12:...  hghgh,g
2    12  :402:agsh,hhjd,:45:hghgh,gruru,:12:fgh,ghgh,:2...  fgh,ghg
3    57  :42:agshhhjd,:57:hghgh,gruru,:120:fghghgh,:12:...  hghgh,g

I did not use regex and this function took nearly 5 seconds to complete on 100000 rows.

  • $\begingroup$ Its giving me key error for the APPEND line $\endgroup$
    – sayan sen
    Commented May 29, 2019 at 14:06
  • $\begingroup$ Can you post the full error and the particular data in which you are getting error? $\endgroup$
    – Ankit Seth
    Commented May 30, 2019 at 6:06
  • $\begingroup$ line 47, in somefunc ix = y[i].find(x[i]) + len(x[i]) line 767, in getitem result = self.index.get_value(self, key) base.py", line 3118, in get_value tz=getattr(series.dtype, 'tz', None)) File "pandas_libs\index.pyx", line 106, in pandas._libs.index.IndexEngine.get_value $\endgroup$
    – sayan sen
    Commented May 30, 2019 at 12:55
  • $\begingroup$ File "pandas_libs\index.pyx", line 114, in pandas._libs.index.IndexEngine.get_value File "pandas_libs\index.pyx", line 162, in pandas._libs.index.IndexEngine.get_loc File "pandas_libs\hashtable_class_helper.pxi", line 958, in pandas._libs.hashtable.Int64HashTable.get_item File "pandas_libs\hashtable_class_helper.pxi", line 964, in pandas._libs.hashtable.Int64HashTable.get_item KeyError: 14 $\endgroup$
    – sayan sen
    Commented May 30, 2019 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.