1

In TableA I have observations (num) and DateTimes (POSIXct) for the observations. In another table (B) I have groupId's (char), StartDateTimes (POSIXct) and EndDateTimes (POSIXct).

I want to join the two tables so that each observation in TableA is assigned a groupId from TableB based on which intervals from TableB have which TableA observation falling within them.

I asked a similar question for SQL and got a working answer here.

But now I am trying to do this with R and RStudio.

I've tried using the sqldf package to just reuse the SQL answer from the other question, and I also tried the fuzzyjoin package using the interval_inner_join function, but in both cases I only got 12 out of the 178 observations matched, and they were matched to intervals that the observation datetimes didn't actually fall in (the intervals are from the following day).

My attempt using sqldf-

Answer <- sqldf::sqldf('SELECT a.*, b.id
FROM a, b
WHERE a.dt between b.start and b.end')

The Result -

   WEIGHT, dt                 , id
1. 1098 , 2019-03-27 06:58:16 , 1928
2. 1074 , 2019-03-27 06:59:13 , 1928
3. 1037 , 2019-03-27 07:02:52 , 1928
4. 1032 , 2019-03-27 07:03:53 , 1928
5. 1073 , 2019-03-27 07:08:21 , 1928
6. 1054 , 2019-03-27 07:09:25 , 1928
7. 996 , 2019-03-27 07:11:54 , 1928
8. 1081 , 2019-03-27 07:12:49 , 1928
9. 1010, 2019-03-27 07:30:40 , 1928
10. 980 , 2019-03-27 07:38:21 , 1928
11. 1044 , 2019-03-27 07:54:18 , 1928
12. 1009 , 2019-03-27 07:58:06 , 1928

And my attempt using fuzzyjoin-

a <- a %>%
  mutate(start = dt,
         end = dt) %>%
  select(-dt)

Answer <- interval_inner_join(a, b, by = c('start','end'))

The Result - (not enough room, but end.y is all 2019-03-26 19:00:00)

   WEIGHT, start.x            ,  end.x              , start.y,     (end.y), id  
1. 1098 ,2019-03-27 06:58:16 , 2019-03-27 06:58:16 , 2019-03-26 15:24:00,1928
2. 1074 ,2019-03-27 06:59:13 , 2019-03-27 06:59:13 , 2019-03-26 15:24:00,1928
3. 1037 ,2019-03-27 07:02:52 , 2019-03-27 07:02:52 , 2019-03-26 15:24:00,1928
4. 1032 ,2019-03-27 07:03:53 , 2019-03-27 07:03:53 , 2019-03-26 15:24:00,1928
5. 1073 ,2019-03-27 07:08:21 , 2019-03-27 07:08:21 , 2019-03-26 15:24:00,1928
6. 1054 ,2019-03-27 07:09:25 , 2019-03-27 07:09:25 , 2019-03-26 15:24:00,1928
7. 996 ,2019-03-27 07:11:54 , 2019-03-27 07:11:54 , 2019-03-26 15:24:00,1928
8. 1081 ,2019-03-27 07:12:49 , 2019-03-27 07:12:49 , 2019-03-26 15:24:00,1928
9. 1010,2019-03-27 07:30:40 , 2019-03-27 07:30:40 , 2019-03-26 15:24:00,1928
10. 980 ,2019-03-27 07:38:21 , 2019-03-27 07:38:21 , 2019-03-26 15:24:00,1928
11. 1044 ,2019-03-27 07:54:18 , 2019-03-27 07:54:18 , 2019-03-26 15:24:00,1928
12. 1009 ,2019-03-27 07:58:06 , 2019-03-27 07:58:06 ,2019-03-26 15:24:00,1928

As you can see above, the results are that I get only 12 matches and they are incorrect. It is matching 12 observations to a nearby but incorrect interval out of the 178 observations.

Ideally I would get at least the majority of the 178 observations matched, obviously correctly. Now, one complication may be that there are some overlapping intervals, and it is possible that some of the observations then fall within multiple intervals. Is this the issue? Can it be handled so that those cases raise an NA id or something?

Any help would be very appreciated.

Here is the raw data in tsv format:

TableA

WEIGHT  dt
894 18/12/2018 11:51
946 18/12/2018 11:52
907 14/02/2019 11:53
921 25/02/2019 10:52
866 25/02/2019 10:53
1021    25/02/2019 18:07
1022    25/02/2019 18:09
1032    25/02/2019 18:12
979 25/02/2019 18:16
1097    25/02/2019 18:19
1094    25/02/2019 18:23
880 26/02/2019 10:58
866 26/02/2019 11:00
876 26/02/2019 11:01
876 26/02/2019 11:01
861 26/02/2019 11:04
869 26/02/2019 11:06
890 26/02/2019 11:07
759 26/02/2019 11:10
813 26/02/2019 11:11
845 26/02/2019 11:30
989 26/02/2019 12:15
894 26/02/2019 15:00
963 26/02/2019 15:01
928 26/02/2019 15:03
822 26/02/2019 15:11
906 26/02/2019 15:14
994 26/02/2019 15:32
1011    26/02/2019 15:34
1004    26/02/2019 16:59
996 26/02/2019 17:01
883 26/02/2019 17:03
942 26/02/2019 17:05
950 26/02/2019 17:07
1018    26/02/2019 17:27
1003    28/02/2019 9:34
1021    28/02/2019 9:38
984 28/02/2019 9:42
942 28/02/2019 9:45
1067    28/02/2019 9:50
1034    28/02/2019 9:53
1087    28/02/2019 9:56
1040    28/02/2019 10:00
1090    28/02/2019 10:10
1029    28/02/2019 10:31
1023    28/02/2019 10:36
1010    28/02/2019 10:38
1051    28/02/2019 10:41
988 28/02/2019 10:42
1100    28/02/2019 10:43
985 1/03/2019 11:21
924 1/03/2019 11:22
988 1/03/2019 11:27
1001    1/03/2019 11:30
994 1/03/2019 11:35
958 1/03/2019 11:46
960 1/03/2019 11:48
990 1/03/2019 11:51
995 1/03/2019 11:52
928 1/03/2019 13:14
925 1/03/2019 13:16
1052    1/03/2019 13:18
1034    1/03/2019 13:27
1031    1/03/2019 13:28
944 1/03/2019 13:56
1038    1/03/2019 13:58
1056    1/03/2019 14:32
1050    1/03/2019 16:45
958 1/03/2019 17:33
1007    14/03/2019 14:19
985 14/03/2019 14:31
978 14/03/2019 14:35
1031    14/03/2019 14:56
976 14/03/2019 15:01
1063    14/03/2019 15:03
965 14/03/2019 15:06
1050    14/03/2019 15:07
894 14/03/2019 15:11
830 25/03/2019 11:14
920 25/03/2019 11:16
966 25/03/2019 11:18
992 25/03/2019 11:20
881 25/03/2019 11:21
823 25/03/2019 11:22
1046    25/03/2019 11:23
913 25/03/2019 11:24
885 25/03/2019 11:26
944 25/03/2019 11:28
938 25/03/2019 11:31
937 25/03/2019 11:36
937 25/03/2019 11:36
946 25/03/2019 11:40
985 25/03/2019 11:41
942 25/03/2019 11:45
1098    27/03/2019 6:58
1074    27/03/2019 6:59
1037    27/03/2019 7:02
1032    27/03/2019 7:03
1073    27/03/2019 7:08
1054    27/03/2019 7:09
996 27/03/2019 7:11
1081    27/03/2019 7:12
1010    27/03/2019 7:30
980 27/03/2019 7:38
1044    27/03/2019 7:54
1009    27/03/2019 7:58
963 27/03/2019 8:07
1047    27/03/2019 8:10
1114    27/03/2019 8:20
1110    27/03/2019 8:24
1009    27/03/2019 8:32
998 27/03/2019 11:49
1076    27/03/2019 17:48
1042    27/03/2019 17:51
1011    27/03/2019 17:53
1068    27/03/2019 17:54
1061    27/03/2019 17:58
1018    27/03/2019 17:59
1063    27/03/2019 18:00
1093    27/03/2019 18:00
1108    27/03/2019 18:01
1083    27/03/2019 18:03
1109    27/03/2019 18:05
1057    27/03/2019 18:06
1066    27/03/2019 18:07
1043    27/03/2019 18:08
1054    27/03/2019 18:09
1018    27/03/2019 18:11
1048    27/03/2019 18:12
1045    27/03/2019 18:13
1065    27/03/2019 18:18
1066    27/03/2019 18:19
1052    5/04/2019 13:50
1056    6/04/2019 16:43
1042    6/04/2019 16:43
1039    6/04/2019 16:49
954 6/04/2019 16:49
1015    6/04/2019 16:52
1009    6/04/2019 16:52
1026    6/04/2019 16:55
991 6/04/2019 16:56
1050    6/04/2019 17:13
1050    6/04/2019 17:13
934 6/04/2019 17:14
892 6/04/2019 17:23
1029    6/04/2019 17:26
1020    6/04/2019 17:29
1060    6/04/2019 17:32
1007    6/04/2019 17:35
989 6/04/2019 17:38
1006    6/04/2019 17:41
993 6/04/2019 17:45
1048    6/04/2019 17:52
1025    6/04/2019 17:57
1046    6/04/2019 18:21
988 6/04/2019 18:22
1024    8/04/2019 7:17
1076    8/04/2019 7:18
963 8/04/2019 7:19
1122    8/04/2019 7:20
1019    8/04/2019 7:21
1014    8/04/2019 7:22
907 8/04/2019 7:22
1033    8/04/2019 7:23
989 8/04/2019 7:25
1010    8/04/2019 7:26
1015    8/04/2019 7:27
979 8/04/2019 7:28
1018    8/04/2019 7:28
1054    8/04/2019 7:29
937 8/04/2019 7:31
918 8/04/2019 7:32
1054    8/04/2019 7:33
977 8/04/2019 7:34
1028    8/04/2019 7:35
942 8/04/2019 7:36
1009    8/04/2019 7:38
927 8/04/2019 7:39

TableB

start   end id
13/11/2018 10:54    13/11/2018 16:30    1900
14/11/2018 8:00 14/11/2018 16:30    1900
22/11/2018 8:00 22/11/2018 16:30    1900
29/11/2018 13:04    29/11/2018 16:30    1903
30/11/2018 8:00 30/11/2018 16:30    1903
3/12/2018 8:00  3/12/2018 16:30 1903
4/12/2018 8:00  4/12/2018 16:30 1903
5/12/2018 8:00  5/12/2018 16:30 1903
6/12/2018 8:00  6/12/2018 9:47  1900
6/12/2018 9:47  6/12/2018 14:30 1903
10/12/2018 8:00 10/12/2018 16:30    1905
12/12/2018 8:00 12/12/2018 16:30    1905
13/12/2018 8:00 13/12/2018 16:33    1901
14/12/2018 8:00 14/12/2018 11:30    1905
14/12/2018 11:19    14/12/2018 16:30    1904
17/12/2018 8:00 17/12/2018 13:45    1904
17/12/2018 13:45    17/12/2018 14:45    1904
17/12/2018 14:41    17/12/2018 16:30    1902
18/12/2018 8:00 18/12/2018 16:30    1902
19/12/2018 8:00 19/12/2018 14:00    1902
19/12/2018 14:07    19/12/2018 16:30    1902
21/12/2018 8:00 21/12/2018 16:30    1905
7/01/2019 8:00  7/01/2019 16:30 1905
8/01/2019 8:00  8/01/2019 16:30 1905
9/01/2019 8:00  9/01/2019 16:30 1905
10/01/2019 8:00 10/01/2019 15:30    1905
10/01/2019 15:23    10/01/2019 16:30    1906
11/01/2019 8:00 11/01/2019 9:50 1902
11/01/2019 9:37 11/01/2019 16:31    1902
14/01/2019 8:00 14/01/2019 11:45    1902
14/01/2019 11:44    14/01/2019 13:00    1909
14/01/2019 12:10    14/01/2019 13:10    1910
14/01/2019 13:00    14/01/2019 16:30    1910
15/01/2019 8:00 15/01/2019 16:30    1910
16/01/2019 8:00 16/01/2019 16:30    1907
17/01/2019 8:00 17/01/2019 16:30    1907
18/01/2019 8:00 18/01/2019 8:17 1905
18/01/2019 8:00 18/01/2019 16:30    1907
21/01/2019 8:00 21/01/2019 16:30    1912
22/01/2019 8:00 22/01/2019 16:30    1912
23/01/2019 8:00 23/01/2019 16:30    1912
24/01/2019 8:00 24/01/2019 13:30    1912
24/01/2019 13:31    24/01/2019 16:30    1921
25/01/2019 8:00 25/01/2019 10:15    1921
25/01/2019 10:15    25/01/2019 16:30    1907
29/01/2019 8:00 29/01/2019 10:15    1902
29/01/2019 13:00    29/01/2019 16:30    1950
30/01/2019 8:00 30/01/2019 16:30    1950
31/01/2019 8:00 31/01/2019 16:30    1920
1/02/2019 8:00  1/02/2019 16:30 1920
2/02/2019 8:00  2/02/2019 16:30 1915
4/02/2019 7:00  4/02/2019 19:00 1950
5/02/2019 7:00  5/02/2019 19:04 1950
7/02/2019 7:00  7/02/2019 11:15 1920
7/02/2019 11:09 7/02/2019 15:25 1924
7/02/2019 15:15 7/02/2019 19:00 1924
8/02/2019 7:00  8/02/2019 8:30  1950
8/02/2019 7:59  8/02/2019 19:00 1950
9/02/2019 7:00  9/02/2019 19:02 1950
9/02/2019 10:16 9/02/2019 11:45 1950
9/02/2019 11:45 9/02/2019 19:00 1912
11/02/2019 7:00 11/02/2019 14:01    1950
11/02/2019 7:00 11/02/2019 10:20    1912
11/02/2019 10:17    11/02/2019 19:00    1950
11/02/2019 11:23    11/02/2019 11:40    1915
11/02/2019 13:45    11/02/2019 19:00    1950
12/02/2019 7:00 12/02/2019 12:00    1950
12/02/2019 7:01 12/02/2019 16:30    1950
12/02/2019 11:38    12/02/2019 19:05    1958
12/02/2019 16:30    12/02/2019 19:06    1905
13/02/2019 7:00 13/02/2019 19:01    1924
13/02/2019 7:00 13/02/2019 9:35 1905
14/02/2019 7:00 14/02/2019 15:00    1924
14/02/2019 14:54    14/02/2019 19:00    1972
15/02/2019 7:00 15/02/2019 15:00    1970
15/02/2019 15:00    15/02/2019 19:00    1958
16/02/2019 7:00 16/02/2019 17:30    1958
16/02/2019 17:22    16/02/2019 19:00    1958
18/02/2019 7:00 18/02/2019 10:30    1958
18/02/2019 10:12    18/02/2019 15:30    1955
18/02/2019 14:49    18/02/2019 19:00    1913
19/02/2019 7:00 19/02/2019 19:00    1924
19/02/2019 8:54 19/02/2019 9:35 1958
19/02/2019 9:37 19/02/2019 9:40 1913
20/02/2019 7:00 20/02/2019 19:00    1924
21/02/2019 7:00 21/02/2019 19:07    1913
21/02/2019 9:53 21/02/2019 10:07    1915
21/02/2019 10:06    21/02/2019 17:40    1913
21/02/2019 17:42    21/02/2019 19:00    1915
22/02/2019 7:00 22/02/2019 19:05    1935
22/02/2019 7:00 22/02/2019 8:15 1915
22/02/2019 8:21 22/02/2019 13:40    1913
22/02/2019 14:00    22/02/2019 19:00    1920
23/02/2019 7:00 23/02/2019 10:00    1953
23/02/2019 7:12 23/02/2019 8:20 1920
23/02/2019 8:21 23/02/2019 10:00    1972
23/02/2019 10:02    23/02/2019 19:04    1943
23/02/2019 10:20    23/02/2019 11:00    1958
25/02/2019 7:00 25/02/2019 13:00    1930
25/02/2019 13:02    25/02/2019 19:00    1972
26/02/2019 7:00 26/02/2019 19:00    1930
27/02/2019 7:00 27/02/2019 19:00    1930
28/02/2019 7:00 28/02/2019 14:45    1943
28/02/2019 8:27 28/02/2019 19:03    1915
28/02/2019 14:26    28/02/2019 19:01    1913
1/03/2019 7:00  1/03/2019 13:00 1913
1/03/2019 7:00  1/03/2019 9:00  3316
1/03/2019 9:32  1/03/2019 13:40 1913
1/03/2019 11:49 1/03/2019 11:49 3316
1/03/2019 13:02 1/03/2019 19:00 1960
1/03/2019 13:48 1/03/2019 19:00 1924
2/03/2019 7:00  2/03/2019 19:09 1960
4/03/2019 7:00  4/03/2019 19:00 1972
5/03/2019 7:00  5/03/2019 14:30 1935
5/03/2019 8:45  5/03/2019 19:00 1924
5/03/2019 14:17 5/03/2019 19:00 1924
6/03/2019 7:00  6/03/2019 18:00 1924
6/03/2019 7:52  6/03/2019 18:00 1950
6/03/2019 17:31 6/03/2019 19:00 1973
6/03/2019 18:00 6/03/2019 19:00 1930
7/03/2019 6:27  7/03/2019 17:10 1973
7/03/2019 17:10 7/03/2019 19:00 1972
8/03/2019 7:00  8/03/2019 19:00 1960
8/03/2019 7:00  8/03/2019 19:00 1915
9/03/2019 7:00  9/03/2019 7:00  1960
9/03/2019 7:00  9/03/2019 15:45 1915
11/03/2019 7:00 11/03/2019 19:00    1978
12/03/2019 7:00 12/03/2019 13:53    1978
12/03/2019 13:44    12/03/2019 19:00    1978
13/03/2019 7:00 13/03/2019 19:00    1928
14/03/2019 7:00 14/03/2019 17:10    1960
14/03/2019 17:10    14/03/2019 19:00    1915
15/03/2019 7:00 15/03/2019 10:20    1960
15/03/2019 10:02    15/03/2019 19:00    1940
16/03/2019 7:00 16/03/2019 19:00    1940
18/03/2019 7:00 18/03/2019 9:00 1978
18/03/2019 8:36 18/03/2019 11:57    1978
18/03/2019 11:44    18/03/2019 19:00    1978
19/03/2019 7:00 19/03/2019 9:40 1978
19/03/2019 9:34 19/03/2019 19:00    1928
20/03/2019 7:00 20/03/2019 19:00    1928
20/03/2019 9:29 20/03/2019 9:50 1960
20/03/2019 9:50 20/03/2019 12:00    1940
20/03/2019 11:53    20/03/2019 14:30    1958
20/03/2019 14:31    20/03/2019 19:00    1960
21/03/2019 7:00 21/03/2019 19:00    1928
22/03/2019 7:00 22/03/2019 19:00    1962
22/03/2019 8:37 22/03/2019 19:00    1960
22/03/2019 14:33    22/03/2019 14:40    1935
22/03/2019 14:41    22/03/2019 16:50    1930
23/03/2019 7:00 23/03/2019 19:00    1962
23/03/2019 7:00 23/03/2019 9:30 1960
23/03/2019 9:37 23/03/2019 13:00    1960
23/03/2019 13:00    23/03/2019 13:59    1958
23/03/2019 14:01    23/03/2019 19:00    1943
25/03/2019 7:00 25/03/2019 19:00    1928
26/03/2019 7:00 26/03/2019 15:30    1928
26/03/2019 15:24    26/03/2019 19:00    1928
27/03/2019 7:00 27/03/2019 19:00    1926
27/03/2019 9:24 27/03/2019 10:20    1960
27/03/2019 10:25    27/03/2019 19:00    1950
28/03/2019 7:00 28/03/2019 19:00    1980
28/03/2019 11:59    28/03/2019 19:00    1950
29/03/2019 7:00 29/03/2019 17:30    1962
29/03/2019 7:04 29/03/2019 9:30 1980
29/03/2019 7:00 29/03/2019 16:25    1910
29/03/2019 16:27    29/03/2019 19:00    1910
29/03/2019 16:53    29/03/2019 19:00    1980
30/03/2019 7:00 30/03/2019 15:30    1980
30/03/2019 15:21    30/03/2019 19:00    1915
1/04/2019 7:00  1/04/2019 19:00 1926
2/04/2019 7:00  2/04/2019 11:15 1926
2/04/2019 10:55 2/04/2019 19:00 1978
3/04/2019 7:00  3/04/2019 19:00 1978
4/04/2019 7:00  4/04/2019 10:15 1954
4/04/2019 9:56  4/04/2019 13:46 1987
4/04/2019 13:46 4/04/2019 19:00 1987
5/04/2019 7:00  5/04/2019 19:00 1987
5/04/2019 10:26 5/04/2019 19:00 1926
6/04/2019 7:00  6/04/2019 19:00 1987
6/04/2019 7:00  6/04/2019 9:15  1935
6/04/2019 9:22  6/04/2019 9:30  1973
6/04/2019 9:35  6/04/2019 14:30 1972
6/04/2019 14:28 6/04/2019 19:00 1924
8/04/2019 7:00  8/04/2019 17:15 1972
8/04/2019 16:53 8/04/2019 18:30 1907
9/04/2019 7:00  9/04/2019 16:30 1932
9/04/2019 16:19 9/04/2019 19:00 1932
10/04/2019 7:00 10/04/2019 10:00    1932
10/04/2019 9:41 10/04/2019 18:15    1978
10/04/2019 17:41    10/04/2019 19:00    1970
ericOss
  • 181
  • 6
S-Can
  • 23
  • 4
  • Can you share the output of `dput(a)` and `dput(b)` instead of the raw data? or maybe the code used to get the two dataframes? and what exactly do you mean by `it is possible that some of the observations then fall within multiple intervals. Can it be handled so that those cases raise an NA id or something? ` ? if an observation is in two intervals how would you choose which one you want? – DS_UNI Apr 11 '19 at 11:49
  • Thanks for the tip, I wasn't sure how to post the raw data, had never heard of dput(). I will post the proper raw data below. Ideally if one observation falls in two different intervals I would just throw that observation out and forget about it. – S-Can Apr 12 '19 at 21:38

3 Answers3

0

The answer of this question has a pretty cool trick to do something like this: 

library(dplyr)
A %>% 
  # add dummy variable to first table
  mutate(dummy=TRUE) %>%
  # join with the second table after adding a dummy variable to it too
  left_join(B %>% mutate(dummy=TRUE)) %>%
  # filter by the conditions you want
  filter(dt >= start, dt <= end) %>%
  # remove the dummy column or choose whatever columns you want 
  select(-dummy)

Note that the raw data posted in the question doesn't have seconds in the date_time columns unlike the examples you show in your attempts, and tableA contains some duplicated rows(see for example when dt == '26/02/2019 11:01')

Raw data after converting date_time columns to POSIXct

Table A:

A <- structure(list(WEIGHT = c(894L, 946L, 907L, 921L, 866L, 1021L, 
                               1022L, 1032L, 979L, 1097L, 1094L, 880L, 866L, 876L, 876L, 861L, 
                               869L, 890L, 759L, 813L, 845L, 989L, 894L, 963L, 928L, 822L, 906L, 
                               994L, 1011L, 1004L, 996L, 883L, 942L, 950L, 1018L, 1003L, 1021L, 
                               984L, 942L, 1067L, 1034L, 1087L, 1040L, 1090L, 1029L, 1023L, 
                               1010L, 1051L, 988L, 1100L, 985L, 924L, 988L, 1001L, 994L, 958L, 
                               960L, 990L, 995L, 928L, 925L, 1052L, 1034L, 1031L, 944L, 1038L, 
                               1056L, 1050L, 958L, 1007L, 985L, 978L, 1031L, 976L, 1063L, 965L, 
                               1050L, 894L, 830L, 920L, 966L, 992L, 881L, 823L, 1046L, 913L, 
                               885L, 944L, 938L, 937L, 937L, 946L, 985L, 942L, 1098L, 1074L, 
                               1037L, 1032L, 1073L, 1054L, 996L, 1081L, 1010L, 980L, 1044L, 
                               1009L, 963L, 1047L, 1114L, 1110L, 1009L, 998L, 1076L, 1042L, 
                               1011L, 1068L, 1061L, 1018L, 1063L, 1093L, 1108L, 1083L, 1109L, 
                               1057L, 1066L, 1043L, 1054L, 1018L, 1048L, 1045L, 1065L, 1066L, 
                               1052L, 1056L, 1042L, 1039L, 954L, 1015L, 1009L, 1026L, 991L, 
                               1050L, 1050L, 934L, 892L, 1029L, 1020L, 1060L, 1007L, 989L, 1006L, 
                               993L, 1048L, 1025L, 1046L, 988L, 1024L, 1076L, 963L, 1122L, 1019L, 
                               1014L, 907L, 1033L, 989L, 1010L, 1015L, 979L, 1018L, 1054L, 937L, 
                               918L, 1054L, 977L, 1028L, 942L, 1009L, 927L), 
                    dt = structure(c(1545133860, 
                                     1545133920, 1550145180, 1551091920, 1551091980, 1551118020, 1551118140, 
                                     1551118320, 1551118560, 1551118740, 1551118980, 1551178680, 1551178800, 
                                     1551178860, 1551178860, 1551179040, 1551179160, 1551179220, 1551179400, 
                                     1551179460, 1551180600, 1551183300, 1551193200, 1551193260, 1551193380, 
                                     1551193860, 1551194040, 1551195120, 1551195240, 1551200340, 1551200460, 
                                     1551200580, 1551200700, 1551200820, 1551202020, 1551346440, 1551346680, 
                                     1551346920, 1551347100, 1551347400, 1551347580, 1551347760, 1551348000, 
                                     1551348600, 1551349860, 1551350160, 1551350280, 1551350460, 1551350520, 
                                     1551350580, 1551439260, 1551439320, 1551439620, 1551439800, 1551440100, 
                                     1551440760, 1551440880, 1551441060, 1551441120, 1551446040, 1551446160, 
                                     1551446280, 1551446820, 1551446880, 1551448560, 1551448680, 1551450720, 
                                     1551458700, 1551461580, 1552573140, 1552573860, 1552574100, 1552575360, 
                                     1552575660, 1552575780, 1552575960, 1552576020, 1552576260, 1553512440, 
                                     1553512560, 1553512680, 1553512800, 1553512860, 1553512920, 1553512980, 
                                     1553513040, 1553513160, 1553513280, 1553513460, 1553513760, 1553513760, 
                                     1553514000, 1553514060, 1553514300, 1553669880, 1553669940, 1553670120, 
                                     1553670180, 1553670480, 1553670540, 1553670660, 1553670720, 1553671800, 
                                     1553672280, 1553673240, 1553673480, 1553674020, 1553674200, 1553674800, 
                                     1553675040, 1553675520, 1553687340, 1553708880, 1553709060, 1553709180, 
                                     1553709240, 1553709480, 1553709540, 1553709600, 1553709600, 1553709660, 
                                     1553709780, 1553709900, 1553709960, 1553710020, 1553710080, 1553710140, 
                                     1553710260, 1553710320, 1553710380, 1553710680, 1553710740, 1554472200, 
                                     1554568980, 1554568980, 1554569340, 1554569340, 1554569520, 1554569520, 
                                     1554569700, 1554569760, 1554570780, 1554570780, 1554570840, 1554571380, 
                                     1554571560, 1554571740, 1554571920, 1554572100, 1554572280, 1554572460, 
                                     1554572700, 1554573120, 1554573420, 1554574860, 1554574920, 1554707820, 
                                     1554707880, 1554707940, 1554708000, 1554708060, 1554708120, 1554708120, 
                                     1554708180, 1554708300, 1554708360, 1554708420, 1554708480, 1554708480, 
                                     1554708540, 1554708660, 1554708720, 1554708780, 1554708840, 1554708900, 
                                     1554708960, 1554709080, 1554709140), class = c("POSIXct", "POSIXt"
                                     ), tzone = "UTC")), row.names = c(NA, -178L), class = "data.frame")

Table B:

B <- structure(list(start = structure(c(1542106440, 1542182400, 1542873600, 
                                   1543496640, 1543564800, 1543824000, 1543910400, 1543996800, 1544083200, 
                                   1544089620, 1544428800, 1544601600, 1544688000, 1544774400, 1544786340, 
                                   1545033600, 1545054300, 1545057660, 1545120000, 1545206400, 1545228420, 
                                   1545379200, 1546848000, 1546934400, 1547020800, 1547107200, 1547133780, 
                                   1547193600, 1547199420, 1547452800, 1547466240, 1547467800, 1547470800, 
                                   1547539200, 1547625600, 1547712000, 1547798400, 1547798400, 1548057600, 
                                   1548144000, 1548230400, 1548316800, 1548336660, 1548403200, 1548411300, 
                                   1548748800, 1548766800, 1548835200, 1548921600, 1549008000, 1549094400, 
                                   1549263600, 1549350000, 1549522800, 1549537740, 1549552500, 1549609200, 
                                   1549612740, 1549695600, 1549707360, 1549712700, 1549868400, 1549868400, 
                                   1549880220, 1549884180, 1549892700, 1549954800, 1549954860, 1549971480, 
                                   1549989000, 1550041200, 1550041200, 1550127600, 1550156040, 1550214000, 
                                   1550242800, 1550300400, 1550337720, 1550473200, 1550484720, 1550501340, 
                                   1550559600, 1550566440, 1550569020, 1550646000, 1550732400, 1550742780, 
                                   1550743560, 1550770920, 1550818800, 1550818800, 1550823660, 1550844000, 
                                   1550905200, 1550905920, 1550910060, 1550916120, 1550917200, 1551078000, 
                                   1551099720, 1551164400, 1551250800, 1551337200, 1551342420, 1551363960, 
                                   1551423600, 1551423600, 1551432720, 1551440940, 1551445320, 1551448080, 
                                   1551510000, 1551682800, 1551769200, 1551775500, 1551795420, 1551855600, 
                                   1551858720, 1551893460, 1551895200, 1551940020, 1551978600, 1552028400, 
                                   1552028400, 1552114800, 1552114800, 1552287600, 1552374000, 1552398240, 
                                   1552460400, 1552546800, 1552583400, 1552633200, 1552644120, 1552719600, 
                                   1552892400, 1552898160, 1552909440, 1552978800, 1552988040, 1553065200, 
                                   1553074140, 1553075400, 1553082780, 1553092260, 1553151600, 1553238000, 
                                   1553243820, 1553265180, 1553265660, 1553324400, 1553324400, 1553333820, 
                                   1553346000, 1553349660, 1553497200, 1553583600, 1553613840, 1553670000, 
                                   1553678640, 1553682300, 1553756400, 1553774340, 1553842800, 1553843040, 
                                   1553842800, 1553876820, 1553878380, 1553929200, 1553959260, 1554102000, 
                                   1554188400, 1554202500, 1554274800, 1554361200, 1554371760, 1554385560, 
                                   1554447600, 1554459960, 1554534000, 1554534000, 1554542520, 1554543300, 
                                   1554560880, 1554706800, 1554742380, 1554793200, 1554826740, 1554879600, 
                                   1554889260, 1554918060), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
               end = structure(c(1542126600, 1542213000, 1542904200, 1543509000, 
                                 1543595400, 1543854600, 1543941000, 1544027400, 1544089620, 
                                 1544106600, 1544459400, 1544632200, 1544718780, 1544787000, 
                                 1544805000, 1545054300, 1545057900, 1545064200, 1545150600, 
                                 1545228000, 1545237000, 1545409800, 1546878600, 1546965000, 
                                 1547051400, 1547134200, 1547137800, 1547200200, 1547224260, 
                                 1547466300, 1547470800, 1547471400, 1547483400, 1547569800, 
                                 1547656200, 1547742600, 1547799420, 1547829000, 1548088200, 
                                 1548174600, 1548261000, 1548336600, 1548347400, 1548411300, 
                                 1548433800, 1548756900, 1548779400, 1548865800, 1548952200, 
                                 1549038600, 1549125000, 1549306800, 1549393440, 1549538100, 
                                 1549553100, 1549566000, 1549614600, 1549652400, 1549738920, 
                                 1549712700, 1549738800, 1549893660, 1549880400, 1549911600, 
                                 1549885200, 1549911600, 1549972800, 1549989000, 1549998300, 
                                 1549998360, 1550084460, 1550050500, 1550156400, 1550170800, 
                                 1550242800, 1550257200, 1550338200, 1550343600, 1550485800, 
                                 1550503800, 1550516400, 1550602800, 1550568900, 1550569200, 
                                 1550689200, 1550776020, 1550743620, 1550770800, 1550775600, 
                                 1550862300, 1550823300, 1550842800, 1550862000, 1550916000, 
                                 1550910000, 1550916000, 1550948640, 1550919600, 1551099600, 
                                 1551121200, 1551207600, 1551294000, 1551365100, 1551380580, 
                                 1551380460, 1551445200, 1551430800, 1551447600, 1551440940, 
                                 1551466800, 1551466800, 1551553740, 1551726000, 1551796200, 
                                 1551812400, 1551812400, 1551895200, 1551895200, 1551898800, 
                                 1551898800, 1551978600, 1551985200, 1552071600, 1552071600, 
                                 1552114800, 1552146300, 1552330800, 1552398780, 1552417200, 
                                 1552503600, 1552583400, 1552590000, 1552645200, 1552676400, 
                                 1552762800, 1552899600, 1552910220, 1552935600, 1552988400, 
                                 1553022000, 1553108400, 1553075400, 1553083200, 1553092200, 
                                 1553108400, 1553194800, 1553281200, 1553281200, 1553265600, 
                                 1553273400, 1553367600, 1553333400, 1553346000, 1553349540, 
                                 1553367600, 1553540400, 1553614200, 1553626800, 1553713200, 
                                 1553682000, 1553713200, 1553799600, 1553799600, 1553880600, 
                                 1553851800, 1553876700, 1553886000, 1553886000, 1553959800, 
                                 1553972400, 1554145200, 1554203700, 1554231600, 1554318000, 
                                 1554372900, 1554385560, 1554404400, 1554490800, 1554490800, 
                                 1554577200, 1554542100, 1554543000, 1554561000, 1554577200, 
                                 1554743700, 1554748200, 1554827400, 1554836400, 1554890400, 
                                 1554920100, 1554922800), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
               id = c(1900L, 1900L, 1900L, 1903L, 1903L, 1903L, 1903L, 1903L, 
                      1900L, 1903L, 1905L, 1905L, 1901L, 1905L, 1904L, 1904L, 1904L, 
                      1902L, 1902L, 1902L, 1902L, 1905L, 1905L, 1905L, 1905L, 1905L, 
                      1906L, 1902L, 1902L, 1902L, 1909L, 1910L, 1910L, 1910L, 1907L, 
                      1907L, 1905L, 1907L, 1912L, 1912L, 1912L, 1912L, 1921L, 1921L, 
                      1907L, 1902L, 1950L, 1950L, 1920L, 1920L, 1915L, 1950L, 1950L, 
                      1920L, 1924L, 1924L, 1950L, 1950L, 1950L, 1950L, 1912L, 1950L, 
                      1912L, 1950L, 1915L, 1950L, 1950L, 1950L, 1958L, 1905L, 1924L, 
                      1905L, 1924L, 1972L, 1970L, 1958L, 1958L, 1958L, 1958L, 1955L, 
                      1913L, 1924L, 1958L, 1913L, 1924L, 1913L, 1915L, 1913L, 1915L, 
                      1935L, 1915L, 1913L, 1920L, 1953L, 1920L, 1972L, 1943L, 1958L, 
                      1930L, 1972L, 1930L, 1930L, 1943L, 1915L, 1913L, 1913L, 3316L, 
                      1913L, 3316L, 1960L, 1924L, 1960L, 1972L, 1935L, 1924L, 1924L, 
                      1924L, 1950L, 1973L, 1930L, 1973L, 1972L, 1960L, 1915L, 1960L, 
                      1915L, 1978L, 1978L, 1978L, 1928L, 1960L, 1915L, 1960L, 1940L, 
                      1940L, 1978L, 1978L, 1978L, 1978L, 1928L, 1928L, 1960L, 1940L, 
                      1958L, 1960L, 1928L, 1962L, 1960L, 1935L, 1930L, 1962L, 1960L, 
                      1960L, 1958L, 1943L, 1928L, 1928L, 1928L, 1926L, 1960L, 1950L, 
                      1980L, 1950L, 1962L, 1980L, 1910L, 1910L, 1980L, 1980L, 1915L, 
                      1926L, 1926L, 1978L, 1978L, 1954L, 1987L, 1987L, 1987L, 1926L, 
                      1987L, 1935L, 1973L, 1972L, 1924L, 1972L, 1907L, 1932L, 1932L, 
                      1932L, 1978L, 1970L)), row.names = c(NA, -191L), class = "data.frame")
DS_UNI
  • 2,600
  • 2
  • 11
  • 22
  • Thanks for this, and yes, I didn't even notice I had lost the seconds in the datetimes. I will post the proper raw data below. – S-Can Apr 12 '19 at 21:41
0

Everybody, it was the timezone. Your requests for the proper raw data with dput() brought up the timezones and I realised I had tableA set to the correct timezone, but tableB was set to a different timezone. All I had to do was lubridate::force_tz() to correct the issue, and both the sqldf and fuzzyjoin solutions worked just fine.

I should have thought of this before, especially when the only matches I was getting were slightly misaligned.

Thanks again for pointing me in a more precise direction.

S-Can
  • 23
  • 4
0

There are several problems:

  • questions to the SO R tag should show data in reproducible form and that is missing. We have provided it in that form in the Note.

  • the SQL statement should use a left join just in case there are A rows which correspond to no B row. We provide such code below. Note that the ORDER BY clause could be omitted if you don't need to guarantee that the output is in the same order as A or you could use ORDER BY b.rowid to order it in the same order as the rows of B.

    library(sqldf)

ans <- sqldf("SELECT a.*, b.id
  FROM A as a 
  LEFT JOIN B as b ON a.dt between b.start and b.end
  ORDER BY a.rowid")

Note

The data in reproducible form:

LinesA <- "WEIGHT  dt
894 18/12/2018 11:51
946 18/12/2018 11:52
907 14/02/2019 11:53
921 25/02/2019 10:52
866 25/02/2019 10:53
1021    25/02/2019 18:07
1022    25/02/2019 18:09
1032    25/02/2019 18:12
979 25/02/2019 18:16
1097    25/02/2019 18:19
1094    25/02/2019 18:23
880 26/02/2019 10:58
866 26/02/2019 11:00
876 26/02/2019 11:01
876 26/02/2019 11:01
861 26/02/2019 11:04
869 26/02/2019 11:06
890 26/02/2019 11:07
759 26/02/2019 11:10
813 26/02/2019 11:11
845 26/02/2019 11:30
989 26/02/2019 12:15
894 26/02/2019 15:00
963 26/02/2019 15:01
928 26/02/2019 15:03
822 26/02/2019 15:11
906 26/02/2019 15:14
994 26/02/2019 15:32
1011    26/02/2019 15:34
1004    26/02/2019 16:59
996 26/02/2019 17:01
883 26/02/2019 17:03
942 26/02/2019 17:05
950 26/02/2019 17:07
1018    26/02/2019 17:27
1003    28/02/2019 9:34
1021    28/02/2019 9:38
984 28/02/2019 9:42
942 28/02/2019 9:45
1067    28/02/2019 9:50
1034    28/02/2019 9:53
1087    28/02/2019 9:56
1040    28/02/2019 10:00
1090    28/02/2019 10:10
1029    28/02/2019 10:31
1023    28/02/2019 10:36
1010    28/02/2019 10:38
1051    28/02/2019 10:41
988 28/02/2019 10:42
1100    28/02/2019 10:43
985 1/03/2019 11:21
924 1/03/2019 11:22
988 1/03/2019 11:27
1001    1/03/2019 11:30
994 1/03/2019 11:35
958 1/03/2019 11:46
960 1/03/2019 11:48
990 1/03/2019 11:51
995 1/03/2019 11:52
928 1/03/2019 13:14
925 1/03/2019 13:16
1052    1/03/2019 13:18
1034    1/03/2019 13:27
1031    1/03/2019 13:28
944 1/03/2019 13:56
1038    1/03/2019 13:58
1056    1/03/2019 14:32
1050    1/03/2019 16:45
958 1/03/2019 17:33
1007    14/03/2019 14:19
985 14/03/2019 14:31
978 14/03/2019 14:35
1031    14/03/2019 14:56
976 14/03/2019 15:01
1063    14/03/2019 15:03
965 14/03/2019 15:06
1050    14/03/2019 15:07
894 14/03/2019 15:11
830 25/03/2019 11:14
920 25/03/2019 11:16
966 25/03/2019 11:18
992 25/03/2019 11:20
881 25/03/2019 11:21
823 25/03/2019 11:22
1046    25/03/2019 11:23
913 25/03/2019 11:24
885 25/03/2019 11:26
944 25/03/2019 11:28
938 25/03/2019 11:31
937 25/03/2019 11:36
937 25/03/2019 11:36
946 25/03/2019 11:40
985 25/03/2019 11:41
942 25/03/2019 11:45
1098    27/03/2019 6:58
1074    27/03/2019 6:59
1037    27/03/2019 7:02
1032    27/03/2019 7:03
1073    27/03/2019 7:08
1054    27/03/2019 7:09
996 27/03/2019 7:11
1081    27/03/2019 7:12
1010    27/03/2019 7:30
980 27/03/2019 7:38
1044    27/03/2019 7:54
1009    27/03/2019 7:58
963 27/03/2019 8:07
1047    27/03/2019 8:10
1114    27/03/2019 8:20
1110    27/03/2019 8:24
1009    27/03/2019 8:32
998 27/03/2019 11:49
1076    27/03/2019 17:48
1042    27/03/2019 17:51
1011    27/03/2019 17:53
1068    27/03/2019 17:54
1061    27/03/2019 17:58
1018    27/03/2019 17:59
1063    27/03/2019 18:00
1093    27/03/2019 18:00
1108    27/03/2019 18:01
1083    27/03/2019 18:03
1109    27/03/2019 18:05
1057    27/03/2019 18:06
1066    27/03/2019 18:07
1043    27/03/2019 18:08
1054    27/03/2019 18:09
1018    27/03/2019 18:11
1048    27/03/2019 18:12
1045    27/03/2019 18:13
1065    27/03/2019 18:18
1066    27/03/2019 18:19
1052    5/04/2019 13:50
1056    6/04/2019 16:43
1042    6/04/2019 16:43
1039    6/04/2019 16:49
954 6/04/2019 16:49
1015    6/04/2019 16:52
1009    6/04/2019 16:52
1026    6/04/2019 16:55
991 6/04/2019 16:56
1050    6/04/2019 17:13
1050    6/04/2019 17:13
934 6/04/2019 17:14
892 6/04/2019 17:23
1029    6/04/2019 17:26
1020    6/04/2019 17:29
1060    6/04/2019 17:32
1007    6/04/2019 17:35
989 6/04/2019 17:38
1006    6/04/2019 17:41
993 6/04/2019 17:45
1048    6/04/2019 17:52
1025    6/04/2019 17:57
1046    6/04/2019 18:21
988 6/04/2019 18:22
1024    8/04/2019 7:17
1076    8/04/2019 7:18
963 8/04/2019 7:19
1122    8/04/2019 7:20
1019    8/04/2019 7:21
1014    8/04/2019 7:22
907 8/04/2019 7:22
1033    8/04/2019 7:23
989 8/04/2019 7:25
1010    8/04/2019 7:26
1015    8/04/2019 7:27
979 8/04/2019 7:28
1018    8/04/2019 7:28
1054    8/04/2019 7:29
937 8/04/2019 7:31
918 8/04/2019 7:32
1054    8/04/2019 7:33
977 8/04/2019 7:34
1028    8/04/2019 7:35
942 8/04/2019 7:36
1009    8/04/2019 7:38
927 8/04/2019 7:39"

LinesB <- "start   end id
13/11/2018 10:54    13/11/2018 16:30    1900
14/11/2018 8:00 14/11/2018 16:30    1900
22/11/2018 8:00 22/11/2018 16:30    1900
29/11/2018 13:04    29/11/2018 16:30    1903
30/11/2018 8:00 30/11/2018 16:30    1903
3/12/2018 8:00  3/12/2018 16:30 1903
4/12/2018 8:00  4/12/2018 16:30 1903
5/12/2018 8:00  5/12/2018 16:30 1903
6/12/2018 8:00  6/12/2018 9:47  1900
6/12/2018 9:47  6/12/2018 14:30 1903
10/12/2018 8:00 10/12/2018 16:30    1905
12/12/2018 8:00 12/12/2018 16:30    1905
13/12/2018 8:00 13/12/2018 16:33    1901
14/12/2018 8:00 14/12/2018 11:30    1905
14/12/2018 11:19    14/12/2018 16:30    1904
17/12/2018 8:00 17/12/2018 13:45    1904
17/12/2018 13:45    17/12/2018 14:45    1904
17/12/2018 14:41    17/12/2018 16:30    1902
18/12/2018 8:00 18/12/2018 16:30    1902
19/12/2018 8:00 19/12/2018 14:00    1902
19/12/2018 14:07    19/12/2018 16:30    1902
21/12/2018 8:00 21/12/2018 16:30    1905
7/01/2019 8:00  7/01/2019 16:30 1905
8/01/2019 8:00  8/01/2019 16:30 1905
9/01/2019 8:00  9/01/2019 16:30 1905
10/01/2019 8:00 10/01/2019 15:30    1905
10/01/2019 15:23    10/01/2019 16:30    1906
11/01/2019 8:00 11/01/2019 9:50 1902
11/01/2019 9:37 11/01/2019 16:31    1902
14/01/2019 8:00 14/01/2019 11:45    1902
14/01/2019 11:44    14/01/2019 13:00    1909
14/01/2019 12:10    14/01/2019 13:10    1910
14/01/2019 13:00    14/01/2019 16:30    1910
15/01/2019 8:00 15/01/2019 16:30    1910
16/01/2019 8:00 16/01/2019 16:30    1907
17/01/2019 8:00 17/01/2019 16:30    1907
18/01/2019 8:00 18/01/2019 8:17 1905
18/01/2019 8:00 18/01/2019 16:30    1907
21/01/2019 8:00 21/01/2019 16:30    1912
22/01/2019 8:00 22/01/2019 16:30    1912
23/01/2019 8:00 23/01/2019 16:30    1912
24/01/2019 8:00 24/01/2019 13:30    1912
24/01/2019 13:31    24/01/2019 16:30    1921
25/01/2019 8:00 25/01/2019 10:15    1921
25/01/2019 10:15    25/01/2019 16:30    1907
29/01/2019 8:00 29/01/2019 10:15    1902
29/01/2019 13:00    29/01/2019 16:30    1950
30/01/2019 8:00 30/01/2019 16:30    1950
31/01/2019 8:00 31/01/2019 16:30    1920
1/02/2019 8:00  1/02/2019 16:30 1920
2/02/2019 8:00  2/02/2019 16:30 1915
4/02/2019 7:00  4/02/2019 19:00 1950
5/02/2019 7:00  5/02/2019 19:04 1950
7/02/2019 7:00  7/02/2019 11:15 1920
7/02/2019 11:09 7/02/2019 15:25 1924
7/02/2019 15:15 7/02/2019 19:00 1924
8/02/2019 7:00  8/02/2019 8:30  1950
8/02/2019 7:59  8/02/2019 19:00 1950
9/02/2019 7:00  9/02/2019 19:02 1950
9/02/2019 10:16 9/02/2019 11:45 1950
9/02/2019 11:45 9/02/2019 19:00 1912
11/02/2019 7:00 11/02/2019 14:01    1950
11/02/2019 7:00 11/02/2019 10:20    1912
11/02/2019 10:17    11/02/2019 19:00    1950
11/02/2019 11:23    11/02/2019 11:40    1915
11/02/2019 13:45    11/02/2019 19:00    1950
12/02/2019 7:00 12/02/2019 12:00    1950
12/02/2019 7:01 12/02/2019 16:30    1950
12/02/2019 11:38    12/02/2019 19:05    1958
12/02/2019 16:30    12/02/2019 19:06    1905
13/02/2019 7:00 13/02/2019 19:01    1924
13/02/2019 7:00 13/02/2019 9:35 1905
14/02/2019 7:00 14/02/2019 15:00    1924
14/02/2019 14:54    14/02/2019 19:00    1972
15/02/2019 7:00 15/02/2019 15:00    1970
15/02/2019 15:00    15/02/2019 19:00    1958
16/02/2019 7:00 16/02/2019 17:30    1958
16/02/2019 17:22    16/02/2019 19:00    1958
18/02/2019 7:00 18/02/2019 10:30    1958
18/02/2019 10:12    18/02/2019 15:30    1955
18/02/2019 14:49    18/02/2019 19:00    1913
19/02/2019 7:00 19/02/2019 19:00    1924
19/02/2019 8:54 19/02/2019 9:35 1958
19/02/2019 9:37 19/02/2019 9:40 1913
20/02/2019 7:00 20/02/2019 19:00    1924
21/02/2019 7:00 21/02/2019 19:07    1913
21/02/2019 9:53 21/02/2019 10:07    1915
21/02/2019 10:06    21/02/2019 17:40    1913
21/02/2019 17:42    21/02/2019 19:00    1915
22/02/2019 7:00 22/02/2019 19:05    1935
22/02/2019 7:00 22/02/2019 8:15 1915
22/02/2019 8:21 22/02/2019 13:40    1913
22/02/2019 14:00    22/02/2019 19:00    1920
23/02/2019 7:00 23/02/2019 10:00    1953
23/02/2019 7:12 23/02/2019 8:20 1920
23/02/2019 8:21 23/02/2019 10:00    1972
23/02/2019 10:02    23/02/2019 19:04    1943
23/02/2019 10:20    23/02/2019 11:00    1958
25/02/2019 7:00 25/02/2019 13:00    1930
25/02/2019 13:02    25/02/2019 19:00    1972
26/02/2019 7:00 26/02/2019 19:00    1930
27/02/2019 7:00 27/02/2019 19:00    1930
28/02/2019 7:00 28/02/2019 14:45    1943
28/02/2019 8:27 28/02/2019 19:03    1915
28/02/2019 14:26    28/02/2019 19:01    1913
1/03/2019 7:00  1/03/2019 13:00 1913
1/03/2019 7:00  1/03/2019 9:00  3316
1/03/2019 9:32  1/03/2019 13:40 1913
1/03/2019 11:49 1/03/2019 11:49 3316
1/03/2019 13:02 1/03/2019 19:00 1960
1/03/2019 13:48 1/03/2019 19:00 1924
2/03/2019 7:00  2/03/2019 19:09 1960
4/03/2019 7:00  4/03/2019 19:00 1972
5/03/2019 7:00  5/03/2019 14:30 1935
5/03/2019 8:45  5/03/2019 19:00 1924
5/03/2019 14:17 5/03/2019 19:00 1924
6/03/2019 7:00  6/03/2019 18:00 1924
6/03/2019 7:52  6/03/2019 18:00 1950
6/03/2019 17:31 6/03/2019 19:00 1973
6/03/2019 18:00 6/03/2019 19:00 1930
7/03/2019 6:27  7/03/2019 17:10 1973
7/03/2019 17:10 7/03/2019 19:00 1972
8/03/2019 7:00  8/03/2019 19:00 1960
8/03/2019 7:00  8/03/2019 19:00 1915
9/03/2019 7:00  9/03/2019 7:00  1960
9/03/2019 7:00  9/03/2019 15:45 1915
11/03/2019 7:00 11/03/2019 19:00    1978
12/03/2019 7:00 12/03/2019 13:53    1978
12/03/2019 13:44    12/03/2019 19:00    1978
13/03/2019 7:00 13/03/2019 19:00    1928
14/03/2019 7:00 14/03/2019 17:10    1960
14/03/2019 17:10    14/03/2019 19:00    1915
15/03/2019 7:00 15/03/2019 10:20    1960
15/03/2019 10:02    15/03/2019 19:00    1940
16/03/2019 7:00 16/03/2019 19:00    1940
18/03/2019 7:00 18/03/2019 9:00 1978
18/03/2019 8:36 18/03/2019 11:57    1978
18/03/2019 11:44    18/03/2019 19:00    1978
19/03/2019 7:00 19/03/2019 9:40 1978
19/03/2019 9:34 19/03/2019 19:00    1928
20/03/2019 7:00 20/03/2019 19:00    1928
20/03/2019 9:29 20/03/2019 9:50 1960
20/03/2019 9:50 20/03/2019 12:00    1940
20/03/2019 11:53    20/03/2019 14:30    1958
20/03/2019 14:31    20/03/2019 19:00    1960
21/03/2019 7:00 21/03/2019 19:00    1928
22/03/2019 7:00 22/03/2019 19:00    1962
22/03/2019 8:37 22/03/2019 19:00    1960
22/03/2019 14:33    22/03/2019 14:40    1935
22/03/2019 14:41    22/03/2019 16:50    1930
23/03/2019 7:00 23/03/2019 19:00    1962
23/03/2019 7:00 23/03/2019 9:30 1960
23/03/2019 9:37 23/03/2019 13:00    1960
23/03/2019 13:00    23/03/2019 13:59    1958
23/03/2019 14:01    23/03/2019 19:00    1943
25/03/2019 7:00 25/03/2019 19:00    1928
26/03/2019 7:00 26/03/2019 15:30    1928
26/03/2019 15:24    26/03/2019 19:00    1928
27/03/2019 7:00 27/03/2019 19:00    1926
27/03/2019 9:24 27/03/2019 10:20    1960
27/03/2019 10:25    27/03/2019 19:00    1950
28/03/2019 7:00 28/03/2019 19:00    1980
28/03/2019 11:59    28/03/2019 19:00    1950
29/03/2019 7:00 29/03/2019 17:30    1962
29/03/2019 7:04 29/03/2019 9:30 1980
29/03/2019 7:00 29/03/2019 16:25    1910
29/03/2019 16:27    29/03/2019 19:00    1910
29/03/2019 16:53    29/03/2019 19:00    1980
30/03/2019 7:00 30/03/2019 15:30    1980
30/03/2019 15:21    30/03/2019 19:00    1915
1/04/2019 7:00  1/04/2019 19:00 1926
2/04/2019 7:00  2/04/2019 11:15 1926
2/04/2019 10:55 2/04/2019 19:00 1978
3/04/2019 7:00  3/04/2019 19:00 1978
4/04/2019 7:00  4/04/2019 10:15 1954
4/04/2019 9:56  4/04/2019 13:46 1987
4/04/2019 13:46 4/04/2019 19:00 1987
5/04/2019 7:00  5/04/2019 19:00 1987
5/04/2019 10:26 5/04/2019 19:00 1926
6/04/2019 7:00  6/04/2019 19:00 1987
6/04/2019 7:00  6/04/2019 9:15  1935
6/04/2019 9:22  6/04/2019 9:30  1973
6/04/2019 9:35  6/04/2019 14:30 1972
6/04/2019 14:28 6/04/2019 19:00 1924
8/04/2019 7:00  8/04/2019 17:15 1972
8/04/2019 16:53 8/04/2019 18:30 1907
9/04/2019 7:00  9/04/2019 16:30 1932
9/04/2019 16:19 9/04/2019 19:00 1932
10/04/2019 7:00 10/04/2019 10:00    1932
10/04/2019 9:41 10/04/2019 18:15    1978
10/04/2019 17:41    10/04/2019 19:00    1970"

fmt <- "%d/%m/%Y %H:%M"

A0 <- read.table(text = LinesA, skip = 1, strip.white = TRUE)
A <- data.frame(WEIGHT = A0$V1, dt = as.Date(paste(A0$V2, A0$V3), format = fmt))

B0 <- read.table(text = LinesB, skip = 1, strip.white = TRUE)
B <- data.frame(start = as.Date(paste(B0$V1, B0$V2), format = fmt), 
  end = as.Date(paste(B0$V3, B0$V4), format = fmt), id = B0$V5)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341