Finding Dates in one array based on the ranges from another array and closest value

Question

I have two Nested NumPy arrays (dateValArr & searchDates). dateValArr contains all dates for May 2011 (1st - 31st) and an associated value each date. searchDates contains 2 dates and an associated value as well (2 dates correspond to a date range).

Using date ranges specified in searchDates Array, I want to find dates in dateValArr array. Next for those selected dates in dateValArr, I want to find the closest value to the specified value of searchDates.

I have come up with is code but for the first part it it only works if only one value is specified.

#setup arrays ---------------------------------------------------------------------------
# Generate dates
st_date = '2011-05-01'
ed_date = '2011-05-31'
dates = pd.date_range(st_date,ed_date).to_numpy(dtype = object)

# Generate Values
val_arr = np.random.uniform(1,12,31)
dateValLs = []
for i,j in zip(dates,val_arr):
   dateValLs.append((i,j))

dateValArr = np.asarray(dateValLs)

print(dateValArr)    
#out:
[[Timestamp('2011-05-01 00:00:00', freq='D') 7.667399233149668]
[Timestamp('2011-05-02 00:00:00', freq='D') 5.906099813052642]
[Timestamp('2011-05-03 00:00:00', freq='D') 3.254485533826182]
...]

#Generate search dates
searchDates = np.array([(datetime(2011,5,11),datetime(2011,5,20),9),(datetime(2011,5,25),datetime(2011,5,29),2)]) 

 print(searchDates)
 #out:
[[datetime.datetime(2011, 5, 11, 0, 0) datetime.datetime(2011, 5, 20, 0, 0) 9]  
[datetime.datetime(2011, 5, 25, 0, 0) datetime.datetime(2011, 5, 29, 0, 0) 2]]

#end setup ------------------------------------------------------------------------------

x = np.where(np.logical_and(dateValArr[:,0] > searchDates[0][0], dateValArr[:,0] < search_dates[0][1]))

print(x)
out: (array([11, 12, 13, 14, 15, 16, 17, 18], dtype=int64),)

However, the code works only if I select the first element searchDates (searchDates[0][0]). It will not run for all values in searcDates. What I mean if I replace by the following code.

 x = np.where(np.logical_and(dateValArr[:,0] > searchDates[0], dateValArr[:,0] < search_dates[0]))

Then, I will get the following error: operands could not be broadcast together with shapes (31,) (3,)

To find the closest value I hoping to somehow combine the following line of the code,

 n = (np.abs(dateValArr[:,1]-searchDates[:,2])).argmin()

Any ideas on how to solve it.Thanks in advance

TRiNE TRiNE · Accepted Answer · 2020-02-10T06:59:50

Only thing came into my mind is a for loop.

Here is the link for my work

result = np.array([])
for search_term in searchDates:
  mask = (dateValArr[:,0] > search_term[0]) & (dateValArr[:,0] < search_term[1])
  date_search_result = dateValArr[mask, :]
  d = np.abs(date_search_result[:,1] - searchDates[0,2])
  result = np.hstack([result, date_search_result[d.argmin()]])
print(result)

Finding Dates in one array based on the ranges from another array and closest value

2 Answers