Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- library(readxl)
- library(openxlsx)
- library(data.table)
- files<-choose.files()
- mylist<-list()
- mylist2<-list()
- mergedlist<-list()
- columns<-c(5,6,7)
- dumName<-NULL
- for(i in 1:length(files)){
- dumName<-paste0("name ",i)
- dummy<-as.data.frame(read_excel(files[i],sheet="Provider by Test",range="D15:AC3150",na=c("NULL","NA", "#N/A",'-','')))
- dummy<-dummy[rowSums(is.na(dummy))!=ncol(dummy),]
- vecX<-c()
- vecR<-c(1:11)
- for(z in 1:nrow(dummy)){#identifying unneeded rows
- if(any(vecR==as.numeric(dummy[z,3]))){
- vecX<-append(vecX,z)
- }
- }
- dummy<-dummy[-vecX,]
- dummy$id<-paste0(dummy[,1],",",dummy[,3])
- if(i==1){
- for(j in 1:length(columns)){
- mylist[[j]]<-data.table(dummy[,c(columns[j],27)])
- }
- }else{
- for(j in 1:length(columns)){
- mylist2[[j]]<-data.table(dummy[,c(columns[j],27)])
- colnames(mylist2[[j]])[1]<-dumName
- if(nrow(mylist[[1]])>nrow(mylist2[[1]])){
- mergedlist[[j]]<-merge(x=mylist[[j]],y=mylist2[[j]],by="id",all.x=TRUE)
- }else if(nrow(mylist[[1]])<nrow(mylist2[[1]])){
- mergedlist[[j]]<-merge(x=mylist[[j]],y=mylist2[[j]],by="id",all.y=TRUE)
- }else if(nrow(mylist[[1]])==nrow(mylist2[[1]])){
- mergedlist[[j]]<-merge(x=mylist[[j]],y=mylist2[[j]],by="id",all=TRUE)
- }
- mylist[[j]]<-mergedlist[[j]]
- }
- }
- }
- wb<-loadWorkbook(choose.files())
- writeData(wb,sheet=5,mergedlist[[1]])
- writeData(wb,sheet=6,mergedlist[[2]])
- writeData(wb,sheet=7,mergedlist[[3]])
- saveWorkbook(wb,choose.files(),overwrite = TRUE)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement