Checking for values from one time series dataframe for another time series dataframe

MemeFast King 0
Viewed 39

df1 looks like this- (it has 3 min time interval window against each ID and Name)

ID  Name  Time    Quantity   Flag
A   A     00:00:00   6         N
          00:03:00   0         N
          00:06:00   9         N
A   B     01:09:00   3         N
C   A     02:00:00   5         N
          02:03:00   0         N

df2 looks like this-

ID  Name  Time
A   A     00:00:03 
A   A     00:04:06
A   B     00:05:02
C   A     02:01:05

I want to change flag values of df1 to 'Y' if any values[ID,Name and time] from df2 is present in df1 time intervals ONLY IF the quantity is 0 for that row.

Desired Output-

df1-
ID  Name  Time     Quantity  Flag
A   A     00:00:00   6        N
          00:03:00   0        Y
          00:06:00   9        N
A   B     01:09:00   3        N
C   A     02:00:00   5        N
          02:03:00   0        Y
dataframe pandas numpy python
1 Answers

IIUC, you can use a merge_asof with 3min tolerance, then a mask:

df1 = (pd
   # identify rows with a matching time with tolerance
   .merge_asof(df1.reset_index().sort_values(by='Time')
                  .assign(key=lambda d: pd.to_timedelta(d['Time']))
               ,
               df2.sort_values(by='Time')
                  .assign(key=lambda d: pd.to_timedelta(d['Time']))
               ,
               by=['ID', 'Name'], on='key',
               tolerance=pd.Timedelta('3min'),
               suffixes=(None, '_')
              )
   # update the Flag on condition of a match and Quantity = 0
   .assign(Flag=lambda d: d['Flag'].mask(d['Time_'].notna()
                                        &d['Quantity'].eq(0),
                                         'Y'
                                        ))
   # cleanup intermediate columns
   .drop(columns=['key', 'Time_'])
)

output:

  ID Name      Time  Quantity Flag
0  A    A  00:00:00         6    N
1  A    A  00:03:00         0    Y
2  A    A  00:06:00         9    N
3  A    B  01:09:00         3    N
4  C    A  02:00:00         5    N
5  C    A  02:03:00         0    Y
mozway0
Related