Brain_overflowed Brain_overflowed - 3 years ago 163
Python Question

Comparing values in the indices of two dataframes in pandas

I am trying to compare in which index does the timedelta value in one dataframe1 is equal to the timedelta value in another dataframe2 and then trim the dataframe that has more values to make them both start at the same time:

Dataset1:

TimeStamp Col1 ... Col2500
0 days 10:37:34 346 ... 635
0 days 10:38:34 124 ... 546
0 days 10:39:34 346 ... 745


Dataset2:

TimeStamp Col1 ... Col50
0 days 10:25:20 123 ... 789
0 days 10:25:45 183 ... 787
...
...
0 days 10:37:40 223 ... 789

for i in df2.index:
if str(df1.index[0])[7:12] == str(df2.index[i])[7:12]:
index_value = i
break
df2 = df2.drop(df2.index[[0,i-1]])


Expected output will be Dataset2 starting at the same time (nearest to the minute) with Dataset1

Answer Source

You can use searchsorted for indices for first higher value in df2.index like first value of df1.index. Then select second df2 by positions by iloc:

#necessary both indices are sorted
df1 = df1.sort_index()
df2 = df2.sort_index()

a = df2.index.searchsorted(df1.index[0])
print (a)
2

df2 = df2.iloc[a:]
print (df2)
           Col1  ...  Col50
TimeStamp                  
10:37:40    223  ...    789
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download