I have multiple data tables in a folder that I want to merge by column V1
. If an ID (V1) is not found in a data frame it should leave NA or 0 for that specific ID in the data table.
I have tried this solution, but it requires that all IDs are in all data frames.
setwd("../")
file_list = list.files(pattern="*.csv")
datalist = lapply(file_list, function(x){
dat = read.csv(file=x, header=F, sep = "\t")
names(dat)[2] = x
return(dat)
})
library(plyr); library(dplyr)
joined <- join_all(dfs = datalist,by = "V1",type ="full" )
example input:
> head(a)
V1 V2
1 oni-miR-8159_MIMAT0042837_Oreochromis_niloticus_miR-8159 990663
2 oni-miR-148-3p_MIMAT0042824_Oreochromis_niloticus_miR-148-3p 205000
3 oni-miR-122_MIMAT0042727_Oreochromis_niloticus_miR-122 164163
4 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 58769
5 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 58769
6 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 58554
> head(b)
V1 V2
1 oni-miR-122_MIMAT0042727_Oreochromis_niloticus_miR-122 224691
2 oni-miR-143_MIMAT0042647_Oreochromis_niloticus_miR-143 173663
3 oni-miR-26a_MIMAT0042651_Oreochromis_niloticus_miR-26a 111156
4 oni-miR-26a_MIMAT0042651_Oreochromis_niloticus_miR-26a 110569
5 oni-miR-26a_MIMAT0042651_Oreochromis_niloticus_miR-26a 110534
6 oni-miR-192_MIMAT0042644_Oreochromis_niloticus_miR-192 80646
> head(c)
V1 V2
1 oni-miR-122_MIMAT0042727_Oreochromis_niloticus_miR-122 176167
2 oni-miR-145_MIMAT0043395_Oreochromis_niloticus_miR-145 96204
3 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 71627
4 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 71627
5 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 71568
6 oni-let-7a_MIMAT0042659_Oreochromis_niloticus_let-7a 71563