CODEWITHSUNDEEP
rdata.table
user10156381
user10156381

Reputation: 133

Match duplicated column names to data table

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!

Upvotes: 0

Views: 32

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388807

  • 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"

Upvotes: 1

Related Questions