Combining column vectors and then merge it to data table of specific format

user_14194481 0
Viewed 152

I want to create a data table in R that shows the final results in a nice format. My final results are the following:

NENA.day <- c(42)
NENA.weeks <- c(38, 40, 41)
EQ.day <- c(20)
EQ.weeks <- c(17, 12, 12)
K.day <- c(10)
K.weeks <- c(12, 15, 14)

The data table should look like this:

enter image description here

How can I combine all vectors in that way, that I get the following table with the respective column and row names? The data table in the picture is created in Excel, the result data table should be created in R.

vector r datatable merge
1 Answers

We can get the vectors in a list and split it to group

lst1 <- mget(ls(pattern = 'NENA|EQ|K'))
out <- sapply(split(lst1, sub("\\..*", "", names(lst1))), unlist)
row.names(out) <- toupper(sub(".*\\.(.)\\D+(\\d*)", "\\1\\2", row.names(out)))

-output

out
#   EQ  K NENA
#D  20 10   42
#W1 17 12   38
#W2 12 15   40
#W3 12 14   41

As we have a lot of vector objects, an easier option to get them into a list would be with mget (returns a named list) after we get the object names as strings from ls. Once, it is in a list, then we split the list to groups of nested list by removing the substring from the names i.e. removing the '.day', '.weeks', loop over the list. with sapply and unlist the nested list to create a singel vector for each of the 'EQ', 'K', 'NENA'.

Or another option with tidyverse

library(dplyr)
library(tidyr)
library(tibble)
library(stringr)
library(data.table)
enframe(lst1) %>%
   unnest(c(value)) %>% 
   separate(name, into = c('name', 'dayweek'), sep="\\.") %>%
   mutate(rn = rowid(name)) %>% 
   pivot_wider(names_from = name, values_from = value) %>% 
   select(-rn) %>%
   mutate(dayweek = str_c(toupper(substr(dayweek, 1, 1)), 
                    rowid(dayweek), sep="+")) %>%
   column_to_rownames('dayweek')

-output

#    EQ  K NENA
#D+1 20 10   42
#W+1 17 12   38
#W+2 12 15   40
#W+3 12 14   41
user_37322710
Related