Python: checking which bins two time points belong to

Question

I have a list of lists with two values that represent a start time-point and an end time-point. I would like to count how much of the time range between the two points fall into bins.

The bins are between 0-300,300-500 and 500-1200. I would also like to bin them between 0-50, 50-100, 100-150 and so on.

The question is similar to Python: Checking to which bin a value belongs, but different since it involves a two-points time-range which can fall into separate bins at the same time.

I have created the a for loop in the code below, which works. But I'm wondering if there is a faster more pythonic way to calculate this, perhaps using pandas or numpy.

import numpy
x = numpy.array([[100, 150],[100, 125],[290, 310],[277, 330],
       [300, 400],[480, 510],[500, 600]])

d = {'0-300': [0], '300-500': [0], '500-1200':[0]}
import pandas as pd
df = pd.DataFrame(data=d)

for i in x:
    start,end = i[0],i[1]

    if start <= 300 and end <= 300: # checks if time ranges falls into only 1st bin
        df['0-300'][0] += end - start

    elif start <= 300 and end > 300:  # checks if time ranges falls into 1st and 2ed bin
        df['0-300'][0] += (300 - start)
        df['300-500'][0] += (end - 300)

    elif start >= 300 and end >= 300 and end <= 500: # checks if time ranges falls into only 2ed bin
        df['300-500'][0] += end - start

    elif start <= 500 and end > 500:  # checks if time ranges falls into 2ed and 3ed bin
        df['300-500'][0] += (500 - start)
        df['500-1200'][0] += (end - 500)

    elif start > 500: # checks if time ranges falls into only 3ed bin
        df['500-1200'][0] += end - start

df: 
   0-300  300-500  500-1200
     108      160       110

thanks for reading

Can you explain a bit what your code is doing here? – Bharath M Shetty Dec 23 '17 at 15:12 — Bharath M Shetty, Dec 23 '17 at 15:12

Divakar · Accepted Answer · 2017-12-23T15:57:23.663

For a generic number of bins, here's a vectorized way leveraging np.add.at to get the counts and then np.add.reduceat for getting binned summations -

bins = [0, 300, 500, 1200] # Declare bins
id_arr = np.zeros(bins[-1], dtype=int)
np.add.at(id_arr, x[:,0], 1)
np.add.at(id_arr, x[:,1], -1)
c = id_arr.cumsum()
out = np.add.reduceat(c, bins[:-1])

# Present in a dataframe format
col_names = [str(i)+'-' + str(j) for i,j in zip(bins[:-1], bins[1:])]
df_out = pd.DataFrame([out], columns=col_names)

Sample output -

In [524]: df_out
Out[524]: 
   0-300  300-500  500-1200
0    108      160       110

score 2 · Answer 2 · answered Dec 23 '17 at 15:25

Here is one way of doing it

In [1]: counts = np.zeros(1200, dtype=int)
In [2]: for x_lower, x_upper in x: counts[x_lower:x_upper] += 1
In [3]: d['0-300'] = counts[0:300].sum()
In [4]: d['300-500'] = counts[300:500].sum()
In [5]: d['500-1200'] = counts[500:1200].sum()
In [6]: d                                                                                                                                                                                                                      
Out[6]: {'0-300': 108, '300-500': 160, '500-1200': 110}

However, in order to sum up the results for all bins, it will be better to wrap those 3 steps into a for loop.

Python: checking which bins two time points belong to

2 Answers2