Noob question: how to combine counts and DESeq data into one CSV?

Question

[cdeantoneo31]

I am trying to replicate the format of the below file, which seems to include both the raw counts AND the DESeq data into one csv

WTS1  WTS2 KOS1 KOS2 baseMean log2FoldChange lfcSE   stat    pvalue    padj  
Gm6166  10  20 4103 4166  1931.08  5.93    0.13    45.68  0.00e+00  0.00e+00  
TNFa  8492 10719 1091 1158  5621.18

However, I can't seem to figure out how to do it with the two separate counts.csv

               WT_S1_12hr WT_S2_12hr KO_S1_12hr KO_S2_12hr
0610005C13Rik     1          0          2          1
0610006L08Rik     0          0          0          0

and deseq data (below).

               baseMean log2FoldChange     lfcSE      stat    pvalue      padj
0610009B22Rik  261.0242      0.0652558 0.1773184  0.368015  0.712862  0.908775
0610009L18Rik   26.6356     -0.4974079 0.5355241 -0.928825  0.352980        NA

I know how to combine annotation files and counts files, but not this kind of data.

Would someone be willing to show me how to do this?

Answer 1

[swbarnes2]

Once the two data frames have the same row names you can merge them. This will work properly even if genes are out of order, which makes it safer than just adding the columns from one to the other.

[Papyrus]

+1 much simpler and safer indeed!!

Answer 2

[Istvan Albert]

you can use the paste command to place join files line by line. For it work the files must be in the exact same order

$ cat a
1
2
3

$ cat b
A
B
C

$ paste a b
1   A
2   B
3   C

To cut out some columns from the results use the cut command.

$ paste a b a b a b a b | cut -f 1-3,5-7
1   A   1   1   A   1
2   B   2   2   B   2
3   C   3   3   C   3

If the files are not in order or the number of lines differ you would need to use join plus some other prior steps.

Answer 3

[Papyrus]

If you want to do it within R, if you have both files as data.frames, you can do, for each of the columns (the files do not need to be in the same order, however they should have the same genes in order to avoid NAs)

# Convert to dataframes if they are matrices
dataframe_deseq2 <- as.data.frame(dataframe_deseq2)
dataframe_counts <- as.data.frame(dataframe_counts)

# Do this for each column you want to add
dataframe_deseq2$WT_S1_12hr <- dataframe_counts$WT_S1_12hr[match(rownames(dataframe_deseq2),rownames(dataframe_counts))]