Cfrgtkky

I am trying to apply a function, cumulatively, to values that lie within a window defined by 'start' and 'finish' columns. So, 'start' and 'finish' define the intervals where the value is 'active'; for each row, I want to get a sum of all 'active' values at the time.

Here is a 'bruteforce' example that does what I am after - is there a more elegant, faster or more memory efficient way of doing this?

df = pd.DataFrame(data=[[1,3,100], [2,4,200], [3,6,300], [4,6,400], [5,6,500]],

    columns=['start', 'finish', 'val'])

df['dummy'] = 1

df = df.merge(df, on=['dummy'], how='left')

df = df[(df['start_y'] <= df['start_x']) & (df['finish_y'] > df['start_x'])]

val = df.groupby('start_x')['val_y'].sum()

Originally, df is:

  start  finish  val

0   1      3     100

1   2      4     200

2   3      6     300

3   4      6     400

4   5      6     500

The result I am after is:

1   100

2   300

3   500

4   700

5   1200

Using numpy boardcast , unfortunately it is still O(n*m) solution , but should be faster than the groupby. So far base on my test Pir 's solution performance is the best

s1=df['start'].values

s2=df['finish'].values

np.sum(((s1<=s1[:,None])&(s2>=s2[:,None]))*df.val.values,1)

Out[44]: array([ 100,  200,  300,  700, 1200], dtype=int64)

Some timing

#df=pd.concat([df]*1000)

%timeit merged(df)

1 loop, best of 3: 5.02 s per loop

%timeit npb(df)

1 loop, best of 3: 283 ms per loop

% timeit PIR(df)

100 loops, best of 3: 9.8 ms per loop

def merged(df):

    df['dummy'] = 1

    df = df.merge(df, on=['dummy'], how='left')

    df = df[(df['start_y'] <= df['start_x']) & (df['finish_y'] > df['start_x'])]

    val = df.groupby('start_x')['val_y'].sum()

    return val



def npb(df):

    s1 = df['start'].values

    s2 = df['finish'].values

    return np.sum(((s1 <= s1[:, None]) & (s2 >= s2[:, None])) * df.val.values, 1)

numba

from numba import njit



@njit

def pir_numba(S, F, V):

  mn = S.min()

  mx = F.max()

  out = np.zeros(mx)

  for s, f, v in zip(S, F, V):

    out[s:f] += v

  return out[mn:]



pir_numba(*[df[c].values for c in ['start', 'finish', 'val']])

np.bincount

s, f, v = [df[col].values for col in ['start', 'finish', 'val']]

np.bincount([i - 1 for r in map(range, s, f) for i in r], v.repeat(f - s))



array([ 100.,  300.,  500.,  700., 1200.])

Comprehension

This depends on the index being unique

pd.Series({

    (k, i): v

    for i, s, f, v in df.itertuples()

    for k in range(s, f)

}).sum(level=0)



1     100

2     300

3     500

4     700

5    1200

dtype: int64

With no dependence on index

pd.Series({

    (k, i): v

    for i, (s, f, v) in enumerate(zip(*map(df.get, ['start', 'finish', 'val'])))

    for k in range(s, f)

}).sum(level=0)