Take one row of each data frame in a list based on highest value and put them all in a new data frame in R

Question

After some hard earned (for me, beginneR) data cleaning I now have a list of data frames from a series of measurements. Now I want to make a new data frame containing only one row of each of the data frames based on the highest value in one of the data frame columns.

This is how one of the data frames looks like:

list(`PA309 ref 6M` = structure(list(Wavelength = c(300, 301, 
302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 
315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 
328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 
341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 
354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 
367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 
380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 
393, 394, 395, 396, 397, 398, 399, 400), mean = c(88050, 97825.5555566667, 
107833.333333333, 119161.333333333, 129208.666666667, 141178, 
152646, 165915.333333333, 178684.666666667, 193356, 208148.666666667, 
224309.333333333, 240792, 258300.666666667, 276427.333333333, 
296127.333333333, 316690, 337663.333333333, 359532.666666667, 
382843.333333333, 404855.333333333, 427523.333333333, 449951.333333333, 
472048.666666667, 494241.333333333, 517214, 540008.666666667, 
561769.333333333, 584009.333333333, 604872.666666667, 624645.333333333, 
643634.666666667, 663086, 680633.333333333, 697850, 714818, 730947.333333333, 
745414, 759252.666666667, 771539.333333333, 782828.666666667, 
791684, 801654, 810408.666666667, 817966, 825253.333333333, 832820.666666667, 
837431.333333333, 841400, 844329.333333333, 845598.666666667, 
845490, 844946.666666667, 843556.666666667, 841356.666666667, 
838448, 833467.333333333, 827916.666666667, 821100, 813840, 805648, 
797787.333333333, 788737.333333333, 777954, 766898, 754910, 742837.333333333, 
729848.666666667, 717330.666666667, 703432.666666667, 688858, 
674260.666666667, 659590.666666667, 645462, 630890.666666667, 
615532, 600835.333333333, 585797.333333333, 570038.666666667, 
554375.333333333, 540131.333333333, 525104, 509776.666666667, 
494048, 479012, 463121.333333333, 446361.333333333, 430677.333333333, 
415540, 400765.333333333, 386695.333333333, 373296.666666667, 
360028, 347335.333333333, 335207.333333333, 323443.333333333, 
311976.666666667, 301112.666666667, 290682, 279893.333336667, 
270703.333333333), SD = c(29278.3315781484, 30734.6965259783, 
33040.5893611681, 34965.2122163349, 37698.2139806826, 40544.9920952021, 
44136.8343223662, 46942.9836858857, 50872.345034737, 54411.1411753145, 
58531.3961676409, 62377.0203306741, 67330.151670704, 71783.6371698546, 
77553.6550352937, 83633.3218121422, 89456.9806555084, 95870.7618063679, 
101839.724613401, 107605.328982041, 113338.684310933, 119160.565445676, 
125450.911679961, 132390.528488005, 139801.985484232, 147083.488305112, 
154968.465764275, 160362.050826663, 166471.876127271, 171326.942356809, 
177255.842412411, 182705.852345603, 188275.117920558, 194214.563515029, 
199580.318939519, 204775.793227618, 209229.729554223, 214325.21399966, 
218498.795688519, 222776.371829091, 225607.005426102, 229513.476135934, 
233571.284613499, 236523.371634461, 238111.11168528, 241117.689581941, 
242808.673999372, 244252.288401426, 245670.066560825, 247515.052894432, 
246855.961380991, 247657.976984389, 247520.093813277, 246938.212404102, 
246803.342378772, 246951.003269879, 244932.72848138, 243787.061595429, 
241813.833409092, 239799.79150116, 237645.340985259, 235767.877339839, 
233018.855385854, 230730.249260906, 227480.318164012, 223401.186863454, 
219657.618582496, 215796.397980442, 211662.799058628, 208152.607145174, 
204022.324896076, 199464.996651877, 194877.357046254, 191649.674364451, 
187410.709516114, 183223.929758097, 179780.148874489, 175469.891278627, 
169521.285322326, 164389.614104217, 160913.012231247, 156156.265439463, 
151334.503803109, 147576.986512125, 143293.528297687, 137884.012609633, 
132944.821340785, 128895.403367744, 124638.695564419, 120372.111127675, 
116247.889276895, 112202.576446949, 108264.290345432, 104680.172570231, 
101703.163693827, 97789.5803106514, 95186.746752546, 92071.7810479049, 
88782.5669374343, 86450.4548513189, 83008.6419195817), `SD%` = c(0.332519381921049, 
0.314178604466753, 0.306404229006196, 0.293427500668574, 0.291762270699199, 
0.287190582776368, 0.28914504358035, 0.282933365728016, 0.28470459152289, 
0.281403944927049, 0.28119995724678, 0.278084818869214, 0.279619554099405, 
0.277907285707824, 0.280557114595374, 0.282423513124339, 0.282474914444752, 
0.283924111214428, 0.283255831959824, 0.281068833157274, 0.27994860133809, 
0.27872295183657, 0.278809956513729, 0.28045949038024, 0.28286178442697, 
0.284376463717363, 0.286974034548103, 0.285458890885221, 0.285050026815674, 
0.283244642712916, 0.283770378090411, 0.28386577325274, 0.283937706301382, 
0.285343890761099, 0.285993148870844, 0.286472631113959, 0.286244603424537, 
0.287525071972971, 0.28778140042338, 0.288742727952203, 0.288194614010178, 
0.289905411926898, 0.291361715420242, 0.291856912892254, 0.291101478160804, 
0.292174148037703, 0.291549770217885, 0.29166843737408, 0.291977735394372, 
0.29314989201816, 0.291930405181565, 0.292916506386106, 0.29294167736024, 
0.29273458697195, 0.293339735877498, 0.294533475266062, 0.293872019556911, 
0.294458453864635, 0.294499858006445, 0.294652255358744, 0.294974158671354, 
0.295527225726621, 0.295432770249479, 0.296585979712048, 0.296623955420423, 
0.295930888269402, 0.29570083344738, 0.295672798809126, 0.295070054710187, 
0.295909782142391, 0.296174719457531, 0.295827721403311, 0.295451962701495, 
0.296918601504737, 0.29705734989916, 0.297667594468032, 0.299217004894001, 
0.299540269806556, 0.297385590197961, 0.296531256388662, 0.297914603913456, 
0.297381595720967, 0.296864320590931, 0.298709814657939, 0.299143921859342, 
0.297727620572361, 0.297841258668131, 0.299285319638547, 0.299943917708089, 
0.300355597442747, 0.300618805701203, 0.300572135960537, 0.300710751234437, 
0.301380719219172, 0.303403755169915, 0.302339143314083, 0.305108544717701, 
0.305771862961278, 0.305428498969438, 0.30886928895635, 0.306640634592291
)), row.names = c(NA, 101L), class = "data.frame"))

To be precise, I want to find the highest value in the "mean" column (for which I figured I would use which.max?) and copy the entire row into the new data frame, also adding a column where the names of the individual data frames should go. For this example, the row I want is:

350 845598.67 246855.96 0.2919304

Adding the data frame name I would like it to look like this:

350 845598.67 246855.96 0.2919304 "PA309 ref 6M"

How do I write a function that does the trick, that I can then presumably lapply to my list? Appreciate any and all directions to a solution!

Answer 1

Try with:

library(dplyr)
library(purrr)

df <- df_list %>%
  imap_dfr(~.x %>% slice(which.max(mean)) %>% mutate(df_from = .y))