How can I merge two data frames in R with turning one of them?

Question

[Kateryna]

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

[Alex Reynolds]

Perhaps run head(df1) and head(df2) and paste the results in your question.

[Kateryna]

Yes, it is good idea :)

Answer 1

[Ron]

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))

Answer 2

[mforde84]

#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