CODEWITHSUNDEEP
pythonpandasmerge
dokondr
dokondr

Reputation: 3539

Merge two dataframes on bounding time of event

Two dataframes df1 and df2 (see below) provide info about an experiment that mesures a parameter val at times t. During measuremnts a flag is set to True or False according to an event at some (not all) times t. Between events flag retains its value.

How to join df1 and df2 to a result that have a column with flag value for each measurement?

df1 = pd.DataFrame({'t':[0.5,1.3,2.1,3.3,4.0,5.6,6.1,7.9,8.3,9.0], 
   'val':['a0','b1','c2','d3','e4','j5','k6','l7','m8', 'n9']})
df2 = pd.DataFrame({'t':[0,2,3,5,7,8], 'flag':[False,True,False,True,False,True]})

# df1
   t val
0  0.5  a0
1  1.3  b1
2  2.1  c2
3  3.3  d3
4  4.0  e4
5  5.6  j5
6  6.1  k6
7  7.9  l7
8  8.3  m8
9  9.0  n9 

# df2
   t   flag
0  0  False
1  2   True
2  3  False
3  5   True
4  7  False
5  8   True

Need to get this result:

result = pd.DataFrame({'t':[0.5,1.3,2.1,3.3,4.0,5.6,6.1,7.9,8.3,9.0], 
                   'val':['a0','b1','c1','d3','e4','j5','k6','l7','m8','n9'],
                   'flag':[False,False,True,False,False,True,True,False,True,True]
                  })
# result:

    t   val flag
0   0.5 a0  False
1   1.3 b1  False
2   2.1 c1  True
3   3.3 d3  False
4   4.0 e4  False
5   5.6 j5  True
6   6.1 k6  True
7   7.9 l7  False
8   8.3 m8  True
9   9.0 n9  True

Upvotes: 1

Views: 37

Answers (2)

BENY
BENY

Reputation: 323326

Method from reindex 

df1['New']=df2.set_index('t').reindex(df1.t,method='ffill').values
df1
     t val    New
0  0.5  a0  False
1  1.3  b1  False
2  2.1  c2   True
3  3.3  d3  False
4  4.0  e4  False
5  5.6  j5   True
6  6.1  k6   True
7  7.9  l7  False
8  8.3  m8   True
9  9.0  n9   True

Upvotes: 1

Henry Yik
Henry Yik

Reputation: 22503

Use merge_asof:

print (pd.merge_asof(df1, df2.assign(t=df2["t"].astype(float)), on="t"))

     t val   flag
0  0.5  a0  False
1  1.3  b1  False
2  2.1  c2   True
3  3.3  d3  False
4  4.0  e4  False
5  5.6  j5   True
6  6.1  k6   True
7  7.9  l7  False
8  8.3  m8   True
9  9.0  n9   True

Upvotes: 1

Related Questions