Comparing values within the same dataframe column

Question

Is there anyway to compare values within the same column of a pandas DataFrame?

The task at hand is something like this:

import pandas as pd
data = pd.DataFrame({"A": [0,-5,2,3,-3,-4,-4,-2,-1,5,6,7,3,-1]});

I need to find the maximum time (in indices) consecutive +/- values appear (Equivalently checking consecutive values because the sign can be encoded by True/False). The above data should yield 5 because there are 5 consecutive negative integers [-3,-4,-4,-2,-1]

If possible, I was hoping to avoid using a loop because the number of data points in the column may very well exceed millions in order.

I've tried using data.A.rolling() and it's variants, but can't seem to figure out any possible way to do this in a vectorized way.

Any suggestions?

Divakar · Accepted Answer · 2017-03-25 15:25:44Z

2

Here's a NumPy approach that computes the max interval lengths for the positive and negative values -

def max_interval_lens(arr):
    # Store mask of positive values
    pos_mask = arr>=0

    # Get indices of shifts
    idx = np.r_[0,np.flatnonzero(pos_mask[1:] != pos_mask[:-1])+1, arr.size]

    # Return max of intervals
    lens = np.diff(idx)
    s = int(pos_mask[0])
    maxs = [0,0]    
    if len(lens)==1:
        maxs[1-s] = lens[0]
    else:
        maxs = lens[1-s::2].max(), lens[s::2].max() 

    return maxs # Positive, negative max lens

Sample run -

In [227]: data
Out[227]: 
    A
0   0
1  -5
2   2
3   3
4  -3
5  -4
6  -4
7  -2
8  -1
9   5
10  6
11  7
12  3
13 -1

In [228]: max_interval_lens(data['A'].values)
Out[228]: (4, 5)

edited Mar 25, 2017 at 15:25

answered Mar 25, 2017 at 14:38

Divakar

222k19 gold badges273 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tiny_rick Over a year ago

Wow, that was seriously creative ... thanks a ton! I was getting stuck at how to isolate switch in signs from only - to +. The reshape bit helped. Thanks, this was awesomely helpful :)

Divakar Over a year ago

@AtulyaUrankar Changed a bit there to simplify, that also avoids the reshaping.

Collectives™ on Stack Overflow

Comparing values within the same dataframe column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related