Most efficient way to get DatetimeIndex sub-section?

user_15347019 0
Viewed 41

Most efficient way to get DatetimeIndex sub-section?

"efficient" in terms of: This function will be called around 50 times over Indices of 1-2 million in length.

df_A:                       |------------------|

index_B:      |------|  |-----|    |-----|       |-------|      |------------------|   

Expected result:            |-|    |-----|

Note: index_B is usually 1-2 million indices long and df_A is between 0.5-1 million indices long. I also included df_A as DataFrame, rather than Index in this question, since there might be some pandas DataFrame options I overlooked.

Sample:

import pandas as pd
import numpy as np

# create index_B
periods_ = 500000
index_B = pd.date_range("01.01.2020", periods = periods_, freq='T')

# drop random Indices out of index_B. (30% of the index_B's lenght).
np.random.seed(100)
drop_indices = np.random.choice(index_B, int(periods_*0.3), replace=False)
index_B      = index_B.drop(drop_indices)


# create df_A, so that it will be in between the range index_B's potentially earliest start and latest end. 
df_A         = pd.DataFrame( np.random.randint(0,100,size=(int(periods_*0.3), 4)), columns=list('ABCD') )
df_A.index   = pd.date_range("01.01.2020", periods = periods_, freq='T') [int(periods_*0.35) : -int(periods_*0.35)]



# options:
# a) 
result = index_B .intersection( df_A.index )

# b) 
mask = (index_B > df_A.index[0]) & (index_B <= df_A.index[-1])
result = index_B .loc[mask] 

# c) are there any other, faster ways?
pandas indexing numpy datetime python
1 Answers

Insteaf of using a mask on index_B as in your option B, you could transform your index into a pd.Series and use .loc to slice based on the date:

index_B.to_series()[df_A.index[0]:df_A.index[-1]]

I quickly tested using periods = 50_000_000 and it was ~6 times faster than option B (which already was ~7-8 times faster than option A).

user_32810970
Related