# Binding columns with matched elements

I have a matrix in which sample IDs are in rows and genes are in column like below

> head(data)
ETV7    PLAUR  SLC11A1
TCGA.JY.A6FB.01A.11R.A336.31 1.791167 6.241607 3.709848
TCGA.JY.A938.01A.11R.A37I.31 5.389175 7.048782 3.124435
BIRC3 TNFRSF1B
TCGA.JY.A6FB.01A.11R.A336.31 6.726991 5.758026
TCGA.JY.A938.01A.11R.A37I.31 8.097783 6.621900
>


In another matrix I have sample IDs, months to death and status while the order of sample IDs is not the same with my previous matrix like below

Patient_ids OS_time  OS_event
TCGA-IG-A4QS    3.933333333 1
TCGA-JY-A6FB    61.23333333 0
TCGA-JY-A938    35.33333333 0
TCGA-JY-A939    22  0
TCGA-JY-A93C    23.5    0
TCGA-JY-A93E    25.56666667 0
TCGA-L5-A4OJ    21.3    0
TCGA-L5-A4OS    59.4    0
TCGA-L5-A8NM    7.866666667 1
TCGA-R6-A6KZ    5.133333333 1
TCGA-R6-A6XG    38.93333333 0


How I can bind OS_time and OS_event columns from second matrix to the first matrix keeping the right sample order?

EDITED

> # Reproducible example:
> patient_id = c("TCGA.JY.A6FB.01A.11R.A336.31","TCGA.JY.A938.01A.11R.A37I.31","TCGA.L5.A4OS.01A.11R.A212.31","TCGA.AA.A400.01A.11R.A212.31")
> ETV7 = c(1.79,5.38,5,6)
> PLAUR = c(6.24,7.05,5,6)
> SLC11A1 = c(3.7,3.12,5,6)
> df = data.frame(ETV7,PLAUR, SLC11A1)
> rownames(df) = patient_id
> # OS data
> Patient_ids = c("TCGA-IG-A4QS","TCGA-JY-A6FB","TCGA-JY-A938","TCGA-JY-A939","TCGA-JY-A93C","TCGA-JY-A93E","TCGA-L5-A4OJ","TCGA-L5-A4OS")
> OS_time = c(3.93,61.23,35.33,22,23.5,25.56,21.3,59.4)
> os_data = data.frame(Patient_ids,OS_time)
> library(rebus.base)
> df$$Patient_id = gsub(DOT,"-",rownames(df)) > position = sapply(os_data$$Patient_ids,function(x){grep(x,df$$Patient_id)}) > position = lapply(position,function(x){c(x,rep(NA,1-length(x)))}) > os_data$$Position_matched = unlist(position)
> m = data.frame(matrix(ncol = length(df),nrow = nrow(os_data)))
> colnames(m) = colnames(df)
> for(i in 1:length(position))
+ {
+   if(is.na(position[i])){}
+   else{
+     m[i,] = df[position[i],]
+   }
+ }
Error in xj[i] : invalid subscript type 'list'
>


Here a little way to do it. I don't think it is a perfect solution but it should work. The trick here is to compare patients_id that are wrote with a . or with a -. So, here is how I do (I modified your example in order to reflect several configurations):

# Reproducible example:
patient_id = c("TCGA.JY.A6FB.01A.11R.A336.31","TCGA.JY.A938.01A.11R.A37I.31","TCGA.L5.A4OS.01A.11R.A212.31","TCGA.AA.A400.01A.11R.A212.31")
ETV7 = c(1.79,5.38,5,6)
PLAUR = c(6.24,7.05,5,6)
SLC11A1 = c(3.7,3.12,5,6)

df = data.frame(ETV7,PLAUR, SLC11A1)
rownames(df) = patient_id

# OS data
Patient_ids = c("TCGA-IG-A4QS","TCGA-JY-A6FB","TCGA-JY-A938","TCGA-JY-A939","TCGA-JY-A93C","TCGA-JY-A93E","TCGA-L5-A4OJ","TCGA-L5-A4OS")
OS_time = c(3.93,61.23,35.33,22,23.5,25.56,21.3,59.4)
os_data = data.frame(Patient_ids,OS_time)


Now, I create a new column in df that will contains patient_id wrote with a -:

library(rebus.base)
df$Patient_id = gsub(DOT,"-",rownames(df))  Now, I'm looking for common pattern between df$Patient_id and os_data$Patient_ids, find their matching row position and extract these lines: position = sapply(os_data$Patient_ids,function(x){grep(x,df$Patient_id)}) position = lapply(position,function(x){c(x,rep(NA,1-length(x)))}) position = unlist(position) os_data$Position_matched = position


Now, we are creating a matrix m that we will fill with matching values

m = data.frame(matrix(ncol = length(df),nrow = nrow(os_data)))
colnames(m) = colnames(df)

for(i in 1:length(position))
{
if(is.na(position[i])){}
else{
m[i,] = df[position[i],]
}
}


And now, we can bind both os_data and m:

New_df = cbind(os_data,m)


And here the final output:

> New_df
Patient_ids OS_time Position_matched ETV7 PLAUR SLC11A1                   Patient_id
1 TCGA-IG-A4QS    3.93               NA   NA    NA      NA                         <NA>
2 TCGA-JY-A6FB   61.23                1 1.79  6.24    3.70 TCGA-JY-A6FB-01A-11R-A336-31
3 TCGA-JY-A938   35.33                2 5.38  7.05    3.12 TCGA-JY-A938-01A-11R-A37I-31
4 TCGA-JY-A939   22.00               NA   NA    NA      NA                         <NA>
5 TCGA-JY-A93C   23.50               NA   NA    NA      NA                         <NA>
6 TCGA-JY-A93E   25.56               NA   NA    NA      NA                         <NA>
7 TCGA-L5-A4OJ   21.30               NA   NA    NA      NA                         <NA>
8 TCGA-L5-A4OS   59.40                3 5.00  5.00    5.00 TCGA-L5-A4OS-01A-11R-A212-31


Warning: This script should work for some simple case like yours but take extra cautious to the grep output as the short patient_id term of the os_data could eventually generate several hits instead of one.

• Sorry I got this Error in xj[i] : invalid subscript type 'list' Nov 15 '19 at 7:33
• On which step of the code ?
– dc37
Nov 15 '19 at 15:52
• On which part ? please give me the part where you get an error and I will try to correct it for you. (also include the code you enter)
– dc37
Nov 17 '19 at 20:37
• When you use the data example that I provided, did you get the same error ? also, did you install the packages rebus.base ?
– dc37
Nov 17 '19 at 20:42
• Thanks ! I found the error. I forgot to to do position = unlist(position) before the for loop, I'm sorry about that. I have edited my code and now it should work.
– dc37
Nov 17 '19 at 20:49