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$ May 29 '19 at 8:09
  • $\begingroup$ notebook or .python file $\endgroup$
    – sayan sen
    May 29 '19 at 8:11
  • $\begingroup$ As you prefer as long as it makes it possible to reproduce your code $\endgroup$ May 29 '19 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
    May 29 '19 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$ May 29 '19 at 8:16

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
    May 29 '19 at 14:06
  • $\begingroup$ Can you post the full error and the particular data in which you are getting error? $\endgroup$
    – Ankit Seth
    May 30 '19 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
    May 30 '19 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
    May 30 '19 at 12:56
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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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