How to flag duplicates in a rolling time window?

Question

I have the following data frame:

       X.RIC       Date     Time  Type    Price Volume  VolumeSEK
64 ALFAs.BCO 11.09.2018 25209.89 Trade 233.3000    158  36861.400
65 ALFAs.BCO 11.09.2018 25224.86 Trade 233.3000      1    233.300
66 ALFAs.BCO 11.09.2018 25248.27 Trade 233.4000    127  29641.800
67 ALFAs.BCO 11.09.2018 25633.92 Trade 233.1000     17   3962.700
68 ALFAs.BCO 11.09.2018 26107.33 Trade 232.9951    153  35648.250
69 ALFAs.BCO 11.09.2018 26389.27 Trade 232.8000     68  15830.400
70 ALFAs.BCO 11.09.2018 26405.14 Trade 232.8000    127  29565.600
71 ALFAs.BCO 11.09.2018 26554.28 Trade 232.8568     86  20025.685
72 ALFAs.BCO 11.09.2018 26652.19 Trade 232.4000    118  27423.200
73 ALFAs.BCO 11.09.2018 26654.21 Trade 232.7000     23   5352.100
74 ALFAs.BCO 11.09.2018 26666.24 Trade 232.7000      9   2094.300
75 ALFAs.BCO 11.09.2018 26978.54 Trade 232.4796     12   2789.755
76 ALFAs.BCO 11.09.2018 26983.92 Trade 232.5000    190  44175.000
77 ALFAs.BCO 11.09.2018 27108.03 Trade 232.5000      3    697.500
78 ALFAs.BCO 11.09.2018 27304.90 Trade 232.6674    274  63750.868
79 ALFAs.BCO 11.09.2018 27401.40 Trade 232.4000    591 137348.400
80 ALFAs.BCO 11.09.2018 27424.90 Trade 232.5468     79  18371.197
81 ALFAs.BCO 11.09.2018 27577.74 Trade 232.2000    190  44118.000
82 ALFAs.BCO 11.09.2018 27792.09 Trade 232.3000    218  50641.400
83 ALFAs.BCO 11.09.2018 27926.07 Trade 232.2699     82  19046.132
84 ALFAs.BCO 11.09.2018 27926.07 Trade 232.2699     78  18117.052
85 ALFAs.BCO 11.09.2018 27926.07 Trade 232.2699     25   5806.748
86 ALFAs.BCO 11.09.2018 27988.57 Trade 232.2500      1    232.250
87 ALFAs.BCO 11.09.2018 28150.02 Trade 232.7000    218  50728.600
88 ALFAs.BCO 11.09.2018 28171.02 Trade 232.7000    190  44213.000
89 ALFAs.BCO 11.09.2018 28198.79 Trade 232.8000     48  11174.400
90 ALFAs.BCO 11.09.2018 28210.15 Trade 232.8000      6   1396.800
91 ALFAs.BCO 11.09.2018 28226.06 Trade 232.5277     68  15811.884
92 ALFAs.BCO 11.09.2018 28226.07 Trade 232.5277     59  13719.134
93 ALFAs.BCO 11.09.2018 28226.07 Trade 232.5277     46  10696.274

with a few thousand rows. Every row represents a transaction and time is given in seconds. I want to add a row that flags if a transaction of same or similar volume has appeared within the previous 60 seconds. I tried to do it with duplicated(), but did not manage to apply it to a rolling time window. Every kind of hint or feedback is highly appreciated. Thanks in advance!

Additional: the dput() is:

structure(list(X.RIC = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L), .Label = c("ALFAs.BCO", "INVEBs.BCO", "KINVBs.BCO", 
"SEBAs.BCO", "SKABs.BCO", "SWEDAs.BCO"), class = "factor"), Date = structure(c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("11.09.2018", 
"12.09.2018", "13.09.2018", "14.09.2018", "17.09.2018"), class = "factor"), 
    Time = c(25209.891063318, 25224.862743496, 25248.266841503, 
    25633.917236141, 26107.32746866, 26389.27354248, 26405.138667668, 
    26554.281062341, 26652.193081185, 26654.208526453, 26666.237043479, 
    26978.544043377, 26983.919406655, 27108.031169088, 27304.898035779, 
    27401.39801531, 27424.901479915, 27577.738223786, 27792.092294417, 
    27926.067647984, 27926.071789697, 27926.071789697, 27988.567139221, 
    28150.023490037, 28171.018701776, 28198.786920791, 28210.153751339, 
    28226.061861331, 28226.065520147, 28226.069560003, 28263.30156887, 
    28349.625180899, 28349.633532835, 28349.633532835, 28403.852984163, 
    28403.853357068, 28403.853357068, 28593.924744396, 28593.924744396, 
    28593.924744396, 28593.932109258, 28686.948138522, 28754.584046006, 
    28754.584046006, 28766.164856168, 28817.79249469, 28817.79249469, 
    29118.669834707, 29125.870160126, 29180.787060028, 29252.170090921, 
    29359.898647305, 29425.88512985, 29425.889294498, 29425.893298658, 
    29425.893298658, 29725.892421023, 29725.892421023, 29725.895962942, 
    29725.900418295), Type = structure(c(1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Trade", class = "factor"), 
    Price = c(233.3, 233.3, 233.4, 233.1, 232.9951, 232.8, 232.8, 
    232.8568, 232.4, 232.7, 232.7, 232.4796, 232.5, 232.5, 232.6674, 
    232.4, 232.5468, 232.2, 232.3, 232.2699, 232.2699, 232.2699, 
    232.25, 232.7, 232.7, 232.8, 232.8, 232.5277, 232.5277, 232.5277, 
    232.7, 232.7, 232.7, 232.7, 232.8, 232.8, 232.8, 232.811, 
    232.811, 232.811, 232.811, 232.8, 232.834, 232.834, 232.9, 
    233.0378, 233.0378, 233.1, 233.0451, 233.15, 233.1, 233.3, 
    233.135, 233.135, 233.135, 233.135, 233.2561, 233.2561, 233.2561, 
    233.2561), Volume = c(158L, 1L, 127L, 17L, 153L, 68L, 127L, 
    86L, 118L, 23L, 9L, 12L, 190L, 3L, 274L, 591L, 79L, 190L, 
    218L, 82L, 78L, 25L, 1L, 218L, 190L, 48L, 6L, 68L, 59L, 46L, 
    5L, 27L, 21L, 6L, 12L, 11L, 5L, 40L, 37L, 16L, 12L, 4L, 25L, 
    17L, 190L, 14L, 9L, 480L, 57L, 354L, 249L, 190L, 120L, 63L, 
    61L, 34L, 80L, 66L, 29L, 16L), VolumeSEK = c(36861.4, 233.3, 
    29641.8, 3962.7, 35648.2503, 15830.4, 29565.6, 20025.6848, 
    27423.2, 5352.1, 2094.3, 2789.7552, 44175, 697.5, 63750.8676, 
    137348.4, 18371.1972, 44118, 50641.4, 19046.1318, 18117.0522, 
    5806.7475, 232.25, 50728.6, 44213, 11174.4, 1396.8, 15811.8836, 
    13719.1343, 10696.2742, 1163.5, 6282.9, 4886.7, 1396.2, 2793.6, 
    2560.8, 1164, 9312.44, 8614.007, 3724.976, 2793.732, 931.2, 
    5820.85, 3958.178, 44251, 3262.5292, 2097.3402, 111888, 13283.5707, 
    82535.1, 58041.9, 44327, 27976.2, 14687.505, 14221.235, 7926.59, 
    18660.488, 15394.9026, 6764.4269, 3732.0976)), .Names = c("X.RIC", 
"Date", "Time", "Type", "Price", "Volume", "VolumeSEK"), row.names = 64:123, class = "data.frame")

start with https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — jangorecki, Mar 05 '19 at 13:46
Sorry for being unclear on the similar volume. Similar would be a volume +/-3 units. — Jakob R., Mar 05 '19 at 14:09

score 0 · Answer 1 · answered Mar 05 '19 at 14:31

The solution here can be represented by a complex self join. We have assumed that two volumes are similar if they are within 5 of each other and if the prior row is within 60 seconds of the latter row and comes before it. dat is the input data frame shown in the quesiton.

library(sqldf)
sqldf("select a.*, coalesce(sum(abs(a.Volume - b.Volume) < 5) > 0, 0) SameVol
       from dat a 
       left join dat b on a.Date = b.Date and 
                 b.Time between a.Time - 60 and a.Time and
                 b.rowid < a.rowid
       group by a.rowid")

giving:

       X.RIC       Date     Time  Type    Price Volume  VolumeSEK SameVol
1  ALFAs.BCO 11.09.2018 25209.89 Trade 233.3000    158  36861.400       0
2  ALFAs.BCO 11.09.2018 25224.86 Trade 233.3000      1    233.300       0
3  ALFAs.BCO 11.09.2018 25248.27 Trade 233.4000    127  29641.800       0
4  ALFAs.BCO 11.09.2018 25633.92 Trade 233.1000     17   3962.700       0
5  ALFAs.BCO 11.09.2018 26107.33 Trade 232.9951    153  35648.250       0
6  ALFAs.BCO 11.09.2018 26389.27 Trade 232.8000     68  15830.400       0
7  ALFAs.BCO 11.09.2018 26405.14 Trade 232.8000    127  29565.600       0
8  ALFAs.BCO 11.09.2018 26554.28 Trade 232.8568     86  20025.685       0
9  ALFAs.BCO 11.09.2018 26652.19 Trade 232.4000    118  27423.200       0
10 ALFAs.BCO 11.09.2018 26654.21 Trade 232.7000     23   5352.100       0
11 ALFAs.BCO 11.09.2018 26666.24 Trade 232.7000      9   2094.300       0
12 ALFAs.BCO 11.09.2018 26978.54 Trade 232.4796     12   2789.755       0
13 ALFAs.BCO 11.09.2018 26983.92 Trade 232.5000    190  44175.000       0
14 ALFAs.BCO 11.09.2018 27108.03 Trade 232.5000      3    697.500       0
15 ALFAs.BCO 11.09.2018 27304.90 Trade 232.6674    274  63750.868       0
16 ALFAs.BCO 11.09.2018 27401.40 Trade 232.4000    591 137348.400       0
17 ALFAs.BCO 11.09.2018 27424.90 Trade 232.5468     79  18371.197       0
18 ALFAs.BCO 11.09.2018 27577.74 Trade 232.2000    190  44118.000       0
19 ALFAs.BCO 11.09.2018 27792.09 Trade 232.3000    218  50641.400       0
20 ALFAs.BCO 11.09.2018 27926.07 Trade 232.2699     82  19046.132       0
21 ALFAs.BCO 11.09.2018 27926.07 Trade 232.2699     78  18117.052       1
22 ALFAs.BCO 11.09.2018 27926.07 Trade 232.2699     25   5806.748       0
23 ALFAs.BCO 11.09.2018 27988.57 Trade 232.2500      1    232.250       0
24 ALFAs.BCO 11.09.2018 28150.02 Trade 232.7000    218  50728.600       0
25 ALFAs.BCO 11.09.2018 28171.02 Trade 232.7000    190  44213.000       0
26 ALFAs.BCO 11.09.2018 28198.79 Trade 232.8000     48  11174.400       0
27 ALFAs.BCO 11.09.2018 28210.15 Trade 232.8000      6   1396.800       0
28 ALFAs.BCO 11.09.2018 28226.06 Trade 232.5277     68  15811.884       0
29 ALFAs.BCO 11.09.2018 28226.07 Trade 232.5277     59  13719.134       0
30 ALFAs.BCO 11.09.2018 28226.07 Trade 232.5277     46  10696.274       1
31 ALFAs.BCO 11.09.2018 28263.30 Trade 232.7000      5   1163.500       1
32 ALFAs.BCO 11.09.2018 28349.63 Trade 232.7000     27   6282.900       0
33 ALFAs.BCO 11.09.2018 28349.63 Trade 232.7000     21   4886.700       0
34 ALFAs.BCO 11.09.2018 28349.63 Trade 232.7000      6   1396.200       0
35 ALFAs.BCO 11.09.2018 28403.85 Trade 232.8000     12   2793.600       0
36 ALFAs.BCO 11.09.2018 28403.85 Trade 232.8000     11   2560.800       1
37 ALFAs.BCO 11.09.2018 28403.85 Trade 232.8000      5   1164.000       1
38 ALFAs.BCO 11.09.2018 28593.92 Trade 232.8110     40   9312.440       0
39 ALFAs.BCO 11.09.2018 28593.92 Trade 232.8110     37   8614.007       1
40 ALFAs.BCO 11.09.2018 28593.92 Trade 232.8110     16   3724.976       0
41 ALFAs.BCO 11.09.2018 28593.93 Trade 232.8110     12   2793.732       1
42 ALFAs.BCO 11.09.2018 28686.95 Trade 232.8000      4    931.200       0
43 ALFAs.BCO 11.09.2018 28754.58 Trade 232.8340     25   5820.850       0
44 ALFAs.BCO 11.09.2018 28754.58 Trade 232.8340     17   3958.178       0
45 ALFAs.BCO 11.09.2018 28766.16 Trade 232.9000    190  44251.000       0
46 ALFAs.BCO 11.09.2018 28817.79 Trade 233.0378     14   3262.529       0
47 ALFAs.BCO 11.09.2018 28817.79 Trade 233.0378      9   2097.340       0
48 ALFAs.BCO 11.09.2018 29118.67 Trade 233.1000    480 111888.000       0
49 ALFAs.BCO 11.09.2018 29125.87 Trade 233.0451     57  13283.571       0
50 ALFAs.BCO 11.09.2018 29180.79 Trade 233.1500    354  82535.100       0
51 ALFAs.BCO 11.09.2018 29252.17 Trade 233.1000    249  58041.900       0
52 ALFAs.BCO 11.09.2018 29359.90 Trade 233.3000    190  44327.000       0
53 ALFAs.BCO 11.09.2018 29425.89 Trade 233.1350    120  27976.200       0
54 ALFAs.BCO 11.09.2018 29425.89 Trade 233.1350     63  14687.505       0
55 ALFAs.BCO 11.09.2018 29425.89 Trade 233.1350     61  14221.235       1
56 ALFAs.BCO 11.09.2018 29425.89 Trade 233.1350     34   7926.590       0
57 ALFAs.BCO 11.09.2018 29725.89 Trade 233.2561     80  18660.488       0
58 ALFAs.BCO 11.09.2018 29725.89 Trade 233.2561     66  15394.903       0
59 ALFAs.BCO 11.09.2018 29725.90 Trade 233.2561     29   6764.427       0
60 ALFAs.BCO 11.09.2018 29725.90 Trade 233.2561     16   3732.098       0

Thank you very much. Works perfectly fine for me. – Jakob R. Mar 05 '19 at 18:24 — Jakob R., Mar 05 '19 at 18:24

How to flag duplicates in a rolling time window?

1 Answers1