I am trying to tidy this data, but I just don't know how to do it. I have been trying using unnest_longer/unnest_wider and pivot_longer/pivot_wider and some other things similar to that but I just can't seem to make it work.
This is the dataset I'm working with
data <- structure(list(pred = list(list(x = c(18874, 18875, 18878, 18881,
18882, 18888, 18889, 18890, 18895, 18896, 18902, 18903, 18909,
18910, 18912, 18916, 18917, 18923, 18924, 18930, 18931, 18932,
18937, 18938, 18944, 18945, 18951, 18952, 18958, 18959, 18965,
18966, 18967, 18969, 18972, 18973, 18979, 18980, 18986, 18987,
18993, 18994, 18998, 19000, 19001, 19007, 19008, 19014, 19015,
19021, 19022, 19028, 19029, 19035, 19036, 19042, 19043, 19049,
19050, 19056, 19057, 19063, 19064, 19070, 19071, 19077, 19078,
19084, 19085, 19089, 19091, 19092, 19096, 19097, 19098, 19099,
19105, 19106, 19112, 19113, 19114, 19119, 19120, 19126, 19127,
19133, 19134, 19140, 19141, 19147, 19148, 19154, 19155, 19161,
19162, 19168, 19169, 19186), y = c(4987838.1159024, 4988906.93007818,
4992735.16448883, 4997000.9190278, 4998074.51048947, 5006674.25658655,
5007700.02620566, 5008730.85230945, 5015447.07398583, 5016746.48839726,
5025581.13071668, 5026863.95839996, 5035015.64736656, 5035904.11764838,
5038111.76642534, 5043113.19344969, 5044229.69252944, 5052451.33213874,
5053642.13792275, 5062112.16527229, 5063334.88337535, 5064549.0708968,
5072058.20858888, 5073299.60970692, 5081192.44626772, 5082056.06415763,
5089980.58503685, 5091015.69283811, 5099158.13613696, 5100082.85959397,
5108233.44479725, 5109384.50771644, 5110512.97399426, 5113002.25597379,
5116732.71163329, 5117801.27266479, 5125398.3229542, 5126348.44923141,
5134047.67218683, 5135335.75296336, 5145462.76775497, 5146985.37531126,
5153717.02703526, 5156633.64943185, 5158126.39786108, 5168445.30543688,
5169982.82614605, 5180989.26423331, 5182128.63051417, 5191654.57076405,
5192816.38367361, 5202520.11236402, 5203623.80464993, 5212447.40283833,
5213614.57625407, 5221999.5659595, 5223238.91581556, 5231902.02888342,
5232891.65914209, 5241370.89640515, 5242412.29130239, 5250665.15664764,
5251859.61868926, 5260686.60041406, 5261784.95201476, 5270233.42100326,
5271683.75549553, 5283091.02997097, 5284794.45085743, 5292417.46060288,
5296318.9655005, 5298073.80497229, 5304868.30586346, 5306661.0308326,
5308511.30567548, 5310425.99187024, 5321267.74956369, 5322684.5544752,
5332814.61992767, 5334266.81022052, 5335751.1981208, 5343866.73288937,
5345409.48001621, 5357234.7301669, 5358619.07954795, 5368459.24680201,
5369693.26109311, 5379435.90832626, 5380843.15844669, 5389968.42204838,
5391331.79564167, 5400352.28006781, 5401881.2824674, 5411009.88574841,
5412376.51787713, 5421437.35691362, 5422702.37317747, 5448655.37667612
)), list(x = c(18874, 18875, 18878, 18881, 18882, 18888, 18889,
18890, 18895, 18896, 18902, 18903, 18909, 18910, 18912, 18916,
18917, 18923, 18924, 18930, 18931, 18932, 18937, 18938, 18944,
18945, 18951, 18952, 18958, 18959, 18965, 18966, 18967, 18969,
18972, 18973, 18979, 18980, 18986, 18987, 18993, 18994, 18998,
19000, 19001, 19007, 19008, 19014, 19015, 19021, 19022, 19028,
19029, 19035, 19036, 19042, 19043, 19049, 19050, 19056, 19057,
19063, 19064, 19070, 19071, 19077, 19078, 19084, 19085, 19089,
19091, 19092, 19096, 19097, 19098, 19099, 19105, 19106, 19112,
19113, 19114, 19119, 19120, 19126, 19127, 19133, 19134, 19140,
19141, 19147, 19148, 19154, 19155, 19161, 19162, 19168, 19169,
19186), y = c(4543634.67229512, 4544626.73327232, 4548063.13652729,
4551556.17014954, 4552593.16024453, 4566629.89570167, 4567362.6818219,
4567925.14310217, 4576575.54027706, 4578995.57231499, 4588801.43060272,
4590682.51803913, 4599745.7408757, 4600652.58606416, 4602798.81099128,
4607512.61779692, 4608567.12622165, 4616170.51349057, 4617411.68664403,
4628873.53274769, 4634466.9166392, 4640651.468224, 4659121.48594591,
4660753.93791295, 4668632.16289388, 4669437.03921979, 4676731.83786372,
4677755.88216385, 4685197.83019288, 4686210.27694338, 4693964.71311769,
4695024.52828326, 4696100.1172305, 4698625.12163736, 4702442.34890326,
4703462.77377223, 4710372.85820629, 4711314.18642898, 4735625.92847586,
4741278.99246463, 4765645.79554771, 4769064.14932847, 4784328.48187221,
4790362.75313618, 4793150.27892555, 4809973.68475803, 4812248.19950986,
4823604.98185921, 4824834.4467584, 4833485.49288174, 4834625.77534634,
4843344.74285102, 4844480.90441896, 4852344.82728485, 4853457.89567931,
4860746.86767346, 4861857.43812705, 4869515.26937112, 4870414.79602584,
4879085.98531143, 4880171.89885683, 4890130.30419433, 4893129.08176856,
4905481.74850888, 4907370.94158851, 4938453.85945609, 4943596.32240004,
4969358.19525194, 4972236.07897703, 4983056.7704366, 4989158.65615597,
4992451.57075646, 5006607.5952214, 5010068.31290117, 5013296.25184891,
5016185.16412616, 5034257.22711952, 5040152.72009795, 5075869.46474071,
5081872.31226968, 5087590.30399235, 5104781.78562601, 5106064.95591044,
5115357.39322838, 5116699.91351997, 5125480.7809375, 5126833.63236929,
5135559.10937401, 5136995.12730332, 5145389.27667221, 5146749.89684873,
5155146.33078983, 5156725.15764261, 5165642.76370224, 5166997.54230692,
5175803.449825, 5177018.33105046, 5199484.00514847)), list(x = c(18874,
18875, 18878, 18881, 18882, 18888, 18889, 18890, 18895, 18896,
18902, 18903, 18909, 18910, 18912, 18916, 18917, 18923, 18924,
18930, 18931, 18932, 18937, 18938, 18944, 18945, 18951, 18952,
18958, 18959, 18965, 18966, 18967, 18969, 18972, 18973, 18979,
18980, 18986, 18987, 18993, 18994, 18998, 19000, 19001, 19007,
19008, 19014, 19015, 19021, 19022, 19028, 19029, 19035, 19036,
19042, 19043, 19049, 19050, 19056, 19057, 19063, 19064, 19070,
19071, 19077, 19078, 19084, 19085, 19089, 19091, 19092, 19096,
19097, 19098, 19099, 19105, 19106, 19112, 19113, 19114, 19119,
19120, 19126, 19127, 19133, 19134, 19140, 19141, 19147, 19148,
19154, 19155, 19161, 19162, 19168, 19169, 19186), y = c(6837674.63161358,
6839095.11925122, 6844542.20835098, 6851054.55024802, 6853070.15145438,
6865726.63320811, 6867888.81673384, 6870044.23505406, 6880527.20817019,
6882582.5229332, 6895796.39919796, 6898074.72719456, 6912468.67545876,
6914743.80093838, 6919351.44021645, 6928886.44831446, 6931278.28072889,
6945132.06378606, 6947351.01332578, 6961459.49343378, 6964025.79587908,
6966603.79763118, 6978585.87478991, 6980827.76888646, 6994205.06634484,
6996221.72463399, 7009993.66025357, 7012249.42794125, 7026246.95842054,
7028522.65478108, 7042030.33098056, 7044094.57120865, 7046121.60308709,
7050144.27780832, 7056013.58698873, 7057918.64032469, 7070278.72977622,
7072240.90479074, 7085509.66819043, 7088159.76145086, 7102730.61561068,
7105063.74522051, 7114263.28990204, 7118743.26800567, 7120991.317835,
7134635.64994705, 7136862.38886909, 7150784.28493939, 7152859.54904185,
7166025.62541072, 7168163.73167175, 7181260.03535752, 7183255.4033301,
7197837.23961366, 7200141.92164776, 7213358.18232103, 7215516.37511619,
7229024.28982619, 7231095.74452759, 7245215.64679095, 7247516.33963663,
7262515.77390649, 7265007.84408975, 7280011.79417829, 7282437.94732497,
7298112.02315812, 7300615.67070639, 7315971.87764194, 7318233.13377219,
7327448.50684943, 7332220.72918957, 7334574.81053332, 7343900.85554346,
7346230.01360395, 7348561.75170728, 7350897.47932561, 7365986.09930336,
7368430.35919467, 7382080.30636531, 7384192.78600761, 7386383.9423996,
7398815.86928654, 7401311.30512776, 7416697.37653029, 7419005.92104344,
7432668.11871956, 7434878.63520601, 7448465.90509644, 7450769.6905135,
7465202.26436915, 7467516.52853225, 7482312.96596662, 7484953.6205842,
7498236.8888241, 7499607.76199112, 7511102.1465873, 7513647.64949595,
7558449.75815214)), list(x = c(18874, 18875, 18878, 18881, 18882,
18888, 18889, 18890, 18895, 18896, 18902, 18903, 18909, 18910,
18912, 18916, 18917, 18923, 18924, 18930, 18931, 18932, 18937,
18938, 18944, 18945, 18951, 18952, 18958, 18959, 18965, 18966,
18967, 18969, 18972, 18973, 18979, 18980, 18986, 18987, 18993,
18994, 18998, 19000, 19001, 19007, 19008, 19014, 19015, 19021,
19022, 19028, 19029, 19035, 19036, 19042, 19043, 19049, 19050,
19056, 19057, 19063, 19064, 19070, 19071, 19077, 19078, 19084,
19085, 19089, 19091, 19092, 19096, 19097, 19098, 19099, 19105,
19106, 19112, 19113, 19114, 19119, 19120, 19126, 19127, 19133,
19134, 19140, 19141, 19147, 19148, 19154, 19155, 19161, 19162,
19168, 19169, 19186), y = c(2470602.06930686, 2470641.90264202,
2470658.82845439, 2470472.77472669, 2470518.03041257, 2477676.50478784,
2477466.14098869, 2477049.79202863, 2480226.68619613, 2481744.46439006,
2484190.38240925, 2485191.88944337, 2486901.41568565, 2486928.9073925,
2486966.95038257, 2486974.01086532, 2486991.02759442, 2487075.85488954,
2487095.81536671, 2491670.50905448, 2497224.83541977, 2503832.14393963,
2525554.0834605, 2526209.82047406, 2526822.16879811, 2526808.35620217,
2526966.78637919, 2527009.12693653, 2527241.70474883, 2527233.73974639,
2527704.17762915, 2527728.51800069, 2527783.96903113, 2528116.78654088,
2528541.67939163, 2528501.07533032, 2527974.48386291, 2528012.11864798,
2546549.19600197, 2552297.44356793, 2584224.89133293, 2588745.72469754,
2608662.48312364, 2615784.07550846, 2619062.67473519, 2639025.130867,
2640803.36887398, 2647022.35068979, 2647237.06884106, 2648773.65328117,
2648732.5526001, 2648981.44087729, 2648999.96334225, 2649229.59025511,
2649309.17198096, 2649474.92130813, 2649499.12240488, 2649609.28962203,
2649561.15326496, 2650709.94293949, 2650876.44988098, 2652988.004158,
2654639.49012543, 2658693.76805024, 2659305.11050414, 2697290.03011849,
2704692.03011314, 2742524.94540696, 2746609.05099356, 2761969.18094178,
2769533.05589864, 2773118.38802635, 2788191.90201124, 2792136.66167375,
2795963.95418609, 2799565.51177564, 2821129.56883631, 2828216.4063665,
2874516.59578085, 2882480.97689492, 2890571.61289263, 2917104.20359972,
2916900.39124414, 2919046.74713679, 2919171.31855735, 2921309.13484986,
2921356.34641115, 2923578.31571715, 2923706.44685229, 2924921.43686509,
2924970.42894827, 2925215.85822255, 2925306.19743447, 2926829.78156041,
2926851.44385321, 2928869.59156056, 2928911.41765244, 2932600.95075214
)), list(x = c(18874, 18875, 18878, 18881, 18882, 18888, 18889,
18895, 18896, 18902, 18903, 18909, 18910, 18912, 18916, 18917,
18923, 18924, 18930, 18931, 18932, 18937, 18938, 18944, 18945,
18951, 18952, 18958, 18959, 18965, 18966, 18967, 18969, 18972,
18973, 18979, 18980, 18986, 18987, 18993, 18994, 18998, 19000,
19001, 19007, 19008, 19014, 19015, 19021, 19022, 19028, 19029,
19035, 19036, 19042, 19043, 19049, 19050, 19056, 19057, 19063,
19064, 19070, 19071, 19077, 19078, 19084, 19085, 19089, 19091,
19092, 19096, 19097, 19098, 19099, 19105, 19106, 19111, 19112,
19113, 19114, 19119, 19120, 19126, 19127, 19133, 19134, 19140,
19141, 19147, 19148, 19154, 19155, 19161, 19162, 19168, 19169,
19186), y = c(17587.7574498014, 17595.2348213605, 17626.5688652453,
17664.7915953224, 17677.3012178767, 17739.7649917326, 17747.182282838,
17821.6234829287, 17826.371518217, 17364.83254308, 17274.8376764278,
17179.2527631239, 17181.5063123316, 17185.5197455186, 17188.3583049669,
17188.2110423459, 17175.5581474151, 17170.9795080035, 17381.0677823317,
17490.9503212124, 17599.505206497, 17838.6438542349, 17842.8662267074,
17864.496424167, 17870.6732631094, 17941.1203503114, 17947.4987329412,
17994.5333745117, 18002.6205294813, 18029.0775657291, 18034.7898305224,
18041.397381719, 18055.5194226531, 18073.9675963282, 18080.5707387076,
18157.5914613566, 18168.0176193884, 18181.7347523956, 18183.8349291214,
18242.1982888084, 18246.6157841911, 18248.9645611007, 18247.608053259,
18246.9301653125, 18259.6429977764, 18268.8757971934, 18326.2022413625,
18330.9578128338, 18337.3539756167, 18336.9480767801, 18340.7150019774,
18347.0396301694, 18435.9454550161, 18446.0673317526, 18496.9821741205,
18502.0145822547, 18530.9749256807, 18536.4682416423, 18575.630652993,
18583.0016884544, 18644.0907960853, 18652.1344712247, 18682.604268057,
18682.809740588, 18680.8829278405, 18680.8883522478, 18681.0105960912,
18681.007901745, 18680.9997491452, 18681.0006759017, 18681.0035437126,
18681.0081855724, 18680.9814283619, 18680.9319902139, 18680.8545073784,
18682.1151342765, 18684.5851862615, 18714.6699920123, 18721.5102517713,
18728.9253257799, 18737.2361799769, 18791.0935324498, 18801.2671025594,
18855.3803814931, 18865.4164000654, 18950.903702181, 18964.7890589136,
19055.9988865211, 19069.5296604342, 19159.8832319673, 19173.4976704051,
19254.3734171428, 19263.3065403369, 19311.2732601796, 19315.6322247739,
19362.9903396964, 19371.834075158, 19520.3350763396)), list(x = c(18875,
18876, 18877, 18878, 18879, 18880, 18881, 18882, 18883, 18884,
18885, 18886, 18887, 18888, 18889, 18890, 18891, 18892, 18893,
18894, 18895, 18896, 18897, 18898, 18899, 18900, 18901, 18902,
18903, 18904, 18905, 18906, 18907, 18908, 18909, 18910, 18911,
18912, 18913, 18914, 18915, 18916, 18917, 18918, 18919, 18920,
18921, 18922, 18923, 18924, 18925, 18926, 18927, 18928, 18929,
18930, 18931, 18932, 18933, 18934, 18935, 18936, 18937, 18938,
18939, 18940, 18941, 18942, 18943, 18944, 18945, 18946, 18947,
18948, 18949, 18950, 18951, 18952, 18953, 18954, 18955, 18956,
18957, 18958, 18959, 18960, 18961, 18962, 18963, 18964, 18965,
18966, 18967, 18968, 18969, 18970, 18971, 18972, 18973, 18974,
18975, 18976, 18977, 18978, 18979, 18980, 18981, 18982, 18983,
18984, 18985, 18986, 18987, 18988, 18989, 18990, 18991, 18992,
18993, 18994, 18995, 18996, 18997, 18998, 18999, 19000, 19001,
19002, 19003, 19004, 19005, 19006, 19007, 19008, 19009, 19010,
19011, 19012, 19013, 19014, 19015, 19016, 19017, 19018, 19019,
19020, 19021, 19022, 19023, 19024, 19025, 19026, 19027, 19028,
19029, 19030, 19031, 19032, 19033, 19034, 19035, 19036, 19037,
19038, 19039, 19040, 19041, 19042, 19043, 19044, 19045, 19046,
19047, 19048, 19049, 19050, 19051, 19052, 19053, 19054, 19055,
19056, 19057, 19058, 19059, 19060, 19061, 19062, 19063, 19064,
19065, 19066, 19067, 19068, 19069, 19070, 19071, 19072, 19073,
19074, 19075, 19076, 19077, 19078, 19079, 19080, 19081, 19082,
19083, 19084, 19085, 19086, 19087, 19088, 19089, 19090, 19091,
19092, 19093, 19094, 19095, 19096, 19097, 19098, 19099, 19100,
19101, 19102, 19103, 19104, 19105, 19106, 19107, 19108, 19109,
19110, 19111, 19112, 19113, 19114, 19115, 19116, 19117, 19118,
19119, 19120, 19121, 19122, 19123, 19124, 19125, 19126, 19127,
19128, 19129, 19130, 19131, 19132, 19133, 19134, 19135, 19136,
19137, 19138, 19139, 19140, 19141, 19142, 19143, 19144, 19145,
19146, 19147, 19148, 19149, 19150, 19151, 19152, 19153, 19154,
19155, 19156, 19157, 19158, 19159, 19160, 19161, 19162, 19163,
19164, 19165, 19166, 19167, 19168, 19169, 19170, 19171, 19172,
19173, 19186), y = c(0.999999999999999, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000001, 1.00000000000001, 1.00000000000001, 1.00000000000001,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000002, 1.00000000000002,
1.00000000000002, 1.00000000000002, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000003, 1.00000000000003, 1.00000000000003, 1.00000000000003,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000004, 1.00000000000004,
1.00000000000004, 1.00000000000004, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000005, 1.00000000000005, 1.00000000000005, 1.00000000000005,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000006, 1.00000000000006,
1.00000000000006, 1.00000000000006, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000007, 1.00000000000007, 1.00000000000007, 1.00000000000007,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000008, 1.00000000000008,
1.00000000000008, 1.00000000000008, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.00000000000009, 1.00000000000009, 1.00000000000009, 1.00000000000009,
1.0000000000001, 1.0000000000001, 1.0000000000001, 1.0000000000001,
1.0000000000001, 1.0000000000001, 1.0000000000001, 1.0000000000001,
1.0000000000001, 1.0000000000001, 1.0000000000001, 1.0000000000001
)))), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"
))
My main issue is that the length of the lists change (and I cannot control it since this comes from reading some external data).
However, the "x" column from each list contains dates that overlap and that is why I want to tidy the data (also, I am not really comfortable with lists). I would fill the column with NA.
My desired output would be something like this:
# A tibble: 98 × 7
date var1 var2 var3 var4 var5 var6
<date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2021-09-04 4987838. 4543635. 6837675. 2470602. 17588. 1
2 2021-09-05 4988907. 4544627. 6839095. 2470642. 17595. 1
3 2021-09-08 4992735. 4548063. 6844542. 2470659. 17627. NA
4 2021-09-11 4997001. 4551556. 6851055. 2470473. 17665. NA
5 2021-09-12 4998075. 4552593. 6853070. 2470518. 17677. NA
6 2021-09-18 5006674. 4566630. 6865727. 2477677. 17740. NA
7 2021-09-19 5007700. 4567363. 6867889. 2477466. 17747. NA
8 2021-09-20 5008731. 4567925. 6870044. 2477050. 17822. NA
9 2021-09-23 NA NA NA NA NA 1
10 2021-09-25 5015447. 4576576. 6880527. 2480227. 17826. NA
11 2021-09-26 5016746. 4578996. 6882583. 2481744. 17365. NA
Merging is not really an issue since I have done it a few times before, but I need to get all variables (I don't care if the date column is repeated, I'll just remove the others later) in columns so I can then use full_join or something similar to join them and get the desired output.
CodePudding user response：
First I turn the nested lists into data.frames to create d2. Then I use reduce() from purrr and full_join() from dplyr to join by x. After I rename the columns after the join. Is this what you are after?
edit changed d2 based on robert's comment
library(tidyverse)
d2 <- lapply(data$pred, as.data.frame)
df3 <- d2 %>% reduce(full_join, by = "x")
colnames(df3) <- c("x", paste0("var",1:6))
CodePudding user response：
Base R approach:
# prepare column names
for (i in seq_along(data$pred))
names(data$pred[[i]]) <- c('date', paste0('var', i))
# merge data frames succesively
output <- Reduce(function(x, y) merge(x, y, by='date', all=T),
data$pred)
# format date
output$date <- as.Date(output$date, origin="1970-01-01")
head(output)
# date var1 var2 var3 var4 var5 var6
# 1 2021-09-04 4987838 4543635 6837675 2470602 17587.76 NA
# 2 2021-09-05 4988907 4544627 6839095 2470642 17595.23 1
# 3 2021-09-06 NA NA NA NA NA 1
# 4 2021-09-07 NA NA NA NA NA 1
# 5 2021-09-08 4992735 4548063 6844542 2470659 17626.57 1
# 6 2021-09-09 NA NA NA NA NA 1
CodePudding user response：
Convert each x, y pair to zoo, merge them all with merge.zoo (which can handle multi-way joins), set the names and use fortify.zoo to convert the resulting zoo object to a data.frame. Omit the last line if a zoo object result is ok.
library(zoo)
nc <- lengths(data) # 6
out <- data$pred |>
lapply(with, zoo(y, as.Date(x, "1970-01-01"))) |>
do.call(what = "merge") |>
setNames(paste0("var", 1:nc)) |>
fortify.zoo(names = "Date")