How can I merge two data frames in R with turning one of them?
I have two df, one of them consist two columns (samples ID and tissue type) and another one is RNA-seq data ( the sample first column is a gene name and another columns os expression data for sample). So I need to make a df where gene name will be columns against of rows like in df #2. I hope I explained everything clear.
Thank you for any future suggestions!
df1
SAMPID primary.tissue tissue
1 GTEX-1117F-0003-SM-58Q7G Blood Whole Blood
2 GTEX-1117F-0003-SM-5DWSB Blood Whole Blood
3 GTEX-1117F-0226-SM-5GZZ7 Adipose Tissue Adipose - Subcutaneous
4 GTEX-1117F-0426-SM-5EGHI Muscle Muscle - Skeletal
5 GTEX-1117F-0526-SM-5EGHJ Blood Vessel Artery - Tibial
6 GTEX-1117F-0626-SM-5N9CS Blood Vessel Artery - Coronary
df2
Name Description GTEX.111CU.1826.SM.5GZYN GTEX.111FC.0226.SM.5N9B8
1 ENSG00000223972.4 DDX11L1 0 0
2 ENSG00000227232.4 WASH7P 591 1317
3 ENSG00000243485.2 MIR1302-11 0 0
4 ENSG00000237613.2 FAM138A 0 0
5 ENSG00000268020.2 OR4G4P 0 0
6 ENSG00000240361.1 OR4G11P 0 0
•
written
by
Kateryna •
2 answers
Assuming you have expression data in a matrix of gene vs Samples in the second table Use melt function from reshape library to get the data in 3 columns format,which will be gene,Sample,Expression Value
library(reshape)
df2_melt= melt(df2,id="gene")
Now you can merge this data with the first table df1 which has 2 columns Sample and tissue,based on the "Sample" Column.
df3= Reduce(function(x, y) merge(x, y,by = c("Sample"), all=FALSE),list(df1,df2_melt))
#reorder df1 so it has the same order of rows as df2
df1 <- df1[df2$Name,]
#transpose df2
df2 <- t(df2)
#all column names to the transposed df2
colnames(df2) <- df2[1,]
#drop the first two rows in the new df2
df2 <- df2[-c(1,2),]
#merge
df3 <- cbind(df1,df2)
#drop redundant sample id info
df3$Name=NULL
Log in to answer this question.
Perhaps run
head(df1)andhead(df2)and paste the results in your question.Yes, it is good idea :)