Slowness for DataFrame.where/mask when dtype=bool

[dalejung]

data = np.random.randn(10000, 500)
df = pd.DataFrame(data)
df = df.where(df > 0) # create nans
bools = df > 0

reg_mask = bools.mask(pd.isnull(df)) # walltime 10s
float_mask = bools.astype(float).mask(pd.isnull(df)) # walltime 200ms
tm.assert_frame_equal(reg_mask, float_mask) # success

I found that while masking boolean DataFrames, converting to float speeds up the process considerably. When you're masking at least one row in each column, the outputs are equivalent.

Note, the mask is slow even when you're only masking one column

data = np.random.randn(10000, 500)
df = pd.DataFrame(data)
df.ix[:, 0] = np.nan
bools = df > 0

reg_mask = bools.mask(pd.isnull(df)) # walltime 10s
# dtypes: bool(499), float64(1)

[jreback]

This is a fundamental problem in that since we alway try to give you back the same dtype you came in with (if possible), this results in a pretty slow conversion. The masking function e.g. df>0 is operating on a boolean array here; when I evaluate in numpy it changs it to float64, which is fine.

Then we do the downcasting back to bools where possible (which in this particular case is degenerate in that we try to convert it back , but since the nans are basically in every column, we can't, so we end up with floats.

This conversion is item-by-item as a-prioi don't know which columns can be converted (well I do know, but again have to convert item-by-item); The degenerate case is where 1/2 of the columns can be converted. So can get some gains back.

This will also happen with integers , e.g. any dtype that cannot hold nans.

So not really sure what do do here; it makes the code a fair bit more complicated to handle the splitting case better than we do now.

any thoughts?

[cpcloud]

Explanation

I'm not sure if you're familiar with the internals at all, but one reason is that the bool dtype cannot hold NaNs. Under the hood pandas checks to see if the Block (a Block is a container for a chunk of data of a uniform dtype) being operated on can hold NaN. If it can't then it will loop over each item (the Int64Index elements in this case) and perform the operation on each column.

Numbers

the following numbers are from my machine, using lprun

• take on a single column of values (the method calling frame): 4.3 ms
• take on a single column of cond (the input boolean frame): 9.6 ms
• number of times each of those is called: 500
• total time = 500 times * (4.3 ms + 9.6 ms) == 6950 ms == 6.95 s

Conclusion

take is 3 orders of magnitude slower (!) than the equivalent slice expression (which times in at about 19 us). it might be hard to get around this when using MultiIndexes but maybe a special case for block items that are Int64indexes can be put in.

[cpcloud]

@jreback what about building a slice expression rather than using take, e.g.,

x.take([0], axis=0) == x[[0]]
x.take([0], axis=1) == x[:, [0]]

in general

def slice_it(x, ind, axis):
    slices = [slice(None)] * x.ndim
    slices[axis] = ind
    return x[tuple(slices)]

i think should work, but i haven't tried it yet

[jreback]

nothing to do with take per se; its all about trying to convert the resulting calculation

take is quite fast, but when called 500 times there is a lot of overhead

I can do it, but as I said, I am not sure the additional code complexity is worth it.

Basically you have to figure out what you can a-priori downcast back to the original type; right now we are doing this item-by-item which is why its slow;

I will give another try to see what I can do

[cpcloud]

hm then lprun is wrong. 97 percent of the computation is spent on the takes

[cpcloud]

i get it down to 235ish ms when using a slice a 29x speedup seems worth it no?

[jreback]

ok...pretty much fixed (these are for 1000 cols btw)
Its still somewhat slower because have to process 2 resulting blocks but much better
(was 400+ ms before)

In [6]: bools.mask(mask)
Out[6]: 
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1000 entries, 0 to 999
Columns: 500 entries, 0 to 499
dtypes: float64(500)

In [7]: %timeit bools.mask(mask)
100 loops, best of 3: 14.8 ms per loop

In [8]: %timeit bools.astype(float).mask(mask)
100 loops, best of 3: 3.01 ms per loop

@cpcloud the issue is take was called once for each item, but calling it once doesn't really help because of all of the other stuff that is going one)

[jreback]

@cpcloud and you get all of the tests to pass?????

[cpcloud]

no no no that was a quick and dirty crap hack to prove my point :/ i can do the general case and try it out

[jreback]

i will add the slicing, not hard

[jreback]

and actually...the reason the original is so fast is the it is handled by numexpr!

[jreback]

the bool dtype is not handled by numexpr

[cpcloud]

tests pass. if u want i can push it

[cpcloud]

(there's an issue with pytables in travis)

[cpcloud]

fixed the travis bug (i think) will push a pr if it works