0
$\begingroup$

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'
> 
$\endgroup$
2
$\begingroup$

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.

$\endgroup$
8
  • $\begingroup$ Sorry I got this Error in xj[i] : invalid subscript type 'list' $\endgroup$
    – Exhausted
    Nov 15 '19 at 7:33
  • 1
    $\begingroup$ On which step of the code ? $\endgroup$
    – dc37
    Nov 15 '19 at 15:52
  • 1
    $\begingroup$ 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) $\endgroup$
    – dc37
    Nov 17 '19 at 20:37
  • 1
    $\begingroup$ When you use the data example that I provided, did you get the same error ? also, did you install the packages rebus.base ? $\endgroup$
    – dc37
    Nov 17 '19 at 20:42
  • 1
    $\begingroup$ 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. $\endgroup$
    – dc37
    Nov 17 '19 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.