'Merging many columns in R

I have an issue with merging many columns by the same ID. I know that this is possible for two lists but I need to combine all species columns into one so I have first column as species (combined) and then w,w.1,w.2,w.3, w.4... The species columns all have the same species in them but are not in order so I can't just drop every other column as this would mean the w values aren't associated with the right species. This is an extremely large dataset of 10000 rows and 2000 columns so would need to automated. I need the w values to be associated to the corresponding species. Dataset attached.

Thank you for any help

dataset

rlistdataframesortingmerge


Solution 1:[1]

If your data is in a frame called dt, you can use lapply() along with bind_rows() like this:

library(dplyr)
library(tidyr)

bind_rows(
  lapply(seq(1,ncol(dt),2), function(x) {
    dt[,c(x,x+1)] %>%
      rename_with(~c("Species", "value")) %>%
      mutate(w = colnames(dt)[x+1])
  })
) %>% 
  pivot_wider(id_cols = Species, names_from = w)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions