r - Match duplicated column names to data table

Question

letters <- data.frame(name=c("a","b","b","b","c","d","d","e"), 
                      row.names=c("a","b","b.1", "b.2", "c","d", "d.1", "e"))
table <- data.table(label=c("red", "orange", "yellow", "green", "blue"),
                    a=c(0,0,0,1,1), b=c(1,1,3,4,0), c=c(1,2,3,4,5), d=c(1,0,0,0,0), e=c(1,1,1,1,1))

I have a data frame, letters, of alphanumeric characters corresponding to their original alphabetical letter. I also have a data table, table, that keeps track of the count of each color for each alphabetical letter, but I'd like this data table to include the alphanumeric characters as columns with the same values as their original alphabetical character. So, in table, I want to add the columns b.1=c(1,1,3,4,0), b.2=c(1,1,3,4,0), and d.1=c(1,0,0,0,0). How would I go about this? Thanks!

question from:https://stackoverflow.com/questions/65912139/match-duplicated-column-names-to-data-table

Answer 1

• find row names in letters that have alphanumeric values in them.
• remove numbers from it to subset columns from table.
• cbind the new columns to original table.

new_col <- grep('[a-z]\.[0-9]', rownames(letters), value = TRUE)
sub_col <- sub('\.\d+', '', new_col)
cbind(table, setNames(table[, ..sub_col], new_col))

#    label a b c d e b.1 b.2 d.1
#1:    red 0 1 1 1 1   1   1   1
#2: orange 0 1 2 0 1   1   1   0
#3: yellow 0 3 3 0 1   3   3   0
#4:  green 1 4 4 0 1   4   4   0
#5:   blue 1 0 5 0 1   0   0   0

where

new_col
#[1] "b.1" "b.2" "d.1"
sub_col
#[1] "b" "b" "d"