CODEWITHSUNDEEP
rsubstring
Mike
Mike

Reputation: 1069

R: Finding column names that match substrings of row names

I am trying to extract a value from a certain column, where that column name matches the first 3 letters of the corresponding row name. I think its easiest to explain with an example:
Matrix 1

BVT IPL LHC MDC
100 200 300 400

Matrix 2

Col1 Col 2
BVTIPL 100/200
IPLBVT 200/100
LHCIPL 300/200
... ...
MDCBVT 400/100

In the above, I am taking the left 3 characters of Matrix 2 row name, finding the corresponding column name and dividing the value in that column by the value in the column corresponding to the rightmost 3 characters and placing that value in the corresponding row of Matrix 2.

Any help with this problem would be greatly appreciated!

Thanks
Mike

Upvotes: 1

Views: 796

Answers (1)

sgibb
sgibb

Reputation: 25736

You could use ?substr and a named vector:

lookupValues <- c(BVT=100, IPL=200, LHC=300, MDC=400)

df <- data.frame(col1=c("BVTIPL", "IPLBVT", "LHCIPL", "MDCBVT"),
                stringsAsFactors=FALSE)

df$col2 <- lookupValues[substr(df$col1, 1, 3)]/lookupValues[substr(df$col1, 4, 6)]
df
#    col1 col2
#1 BVTIPL  0.5
#2 IPLBVT  2.0
#3 LHCIPL  1.5
#4 MDCBVT  4.0

Upvotes: 1

Related Questions