The basic process is to make each tibble have one or more columns to merge by.
Then merge the tibbles. Can you show us a couple of tries and describe why or
how they failed? Note that you cannot merge using a row in one tibble and a
column in the other tibble without first reshaping the first tibble.
-----Original Message-----
From: R-help <r-help-boun...@r-project.org> On Behalf Of Arnaud Gaboury
Sent: Sunday, April 6, 2025 11:48 PM
To: r-help@r-project.org
Subject: [R] Can't join/merge two tibbles with lots of variables the way I want
[External Email]
I work on writing a portfolio journal with a daily valuation of my portfolio +
the trades I have done on the day.
My "time" column is made of dates (day + time) which are either the closing
prices of my assets (each day @ 19:59:59 for convenience. I work on crypto
currencies and markets are open 24/24 7/7, so thete is no real official closing
time).
To build this journal, I can rely on two tibbles:
- one is the data frame of daily closing prices of the assets I own in my
portfolio. Here it is:
daily_clsong_price <- structure(list(time = structure(c(1734371999.999,
1734458399.999, 1734544799.999, 1734631199.999, 1734717599.999, 1734803999.999,
1734890399.999, 1734976799.999, 1735063199.999, 1735149599.999, 1735235999.999,
1735322399.999, 1735408799.999, 1735495199.999, 1735581599.999, 1735667999.999,
1735754399.999, 1735840799.999, 1735927199.999, 1736013599.999, 1736099999.999,
1736186399.999, 1736272799.999, 1736359199.999, 1736445599.999, 1736531999.999,
1736618399.999, 1736704799.999, 1736791199.999, 1736877599.999, 1736963999.999,
1737050399.999, 1737136799.999, 1737223199.999, 1737309599.999, 1737395999.999,
1737482399.999, 1737568799.999, 1737655199.999, 1737741599.999, 1737827999.999,
1737914399.999, 1738000799.999, 1738087199.999, 1738173599.999, 1738259999.999,
1738346399.999, 1738432799.999, 1738519199.999, 1738605599.999, 1738691999.999,
1738778399.999, 1738864799.999, 1738951199.999, 1739037599.999, 1739123999.999,
1739210399.999, 1739296799.999, 1739383199.999
, 1739469599.999, 1739555999.999, 1739642399.999, 1739728799.999,
1739815199.999, 1739901599.999, 1739987999.999, 1740074399.999, 1740160799.999,
1740247199.999, 1740333599.999, 1740419999.999, 1740506399.999, 1740592799.999,
1740679199.999, 1740765599.999, 1740851999.999, 1740938399.999, 1741024799.999,
1741111199.999, 1741197599.999, 1741283999.999, 1741370399.999, 1741456799.999,
1741543199.999, 1741629599.999, 1741715999.999, 1741802399.999, 1741888799.999,
1741975199.999, 1742061599.999, 1742147999.999, 1742234399.999, 1742320799.999,
1742407199.999, 1742493599.999, 1742579999.999, 1742666399.999, 1742752799.999,
1742839199.999, 1742925599.999, 1743011999.999, 1743098399.999, 1743184799.999,
1743271199.999), tzone = "", class = c("POSIXct", "POSIXt")), BTCUSDC_close =
c(107112.36, 107517.25, 104630.49, 98692.01, 97172, 97559.99, 95555.99, 93260,
98743.99, 98475.99, 95740.01, 94156.03, 94860.02, 93788.55, 93672.01, 93972.01,
93942.68, 96673.8, 97966.09, 98004.92, 97722.38, 10186
5.33, 97236.45, 94004.02, 92686.72, 95786.01, 94113, 95097.6, 91933.25,
96481.8, 98651.62, 99823.04, 104162, 103742.8, 104997.99, 102844.44, 106166,
104336, 105945.99, 106516.81, 104880.02, 104927.47, 99111.87, 102587, 102404,
105296.86, 104591.47, 102247.03, 97166.51, 99352, 99215.17, 97570.44, 96738.02,
97462, 96363.99, 96190, 97523.98, 96164.78, 97368.43, 95716.93, 98570.1,
97431.42, 96994, 95494.02, 94068, 96394.6, 97991.02, 96982, 96635.96, 95664.1,
94650, 87156.01, 85969.64, 84507.03, 83605.12, 85172, 94100.16, 90258.01,
85415.99, 89683.45, 88625.27, 87676.78, 85929.38, 82358.02, 78414, 81291.59,
81839.98, 80047.99, 84365.03, 84427.7, 83916.04, 84189.04, 81453.98, 84236,
84048.03, 83983.62, 84232.07, 85008, 88482.01, 87958.04, 86578.83, 86890.11,
83945.98, 82308), SUIUSDC_close = c(4.7252, 4.6923, 4.7017, 4.2422, 4.4909,
4.5497, 4.3416, 4.293, 4.6079, 4.5242, 4.1963, 4.2141, 4.1772, 4.123, 4.1906,
4.2085, 4.1817, 4.3391, 4.708, 5.1388, 5.2029, 5.224, 4.8313, 4.5976, 4.7354, 5.
1894, 4.941, 4.8115, 4.4785, 4.5784, 4.6566, 4.9161, 4.7858, 4.8951, 4.948,
4.3919, 4.6632, 4.6118, 4.3972, 4.4267, 4.1615, 4.1407, 3.7285, 3.7674, 3.7116,
4.1375, 4.1282, 3.935, 3.3428, 3.5558, 3.7017, 3.3519, 3.1784, 3.1207, 3.0176,
3.0415, 3.2668, 3.3746, 3.38, 3.4623, 3.7018, 3.4095, 3.298, 3.1857, 2.9762,
3.1934, 3.4429, 3.3604, 3.4162, 3.3258, 3.0418, 2.8179, 2.8598, 2.8307, 2.7903,
2.8014, 3.1772, 2.8541, 2.416, 2.5384, 2.816, 2.6891, 2.5263, 2.271, 2.1051,
2.1651, 2.2203, 2.1238, 2.3503, 2.362, 2.2691, 2.3323, 2.2459, 2.3785, 2.3867,
2.2767, 2.2898, 2.2543, 2.4363, 2.4686, 2.5708, 2.7651, 2.5222, 2.2825),
ENAUSDC_close = c(1.1862, 1.1412, 1.0928, 1.0256, 1.0704, 1.0716, 1.0395,
1.0255, 1.0493, 1.0278, 0.9206, 0.9422, 0.9371, 0.9415, 0.9833, 0.9192, 0.9212,
1.0404, 1.2138, 1.2446, 1.2394, 1.1984, 1.0946, 0.9606, 0.916, 0.9408, 0.8888,
0.8978, 0.7794, 0.8277, 0.9005, 0.9133, 0.9355, 0.8662, 0.9445, 0.9112, 0.9168,
0.8951, 0.846, 0.9025, 0.8719, 0.8238, 0.7188, 0.7147, 0.6877,
0.7732, 0.7878, 0.7231, 0.5993, 0.6344, 0.6393, 0.5535, 0.5022, 0.5092,
0.5175, 0.4806, 0.4975, 0.4564, 0.429, 0.4394, 0.4945, 0.4529, 0.4493, 0.4496,
0.411, 0.401, 0.4058, 0.4348, 0.4575, 0.454, 0.4036, 0.4067, 0.4129, 0.4285,
0.4164, 0.3944, 0.447, 0.395, 0.359, 0.3626, 0.38, 0.4372, 0.4457, 0.4142,
0.4034, 0.3826, 0.366, 0.3394, 0.3549, 0.3696, 0.3657, 0.3802, 0.356, 0.3815,
0.3791, 0.3612, 0.3547, 0.364, 0.4019, 0.4252, 0.4156, 0.4196, 0.3882, 0.3672),
AAVEUSDC_close = c(388, 365.68, 373.15, 316.69, 319.8, 306.06, 308.15, 352.45,
376.21, 369.33, 334.15, 327.66, 334.95, 331.35, 331.56, 312.13, 313.71, 332.95,
348.72, 353, 340.82, 342.41, 312.68, 284.99, 283.29, 289.79, 285.09, 292.89,
267.83, 291.29, 311.21, 317.28, 324.28, 309, 346.95, 332, 366.22, 353.36,
336.3, 352.42, 330.79, 332.13, 296.62, 293.04, 290.18, 317.82, 338.3, 314.94,
265.25, 256.79, 267.67, 263.11, 245.37, 244.15, 238.36, 243.35, 252.79, 247.54,
248.14, 250.2, 268, 252.71, 255.93, 263.12, 243.33, 252.98, 259.4, 2
47.46, 250.4, 247.17, 229.86, 198.12, 198.67, 204.25, 188.26, 190.2, 221.87,
198.69, 206.74, 215.48, 212.08, 203.07, 192.45, 180.05, 180.94, 174.26, 168.79,
164.29, 174.22, 174.76, 168.53, 173.55, 165.31, 178.45, 174.11, 178.43, 180.33,
182.09, 192.97, 187.89, 178.53, 182.08, 170.56, 169.11), ETHUSDC_close =
c(4034.74, 3975.39, 3879.06, 3474.91, 3436.86, 3380.71, 3313.11, 3332.66,
3499.69, 3467.64, 3325.02, 3345.58, 3388.82, 3355.39, 3385.8, 3358.42, 3336.65,
3447.37, 3573.18, 3633.24, 3627.35, 3674.61, 3441.2, 3262.6, 3250.57, 3314.51,
3269.8, 3286.72, 3019.4, 3210.99, 3326.21, 3343.59, 3415.67, 3255.79, 3438.6,
3289.19, 3350.5, 3285.05, 3265.79, 3399.59, 3344.04, 3337.1, 3071.81, 3167.03,
3111.09, 3272.72, 3376.27, 3261.2, 2970.48, 2711.8, 2816.61, 2741.62, 2700.49,
2680.88, 2629.49, 2642.7, 2679.99, 2640.79, 2673.27, 2631.72, 2775.09, 2686.11,
2688.91, 2733.38, 2618.98, 2710.88, 2735.95, 2673.5, 2785.11, 2814.66, 2664.58,
2418.3, 2364.72, 2319.9, 2209.36, 2196.1, 2481.43, 2276.12
, 2118.01, 2194, 2191, 2161.81, 2198.3, 2024.1, 1916.8, 1904.59, 1867.86,
1826.58, 1935.1, 1940.6, 1910.21, 1930.4, 1876.44, 2027.93, 1970, 1967.94,
1992.31, 1992.71, 2088.6, 2076.9, 2006.72, 1999.82, 1882.6, 1817.51),
FTMUSDC_close = c(1.381, 1.3596, 1.2222, 1.0445, 0.9705, 1.0203, 0.9958,
0.9727, 0.9813, 0.9725, 0.8807, 0.8757, 0.8086, 0.7783, 0.7531, 0.6871, 0.7899,
0.8195, 0.8206, 0.8372, 0.819, 0.7973, 0.7142, 0.637, 0.6573, 0.6902, 0.7047,
0.7072, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA),
LINKUSDC_close = c(29.81, 28.04, 27.65, 23.1, 22.93, 22.65, 22.13, 22.57,
24.75, 24.58, 22.69, 22.67, 22.08, 21.26, 20.84, 20.24, 20.79, 22.2, 23.58,
23.4, 23.23, 23.94, 21.78, 20.25, 19.73, 20.45, 19.93, 20.12, 18.47, 20.25,
21.26, 2
3.51, 24.24, 23.57, 26.55, 24.65, 26.61, 25.63, 25.58, 26.11, 24.95, 25.93,
23.12, 23.73, 22.89, 24.92, 25.39, 24.36, 20.71, 20.63, 20.59, 19.11, 18.59,
18.78, 18.26, 18.7, 18.76, 18.77, 18.79, 18.33, 19.7, 18.86, 18.91, 19.07,
17.5, 18.02, 18.21, 17.71, 17.95, 17.63, 16.36, 14.84, 15.05, 15.3, 14.54,
14.61, 17.08, 16.21, 14.1, 16.09, 16.63, 16.47, 15.33, 13.74, 12.88, 12.91,
13.06, 12.74, 14.54, 14.13, 13.68, 13.95, 13.52, 14.34, 14.19, 13.98, 14.36,
14.32, 15.12, 15.46, 15.19, 15.33, 14.13, 13.5), SUSDC_close = c(NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, 0.7845, 0.7695, 0.6634, 0.6347, 0.5203, 0.6205,
0.6091, 0.5908, 0.5589, 0.5404, 0.5382, 0.4722, 0.4615, 0.463, 0.5246, 0.5934,
0.5227, 0.4553, 0.4634, 0.4428, 0.4278, 0.436, 0.424, 0.403, 0.4082, 0.4274,
0.4568, 0.5269, 0.5379, 0.556, 0.5114, 0.5185, 0.5784, 0.571, 0.7145, 0.8272,
0.8685, 0.8384, 0.8258, 0.8386, 0.7306, 0.7, 0.7114, 0.6713, 0.7287, 0
.7645, 0.6435, 0.5438, 0.598, 0.5449, 0.5111, 0.519, 0.4459, 0.4174, 0.4305,
0.4379, 0.4628, 0.5339, 0.5556, 0.5116, 0.5051, 0.4768, 0.5134, 0.5135, 0.4959,
0.5128, 0.5276, 0.6055, 0.6179, 0.6003, 0.6033, 0.5361, 0.4822), OMUSDC_close =
c(4.0706, 4.025, 4.0875, 4.0503, 3.7564, 3.724, 3.6945, 3.6919, 3.8088, 3.7536,
3.6219, 3.6653, 3.7173, 3.7523, 3.5501, 3.8525, 3.7567, 3.8019, 3.8903, 3.9035,
3.9537, 3.9755, 3.862, 3.8991, 3.9685, 4.0064, 3.9105, 3.8044, 3.7607, 3.693,
3.7698, 3.872, 3.872, 3.7408, 3.8513, 3.6083, 3.726, 3.5371, 3.6244, 3.6144,
3.2608, 4.5631, 4.4803, 4.602, 4.4968, 5.6786, 5.2307, 5.3276, 5.0127, 5.6513,
5.8682, 5.7861, 5.6768, 6.1531, 5.8486, 5.8408, 6.0812, 5.9543, 5.7494, 5.6397,
5.5255, 7.5877, 7.5123, 7.3906, 7.4788, 7.6039, 7.5314, 7.5341, 7.6558, 8.4069,
8.268, 7.6702, 7.3587, 7.4413, 7.4682, 7.4288, 7.8594, 7.3667, 7.1693, 7.1423,
6.9744, 6.5149, 6.5599, 6.0548, 6.2294, 6.3179, 6.3857, 6.3399, 6.2524, 6.566,
6.8212, 6.9633, 6.9234, 6.8089, 6.653, 6.3123, 6
.3202, 6.4494, 6.993, 6.726, 6.5015, 6.5134, 6.264, 6.2462)), class =
c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -104L))
- one is a data frame with all my trades : qty x price, with dates
token_daily_close <- structure(list(time = structure(c(1734371999.999,
1734458399.999, 1734544799.999, 1734631199.999, 1734717599.999, 1734803999.999,
1734890399.999, 1734976799.999, 1735063199.999, 1735149599.999, 1735235999.999,
1735322399.999, 1735408799.999, 1735495199.999, 1735581599.999, 1735667999.999,
1735754399.999, 1735840799.999, 1735927199.999, 1736013599.999, 1736099999.999,
1736186399.999, 1736272799.999, 1736359199.999, 1736445599.999, 1736531999.999,
1736618399.999, 1736704799.999, 1736791199.999, 1736877599.999, 1736963999.999,
1737050399.999, 1737136799.999, 1737223199.999, 1737309599.999, 1737395999.999,
1737482399.999, 1737568799.999, 1737655199.999, 1737741599.999, 1737827999.999,
1737914399.999, 1738000799.999, 1738087199.999, 1738173599.999, 1738259999.999,
1738346399.999, 1738432799.999, 1738519199.999, 1738605599.999, 1738691999.999,
1738778399.999, 1738864799.999, 1738951199.999, 1739037599.9
99, 1739123999.999, 1739210399.999, 1739296799.999, 1739383199.999,
1739469599.999, 1739555999.999, 1739642399.999, 1739728799.999, 1739815199.999,
1739901599.999, 1739987999.999, 1740074399.999, 1740160799.999, 1740247199.999,
1740333599.999, 1740419999.999, 1740506399.999, 1740592799.999, 1740679199.999,
1740765599.999, 1740851999.999, 1740938399.999, 1741024799.999, 1741111199.999,
1741197599.999, 1741283999.999, 1741370399.999, 1741456799.999, 1741543199.999,
1741629599.999, 1741715999.999, 1741802399.999, 1741888799.999, 1741975199.999,
1742061599.999, 1742147999.999, 1742234399.999, 1742320799.999, 1742407199.999,
1742493599.999, 1742579999.999, 1742666399.999, 1742752799.999, 1742839199.999,
1742925599.999, 1743011999.999, 1743098399.999, 1743184799.999,
1743271199.999), tzone = "", class = c("POSIXct", "POSIXt")), BTCUSDC_close =
c(107112.36, 107517.25, 104630.49, 98692.01, 97172, 97559.99, 95555.99, 93260,
98743.99, 98475.99, 95740.01, 94156.03, 94860.02, 93788.55, 93672.01
, 93972.01, 93942.68, 96673.8, 97966.09, 98004.92, 97722.38, 101865.33,
97236.45, 94004.02, 92686.72, 95786.01, 94113, 95097.6, 91933.25, 96481.8,
98651.62, 99823.04, 104162, 103742.8, 104997.99, 102844.44, 106166, 104336,
105945.99, 106516.81, 104880.02, 104927.47, 99111.87, 102587, 102404,
105296.86, 104591.47, 102247.03, 97166.51, 99352, 99215.17, 97570.44, 96738.02,
97462, 96363.99, 96190, 97523.98, 96164.78, 97368.43, 95716.93, 98570.1,
97431.42, 96994, 95494.02, 94068, 96394.6, 97991.02, 96982, 96635.96, 95664.1,
94650, 87156.01, 85969.64, 84507.03, 83605.12, 85172, 94100.16, 90258.01,
85415.99, 89683.45, 88625.27, 87676.78, 85929.38, 82358.02, 78414, 81291.59,
81839.98, 80047.99, 84365.03, 84427.7, 83916.04, 84189.04, 81453.98, 84236,
84048.03, 83983.62, 84232.07, 85008, 88482.01, 87958.04, 86578.83, 86890.11,
83945.98, 82308), SUIUSDC_close = c(4.7252, 4.6923, 4.7017, 4.2422, 4.4909,
4.5497, 4.3416, 4.293, 4.6079, 4.5242, 4.1963, 4.2141, 4.1772, 4.123, 4.1906,
4.2085, 4.1817
, 4.3391, 4.708, 5.1388, 5.2029, 5.224, 4.8313, 4.5976, 4.7354, 5.1894, 4.941,
4.8115, 4.4785, 4.5784, 4.6566, 4.9161, 4.7858, 4.8951, 4.948, 4.3919, 4.6632,
4.6118, 4.3972, 4.4267, 4.1615, 4.1407, 3.7285, 3.7674, 3.7116, 4.1375, 4.1282,
3.935, 3.3428, 3.5558, 3.7017, 3.3519, 3.1784, 3.1207, 3.0176, 3.0415, 3.2668,
3.3746, 3.38, 3.4623, 3.7018, 3.4095, 3.298, 3.1857, 2.9762, 3.1934, 3.4429,
3.3604, 3.4162, 3.3258, 3.0418, 2.8179, 2.8598, 2.8307, 2.7903, 2.8014, 3.1772,
2.8541, 2.416, 2.5384, 2.816, 2.6891, 2.5263, 2.271, 2.1051, 2.1651, 2.2203,
2.1238, 2.3503, 2.362, 2.2691, 2.3323, 2.2459, 2.3785, 2.3867, 2.2767, 2.2898,
2.2543, 2.4363, 2.4686, 2.5708, 2.7651, 2.5222, 2.2825), ENAUSDC_close =
c(1.1862, 1.1412, 1.0928, 1.0256, 1.0704, 1.0716, 1.0395, 1.0255, 1.0493,
1.0278, 0.9206, 0.9422, 0.9371, 0.9415, 0.9833, 0.9192, 0.9212, 1.0404, 1.2138,
1.2446, 1.2394, 1.1984, 1.0946, 0.9606, 0.916, 0.9408, 0.8888, 0.8978, 0.7794,
0.8277, 0.9005, 0.9133, 0.9355, 0.8662, 0.9445, 0.9112, 0.916
8, 0.8951, 0.846, 0.9025, 0.8719, 0.8238, 0.7188, 0.7147, 0.6877, 0.7732,
0.7878, 0.7231, 0.5993, 0.6344, 0.6393, 0.5535, 0.5022, 0.5092, 0.5175, 0.4806,
0.4975, 0.4564, 0.429, 0.4394, 0.4945, 0.4529, 0.4493, 0.4496, 0.411, 0.401,
0.4058, 0.4348, 0.4575, 0.454, 0.4036, 0.4067, 0.4129, 0.4285, 0.4164, 0.3944,
0.447, 0.395, 0.359, 0.3626, 0.38, 0.4372, 0.4457, 0.4142, 0.4034, 0.3826,
0.366, 0.3394, 0.3549, 0.3696, 0.3657, 0.3802, 0.356, 0.3815, 0.3791, 0.3612,
0.3547, 0.364, 0.4019, 0.4252, 0.4156, 0.4196, 0.3882, 0.3672), AAVEUSDC_close
= c(388, 365.68, 373.15, 316.69, 319.8, 306.06, 308.15, 352.45, 376.21, 369.33,
334.15, 327.66, 334.95, 331.35, 331.56, 312.13, 313.71, 332.95, 348.72, 353,
340.82, 342.41, 312.68, 284.99, 283.29, 289.79, 285.09, 292.89, 267.83, 291.29,
311.21, 317.28, 324.28, 309, 346.95, 332, 366.22, 353.36, 336.3, 352.42,
330.79, 332.13, 296.62, 293.04, 290.18, 317.82, 338.3, 314.94, 265.25, 256.79,
267.67, 263.11, 245.37, 244.15, 238.36, 243.35, 252.79, 247.54, 24
8.14, 250.2, 268, 252.71, 255.93, 263.12, 243.33, 252.98, 259.4, 247.46,
250.4, 247.17, 229.86, 198.12, 198.67, 204.25, 188.26, 190.2, 221.87, 198.69,
206.74, 215.48, 212.08, 203.07, 192.45, 180.05, 180.94, 174.26, 168.79, 164.29,
174.22, 174.76, 168.53, 173.55, 165.31, 178.45, 174.11, 178.43, 180.33, 182.09,
192.97, 187.89, 178.53, 182.08, 170.56, 169.11), ETHUSDC_close = c(4034.74,
3975.39, 3879.06, 3474.91, 3436.86, 3380.71, 3313.11, 3332.66, 3499.69,
3467.64, 3325.02, 3345.58, 3388.82, 3355.39, 3385.8, 3358.42, 3336.65, 3447.37,
3573.18, 3633.24, 3627.35, 3674.61, 3441.2, 3262.6, 3250.57, 3314.51, 3269.8,
3286.72, 3019.4, 3210.99, 3326.21, 3343.59, 3415.67, 3255.79, 3438.6, 3289.19,
3350.5, 3285.05, 3265.79, 3399.59, 3344.04, 3337.1, 3071.81, 3167.03, 3111.09,
3272.72, 3376.27, 3261.2, 2970.48, 2711.8, 2816.61, 2741.62, 2700.49, 2680.88,
2629.49, 2642.7, 2679.99, 2640.79, 2673.27, 2631.72, 2775.09, 2686.11, 2688.91,
2733.38, 2618.98, 2710.88, 2735.95, 2673.5, 2785.11, 2814.66, 2
664.58, 2418.3, 2364.72, 2319.9, 2209.36, 2196.1, 2481.43, 2276.12, 2118.01,
2194, 2191, 2161.81, 2198.3, 2024.1, 1916.8, 1904.59, 1867.86, 1826.58, 1935.1,
1940.6, 1910.21, 1930.4, 1876.44, 2027.93, 1970, 1967.94, 1992.31, 1992.71,
2088.6, 2076.9, 2006.72, 1999.82, 1882.6, 1817.51), FTMUSDC_close = c(1.381,
1.3596, 1.2222, 1.0445, 0.9705, 1.0203, 0.9958, 0.9727, 0.9813, 0.9725, 0.8807,
0.8757, 0.8086, 0.7783, 0.7531, 0.6871, 0.7899, 0.8195, 0.8206, 0.8372, 0.819,
0.7973, 0.7142, 0.637, 0.6573, 0.6902, 0.7047, 0.7072, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), LINKUSDC_close = c(29.81, 28.04,
27.65, 23.1, 22.93, 22.65, 22.13, 22.57, 24.75, 24.58, 22.69, 22.67, 22.08,
21.26, 20.84, 20.24, 20.79, 22.2, 23.58, 23.4, 23.23, 23.94
, 21.78, 20.25, 19.73, 20.45, 19.93, 20.12, 18.47, 20.25, 21.26, 23.51, 24.24,
23.57, 26.55, 24.65, 26.61, 25.63, 25.58, 26.11, 24.95, 25.93, 23.12, 23.73,
22.89, 24.92, 25.39, 24.36, 20.71, 20.63, 20.59, 19.11, 18.59, 18.78, 18.26,
18.7, 18.76, 18.77, 18.79, 18.33, 19.7, 18.86, 18.91, 19.07, 17.5, 18.02,
18.21, 17.71, 17.95, 17.63, 16.36, 14.84, 15.05, 15.3, 14.54, 14.61, 17.08,
16.21, 14.1, 16.09, 16.63, 16.47, 15.33, 13.74, 12.88, 12.91, 13.06, 12.74,
14.54, 14.13, 13.68, 13.95, 13.52, 14.34, 14.19, 13.98, 14.36, 14.32, 15.12,
15.46, 15.19, 15.33, 14.13, 13.5), SUSDC_close = c(NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, 0.7845, 0.7695, 0.6634, 0.6347, 0.5203, 0.6205, 0.6091, 0.5908,
0.5589, 0.5404, 0.5382, 0.4722, 0.4615, 0.463, 0.5246, 0.5934, 0.5227, 0.4553,
0.4634, 0.4428, 0.4278, 0.436, 0.424, 0.403, 0.4082, 0.4274, 0.4568, 0.5269,
0.5379, 0.556, 0.5114, 0.5185, 0.5784, 0.571, 0.7145, 0.8272, 0.86
85, 0.8384, 0.8258, 0.8386, 0.7306, 0.7, 0.7114, 0.6713, 0.7287, 0.7645,
0.6435, 0.5438, 0.598, 0.5449, 0.5111, 0.519, 0.4459, 0.4174, 0.4305, 0.4379,
0.4628, 0.5339, 0.5556, 0.5116, 0.5051, 0.4768, 0.5134, 0.5135, 0.4959, 0.5128,
0.5276, 0.6055, 0.6179, 0.6003, 0.6033, 0.5361, 0.4822), OMUSDC_close =
c(4.0706, 4.025, 4.0875, 4.0503, 3.7564, 3.724, 3.6945, 3.6919, 3.8088, 3.7536,
3.6219, 3.6653, 3.7173, 3.7523, 3.5501, 3.8525, 3.7567, 3.8019, 3.8903, 3.9035,
3.9537, 3.9755, 3.862, 3.8991, 3.9685, 4.0064, 3.9105, 3.8044, 3.7607, 3.693,
3.7698, 3.872, 3.872, 3.7408, 3.8513, 3.6083, 3.726, 3.5371, 3.6244, 3.6144,
3.2608, 4.5631, 4.4803, 4.602, 4.4968, 5.6786, 5.2307, 5.3276, 5.0127, 5.6513,
5.8682, 5.7861, 5.6768, 6.1531, 5.8486, 5.8408, 6.0812, 5.9543, 5.7494, 5.6397,
5.5255, 7.5877, 7.5123, 7.3906, 7.4788, 7.6039, 7.5314, 7.5341, 7.6558, 8.4069,
8.268, 7.6702, 7.3587, 7.4413, 7.4682, 7.4288, 7.8594, 7.3667, 7.1693, 7.1423,
6.9744, 6.5149, 6.5599, 6.0548, 6.2294, 6.3179, 6.3857, 6.339
9, 6.2524, 6.566, 6.8212, 6.9633, 6.9234, 6.8089, 6.653, 6.3123, 6.3202,
6.4494, 6.993, 6.726, 6.5015, 6.5134, 6.264, 6.2462)), class = c("tbl_df",
"tbl", "data.frame"), row.names = c(NA, -104L)) gabx@magnolia [R]
dout(all_trade_final) Error in dout(all_trade_final) : could not find function
"dout"
gabx@magnolia [R] dput(all_trade_final)
structure(list(time = structure(c(1734372120, 1734372240, 1734372360,
1734372480, 1734372600, 1734372720, 1734426090.135, 1734553973.032,
1735810398.277, 1735810489.902, 1735812883.798, 1735812912.427, 1735812922.868,
1735812934.674, 1735812945.707, 1735812955.168, 1736177563.787, 1736177649.381,
1736261991.422, 1736262093.964, 1736779190.992, 1736854468.083, 1736865119.74,
1736865312.915, 1736865453.951, 1736974766.64, 1736975085.933, 1736975211.156,
1736975380.095, 1737041368.809, 1737295523.705, 1737295650.336, 1737315384.148,
1737333105.442, 1737335082.949, 1737336735.697, 1737625581.878, 1737625599.839,
1737967031.492, 1737969957.279, 1737982296.161, 1737983492.087, 1737989818.863,
1737990022.153, 1738058503.483, 1738059550.671, 1738076112.736, 1738142709.422,
1738157053.345, 1738157206.539, 1738161507.243, 1738163335.254, 1738169351.788,
1738169494.283, 1738169531.258, 1738171799.704, 1738176968.536, 1738216843.704,
1738769416.056, 1738854703.218, 1738856868.753, 1738936692.68,
1738937334.883, 1739296718.084, 1739379482.659, 1739446986.289,
1739979238.947, 1739996323.332, 1740115921.015, 1740491373.055, 1740596755.646,
1740667962.173, 1740768770.074, 1740768953.683, 1740786989.548, 1741012632.874,
1741100761.788, 1741109444.315, 1741185527.418, 1741208042.454, 1741283447.971,
1741283872.186, 1741295325.927, 1741295477.314, 1741295693.088, 1741342541.164,
1741629884.419, 1741635304.779, 1741685200.989, 1741686554.221, 1741698998.622,
1741707118.96, 1741802675.392, 1742141908.962, 1742257808.523, 1742289195.923,
1742297055.385, 1742302133.885, 1742302466.715, 1742325681.41, 1742325806.343,
1742325896.024, 1742325942.537, 1742378556.512, 1742387323.985, 1742391238.519,
1742398133.361, 1742398190.194, 1742398284.155, 1742398685.416, 1742403613.643,
1742404126.746, 1742871155.426, 1742891897.907, 1742892224.948, 1742998171.435,
1743000224.05, 1743001791.162, 1743011323.324, 1743017318.549, 1743406396.586,
1743408729.623, 1743425658.334, 1743428419.647, 1743429
808.131, 1743430795.489, 1743431687.034, 1743439548.713, 1743440894.393,
1743445384.52, 1743495501.973, 1743504926.243, 1743519851.83, 1743520420.807,
1743530499.577, 1743531476.483, 1743546009.677, 1743584674.851, 1743585101.461,
1743601492.33, 1743601850.895, 1743672899.162, 1743679321.994, 1743679387.991,
1743780565.767, 1743785970.297), tzone = "UTC", class = c("POSIXct",
"POSIXt")), symbol = c("FTMUSDC", "SUIUSDC", "ETHUSDC", "ENAUSDC", "BTCUSDC",
"AAVEUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "ETHUSDC", "AAVEUSDC", "BTCUSDC",
"ENAUSDC", "ETHUSDC", "FTMUSDC", "SUIUSDC", "ETHUSDC", "ENAUSDC", "AAVEUSDC",
"FTMUSDC", "SUIUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"AAVEUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "AAVEUSDC", "AAVEUSDC",
"AAVEUSDC", "AAVEUSDC", "AAVEUSDC", "AAVEUSDC", "BTCUSDC", "AAVEUSDC",
"AAVEUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"SUIUSDC", "BTCUSDC", "AAVEUSDC", "SUIUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"ETHUSDC",
"ETHUSDC", "ETHUSDC", "BTCUSDC", "SUIUSDC", "SUIUSDC", "BTCUSDC", "SUIUSDC",
"BTCUSDC", "BTCUSDC", "BTCUSDC", "SUIUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"BTCUSDC", "SUSDC", "BTCUSDC", "SUIUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"BTCUSDC", "BTCUSDC", "SUIUSDC", "SUIUSDC", "SUIUSDC", "SUIUSDC", "SUSDC",
"BTCUSDC", "SUSDC", "SUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "SUSDC", "SUSDC",
"OMUSDC", "SUIUSDC", "OMUSDC", "SUIUSDC", "BTCUSDC", "OMUSDC", "OMUSDC",
"OMUSDC", "BTCUSDC", "ETHUSDC", "SUIUSDC", "OMUSDC", "OMUSDC", "OMUSDC",
"BTCUSDC", "SUIUSDC", "BTCUSDC", "SUSDC", "ENAUSDC", "ENAUSDC", "ETHUSDC",
"BTCUSDC", "ETHUSDC", "BTCUSDC", "SUSDC", "SUIUSDC", "BTCUSDC", "BTCUSDC",
"BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC",
"BTCUSDC", "BTCUSDC", "BTCUSDC", "BTCUSDC", "SUSDC", "BTCUSDC", "SUSDC",
"BTCUSDC", "BTCUSDC"),
executed_qty = c(284270, 80024, 91.8981, 207836, 9.3372107, 1152.058, 0.04811,
0.12324, -0.06504, -32.0833, 94.408, 0.96557, 17708.79, 4.882, 22584, 4641.4,
-64.6968, 194251.85, -1246.466, -306854, -61748.4, 2.42992, 2.42014, 4.78121,
0.04825, 0.29346, 835.68, -0.001, -0.01505, -0.125, -621.323, -214.357, 835,
15.472, 866.666, -834.998, 1.20446, 54.943, -936.69, -1.76856, -1.73956,
-1.71811, -0.72513, -0.98166, 4.72757, 67649.3, 4.73485, -0.393, -67622, -1.7,
-1.7, 8.98573, 36.1158, -36.1158, -8.8583, -0.70622, -27.3, 73781.2, 0.84257,
-73781.2, -3.50343, 6.34343, -0.45516, 84682, 0.26061, 0.254, -0.36895,
0.75773, 323689.3, -2.69675, 22917.9, 0.58439, 2.06429, -2.06429, 2.07134,
-6.12345, -107599.9, 102163, -102163, 92527.9, -323689.3, -3.09273, 273454,
-273454, 3.09273, 1.54911, 2.54393, 2, 1.52842, 1.48698, 1.36813, -0.01848,
0.42519, 528333.3, -528333.3, 37580, -43102.8, -37580, -49425.1, -3, 354, 502,
34532, -0.01435, 124.7401, 106269.9, -2178, -586, -32624, 2.82199, 13326.3, 1
.40107, 447761.1, 142861.76, 490049.63, -0.3663, -0.07286, -140.3605, 0.4878,
63813.8, -119596.2, 5.78271, -3.37969, -3.41518, 6.7704, -6.75106, 6.71609,
-6.72629, 6.71421, -6.7396, 6.6851, -6.74, 6.717, -0.17957, -6.59629, -3.30205,
10.01426, -0.03538, -6.5872, 6.56085, -6.56, -359471.3, -6.56, -152437.4, 7,
7.40359), price = c(1.382, 4.7256, 4038, 1.1855, 107012, 386.55, 107358.81,
101422.93, 96408.23, 3443.981937, 332.340557, 96606.12, 1.043348, 3460.4,
0.856681, 4.411298, 3698.158383, 1.231695, 323.031195, 0.732393, 4.368867,
96526.3, 96916.53115, 97133.32, 97228.23, 99651.73, 0.003195, 99519.61,
99663.37, 98394.97, 0.00302, 0.00301, 0.003131, 0.003003, 0.003005, 0.003131,
101941.107914, 333.943712, 293.405892, 98950.008481, 100600, 101335.283692,
102050, 101868.208951, 102900, 0.000038, 102741.596027, 294.738321, 3.649213,
101846.9, 101845.270978, 102368.611214, 0.03027, 0.03026, 0.03027,
102384.062461, 3.757, 0.000038, 98487.23, 3.274561, 97076.26334, 98853.757704,
99403.96275
2, 0.000035, 96870.45, 96050, 96382, 96426.612765, 0.000009, 88900, 2.791,
85558.02, 84977.109628, 84683.133951, 84394.870663, 92227.890836, 2.306263,
2.428989, 2.447165, 2.701886, 0.543194, 88418.629504, 0.5525, 0.5515,
89720.689866, 88856.101525, 78618.406955, 79413.144936, 81500, 80700, 80000,
81125.99, 81928.04, 0.000006, 0.000006, 0.000085, 0.000028, 0.000083, 0.000027,
82025.99514, 6.946406, 6.945675, 6.949552, 83618, 0.024, 0.000028, 6.732009,
6.733288, 6.725032, 84392.65, 2.401251, 84686.879799, 0.000007, 0.000005,
0.000005, 2033.5, 86605.99, 0.023223, 86100, 0.595482, 2.247826, 82214.102411,
82936.397434, 82074.529897, 82801.529337, 83038.750762, 83471.184268,
83344.568664, 83494.401651, 83179.906336, 83857.962478, 84146.764724,
84460.185312, 84642.9, 84987.11819, 84795.687321, 85376.758785, 84783.59,
85104.418136, 85446.292013, 85867.737502, 0.000006, 83126.156483, 0.000005,
83255.75794, 83355.928936),
cummulative_quote_qty = c(392861.14, 378161.4144, 371084.5278,
246389.578, 999193.5914284, 445328.0199, 5165.0323491, 12499.3618932,
-6270.3912792, -110494.305684, 31375.60727, 93279.9712884,
18476.435418, 16893.6728, 19347.2739, 20474.59783, -239259.013301,
239258.996981, -402647.40174, -224737.6957, -269770.56434,
234551.186896, 234551.5736976, 464414.8009172, 4691.2620975,
29243.7966858, 266319.604811376, -99.51961, -1499.9337185,
-12299.37125, -197239.18517336, -67873.8886713888, 274484.30729385,
4661.01711291216, 262699.317950113, -263696.723173419, 122783.9868375,
18347.86937, -274830.36532, -174999.0269994, -174999.736,
-174105.1642638, -73999.5165, -99999.9459993, 486466.953,
263987.20719179, 486466.0459494, -115.83216, -246767.10508,
-173139.73, -173136.9606632, 919856.7008428, 111456.491386979,
-111574.857313511, -27375.5581302466, -72305.6725914, -102.5661,
295672.253552752, 82982.3853811, -241601.07335, -340099.8932747,
627071.8922352, -45244.7076862, 285495.9929367, 25245.4079745,
24396.7, -35560.1389, 73065.3372901, 285857.093441205, -239741.075,
63963.8589, 49999.2513078, 175417.3976347, -174810.5465838,
174810.4713998, -564752.8781372, -248153.65645, 248152.80384,
-250009.68949, 249999.80453, -175826.00598, -273454.9480248,
151083.27835, -150809.881, 277481.8691678, 137647.8754335,
199999.7240045, 158826.2898715, 124566.23, 119999.286, 109450.4,
-1499.2082952, 34834.9833276, 263584.405570068, -265801.376630196,
265446.361943, -99795.399054624, -257554.5326306, -110104.81917606,
-246077.9854208, 2459.0276, 3486.7288, 239981.9146, -1199.9183,
250883.12695668, 249352.169449152, -14662.3161, -3945.707,
-219397.4406, 238155.2143735, 31999.78779, 118652.2466801,
273464.042572539, 61729.130735312, 211648.367785071, -744.87105,
-6310.1124314, -283927.208051972, 41999.58, 37999.95756,
-268831.48716, 475420.3121534, -280299.3130424, -280299.2930129,
560599.4742213, -560599.5887202, 560599.9859508, -560599.7387605,
560598.9465102, -560599.2967411, 560598.8649637, -567149.194237,
567319.0647382, -15199.325553, -560599.6778464, -279999.5993174,
854985.0604314, -2999.6434142, -560599.8231446, 560600.3049556,
-563292.3580137, -180282.355521318, -545307.5865265, - 63604.52039374,
582790.305582, 617133.121909), day = structure(c(20073, 20073,
20073, 20073, 20073, 20073, 20074, 20075, 20090, 20090, 20090,
20090, 20090, 20090, 20090, 20090, 20094, 20094, 20095, 20095,
20101, 20102, 20102, 20102, 20102, 20103, 20103, 20103, 20103,
20104, 20107, 20107, 20107, 20108, 20108, 20108, 20111, 20111,
20115, 20115, 20115, 20115, 20115, 20115, 20116, 20116, 20116,
20117, 20117, 20117, 20117, 20117, 20117, 20117, 20117, 20117,
20117, 20118, 20124, 20125, 20125, 20126, 20126, 20130, 20131,
20132, 20138, 20138, 20140, 20144, 20145, 20146, 20147, 20147,
20147, 20150, 20151, 20151, 20152, 20152, 20153, 20153, 20153,
20153, 20153, 20154, 20157, 20157, 20158, 20158, 20158, 20158,
20159, 20163, 20165, 20165, 20165, 20165, 20165, 20165, 20165,
20165, 20165, 20166, 20166, 20166, 20166, 20166, 20166, 20166,
20166, 20166, 20172, 20172, 20172, 20173, 20173, 20173, 20173,
20173, 20178, 20178, 20178, 20178, 20178, 20178, 20178, 20178,
20178, 20178, 20179, 20179, 20179, 20179, 20179, 20179, 20179,
20180, 20180, 20180, 20180, 20181, 20181, 20181, 20182, 20182
), class = "Date")), row.names = c(NA, -146L), class = c("tbl_df", "tbl",
"data.frame"))
All prices, quantity, date/time etc are given by functions from Binancer
package which connect to Binance (the exchange) API and there is no manual
operations.
Below is the first lines of the final tibble I would like:
result <- structure(list(time = structure(c(1734375599, 1734375720, 1734375840,
1734375960, 1734376080, 1734376200, 1734376320, 1734461999), tzone = "", class
= c("POSIXct", "POSIXt")), BTCUSDC = c(NA, NA, NA, NA, NA, 107112.36, NA, NA ),
SUIUSDC = c(NA, NA, 4.6923, NA, NA, NA, NA, NA), ENAUSDC = c(NA, NA, NA, NA,
1.0256, NA, NA, NA), AAVEUSDC = c(NA, NA, NA, NA, NA, NA, 386.55, NA), ETHUSDC
= c(NA, NA, NA, 3879.06, NA, NA, NA, NA), FTMUSDC = c(NA, 1.381, NA, NA, NA,
NA, NA, NA), executed_qty = c(NA, 284270, 80024, 91.8981, 207836, 9.3372107,
1152.058, NA), price = c(NA, NA, NA, NA, NA, NA, NA, NA), BTCUSDC_close =
c(107112.36, NA, NA, NA, NA, NA, NA, 107517.25), SUIUSDC_close = c(4.7252, NA,
NA, NA, NA, NA, NA, 4.6923), ENAUSDC_close = c(1.1862, NA, NA, NA, NA, NA, NA,
1.1412), AAVEUSDC_close = c(388, NA, NA, NA, NA, NA, NA, 365.68), ETHUSDC_close
= c(4034.74, NA, NA, NA, NA, NA, NA, 3975.39), FTMUSDC_close = c(1.381, NA, NA,
NA, NA, NA, NA, 1.3596), LINKUSDC_close = c(29.81, NA, NA
, NA, NA, NA, NA, 28.04), SUSDC_close = c(NA, NA, NA, NA, NA, NA, NA, NA),
OMUSDC_close = c(4.0706, NA, NA, NA, NA, NA, NA, 4.025)), row.names = c(NA,
-8L), class = c("tbl_df", "tbl", "data.frame"))
The idea is to have
- date for closing price (everyday @ 19:59:59)
- date for trades
These two operations must be ordered by time, mix time of closing prices and
time of trades
- price of token for the daily closes (name TOKEN_close) and price when the
trade was executed .
I have tried many many way to build my final tibble "result", and I am looking
for help.
Thank you in advance.
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
