Home > Back-end >  R tidying a list of list with different lengths
R tidying a list of list with different lengths

Time:07-28

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")
  • Related