Compare identical columns of 2 df`s on the basis of a time window and create 3. df

General
1

Hello RStudio Community,

I am an R-noob and have a problem with which you can possibly help me. It concerns 2 dfs (df1 = 38 columns, df2 = 49 columns), which I have attached in form of a of 2 pic. In each of the 2 df`s there is a time specification (df1= 2nd column ,df2 = 13th column), the remaining columns consist of doubles with values >= 0. Each column of df1 belongs to a column of df2 (correspond to identical designation). I want R to go to the 3rd column (MT_19_2) of df1 and see if there is a value > 0. If yes, the script should remember the corresponding time and look in df2 if there is a value > 0 in the corresponding 14th column (also called MT_19_2) in a time window of +.-01 min. If yes, the complete row of df2 is to be noted in a new df3. If there are several values in the time window, only the closest one should be selected. After that, the script should check the lines below df1 to see if there is a value > 0 and do the same comparison again. The values already assigned from df2 should not be available for this comparison.

I hope you understand what I mean and can help me! Here are pics of df1, df2 and what df3 should look like:

df`s
df`s1222×1278 70.7 KB

Best wishes,
Luis

2

It would really help if we had some sample tata to work with.
A handy way to supply sample data is to use the dput() function. See ?dput. If you have a very large data set then something like head(dput(myfile), 100) will likely supply enough data for us to work with.

3

Hello jrkrideau,

thank you for your answer !!! I remove some culms and lines otherwise, the message would be too long. Here are the main the info. from both df `s.

Here is df1 :

structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59),
time = c(4.68, 6.34, 6.79, 8.16, 8.35, 8.49, 8.53, 8.54,
9.1, 9.34, 9.35, 9.44, 9.59, 9.91, 10.73, 10.91, 11.26, 11.46,
11.51, 11.85, 12.33, 12.36, 12.45, 12.65, 12.72, 12.83, 12.85,
13.78, 13.79, 13.9, 13.94, 14.04, 14.09, 14.31, 14.96, 14.99,
15.68, 16.73, 16.87, 16.87, 16.99, 17.06, 17.09, 17.18, 17.19,
17.21, 17.33, 17.33, 17.33, 17.5, 19.73, 20.9, 21.12, 21.65,
21.99, 22.6, 23.66, 23.71, 24.22), MT_19_2 = c(0, 1, 0, 0,
0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 1,
0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 1, 0, 0), MT_19_3 = c(0,
1, 0, 0, 0, 0, 1, 1, 0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1,
0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0,
0), MT_19_4 = c(0, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 0,
1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0,
1, 1, 0, 1, 1, 0, 0), MT_19_5 = c(0, 1, 0, 0, 0, 1, 1, 1,
1, 1, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1,
1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1,
0, 0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 0, 0), MT_19_6 = c(1, 1,
0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0,
0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0,
0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0),
MT_19_7 = c(0, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0,
0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0,
1, 0, 1, 1, 0, 0), MT_19_9 = c(0, 1, 0, 0, 0, 1, 1, 1, 0,
1, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0,
0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0)), row.names = c(NA, -59L
), class = c("tbl_df", "tbl", "data.frame"))

and df 2:

structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60,
61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72), Formula = c("C18H18O5S",
"C18H20O6S", "C17H20O6S", "C3H7NO4S", "C5H12O4S", "C4H9NO5S",
"C4H9NO4S", "C4H8O4S", "C2H5NO4S", "C7H14O4S", "C3H7NO4S", "C4H7NO4S",
"C5H9NO4S", "C3H6O4S", "C3H7NO5S", "C4H8O4S", "C6H10O5S", "C4H10O6S",
"C4H11NO4S", "C7H16O6S", "C6H14O6S", "C10H16O7S", "C5H6O4S",
"C8H16O6S", "C6H12O6S", "C9H14O7S", "C5H11NO5S", "C5H12O5S",
"C7H16O5S", "C6H14O6S", "C7H16O6S", "C5H12O6S", "C4H10O5S", "C4H10O6S",
"C9H14O7S", "C5H10O6S", "C7H12O6S", "C4H10O6S", "C4H10O7S", "C6H14O5S",
"C3H8O5S", "C6H12O5S", "C10H16O7S", "C4H10O7S", "C8H12O7S", "C4H10O5S",
"C6H12O6S", "C7H6O5S", "C8H16O6S", "C3H8O6S", "C5H12O6S", "C5H12O5S",
"C2H6O5S", "C4H8O5S", "C6H14O5S", "C5H10O5S", "C7H16O5S", "C4H10O5S",
"C3H8O5S", "C5H12O5S", "C7H14O5S", "C3H8O4S", "C7H16O5S", "C6H14O5S",
"C9H14O7S", "C5H11NO4S", "C7H14O5S", "C6H12O5S", "C2H6O4S", "C3H7NO5S",
"C8H14O5S", "C7H12O6S"), time = c(3.20783518571429, 3.3058845,
5.16564968333333, 6.11775966, 6.14019345, 6.148714175, 6.15885244545455,
6.27332518, 6.51033615, 6.56079755, 6.64471367, 6.74574776, 6.75789398,
6.80451824, 6.96669176, 7.0671294, 7.15878750833333, 7.2430858,
7.337671075, 7.37518743333333, 7.505341575, 7.56266235, 7.61408512222222,
7.62664083333333, 7.67808926666667, 7.71709731, 7.721307925,
7.7771714, 7.84045221111111, 7.88049126666667, 7.8946845875,
7.93216607, 7.93392067, 7.9579363625, 8.02879474, 8.0384297,
8.08051208181818, 8.08851843333333, 8.09120095, 8.1206633, 8.13128057,
8.16548933, 8.25074975, 8.27318165555555, 8.32513095, 8.33442142222222,
8.3350968, 8.3710613, 8.38630245, 8.40933818, 8.4256509, 8.44968824,
8.51020837272727, 8.5475135, 8.5493729375, 8.55494652, 8.66194003333333,
8.73876549, 8.74121635714286, 8.7666669, 8.78334723333333, 8.80910283,
8.8721785, 8.95693963333333, 8.98171381428572, 9.05258299285714,
9.05975823333333, 9.13528001111111, 9.13618929166667, 9.230259,
9.24780721666667, 9.26271359), MT_19_02 = c(0, 0, 0, 40921014.8873828,
13097332.383734, 22555440.5230714, 27393164.3336271, 0, 61707683.456742,
0, 100825067.571966, 23996391.4579917, 0, 135570473.008681, 0,
0, 44727056.0540707, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 114230544.061525, 0, 0, 0, 0, 0, 0, 14166579.9671584, 181241230.517958,
139007786.131602, 0, 0, 5478089.59689469, 14148724.838309, 0,
0, 0, 46339949.390312, 0, 35121833.3666845, 546486089.366755,
0, 8999722.57463175, 566659114.471751, 0, 78788825.482585, 0,
0, 0, 505233322.327622, 0, 0, 0, 0, 0, 140740069.778226, 227029586.637094,
0, 0, 0), MT_19_03 = c(0, 0, 0, 29714890.4951513, 8146720.13717684,
3214870.59465608, 13743262.6920483, 0, 41082616.2199964, 0, 97692415.9346813,
16625387.5438798, 0, 77546680.369805, 0, 0, 16519006.1476685,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 67652527.1833035,
0, 0, 0, 0, 0, 0, 6958642.14686492, 95384109.9542679, 86246742.5780535,
0, 0, 2670751.32332937, 8080375.00730327, 0, 0, 0, 0, 0, 15617113.11937,
302314176.353279, 0, 4606425.64343903, 272894031.239623, 0, 33243796.9321669,
0, 0, 0, 255781542.924426, 0, 0, 0, 0, 0, 61850087.6789327, 164588829.010743,
0, 0, 0), MT_19_04 = c(0, 0, 0, 15144592.2799419, 5254317.18339502,
0, 7732797.715469, 0, 32177926.2347124, 0, 40307312.5012659,
18785120.9731572, 0, 83892293.5458507, 0, 0, 18921989.3909306,
5275334.06365532, 0, 0, 0, 0, 7768704.24818661, 6390167.91532848,
0, 0, 0, 0, 0, 0, 0, 0, 53429035.1168837, 0, 0, 0, 0, 0, 0, 12102283.7733221,
155997179.118513, 122010631.322574, 0, 0, 0, 12562106.7430907,
8464006.89399269, 14804564.3381483, 0, 26165849.7385138, 0, 28828078.6956566,
479870015.302095, 0, 8780729.69101185, 498815802.04719, 0, 57228783.4254006,
0, 0, 0, 499986702.14322, 0, 0, 0, 0, 171240861.794702, 91591746.2795261,
249646598.345084, 4095091.86889109, 5005165.4397847, 0), MT_19_05 = c(0,
0, 0, 17238273.257216, 7927180.53347917, 0, 21040616.4729868,
0, 30305016.5567727, 0, 47817237.7764565, 12157255.8264659, 0,
66945672.1885907, 0, 0, 14467252.8359016, 4545334.33871705, 0,
0, 0, 0, 7104881.41391828, 9101361.07322816, 0, 0, 0, 8216769.55533557,
0, 0, 0, 0, 80876143.7091276, 0, 0, 0, 0, 0, 0, 8045542.77117793,
0, 179158483.966825, 0, 0, 0, 9306865.71953042, 8822438.39622112,
13089896.9263799, 0, 24476478.9306488, 0, 21540895.2068399, 468648886.861163,
0, 8387501.72707726, 723585509.017031, 0, 44977684.6401131, 27448174.8216997,
0, 0, 365380092.045645, 0, 0, 0, 0, 307329325.604008, 157698694.584444,
226230564.11256, 37505932.6833481, 0, 0), MT_19_06 = c(0, 0,
0, 31281597.6281464, 21990817.3263903, 0, 13220859.4314389, 0,
56650959.227831, 0, 95073669.6785226, 35876430.1988854, 0, 152305981.985153,
0, 0, 13253743.9716474, 12794494.8366698, 0, 0, 0, 0, 13996820.3370385,
8046288.83204314, 0, 0, 0, 0, 0, 0, 0, 0, 70735538.1881517, 0,
0, 0, 0, 0, 0, 10101152.6872459, 145892632.359074, 120724420.23238,
0, 0, 0, 11285493.8946831, 11993737.1250124, 15134384.9980447,
0, 52059113.6259326, 0, 0, 479339285.38645, 0, 7502671.30844373,
520127454.404959, 0, 66158572.7068049, 0, 0, 0, 300617114.052477,
0, 0, 0, 0, 171309005.847815, 122911178.858373, 262749108.731238,
7370018.21512647, 8011806.50201363, 0), MT_19_07 = c(0, 0, 0,
11154985.6802547, 4843497.49005086, 0, 8411666.59484592, 0, 23836686.1036455,
0, 20590333.6632542, 8579602.21553705, 0, 65970272.9623348, 0,
0, 3126985.93252623, 3559164.70676559, 0, 0, 0, 0, 0, 7576524.87154396,
0, 0, 0, 8619844.19917643, 0, 0, 0, 0, 58941790.8554014, 0, 0,
0, 0, 0, 0, 10232903.8186486, 126363697.805438, 0, 0, 0, 0, 8263571.17835424,
7729073.72356839, 0, 0, 26873616.4408984, 0, 21233890.8395023,
377388507.641626, 0, 5873236.33247528, 595290232.638844, 0, 43152679.6590801,
0, 0, 0, 244845403.332167, 0, 0, 0, 0, 123142463.077619, 83502281.6532513,
146424886.507455, 22533371.8733195, 1075773.51166033, 0), MT_19_09 = c(0,
0, 0, 168956182.4262, 9180777.77582912, 0, 62681238.2534124,
0, 112395752.116438, 0, 620140025.762412, 105794856.047077, 150150822.806724,
336794721.546787, 41336679.4434988, 37421709.143196, 0, 0, 0,
0, 18036534.6210427, 3800136.52603438, 0, 20137831.0931866, 0,
14890147.5008399, 0, 0, 0, 0, 0, 17374891.7161397, 104496605.50052,
0, 4518512.02977477, 0, 0, 0, 0, 11081421.5396069, 195950810.060938,
99775729.404847, 4383696.69767469, 0, 20081636.7485911, 22089580.8097382,
0, 16546901.2809536, 0, 65731294.9068595, 0, 44446981.4538852,
445424535.748512, 0, 9803287.97667728, 411679146.262993, 0, 102708907.554563,
0, 0, 0, 254539577.194814, 0, 0, 0, 0, 259334470.850421, 115318215.100194,
215722282.598193, 14520528.3978189, 0, 0)), row.names = c(NA,
-72L), class = c("tbl_df", "tbl", "data.frame"))

4

Does the following mean plus or minus 0.01 minutes?
"time window of +.-01 min"

5

oh, thats a typo I mean +- 0.1min. So I mean in a range of +0.1 min and -0.1 min

6

Probably not the most elegant answer.

df1 <- structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
                             13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
                             29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
                             45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59),
                      time = c(4.68, 6.34, 6.79, 8.16, 8.35, 8.49, 8.53, 8.54,
                               9.1, 9.34, 9.35, 9.44, 9.59, 9.91, 10.73, 10.91, 11.26, 11.46,
                               11.51, 11.85, 12.33, 12.36, 12.45, 12.65, 12.72, 12.83, 12.85,
                               13.78, 13.79, 13.9, 13.94, 14.04, 14.09, 14.31, 14.96, 14.99,
                               15.68, 16.73, 16.87, 16.87, 16.99, 17.06, 17.09, 17.18, 17.19,
                               17.21, 17.33, 17.33, 17.33, 17.5, 19.73, 20.9, 21.12, 21.65,
                               21.99, 22.6, 23.66, 23.71, 24.22), 
                      MT_19_2 = c(0, 1, 0, 0,
                                  0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 1,
                                  0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                  0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 1, 0, 0), 
                      MT_19_3 = c(0,
                                  1, 0, 0, 0, 0, 1, 1, 0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1,
                                  0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0,
                                  0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0,
                                  0), 
                      MT_19_4 = c(0, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 0,
                                  1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0,
                                  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0,
                                  1, 1, 0, 1, 1, 0, 0), 
                      MT_19_5 = c(0, 1, 0, 0, 0, 1, 1, 1,
                                  1, 1, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1,
                                  1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1,
                                  0, 0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 0, 0), 
                      MT_19_6 = c(1, 1,
                                  0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0,
                                  0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0,
                                  0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0),
                      MT_19_7 = c(0, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0,
                                  0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0,
                                  0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0,
                                  1, 0, 1, 1, 0, 0), 
                      MT_19_9 = c(0, 1, 0, 0, 0, 1, 1, 1, 0,
                                  1, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                  0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0,
                                  0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0)), row.names = c(NA, -59L
                                  ), class = c("tbl_df", "tbl", "data.frame"))

df2 <- structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
                             13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
                             29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
                             45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60,
                             61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72), 
                      Formula = c("C18H18O5S",
                                  "C18H20O6S", "C17H20O6S", "C3H7NO4S", "C5H12O4S", "C4H9NO5S",
                                  "C4H9NO4S", "C4H8O4S", "C2H5NO4S", "C7H14O4S", "C3H7NO4S", "C4H7NO4S",
                                  "C5H9NO4S", "C3H6O4S", "C3H7NO5S", "C4H8O4S", "C6H10O5S", "C4H10O6S",
                                  "C4H11NO4S", "C7H16O6S", "C6H14O6S", "C10H16O7S", "C5H6O4S",
                                  "C8H16O6S", "C6H12O6S", "C9H14O7S", "C5H11NO5S", "C5H12O5S",
                                  "C7H16O5S", "C6H14O6S", "C7H16O6S", "C5H12O6S", "C4H10O5S", "C4H10O6S",
                                  "C9H14O7S", "C5H10O6S", "C7H12O6S", "C4H10O6S", "C4H10O7S", "C6H14O5S",
                                  "C3H8O5S", "C6H12O5S", "C10H16O7S", "C4H10O7S", "C8H12O7S", "C4H10O5S",
                                  "C6H12O6S", "C7H6O5S", "C8H16O6S", "C3H8O6S", "C5H12O6S", "C5H12O5S",
                                  "C2H6O5S", "C4H8O5S", "C6H14O5S", "C5H10O5S", "C7H16O5S", "C4H10O5S",
                                  "C3H8O5S", "C5H12O5S", "C7H14O5S", "C3H8O4S", "C7H16O5S", "C6H14O5S",
                                  "C9H14O7S", "C5H11NO4S", "C7H14O5S", "C6H12O5S", "C2H6O4S", "C3H7NO5S",
                                  "C8H14O5S", "C7H12O6S"), 
                      time = c(3.20783518571429, 3.3058845,
                               5.16564968333333, 6.11775966, 6.14019345, 6.148714175, 6.15885244545455,
                               6.27332518, 6.51033615, 6.56079755, 6.64471367, 6.74574776, 6.75789398,
                               6.80451824, 6.96669176, 7.0671294, 7.15878750833333, 7.2430858,
                               7.337671075, 7.37518743333333, 7.505341575, 7.56266235, 7.61408512222222,
                               7.62664083333333, 7.67808926666667, 7.71709731, 7.721307925,
                               7.7771714, 7.84045221111111, 7.88049126666667, 7.8946845875,
                               7.93216607, 7.93392067, 7.9579363625, 8.02879474, 8.0384297,
                               8.08051208181818, 8.08851843333333, 8.09120095, 8.1206633, 8.13128057,
                               8.16548933, 8.25074975, 8.27318165555555, 8.32513095, 8.33442142222222,
                               8.3350968, 8.3710613, 8.38630245, 8.40933818, 8.4256509, 8.44968824,
                               8.51020837272727, 8.5475135, 8.5493729375, 8.55494652, 8.66194003333333,
                               8.73876549, 8.74121635714286, 8.7666669, 8.78334723333333, 8.80910283,
                               8.8721785, 8.95693963333333, 8.98171381428572, 9.05258299285714,
                               9.05975823333333, 9.13528001111111, 9.13618929166667, 9.230259,
                               9.24780721666667, 9.26271359), 
                      MT_19_02 = c(0, 0, 0, 40921014.8873828,
                                   13097332.383734, 22555440.5230714, 27393164.3336271, 0, 61707683.456742,
                                   0, 100825067.571966, 23996391.4579917, 0, 135570473.008681, 0,
                                   0, 44727056.0540707, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                   0, 114230544.061525, 0, 0, 0, 0, 0, 0, 14166579.9671584, 181241230.517958,
                                   139007786.131602, 0, 0, 5478089.59689469, 14148724.838309, 0,
                                   0, 0, 46339949.390312, 0, 35121833.3666845, 546486089.366755,
                                   0, 8999722.57463175, 566659114.471751, 0, 78788825.482585, 0,
                                   0, 0, 505233322.327622, 0, 0, 0, 0, 0, 140740069.778226, 227029586.637094,
                                   0, 0, 0), 
                      MT_19_03 = c(0, 0, 0, 29714890.4951513, 8146720.13717684,
                                   3214870.59465608, 13743262.6920483, 0, 41082616.2199964, 0, 97692415.9346813,
                                   16625387.5438798, 0, 77546680.369805, 0, 0, 16519006.1476685,
                                   0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 67652527.1833035,
                                   0, 0, 0, 0, 0, 0, 6958642.14686492, 95384109.9542679, 86246742.5780535,
                                   0, 0, 2670751.32332937, 8080375.00730327, 0, 0, 0, 0, 0, 15617113.11937,
                                   302314176.353279, 0, 4606425.64343903, 272894031.239623, 0, 33243796.9321669,
                                   0, 0, 0, 255781542.924426, 0, 0, 0, 0, 0, 61850087.6789327, 164588829.010743,
                                   0, 0, 0), 
                      MT_19_04 = c(0, 0, 0, 15144592.2799419, 5254317.18339502,
                                   0, 7732797.715469, 0, 32177926.2347124, 0, 40307312.5012659,
                                   18785120.9731572, 0, 83892293.5458507, 0, 0, 18921989.3909306,
                                   5275334.06365532, 0, 0, 0, 0, 7768704.24818661, 6390167.91532848,
                                   0, 0, 0, 0, 0, 0, 0, 0, 53429035.1168837, 0, 0, 0, 0, 0, 0, 12102283.7733221,
                                   155997179.118513, 122010631.322574, 0, 0, 0, 12562106.7430907,
                                   8464006.89399269, 14804564.3381483, 0, 26165849.7385138, 0, 28828078.6956566,
                                   479870015.302095, 0, 8780729.69101185, 498815802.04719, 0, 57228783.4254006,
                                   0, 0, 0, 499986702.14322, 0, 0, 0, 0, 171240861.794702, 91591746.2795261,
                                   249646598.345084, 4095091.86889109, 5005165.4397847, 0), 
                      MT_19_05 = c(0,
                                   0, 0, 17238273.257216, 7927180.53347917, 0, 21040616.4729868,
                                   0, 30305016.5567727, 0, 47817237.7764565, 12157255.8264659, 0,
                                   66945672.1885907, 0, 0, 14467252.8359016, 4545334.33871705, 0,
                                   0, 0, 0, 7104881.41391828, 9101361.07322816, 0, 0, 0, 8216769.55533557,
                                   0, 0, 0, 0, 80876143.7091276, 0, 0, 0, 0, 0, 0, 8045542.77117793,
                                   0, 179158483.966825, 0, 0, 0, 9306865.71953042, 8822438.39622112,
                                   13089896.9263799, 0, 24476478.9306488, 0, 21540895.2068399, 468648886.861163,
                                   0, 8387501.72707726, 723585509.017031, 0, 44977684.6401131, 27448174.8216997,
                                   0, 0, 365380092.045645, 0, 0, 0, 0, 307329325.604008, 157698694.584444,
                                   226230564.11256, 37505932.6833481, 0, 0), 
                      MT_19_06 = c(0, 0,
                                   0, 31281597.6281464, 21990817.3263903, 0, 13220859.4314389, 0,
                                   56650959.227831, 0, 95073669.6785226, 35876430.1988854, 0, 152305981.985153,
                                   0, 0, 13253743.9716474, 12794494.8366698, 0, 0, 0, 0, 13996820.3370385,
                                   8046288.83204314, 0, 0, 0, 0, 0, 0, 0, 0, 70735538.1881517, 0,
                                   0, 0, 0, 0, 0, 10101152.6872459, 145892632.359074, 120724420.23238,
                                   0, 0, 0, 11285493.8946831, 11993737.1250124, 15134384.9980447,
                                   0, 52059113.6259326, 0, 0, 479339285.38645, 0, 7502671.30844373,
                                   520127454.404959, 0, 66158572.7068049, 0, 0, 0, 300617114.052477,
                                   0, 0, 0, 0, 171309005.847815, 122911178.858373, 262749108.731238,
                                   7370018.21512647, 8011806.50201363, 0), 
                      MT_19_07 = c(0, 0, 0,
                                   11154985.6802547, 4843497.49005086, 0, 8411666.59484592, 0, 23836686.1036455,
                                   0, 20590333.6632542, 8579602.21553705, 0, 65970272.9623348, 0,
                                   0, 3126985.93252623, 3559164.70676559, 0, 0, 0, 0, 0, 7576524.87154396,
                                   0, 0, 0, 8619844.19917643, 0, 0, 0, 0, 58941790.8554014, 0, 0,
                                   0, 0, 0, 0, 10232903.8186486, 126363697.805438, 0, 0, 0, 0, 8263571.17835424,
                                   7729073.72356839, 0, 0, 26873616.4408984, 0, 21233890.8395023,
                                   377388507.641626, 0, 5873236.33247528, 595290232.638844, 0, 43152679.6590801,
                                   0, 0, 0, 244845403.332167, 0, 0, 0, 0, 123142463.077619, 83502281.6532513,
                                   146424886.507455, 22533371.8733195, 1075773.51166033, 0), 
                      MT_19_09 = c(0,
                                   0, 0, 168956182.4262, 9180777.77582912, 0, 62681238.2534124,
                                   0, 112395752.116438, 0, 620140025.762412, 105794856.047077, 150150822.806724,
                                   336794721.546787, 41336679.4434988, 37421709.143196, 0, 0, 0,
                                   0, 18036534.6210427, 3800136.52603438, 0, 20137831.0931866, 0,
                                   14890147.5008399, 0, 0, 0, 0, 0, 17374891.7161397, 104496605.50052,
                                   0, 4518512.02977477, 0, 0, 0, 0, 11081421.5396069, 195950810.060938,
                                   99775729.404847, 4383696.69767469, 0, 20081636.7485911, 22089580.8097382,
                                   0, 16546901.2809536, 0, 65731294.9068595, 0, 44446981.4538852,
                                   445424535.748512, 0, 9803287.97667728, 411679146.262993, 0, 102708907.554563,
                                   0, 0, 0, 254539577.194814, 0, 0, 0, 0, 259334470.850421, 115318215.100194,
                                   215722282.598193, 14520528.3978189, 0, 0)), row.names = c(NA,
                                                                                             -72L), class = c("tbl_df", "tbl", "data.frame"))
LittleDiff <- function(x,y) abs(x-y) <= 0.1 #return TRUE or FALSE
library(dplyr)
library(fuzzyjoin)

df1 <- df1 |> filter(MT_19_2 > 0) |> select(time, MT_19_2)
df2 <- df2 |> filter(MT_19_02 > 0)
df3List <- vector(mode = "list", length = nrow(df1))
for (i in 1:nrow(df1)) {
  tmp <- fuzzy_inner_join(df1[i,], df2, by="time", match_fun = LittleDiff)
  if (nrow(tmp > 0)) {
    df3List[[i]] <- tmp |> mutate(DIFF = abs(time.x-time.y)) |> 
      slice_min(DIFF)
    df2 <- anti_join(df2, df3List[[i]], by = c(time="time.y")) #filter out the row that matched
  }
  
}
df3 <- bind_rows(df3List)
print(df3)
#> # A tibble: 2 x 13
#>   time.x MT_19_2    ID Formula  time.y   MT_19_02   MT_19_03   MT_19_04 MT_19_05
#>    <dbl>   <dbl> <dbl> <chr>     <dbl>      <dbl>      <dbl>      <dbl>    <dbl>
#> 1   8.53       1    55 C6H14O5S   8.55   8999723.   4606426.   8780730.   8.39e6
#> 2   8.54       1    56 C5H10O5S   8.55 566659114. 272894031. 498815802.   7.24e8
#> # ... with 4 more variables: MT_19_06 <dbl>, MT_19_07 <dbl>, MT_19_09 <dbl>,
#> #   DIFF <dbl>

Created on 2022-01-29 by the reprex package (v2.0.1)

7

Hi FJCC,

thanks for your help, I'm still impressed! It only took you a couple of hours to solve the problem,
where it might have taken me a couple of weeks !

But if I understand the script correctly, there is something that needs to be changed, probably because I was a bit vague in the 2nd part of my message. Sorry for that ! As I understand your code, the first step is to see if there is a number >0 in the MT_19_02 column of df1. Then the script looks if there are values >0 in "ALL" other columns of df2 within the defined window, if so the rows are written to df3.

But I only want columns with identical names to be compared. That means values from the column: MT_19_02 of df1, should only be compared with the column MT_19_02 of df2. After writing out the corresponding values in df3, it should be checked whether values from the column: MT_19_03 of df1 > 0 and these should only be compared with column MT_19_03 of df2...etc. The pattern is to repeat up to and including column MT_19_42 (not included in the data I gave, as the message would be too long otherwise ). So the comparison regarding the times should only be done with columns of the same name. Can you write this in somehow? Besides that, the code is perfect! I hope my concern has been expressed more precisely.

8

This now seems very complicated but I will think about a way to do it.

The code I posted only considers MT_19_02. I keep all the other columns of df2 because, as I understand it, you want the entire row moved to df3.

I have a question about comparing all of the columns, MT_19_02, MT_19_03, etc. Let's say I start with the first row of df1 that has MT_19_02 > 0, I'll call it row Z, and I find multiple rows in df2 where the time value matches within 0.1 and MT_19_02 > 0. I pick the row in df2 whose time value is closest to the time in df1, copy it to df3, and I remove it from df2 so it cannot be matched again. Later, I start iterating through the rows of df1 again to process based on the MT_19_03 values. Row Z also has MT_19_03 > 0 and its time value is within 0.1 of multiple rows in df2, as we saw when looking at MT_19_02. We already removed from df2 the row that had been closest in time to row Z but now one of the remaining rows is the closest one. Do yo want that row of df2 to be copied to df3? The alternative, I think, is to remove a row from df1 as soon as it matches a row in df2.

I am wondering if we understood the higher level goal of this process whether there would be another way to get there. I suspect we might have a XY problem. It is easy to fall into that trap.
https://xyproblem.info/

9

Thanks for the link, that gave me a bit more clarity. You hit the mark with your explanation Later.

You said: " … We already removed from df2 the row that had been closest in time to row Z but now one of the remaining rows is the closest one. Do yo want that row of df2 to be copied to df3? The alternative, I think, is to remove a row from df1 as soon as it matches a row in df2."

I want that the line information from df2 are still available for a future matches.
I have also made a drawing with 4. examples that may provide a little more clarity. In the explanation I will refer to the ID designation of df1.

a) For ID 7 we have values >0 for all columns of df1 as well as for df2, accordingly for MT_19_02 to MT_19_04 all values of df2 should be visible in df3.

b) For ID 14, only for MT_19_03 the value in df1 is >0, accordingly only the value of this column is listed in df3.

c) For ID 14, no column value of df1 >0, accordingly it does not matter whether there is a number in df2 or not, the row will not appear in df3.

d) For ID 17, the first and last values are >1, so the corresponding values of df2 should also be found in df3.

df1+ df2 = df3
df1+ df2 = df31920×280 12.3 KB

10

I think this is close to what you want. The logic is:

  1. Filter out rows from df1 and df2 that have all zeros in MT_19_2:MT_19_9. I do this by calculating the sum of those columns and storing it in a column called Flag then filtering out rows where Flag is not > 0.
  2. Do a join of df1 and df2 with the criterion that the absolute difference in time be <= 0.1. This may match one row in df1 with multiple rows in df2.
  3. For each value of df1's time, called time.x in the tmp data frame, keep the row that has the smallest absolute time difference with df2's time (time.y).
  4. Make new data frames that take from tmp the MT_19_# columns from df1 and df2.
  5. Do a column by column multiplication of those data frames. The 0 and 1 values from df1 will eliminate or keep the values from df2.
  6. Column bind the labeling columns from df2 (stored in tmp as ID.y, Formula, time.y) with the result of the multiplication.
df1 <- structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
                             13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
                             29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
                             45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59),
                      time = c(4.68, 6.34, 6.79, 8.16, 8.35, 8.49, 8.53, 8.54,
                               9.1, 9.34, 9.35, 9.44, 9.59, 9.91, 10.73, 10.91, 11.26, 11.46,
                               11.51, 11.85, 12.33, 12.36, 12.45, 12.65, 12.72, 12.83, 12.85,
                               13.78, 13.79, 13.9, 13.94, 14.04, 14.09, 14.31, 14.96, 14.99,
                               15.68, 16.73, 16.87, 16.87, 16.99, 17.06, 17.09, 17.18, 17.19,
                               17.21, 17.33, 17.33, 17.33, 17.5, 19.73, 20.9, 21.12, 21.65,
                               21.99, 22.6, 23.66, 23.71, 24.22), 
                      MT_19_2 = c(0, 1, 0, 0,
                                  0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 1,
                                  0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                  0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 1, 0, 0), 
                      MT_19_3 = c(0,
                                  1, 0, 0, 0, 0, 1, 1, 0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1,
                                  0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0,
                                  0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0,
                                  0), 
                      MT_19_4 = c(0, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 0,
                                  1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0,
                                  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0,
                                  1, 1, 0, 1, 1, 0, 0), 
                      MT_19_5 = c(0, 1, 0, 0, 0, 1, 1, 1,
                                  1, 1, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1,
                                  1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1,
                                  0, 0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 0, 0), 
                      MT_19_6 = c(1, 1,
                                  0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0,
                                  0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0,
                                  0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0),
                      MT_19_7 = c(0, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0,
                                  0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0,
                                  0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0,
                                  1, 0, 1, 1, 0, 0), 
                      MT_19_9 = c(0, 1, 0, 0, 0, 1, 1, 1, 0,
                                  1, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                  0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0,
                                  0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0)), row.names = c(NA, -59L
                                  ), class = c("tbl_df", "tbl", "data.frame"))

df2 <- structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
                             13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
                             29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,
                             45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60,
                             61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72), 
                      Formula = c("C18H18O5S",
                                  "C18H20O6S", "C17H20O6S", "C3H7NO4S", "C5H12O4S", "C4H9NO5S",
                                  "C4H9NO4S", "C4H8O4S", "C2H5NO4S", "C7H14O4S", "C3H7NO4S", "C4H7NO4S",
                                  "C5H9NO4S", "C3H6O4S", "C3H7NO5S", "C4H8O4S", "C6H10O5S", "C4H10O6S",
                                  "C4H11NO4S", "C7H16O6S", "C6H14O6S", "C10H16O7S", "C5H6O4S",
                                  "C8H16O6S", "C6H12O6S", "C9H14O7S", "C5H11NO5S", "C5H12O5S",
                                  "C7H16O5S", "C6H14O6S", "C7H16O6S", "C5H12O6S", "C4H10O5S", "C4H10O6S",
                                  "C9H14O7S", "C5H10O6S", "C7H12O6S", "C4H10O6S", "C4H10O7S", "C6H14O5S",
                                  "C3H8O5S", "C6H12O5S", "C10H16O7S", "C4H10O7S", "C8H12O7S", "C4H10O5S",
                                  "C6H12O6S", "C7H6O5S", "C8H16O6S", "C3H8O6S", "C5H12O6S", "C5H12O5S",
                                  "C2H6O5S", "C4H8O5S", "C6H14O5S", "C5H10O5S", "C7H16O5S", "C4H10O5S",
                                  "C3H8O5S", "C5H12O5S", "C7H14O5S", "C3H8O4S", "C7H16O5S", "C6H14O5S",
                                  "C9H14O7S", "C5H11NO4S", "C7H14O5S", "C6H12O5S", "C2H6O4S", "C3H7NO5S",
                                  "C8H14O5S", "C7H12O6S"), 
                      time = c(3.20783518571429, 3.3058845,
                               5.16564968333333, 6.11775966, 6.14019345, 6.148714175, 6.15885244545455,
                               6.27332518, 6.51033615, 6.56079755, 6.64471367, 6.74574776, 6.75789398,
                               6.80451824, 6.96669176, 7.0671294, 7.15878750833333, 7.2430858,
                               7.337671075, 7.37518743333333, 7.505341575, 7.56266235, 7.61408512222222,
                               7.62664083333333, 7.67808926666667, 7.71709731, 7.721307925,
                               7.7771714, 7.84045221111111, 7.88049126666667, 7.8946845875,
                               7.93216607, 7.93392067, 7.9579363625, 8.02879474, 8.0384297,
                               8.08051208181818, 8.08851843333333, 8.09120095, 8.1206633, 8.13128057,
                               8.16548933, 8.25074975, 8.27318165555555, 8.32513095, 8.33442142222222,
                               8.3350968, 8.3710613, 8.38630245, 8.40933818, 8.4256509, 8.44968824,
                               8.51020837272727, 8.5475135, 8.5493729375, 8.55494652, 8.66194003333333,
                               8.73876549, 8.74121635714286, 8.7666669, 8.78334723333333, 8.80910283,
                               8.8721785, 8.95693963333333, 8.98171381428572, 9.05258299285714,
                               9.05975823333333, 9.13528001111111, 9.13618929166667, 9.230259,
                               9.24780721666667, 9.26271359), 
                      MT_19_02 = c(0, 0, 0, 40921014.8873828,
                                   13097332.383734, 22555440.5230714, 27393164.3336271, 0, 61707683.456742,
                                   0, 100825067.571966, 23996391.4579917, 0, 135570473.008681, 0,
                                   0, 44727056.0540707, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                   0, 114230544.061525, 0, 0, 0, 0, 0, 0, 14166579.9671584, 181241230.517958,
                                   139007786.131602, 0, 0, 5478089.59689469, 14148724.838309, 0,
                                   0, 0, 46339949.390312, 0, 35121833.3666845, 546486089.366755,
                                   0, 8999722.57463175, 566659114.471751, 0, 78788825.482585, 0,
                                   0, 0, 505233322.327622, 0, 0, 0, 0, 0, 140740069.778226, 227029586.637094,
                                   0, 0, 0), 
                      MT_19_03 = c(0, 0, 0, 29714890.4951513, 8146720.13717684,
                                   3214870.59465608, 13743262.6920483, 0, 41082616.2199964, 0, 97692415.9346813,
                                   16625387.5438798, 0, 77546680.369805, 0, 0, 16519006.1476685,
                                   0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 67652527.1833035,
                                   0, 0, 0, 0, 0, 0, 6958642.14686492, 95384109.9542679, 86246742.5780535,
                                   0, 0, 2670751.32332937, 8080375.00730327, 0, 0, 0, 0, 0, 15617113.11937,
                                   302314176.353279, 0, 4606425.64343903, 272894031.239623, 0, 33243796.9321669,
                                   0, 0, 0, 255781542.924426, 0, 0, 0, 0, 0, 61850087.6789327, 164588829.010743,
                                   0, 0, 0), 
                      MT_19_04 = c(0, 0, 0, 15144592.2799419, 5254317.18339502,
                                   0, 7732797.715469, 0, 32177926.2347124, 0, 40307312.5012659,
                                   18785120.9731572, 0, 83892293.5458507, 0, 0, 18921989.3909306,
                                   5275334.06365532, 0, 0, 0, 0, 7768704.24818661, 6390167.91532848,
                                   0, 0, 0, 0, 0, 0, 0, 0, 53429035.1168837, 0, 0, 0, 0, 0, 0, 12102283.7733221,
                                   155997179.118513, 122010631.322574, 0, 0, 0, 12562106.7430907,
                                   8464006.89399269, 14804564.3381483, 0, 26165849.7385138, 0, 28828078.6956566,
                                   479870015.302095, 0, 8780729.69101185, 498815802.04719, 0, 57228783.4254006,
                                   0, 0, 0, 499986702.14322, 0, 0, 0, 0, 171240861.794702, 91591746.2795261,
                                   249646598.345084, 4095091.86889109, 5005165.4397847, 0), 
                      MT_19_05 = c(0,
                                   0, 0, 17238273.257216, 7927180.53347917, 0, 21040616.4729868,
                                   0, 30305016.5567727, 0, 47817237.7764565, 12157255.8264659, 0,
                                   66945672.1885907, 0, 0, 14467252.8359016, 4545334.33871705, 0,
                                   0, 0, 0, 7104881.41391828, 9101361.07322816, 0, 0, 0, 8216769.55533557,
                                   0, 0, 0, 0, 80876143.7091276, 0, 0, 0, 0, 0, 0, 8045542.77117793,
                                   0, 179158483.966825, 0, 0, 0, 9306865.71953042, 8822438.39622112,
                                   13089896.9263799, 0, 24476478.9306488, 0, 21540895.2068399, 468648886.861163,
                                   0, 8387501.72707726, 723585509.017031, 0, 44977684.6401131, 27448174.8216997,
                                   0, 0, 365380092.045645, 0, 0, 0, 0, 307329325.604008, 157698694.584444,
                                   226230564.11256, 37505932.6833481, 0, 0), 
                      MT_19_06 = c(0, 0,
                                   0, 31281597.6281464, 21990817.3263903, 0, 13220859.4314389, 0,
                                   56650959.227831, 0, 95073669.6785226, 35876430.1988854, 0, 152305981.985153,
                                   0, 0, 13253743.9716474, 12794494.8366698, 0, 0, 0, 0, 13996820.3370385,
                                   8046288.83204314, 0, 0, 0, 0, 0, 0, 0, 0, 70735538.1881517, 0,
                                   0, 0, 0, 0, 0, 10101152.6872459, 145892632.359074, 120724420.23238,
                                   0, 0, 0, 11285493.8946831, 11993737.1250124, 15134384.9980447,
                                   0, 52059113.6259326, 0, 0, 479339285.38645, 0, 7502671.30844373,
                                   520127454.404959, 0, 66158572.7068049, 0, 0, 0, 300617114.052477,
                                   0, 0, 0, 0, 171309005.847815, 122911178.858373, 262749108.731238,
                                   7370018.21512647, 8011806.50201363, 0), 
                      MT_19_07 = c(0, 0, 0,
                                   11154985.6802547, 4843497.49005086, 0, 8411666.59484592, 0, 23836686.1036455,
                                   0, 20590333.6632542, 8579602.21553705, 0, 65970272.9623348, 0,
                                   0, 3126985.93252623, 3559164.70676559, 0, 0, 0, 0, 0, 7576524.87154396,
                                   0, 0, 0, 8619844.19917643, 0, 0, 0, 0, 58941790.8554014, 0, 0,
                                   0, 0, 0, 0, 10232903.8186486, 126363697.805438, 0, 0, 0, 0, 8263571.17835424,
                                   7729073.72356839, 0, 0, 26873616.4408984, 0, 21233890.8395023,
                                   377388507.641626, 0, 5873236.33247528, 595290232.638844, 0, 43152679.6590801,
                                   0, 0, 0, 244845403.332167, 0, 0, 0, 0, 123142463.077619, 83502281.6532513,
                                   146424886.507455, 22533371.8733195, 1075773.51166033, 0), 
                      MT_19_09 = c(0,
                                   0, 0, 168956182.4262, 9180777.77582912, 0, 62681238.2534124,
                                   0, 112395752.116438, 0, 620140025.762412, 105794856.047077, 150150822.806724,
                                   336794721.546787, 41336679.4434988, 37421709.143196, 0, 0, 0,
                                   0, 18036534.6210427, 3800136.52603438, 0, 20137831.0931866, 0,
                                   14890147.5008399, 0, 0, 0, 0, 0, 17374891.7161397, 104496605.50052,
                                   0, 4518512.02977477, 0, 0, 0, 0, 11081421.5396069, 195950810.060938,
                                   99775729.404847, 4383696.69767469, 0, 20081636.7485911, 22089580.8097382,
                                   0, 16546901.2809536, 0, 65731294.9068595, 0, 44446981.4538852,
                                   445424535.748512, 0, 9803287.97667728, 411679146.262993, 0, 102708907.554563,
                                   0, 0, 0, 254539577.194814, 0, 0, 0, 0, 259334470.850421, 115318215.100194,
                                   215722282.598193, 14520528.3978189, 0, 0)), row.names = c(NA,
                                                                                             -72L), class = c("tbl_df", "tbl", "data.frame"))
LittleDiff <- function(x,y) abs(x-y) <= 0.1 #return TRUE or FALSE
library(dplyr)
library(fuzzyjoin)
#> Warning: package 'fuzzyjoin' was built under R version 4.1.2
library(purrr)
df1 <- df1 |> rowwise() |> 
  mutate(Flag = sum(c_across(MT_19_2:MT_19_9))) |> 
  filter(Flag > 0) |> select(-Flag)
df2 <- df2 |> rowwise() |> 
  mutate(Flag = sum(c_across(MT_19_02:MT_19_09))) |> 
  filter(Flag > 0) |> select(-Flag)

tmp <- fuzzy_inner_join(df1, df2, by="time", match_fun = LittleDiff)
tmp <- tmp |> group_by(time.x) |> mutate(DIFF = abs(time.x-time.y)) |> 
  slice_min(DIFF) |> ungroup()
df1Data <- tmp |> select(MT_19_2:MT_19_9) 
df2Data <- tmp |> select(MT_19_02:MT_19_09) 

Products <- map2_dfc(df1Data,df2Data,.f = `*`)
Final <- cbind(select(tmp, ID=ID.y, Formula, time=time.y), Products)
Final
#>   ID  Formula     time MT_19_2 MT_19_3  MT_19_4   MT_19_5   MT_19_6  MT_19_7
#> 1 53  C2H6O5S 8.510208       0       0        0 468648887 479339285        0
#> 2 55 C6H14O5S 8.549373 8999723 4606426  8780730   8387502   7502671  5873236
#> 3 55 C6H14O5S 8.549373 8999723 4606426  8780730   8387502   7502671  5873236
#> 4 68 C6H12O5S 9.135280       0       0 91591746 157698695 122911179 83502282
#> 5 71 C8H14O5S 9.247807       0       0  5005165         0   8011807  1075774
#>     MT_19_9
#> 1 445424536
#> 2   9803288
#> 3   9803288
#> 4         0
#> 5         0

Created on 2022-01-31 by the reprex package (v2.0.1)

11

Thank you for your help, the code works perfectly with the uploaded dataset. But as I said, I have reduced the supplied data because rstudio can only upload a certain amount. For my whole dataset the code runs until the following line:

df1Data <- tmp %>% select(MT_19_02:MT_19_09)
Error: Can't subset columns that don't exist.
x Column MT_19_02 doesn't exist

I also corrected the column names so that R knows that it should also perform the process with the remaining columns. But that didn't change anything. I'll try uploading the dataset after I clean it up with your code so it doesn't go over the limit. For df2, it also deleted a lot of rows and colums (), otherwise even the R console would have shown me the whole df. So sorry for the trouble.

df1 <-structure(list(ID = c(1, 2, 6, 7, 8, 9, 10, 11, 13, 14, 15, 17,
18, 20, 23, 27, 28, 29, 31, 32, 33, 34, 42, 44, 46, 49, 50, 51,
53, 54, 56, 57, 61, 62, 66, 68, 70, 71, 72, 73, NA), time = c(4.68,
6.34, 8.49, 8.53, 8.54, 9.1, 9.34, 9.35, 9.59, 9.91, 10.73, 11.26,
11.46, 11.85, 12.45, 12.85, 13.78, 13.79, 13.94, 14.04, 14.09,
14.31, 17.06, 17.18, 17.21, 17.33, 17.5, 19.73, 21.12, 21.65,
22.6, 23.66, 24.51, 25.25, 28.07, 30.21, 31.06, 31.08, 32.11,
32.38, NA), MT_19_02 = c(0, 1, 0, 1, 1, 0, 1, 1, 1, 0, 1, 1,
0, 1, 1, 0, 0, 0, 0, 1, 1, 0, 0, 1, 1, 1, 0, 1, 1, 0, 1, 1, 0,
1, 1, 1, 0, 0, 1, 0, 23), MT_19_03 = c(0, 1, 0, 1, 1, 0, 1, 1,
1, 1, 0, 1, 0, 1, 1, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 1, 0, 1, 0,
0, 1, 0, 0, 1, 0, 1, 1, 1, 0, 1, 23), MT_19_04 = c(0, 1, 0, 1,
1, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1,
1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 0, 0, 1, 0, 24), MT_19_05 = c(0,
1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 0, 1, 1, 1, 0, 0, 0, 0,
0, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 28), MT_19_06 = c(1,
1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 1, 1, 1, 0, 0, 1, 1, 0,
1, 1, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 1, 1, 0, 0, 1, 0, 26), MT_19_07 = c(0,
1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 0, 1, 1, 0, 0, 1, 0, 0, 0, 0,
0, 1, 1, 1, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0, 0, 1, 23), MT_19_09 = c(0,
1, 1, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 1, 0, 1,
1, 0, 1, 0, 1, 1, 1, 0, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 21), MT_19_10 = c(1,
1, 1, 1, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 0, 1, 0, 1,
0, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 27), MT_19_11 = c(0,
1, 0, 1, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0,
1, 0, 0, 1, 0, 1, 0, 0, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 22), MT_19_12 = c(0,
1, 0, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 22), MT_19_13 = c(0,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 1, 0,
1, 0, 0, 1, 1, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 29), MT_19_14 = c(0,
1, 1, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 1, 0, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 30), MT_19_16 = c(0,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 0, 27), MT_19_17 = c(0,
1, 1, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 37), MT_19_18 = c(0,
1, 0, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 1, 0, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 22), MT_19_19 = c(0,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 0, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 27), MT_19_20 = c(0,
1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 0, 1, 0, 0,
0, 1, 1, 1, 0, 1, 0, 0, 1, 0, 1, 1, 1, 1, 1, 0, 1, 0, 25), MT_19_21 = c(0,
1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 1, 1, 0,
0, 1, 1, 1, 0, 1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 28), MT_19_23 = c(0,
1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0,
0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 1, 0, 20), MT_19_24 = c(0,
1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 0, 1, 0, 1, 0, 1, 1, 0, 0, 0,
0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 27), MT_19_25 = c(0,
1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 0, 0,
0, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 21), MT_19_26 = c(0,
1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0,
0, 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 29), MT_19_27 = c(0,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 0, 0,
0, 1, 1, 1, 0, 1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 29), MT_19_28 = c(0,
1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 1, 1, 0,
0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1, 1, 0, 1, 1, 0, 1, 0, 24), MT_19_30 = c(1,
1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0,
0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1, 0, 19), MT_19_31 = c(0,
1, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0,
0, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 21), MT_19_32 = c(1,
1, 1, 1, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0,
0, 1, 1, 0, 1, 1, 0, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 0, 22), MT_19_33 = c(1,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0,
0, 1, 1, 1, 0, 1, 0, 1, 1, 1, 1, 1, 0, 1, 0, 0, 1, 0, 24), MT_19_34 = c(1,
1, 1, 1, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0,
0, 1, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 0, 28), MT_19_35 = c(0,
1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0,
0, 1, 1, 1, 0, 1, 1, 1, 1, 1, 0, 1, 0, 1, 0, 0, 1, 1, 27), MT_19_37 = c(1,
1, 1, 1, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 1, 1, 0,
0, 1, 1, 0, 1, 1, 0, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 0, 25), MT_19_38 = c(0,
1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 1, 0,
0, 1, 1, 0, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 29), MT_19_39 = c(1,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0,
1, 0, 0, 1, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 26), MT_19_40 = c(1,
1, 1, 1, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0,
1, 0, 0, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 28), MT_19_41 = c(1,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0,
1, 0, 0, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0, 29), MT_19_42 = c(1,
1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0,
1, 0, 0, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 32)), row.names = c(NA,
-41L), groups = structure(list(.rows = structure(list(1L, 2L,
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L,
16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L,
28L, 29L, 30L, 31L, 32L, 33L, 34L, 35L, 36L, 37L, 38L, 39L,
40L, 41L), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), row.names = c(NA, -41L), class = c("tbl_df",
"tbl", "data.frame")), class = c("rowwise_df", "tbl_df", "tbl",
"data.frame"))

df2<-structure(list(ID_MS1 = c(4, 5, 6, 7, 9, 11, 12, 13, 14, 15,
16, 17, 18, 21, 22, 23, 24, 26, 28), mz = c(152.002294921875,
167.038227081299, 182.012874603271, 166.017890236594, 137.986631774902,
152.002294921875, 164.002365112305, 178.018017578125, 136.991384887695,
167.997035217285, 151.007039642334, 193.017663955688, 185.012418365479,
213.043928146362, 279.05419921875, 160.991397433811, 239.059560139974,
265.03872833252, 183.033332824707), Formula = c("C3H7NO4S", "C5H12O4S",
"C4H9NO5S", "C4H9NO4S", "C2H5NO4S", "C3H7NO4S", "C4H7NO4S", "C5H9NO4S",
"C3H6O4S", "C3H7NO5S", "C4H8O4S", "C6H10O5S", "C4H10O6S", "C6H14O6S",
"C10H16O7S", "C5H6O4S", "C8H16O6S", "C9H14O7S", "C5H12O5S"),
MW = c(153.009574921875, 168.045507081299, 183.020154603271,
167.025170236594, 138.993911774902, 153.009574921875, 165.009645112305,
179.025297578125, 137.998664887695, 169.004315217285, 152.014319642334,
194.024943955688, 186.019698365479, 214.051208146362, 280.06147921875,
161.998677433811, 240.066840139974, 266.04600833252, 184.040612824707
), OtoC = c(1.33333333333333, 0.8, 1.25, 1, 2, 1.33333333333333,
1, 0.8, 1.33333333333333, 1.66666666666667, 1, 0.833333333333333,
1.5, 1, 0.7, 0.8, 0.75, 0.777777777777778, 1), HtoC = c(2.33333333333333,
2.4, 2.25, 2.25, 2.5, 2.33333333333333, 1.75, 1.8, 2, 2.33333333333333,
2, 1.66666666666667, 2.5, 2.33333333333333, 1.6, 1.2, 2,
1.55555555555556, 2.4), OtoS = c(4, 4, 5, 4, 4, 4, 4, 4,
4, 5, 4, 5, 6, 6, 7, 4, 6, 7, 5), RDBE = c(1, 0, 1, 1, 1,
1, 2, 2, 1, 1, 1, 2, 0, 0, 3, 3, 1, 3, 0), Xc = c(0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, 0, 0, 0), KMD_CH2 = c(0.167432198560192,
0.148289295310178, 0.190362598538485, 0.167486822779097,
0.167445330853155, 0.167432198560192, 0.18076139370848, 0.180758930641815,
0.161580916494785, 0.190551773054409, 0.161576167164839,
0.197861292224644, 0.194168153692061, 0.193958607052281,
0.257395193052048, 0.188366998793953, 0.207375900537187,
0.25721627928209, 0.171043829868893), KMD_O = c(-0.0506185090698921,
-0.0913307948672184, -0.070738959996703, -0.0706695712671035,
-0.0304995930539178, -0.0506185090698921, -0.0545036842571278,
-0.0746119157244607, -0.0349363033889176, -0.0504437489389318,
-0.055046824402865, -0.0790268839195107, -0.0712363177712234,
-0.111657695073177, -0.142914326209279, -0.0425787783789815,
-0.135560410266862, -0.122987732058562, -0.0915215989151363
), log10_C0 = c(2.39791428571429, 2.63866644444444, 0.705377777777778,
1.8462, 3.00526666666667, 2.39791428571429, 1.8462, 1.33157777777778,
2.96093628571429, 1.293025, 2.825122, 1.35079472727273, 0.490162,
0.241322, -1.64904270588235, 2.63866644444444, -0.144249428571429,
-1.429028, 1.533222), time = c(6.11775966, 6.14019345, 6.148714175,
6.15885244545455, 6.51033615, 6.64471367, 6.74574776, 6.75789398,
6.80451824, 6.96669176, 7.0671294, 7.15878750833333, 7.2430858,
7.505341575, 7.56266235, 7.61408512222222, 7.62664083333333,
7.71709731, 7.7771714), MT_19_02 = c(40921014.8873828, 13097332.383734,
22555440.5230714, 27393164.3336271, 61707683.456742, 100825067.571966,
23996391.4579917, 0, 135570473.008681, 0, 0, 44727056.0540707,
0, 0, 0, 0, 0, 0, 0), MT_19_03 = c(29714890.4951513, 8146720.13717684,
3214870.59465608, 13743262.6920483, 41082616.2199964, 97692415.9346813,
16625387.5438798, 0, 77546680.369805, 0, 0, 16519006.1476685,
0, 0, 0, 0, 0, 0, 0), MT_19_04 = c(15144592.2799419, 5254317.18339502,
0, 7732797.715469, 32177926.2347124, 40307312.5012659, 18785120.9731572,
0, 83892293.5458507, 0, 0, 18921989.3909306, 5275334.06365532,
0, 0, 7768704.24818661, 6390167.91532848, 0, 0), MT_19_05 = c(17238273.257216,
7927180.53347917, 0, 21040616.4729868, 30305016.5567727,
47817237.7764565, 12157255.8264659, 0, 66945672.1885907,
0, 0, 14467252.8359016, 4545334.33871705, 0, 0, 7104881.41391828,
9101361.07322816, 0, 8216769.55533557), MT_19_06 = c(31281597.6281464,
21990817.3263903, 0, 13220859.4314389, 56650959.227831, 95073669.6785226,
35876430.1988854, 0, 152305981.985153, 0, 0, 13253743.9716474,
12794494.8366698, 0, 0, 13996820.3370385, 8046288.83204314,
0, 0), MT_19_07 = c(11154985.6802547, 4843497.49005086, 0,
8411666.59484592, 23836686.1036455, 20590333.6632542, 8579602.21553705,
0, 65970272.9623348, 0, 0, 3126985.93252623, 3559164.70676559,
0, 0, 0, 7576524.87154396, 0, 8619844.19917643), MT_19_09 = c(168956182.4262,
9180777.77582912, 0, 62681238.2534124, 112395752.116438,
620140025.762412, 105794856.047077, 150150822.806724, 336794721.546787,
41336679.4434988, 37421709.143196, 0, 0, 18036534.6210427,
3800136.52603438, 0, 20137831.0931866, 14890147.5008399,
0), MT_19_10 = c(48332207.2147292, 10791331.433936, 0, 10726123.8188046,
52105449.8265881, 166826389.789969, 25050390.8011113, 14634181.2048366,
151319023.847103, 8091003.70490734, 16583649.2658514, 0,
0, 0, 913684.099831249, 0, 9031926.37868458, 4108485.37661528,
0), MT_19_11 = c(89045678.8045282, 11128901.0833596, 0, 11895773.4274177,
86266868.0670156, 322166775.975469, 54437706.3449453, 36140549.6608461,
242814356.825769, 13362616.2069587, 25629733.5447676, 0,
0, 0, 2956775.90998354, 0, 17503352.8292196, 0, 0), MT_19_12 = c(60325822.6125821,
4589099.65020091, 0, 8215870.87693034, 84933458.7529233,
210064975.304232, 34958378.1954598, 15221319.5971153, 271706586.807697,
11881264.6635966, 28479304.1440168, 0, 0, 11843495.6805816,
0, 0, 20779590.1651981, 10313661.0852359, 0), MT_19_13 = c(148175517.718731,
7677746.21327165, 0, 23711695.1970543, 184311834.441988,
523204044.60661, 97312990.1766912, 51716503.9054531, 583243851.633892,
33010421.3798326, 55650232.9395411, 0, 0, 0, 5395789.96014627,
0, 27702309.7845655, 22945321.602601, 0), MT_19_14 = c(63110115.938353,
4734417.83510925, 0, 9999045.20142465, 152315895.989358,
192840188.07886, 41548476.5961704, 12846278.123611, 393381834.682904,
21025846.1320578, 34513050.4895301, 0, 0, 0, 1497376.9396104,
0, 36237669.0331675, 7991941.71247108, 0), MT_19_16 = c(155910385.682887,
7022209.01633734, 0, 0, 220945203.155195, 562623184.056087,
129748565.122248, 45585863.0446495, 490242984.403488, 0,
44667806.745936, 588772.343743745, 0, 36767430.6246252, 0,
0, 51337011.1700519, 32171847.1995221, 0), MT_19_17 = c(196757757.045999,
8005896.43915949, 0, 0, 319933747.25619, 708248731.324837,
171748791.89215, 84967922.0747553, 667446445.113889, 53671419.6389721,
67592295.4022893, 1907717.78671551, 0, 27566716.5790971,
0, 13166830.4269339, 59328588.4121515, 21335401.6265081,
19622897.425811), MT_19_18 = c(81409546.3532091, 5153703.65685277,
0, 0, 158782949.35829, 248733107.763939, 65540328.1100771,
21767211.4077271, 300440683.103221, 23276281.1091841, 26238057.1116834,
3140351.55509517, 0, 11680569.9689264, 0, 6854050.2806557,
17402158.8305233, 8467328.84993947, 0), MT_19_19 = c(78710956.950067,
7890121.46458072, 0, 0, 173369962.846792, 265192020.115434,
70204261.5862513, 22472061.3954434, 333705296.216223, 26591139.7663504,
31114884.3997605, 1607672.49700778, 0, 12654848.1854707,
0, 4232327.31230244, 35684104.1333406, 10018775.4596541,
0), MT_19_20 = c(36119315.4816924, 7801985.3742186, 0, 0,
102842183.040102, 94999775.4614597, 41108701.3431496, 15310416.0795319,
207199789.283073, 13853048.8878959, 21525439.975603, 28006588.7001422,
0, 6736600.81510299, 0, 11228625.6549107, 10925450.4570871,
2201921.75119878, 0), MT_19_21 = c(67471119.7203984, 16329123.5533808,
0, 0, 136267566.105173, 211437971.442176, 72226607.1736073,
18025087.5084924, 288446129.160453, 21262308.5130413, 26463436.1162096,
2180275.6882263, 0, 12433036.9567383, 0, 5248669.97491262,
19113579.7627601, 8074260.72958999, 0), MT_19_23 = c(93356940.7041948,
9780627.26823907, 0, 24287504.5155847, 206994843.575131,
255956544.547918, 116209413.99731, 41315447.0616982, 385048231.840644,
26105130.3712, 37553623.7336112, 17356596.6352699, 0, 13507608.1096696,
0, 0, 11846827.8774801, 0, 0), MT_19_24 = c(79406084.6496126,
10548576.1511658, 0, 26063373.6782869, 176085665.634575,
227449059.674019, 84964043.1581787, 28140307.9768429, 316844732.059697,
20299685.0269888, 33282292.4162003, 16209824.0636498, 0,
7110349.37869523, 0, 0, 11480406.645973, 0, 0), MT_19_25 = c(66317935.5140773,
17776902.9971029, 0, 16767103.7959746, 127998165.586454,
195828973.214219, 73607753.2755626, 23818806.1830449, 254727404.787772,
18220928.1847117, 26156798.8655035, 0, 0, 0, 0, 0, 11724843.8933971,
0, 0), MT_19_26 = c(73966030.1956294, 7790208.74647162, 0,
14282628.3472864, 116746987.33808, 268617096.799994, 64340879.7195277,
33264911.3978782, 308831445.043396, 15676852.8875634, 31117492.5072407,
827115.566329538, 0, 10370815.4561654, 0, 0, 20997489.8303708,
0, 0), MT_19_27 = c(73263135.8687723, 8644685.76781408, 0,
0, 157276222.755387, 247498563.882778, 81566984.7800822,
27196894.7784803, 400625663.563823, 18745048.5740653, 39418814.7629848,
1204908.30750617, 0, 20549192.5992552, 0, 0, 36805934.3139364,
0, 0), MT_19_28 = c(25295924.6168354, 10712368.2823861, 0,
10717885.2593031, 35900857.1983536, 83714014.424904, 19797154.3775061,
7158775.05206975, 72748908.5578961, 4085545.82548162, 8318665.50708304,
36186703.9598856, 0, 0, 0, 0, 5675262.62449437, 0, 0), MT_19_30 = c(13087436.8298778,
11370574.2402637, 0, 0, 25056810.4144313, 31794692.8206347,
13804889.3568887, 0, 48927556.8276519, 0, 5555974.34889052,
0, 1073897.3859887, 0, 0, 0, 4242772.32174747, 0, 0), MT_19_31 = c(3359975.68980802,
8226099.14090978, 0, 0, 16621260.1605823, 11760756.0403619,
4111417.36552585, 0, 48035330.5318934, 0, 4967246.67090007,
0, 0, 1387413.74988442, 0, 402505.342961813, 0, 0, 0), MT_19_32 = c(24772063.3129362,
6872232.01837121, 0, 0, 38299077.0544131, 82719930.1611535,
19513114.6026037, 0, 113618647.998244, 0, 11474416.1210034,
0, 2456462.6967326, 0, 0, 2608609.95692659, 10077282.2276907,
7774030.53094054, 0), MT_19_33 = c(45961891.3887397, 7837068.6272625,
0, 0, 62419220.0352829, 156937625.747417, 41131090.6472434,
0, 243945795.267734, 0, 33076166.1186556, 0, 39679882.7705766,
0, 0, 6424452.35182053, 28514869.798599, 31585231.5400067,
0), MT_19_34 = c(130182512.841209, 7823865.28469754, 0, 0,
398778915.074548, 399184488.779742, 89772955.4743518, 0,
616842658.300056, 0, 64233044.8658416, 0, 8097399.12308049,
0, 0, 14936261.8002959, 12648484.5161129, 7344251.21070405,
13029943.2801399), MT_19_35 = c(20088367.6603279, 8705141.09750892,
0, 0, 21481372.7745764, 63112139.6209376, 9783996.60656047,
6336429.43035296, 54794731.7315979, 0, 6561800.05299594,
0, 45792553.9865544, 0, 0, 0, 7379571.42940555, 0, 0), MT_19_37 = c(130962496.92524,
37380608.2287576, 0, 0, 120554481.082237, 439696996.914473,
62575664.3373891, 33402609.6430797, 285871560.332436, 0,
28275881.0327855, 0, 11122257.8034333, 0, 0, 0, 16468193.9586932,
12263833.2745809, 0), MT_19_38 = c(99025763.2410848, 0, 0,
0, 118263522.832547, 357194791.078879, 46825477.3344541,
0, 289711519.56038, 18926584.3038094, 27276843.1181931, 0,
47134426.1702013, 9979801.72524031, 0, 0, 17554919.2538222,
9791146.00301455, 0), MT_19_39 = c(181333980.577088, 0, 0,
0, 165790053.373603, 635945809.930321, 102566915.535881,
0, 498159954.142824, 21961638.7267748, 50614873.7822084,
0, 9186025.94990057, 0, 0, 0, 25470256.9320132, 24677064.727387,
0), MT_19_40 = c(66352374.6142799, 0, 0, 0, 111462578.070155,
215124120.021978, 39700657.9507191, 0, 343570683.303895,
13090930.3760042, 37297431.318544, 0, 36127718.1194675, 14198602.0956247,
0, 0, 0, 13668510.6523387, 0), MT_19_41 = c(225155758.104914,
0, 0, 0, 250240400.137729, 785999048.689405, 126333645.124465,
48725326.8373349, 621683497.341299, 30058705.8839785, 57063187.963766,
0, 8476172.57331538, 31227115.8015165, 0, 0, 37605445.2067962,
29103996.394836, 0), MT_19_42 = c(184695835.355027, 0, 0,
0, 318172765.147933, 569695490.43545, 98665087.6579018, 0,
792193411.3729, 29987650.888983, 61861270.4015254, 0, 5610581.37426772,
34372642.1533453, 0, 0, 49072451.2274331, 27367937.8802208,
0)), row.names = c(NA, -19L), groups = structure(list(.rows = structure(list(
1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L,
15L, 16L, 17L, 18L, 19L), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), row.names = c(NA, -19L), class = c("tbl_df",
"tbl", "data.frame")), class = c("rowwise_df", "tbl_df", "tbl",
"data.frame"))

12

I modified the code to work with the data you posted. Unfortunately, none of the rows in df1 where within 0.1 minutes of time values in df2. I manually modified one row of df1 to match with some rows of df2. Also, I saved the data you posted in an Rdata file so my reprex would not include hundreds of lines of data definition.
The main thing that tripped you up, I think, is that if fuzzy_inner_join finds columns with identical names in the data frames it is operating on, it appends .x and .y to the column names so they can be distinguished. You can see how I changed the code accordingly in these lines

df1Data <- tmp |> select(MT_19_02.x:MT_19_42.x) 
df2Data <- tmp |> select(MT_19_02.y:MT_19_42.y)

You could have seen the appropriate column names by running

colnames(tmp)

I also changed

Final <- cbind(select(tmp, ID_MS1:time.y), Products)

to select all of the labeling columns needed for df2.

#save(df1, df2, file = "Forum.Rdata") #save the posted data

load("~/R/Play/Forum.Rdata") #load your data

LittleDiff <- function(x,y) abs(x-y) <= 0.1 #return TRUE or FALSE
library(dplyr)

library(fuzzyjoin)
#> Warning: package 'fuzzyjoin' was built under R version 4.1.2
library(purrr)
df1[2,"time"] <- 6.14 #Manually set one time in df1 to match a time in df2
df1 <- df1 |> rowwise() |> 
  mutate(Flag = sum(c_across(MT_19_02:MT_19_42))) |> 
  filter(Flag > 0) |> select(-Flag)
df2 <- df2 |> rowwise() |> 
  mutate(Flag = sum(c_across(MT_19_02:MT_19_42))) |> 
  filter(Flag > 0) |> select(-Flag)

tmp <- fuzzy_inner_join(df1, df2, by="time", match_fun = LittleDiff)
tmp <- tmp |> group_by(time.x) |> mutate(DIFF = abs(time.x-time.y)) |> 
  slice_min(DIFF) |> ungroup()
df1Data <- tmp |> select(MT_19_02.x:MT_19_42.x) 
df2Data <- tmp |> select(MT_19_02.y:MT_19_42.y) 

Products <- map2_dfc(df1Data,df2Data,.f = `*`)
Final <- cbind(select(tmp, ID_MS1:time.y), Products)

Created on 2022-02-01 by the reprex package (v2.0.1)

13

I can only thank you for your patience and help. Now the script is working perfect !!

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.