Get element number of list while iterating through it

Viewed 183

I have a list of the following structure,

myList <- replicate(5, data.frame(id = 1:10, mean = runif(10)), simplify =F)

and I want to reduce it with a merge

myList %>% reduce(function(x, y) merge(x, y, by = 'id'))

That, however, leads to the following colnames:

    id     mean.x    mean.y    mean.x    mean.y       mean

While I would like something like

 id     mean1    mean2    mean3    mean4       mean5

Where the numbers are based on the order of myList.

Obviously I could iterate over 1:length(myList), but I find this solution unelegant. Other option would be to introduce a check in the reducing function, but that would indue a new linear time search for each element of the list, so I don't believe it to be very efficient.

Is there another way to achieve this?

5 Answers

New answer:

Using rbindlist and dcast from the data.table-package:

library(data.table)
mydata <- rbindlist(myList, idcol = 'df')
dcast(mydata, id ~ paste0('mean',df), value.var = 'mean')

Or with the tidyverse packages:

library(dplyr)
library(tidyr)
myList %>% 
  bind_rows(., .id = 'df') %>% 
  spread(df, mean) %>% 
  rename_at(-1, funs(paste0('mean',.)))

which both give (data.table-output is shown):

    id     mean1       mean2     mean3      mean4      mean5
 1:  1 0.6937674 0.005642891 0.4155868 0.74184186 0.54513885
 2:  2 0.3602352 0.569412043 0.8018570 0.29177043 0.34521060
 3:  3 0.6353133 0.512876032 0.8711914 0.44660086 0.16338451
 4:  4 0.2106574 0.555638598 0.8240744 0.37495213 0.57443740
 5:  5 0.9530160 0.059930577 0.0930678 0.39862717 0.91568414
 6:  6 0.3723244 0.598526326 0.4970844 0.01978011 0.07832631
 7:  7 0.2923137 0.712971846 0.3805590 0.25676592 0.11682605
 8:  8 0.6208868 0.426853621 0.5533876 0.64054247 0.78949419
 9:  9 0.9032609 0.274705843 0.3525957 0.46994429 0.32883110
10: 10 0.9707088 0.351394642 0.1089803 0.97969335 0.77791085

When there are duplicates in id in one or more of the dataframes in myList, you have to adapt the dcast-step to dcast(mydata, id + rowid(id,df) ~ paste0('mean',df), value.var = 'mean') to get the correct outcome. Check the following example to see the result:

myList <- replicate(5, data.frame(id = sample(1:10, 10, TRUE), mean = runif(10)), simplify = FALSE)
mydata <- rbindlist(myList, idcol = 'df')
dcast(mydata, id + rowid(id,df) ~ paste0('mean',df), value.var = 'mean')

This also works when there are no duplicates in id.

The tidyverse-code has then to be adapted to:

myList %>% 
  bind_rows(., .id = 'df') %>% 
  group_by(df, id) %>% 
  mutate(ri = row_number()) %>% 
  ungroup() %>% 
  spread(df, mean) %>% 
  rename_at(3:7, funs(paste0('mean',.)))

Old answer (still valid):

A possible solution:

# option 1
myList <- mapply(function(x,y) {names(x)[2] = paste0('mean',y); x}, myList, 1:length(myList), SIMPLIFY = FALSE)
Reduce(function(x, y) merge(x, y, by = 'id'), myList)

# option 2 (quite similar to @zx8754's solution)
mydata <- Reduce(function(x, y) merge(x, y, by = 'id'), myList)
setNames(mydata, c('id', paste0('mean', seq_along(myList))))

which gives:

   id     mean1     mean2      mean3      mean4      mean5
1   1 0.1119114 0.4193226 0.86619590 0.52543072 0.52879193
2   2 0.4630863 0.8786721 0.02012432 0.77274088 0.09227344
3   3 0.9832522 0.4687838 0.49074271 0.01611625 0.69919423
4   4 0.7017467 0.7845002 0.44692958 0.64485570 0.40808345
5   5 0.6204856 0.1687563 0.54407165 0.54236973 0.09947167
6   6 0.1480965 0.7654041 0.43591864 0.22468554 0.84557988
7   7 0.0179509 0.3610114 0.45420122 0.20612154 0.76899342
8   8 0.9862083 0.5579173 0.13540519 0.97311401 0.13947602
9   9 0.3140737 0.2213044 0.05187671 0.07870425 0.23880332
10 10 0.4515313 0.2367271 0.65728768 0.22149073 0.90578043

You can also try to modify the function in the Reduce (or reduce) call to make the adding of indices automatic :

Reduce(function(x, y){

         # get indices of columns that are not the common one, in x and y
         col_noby_x <- which(colnames(x) != "id")
         col_noby_y <- which(colnames(y) != "id")

         # maximum of indices in x (at the end of the column names)
         ind_x <- max(as.numeric(sub(".+(\\d+)$", "\\1", colnames(x)[col_noby_x]))) 

         # if there is no indice yet, put 1 and 2, else modify names only in y, taking the max value of indices in x plus one.
         if(!is.na(ind_x)) colnames(y)[col_noby_y] <- paste0(colnames(y)[col_noby_y], ind_x +1) else {colnames(x)[col_noby_x] <- paste0(colnames(x)[col_noby_x], 1); colnames(y)[col_noby_y] <- paste0(colnames(y)[col_noby_y], 2)}  

         # finally merge
         merge(x, y, by="id")}, myList)      

#   id      mean1     mean2     mean3     mean4      mean5
#1   1 0.10698388 0.0277198 0.5109345 0.8885772 0.79983437
#2   2 0.29750846 0.7951743 0.9558739 0.9691619 0.31805857
#3   3 0.07115142 0.2401011 0.8106464 0.5101563 0.78697618
#4   4 0.39564336 0.7225532 0.7583893 0.4275574 0.77151883
#5   5 0.55860511 0.4111913 0.8403031 0.4284490 0.51489116
#6   6 0.92191777 0.9142926 0.4708712 0.2451099 0.84142501
#7   7 0.08218166 0.2741819 0.6772842 0.7939364 0.86930336
#8   8 0.35392512 0.2088531 0.0801731 0.2734870 0.62963218
#9   9 0.64068537 0.8427225 0.1904426 0.2389339 0.73145206
#10 10 0.31304719 0.9898133 0.8173664 0.2013031 0.04658273

Merge with Reduce, then update column names:

res <- Reduce(function(...) merge(..., all = TRUE, by = "id"), myList)
colnames(res)[2:ncol(res)] <- paste0("mean", 1:length(myList))

We can use set_names

library(tidyverse)
myList %>%
      reduce(merge, by = 'id') %>% 
      set_names(c("id", paste0("mean", 1:5)))
#   id      mean1       mean2      mean3      mean4     mean5
#1   1 0.07122593 0.480300675 0.34944190 0.48718226 0.9118796
#2   2 0.18375430 0.850652470 0.24780063 0.45148232 0.2587470
#3   3 0.18617054 0.526188340 0.48716956 0.53354343 0.9057241
#4   4 0.87838756 0.811985522 0.49024819 0.10412944 0.7830501
#5   5 0.29287646 0.974811919 0.31413846 0.01508965 0.4587954
#6   6 0.62304018 0.004421152 0.81053625 0.80032467 0.7630185
#7   7 0.78445890 0.006362844 0.73643248 0.15952795 0.4386658
#8   8 0.71568076 0.081139996 0.36933728 0.31771823 0.2794372
#9   9 0.25523328 0.081603285 0.00298272 0.33698950 0.2413859
#10 10 0.86274552 0.432177738 0.26064580 0.75639537 0.3125151

Here are two one liners

Using purrr:reduce2 and dplyr::inner_join in place of merge:

library(dplyr)
library(purrr)
myList %>% reduce2(map(2:length(.),~c("",.x)), inner_join, by = 'id',copy=F)

#    id       mean     mean2     mean3      mean4      mean5
# 1   1 0.44560715 0.4575765 0.6075921 0.06504922 0.90410342
# 2   2 0.60606716 0.5004711 0.7866959 0.89632285 0.09890028
# 3   3 0.59928281 0.4894146 0.4495071 0.66090212 0.56046997
# 4   4 0.55630819 0.4166869 0.1984523 0.08040737 0.18375885
# 5   5 0.97714203 0.1223497 0.7923596 0.53054508 0.93747149
# 6   6 0.07751312 0.6217220 0.3861749 0.30062805 0.03177210
# 7   7 0.22839323 0.3994350 0.6382234 0.98578452 0.27032222
# 8   8 0.73628572 0.8804618 0.8240999 0.44205508 0.73901477
# 9   9 0.81894510 0.2186181 0.9317510 0.60035660 0.65002083
# 10 10 0.26197059 0.5569660 0.9167330 0.58912675 0.81367176

Or using plyr::join_all and tibble::repair_names(same output):

myList %>% join_all('id','inner') %>% repair_names
Related