# Subset data frame by similar elements from rows and column between two data frames

My first data frame whose first column i intend to use which is PATIENT_ID

PATIENT_ID  SEX RACE    FAB AGE BM_BLAST_PERCENTAGE WBC
TCGA-AB-2805    Male    WHITE   M0  77  67  92
TCGA-AB-2822    Male    WHITE   M0  65  99  2.9
TCGA-AB-2831    Male    WHITE   M0  59  61  1.4
TCGA-AB-2890    Male    NH/C    M0  69  60  6.1
TCGA-AB-2907    Male    WHITE   M0  69  82  1.6
TCGA-AB-2913    Male    WHITE   M0  61  95  61.6
TCGA-AB-2922    Male    WHITE   M0  83  75  77.9
TCGA-AB-2936    Female  NH/C    M0  60  50  2.1


My second data frame where i have the respective patient ID as individual column with expression values

Hugo_Symbol TCGA-AB-2803    TCGA-AB-2805    TCGA-AB-2806    TCGA-AB-2807    TCGA-AB-2808
A1BG    0   0   0   0   0
A1CF    0   0   0   0   0
A2BP1   71.4095 48.5475 50.8339 45.7498 47.1328
A2LD1   2.0576  3.3467  5.7288  0.993   5.4988
A2ML1   467.0782    16.0643 7.0019  343.5948    255.9152
A2M 11.3169 0.6693  17.823  116.1867    182.2467
A4GALT  0   2.6774  1.2731  4.9652  0.7855
A4GNT   0   0   0   0   0
AAA1    514.4033    532.7979    504.1375    697.1202    443.8335
AAAS    0   0   0   0   0
AACSL   190.3292    74.2972 84.6595 559.0864    317.3606
AACS    0   0   0   1.9861  0
AADACL2 0   0   0   0   0
AADACL3 0   0   0   0   0
AADACL4 1.0288  0   0.6365  0   0


What I tried so far transposing the second data frame so that i can get the individual column as my first column ,and subset by using dplyr library but so far im not able to ..any help or suggestion how to do it would be highly appreciable

I would like to have my expected data frame as such

PATIENT_ID  Gene B  GeneA   GeneC   GeneA   GeneA
TCGA-AB-2803-03 0   0   0   92.5926 876.5432
TCGA-AB-2805-03 0.6693  1.3387  0   92.6305 421.6867
TCGA-AB-2806-03 0   14.1884 16.3654 119.599 625.0796
TCGA-AB-2807-03 2.006   9.3247  13.5154 178.4211    537.2393
TCGA-AB-2808-03 0   42.3016 28.3975 170.2042    653.5742
TCGA-AB-2810-03 0   1.5668  8.9665  152.0747    1087.5648
TCGA-AB-2811-03 0   0   6.9556  30.3103 1414.6602
TCGA-AB-2812-03 0   14.3289 11.9466 120.3591    962.5575
TCGA-AB-2813-03 0   3.4938  1.3813  51.1883 594.7593
TCGA-AB-2814-03 0   56.8659 118.7051    87.1817 472.6912
TCGA-AB-2815-03 0   2.0692  3.6986  104.6575    502.5234

• okay so I what im expecting is I would like to have total 18 patient which are in first data frame my patient ID so I would like to use the patient ID and use the same to subset the 18 patient from my second data frame with their expression values my second data frame 22000*174 in dimension .so my expected data fram would be 22000*18 , so i need my rows as my genes and columns as my patient sample ID..
– kcm
Mar 22, 2018 at 11:47
• Are you sure that those numbers on that expression matrix are hugo symbols?? I highly doubt it. BTW I think this is not about bioinformatics...
– llrs
Mar 22, 2018 at 13:31
• well i just put it as since it didn't contain any symbol but entrez ID
– kcm
Mar 23, 2018 at 5:12

If you're still looking for an answer on this, I would use the gather and spread functions in the tidyr package (part of the tidyverse suite of packages - which also includes dplyr and ggplot2:

library(tidyverse)
df2_long <- gather(PATIENT_ID, value, -Hugo_Symbol)
df1 %>%
left_join(df2_long) %>%   # merges the 2 data frames together


You can take the PATIENT_ID from the first dataframe and use them to select from your second dataframe. Than transpose, so something like this:

ID_Names <- First_df$PATIENT_ID rownames(Second_df) <- Second_df$Hugo_Symbol

Selected_df <- Second_df[, ID_Names]

Expected_df <- t(Selected_df)

• well i m running into an error "Error: Columns TCGA-AB-2831, TCGA-AB-2907, TCGA-AB-2922 not found" may be one problem i think is my ID_Names vector is in character , but the column i m trying to map are in numeric ...any suggestion
– kcm
Mar 23, 2018 at 5:14
• Are you sure these three patient_IDs are in the second file? You select from the header which is not numeric.
– benn
Mar 23, 2018 at 8:45
• i mean all my header in the second data frame are numeric...so do i need to convert them to character if yes how do i do that i tried converting them to character it changes my data frame
– kcm
Mar 23, 2018 at 10:38
• The error is pretty clear, you miss the three IDs in your second df. Check the colnames and look for the specific three IDs in the error.
– benn
Mar 23, 2018 at 12:47
• Make rownames with the gene names for the second df, before you select. Something like this: rownames(second_df) <- second_df\$Hugo_Symbol. If you then select the columns the gene names are stored as rownames.
– benn
Mar 24, 2018 at 10:20