Cythonizing string array comparison function to be applied to pandas DataFrame

Question

I am just getting started with Cython and would appreciate some pointers as to how to approach this process. I have identified a speed bottleneck in my code and would like to optimize the performance of a specific operation.

I have a pandas DataFrame trades that looks like this:

                              Codes    Price  Size
Time
2015-02-24 15:30:01-05:00     R6,IS  11.6100   100
2015-02-24 15:30:01-05:00     R6,IS  11.6100   100
2015-02-24 15:30:01-05:00     R6,IS  11.6100   100
2015-02-24 15:30:01-05:00            11.6100   375
2015-02-24 15:30:01-05:00     R6,IS  11.6100   100
...                             ...      ...   ...
2015-02-24 15:59:55-05:00     R6,IS  11.5850   100
2015-02-24 15:59:55-05:00     R6,IS  11.5800   200
2015-02-24 15:59:55-05:00         T  11.5850   100
2015-02-24 15:59:56-05:00     R6,IS  11.5800   175
2015-02-24 15:59:56-05:00     R6,IS  11.5800   225

[5187 rows x 3 columns]

I have a numpy array called codes:

array(['4', 'AP', 'CM', 'BP', 'FA', 'FI', 'NC', 'ND', 'NI', 'NO', 'PT',
       'PV', 'PX', 'SD', 'WO'],
      dtype='|S2')

I need to filter trades such that if any of the values in codes is included in trades['Codes'] that row is excluded. Currently I am doing this:

ix = trades.Codes.str.split(',').apply(lambda cs: not any(c in excludes for c in cs))
trades = trades[ix]

However, this is too slow and I need to make it faster. I want to use cython (as described here or maybe numba, it seems like cython is the better option.

I basically need a function like this:

def isinCodes(codes_array1, codes_array2):

    for x in codes_array1:
        for y in codes_array2:
            if x == y: return True

    return False

What types do I need to use when cythonizing?

Your algorithm is quadratic, cython is not going to help that much — Padraic Cunningham
– Padraic Cunningham, Commented Jun 4, 2015 at 16:31

Jeff · Accepted Answer · 2015-06-04 16:49:02Z

This is easily vectorizable.

Construct a frame, I took 100000 * your example, 1M rows.

In [76]: df2.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1000000 entries, 0 to 9
Data columns (total 4 columns):
date      1000000 non-null datetime64[ns]
code      900000 non-null object
price     1000000 non-null float64
volume    1000000 non-null int64
dtypes: datetime64[ns](1), float64(1), int64(1), object(1)
memory usage: 38.1+ MB

In [77]: df2.head()   
Out[77]: 
                 date   code  price  volume
0 2015-02-24 20:30:01  R6,IS  11.61     100
1 2015-02-24 20:30:01  R6,IS  11.61     100
2 2015-02-24 20:30:01  R6,IS  11.61     100
3 2015-02-24 20:30:01    NaN  11.61     375
4 2015-02-24 20:30:01  R6,IS  11.61     100

This code would actually be: df2.code.str.split(',',expand=True), but there is a perf issue ATM, going to be fixed for 0.16.2, see here. So this code does this splitting in a very performant way.

In [78]: result = DataFrame([ [ s ] if not isinstance(s, list) else s for s in df2.code.str.split(',') ],columns=['A','B'])

In [79]: %timeit DataFrame([ [ s ] if not isinstance(s, list) else s for s in df2.code.str.split(',') ],columns=['A','B'])
1 loops, best of 3: 941 ms per loop

In [80]: result.head()
Out[80]: 
     A     B
0   R6    IS
1   R6    IS
2   R6    IS
3  NaN  None
4   R6    IS

I added 'T' to the end of the isin

In [81]: isin                     
Out[81]: 
['4',
 'AP',
 'CM',
 'BP',
 'FA',
 'FI',
 'NC',
 'ND',
 'NI',
 'NO',
 'PT',
 'PV',
 'PX',
 'SD',
 'WO',
 'T']

Results

In [82]: df2[(result.A.isin(isin) | result.A.isin(isin))].info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 100000 entries, 7 to 7
Data columns (total 4 columns):
date      100000 non-null datetime64[ns]
code      100000 non-null object
price     100000 non-null float64
volume    100000 non-null int64
dtypes: datetime64[ns](1), float64(1), int64(1), object(1)
memory usage: 3.8+ MB

In [83]: df2[(result.A.isin(isin) | result.A.isin(isin))].head()
Out[83]: 
                 date code   price  volume
7 2015-02-24 20:59:55    T  11.585     100
7 2015-02-24 20:59:55    T  11.585     100
7 2015-02-24 20:59:55    T  11.585     100
7 2015-02-24 20:59:55    T  11.585     100
7 2015-02-24 20:59:55    T  11.585     100

The actual operation is much faster than the splitting to get here.

In [84]: %timeit df2[(result.A.isin(isin) | result.A.isin(isin))]       
10 loops, best of 3: 106 ms per loop

Collectives™ on Stack Overflow

Cythonizing string array comparison function to be applied to pandas DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related