Advertisement
Guest User

Untitled

a guest
Feb 21st, 2019
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.49 KB | None | 0 0
  1. library(readxl)
  2. library(openxlsx)
  3. library(data.table)
  4.  
  5. files<-choose.files()
  6.  
  7. mylist<-list()
  8. mylist2<-list()
  9. mergedlist<-list()
  10. columns<-c(5,6,7)
  11. dumName<-NULL
  12.  
  13. for(i in 1:length(files)){
  14. dumName<-paste0("name ",i)
  15. dummy<-as.data.frame(read_excel(files[i],sheet="Provider by Test",range="D15:AC3150",na=c("NULL","NA", "#N/A",'-','')))
  16. dummy<-dummy[rowSums(is.na(dummy))!=ncol(dummy),]
  17.  
  18. vecX<-c()
  19. vecR<-c(1:11)
  20. for(z in 1:nrow(dummy)){#identifying unneeded rows
  21. if(any(vecR==as.numeric(dummy[z,3]))){
  22. vecX<-append(vecX,z)
  23. }
  24. }
  25.  
  26. dummy<-dummy[-vecX,]
  27. dummy$id<-paste0(dummy[,1],",",dummy[,3])
  28. if(i==1){
  29. for(j in 1:length(columns)){
  30. mylist[[j]]<-data.table(dummy[,c(columns[j],27)])
  31. }
  32. }else{
  33. for(j in 1:length(columns)){
  34. mylist2[[j]]<-data.table(dummy[,c(columns[j],27)])
  35. colnames(mylist2[[j]])[1]<-dumName
  36. if(nrow(mylist[[1]])>nrow(mylist2[[1]])){
  37. mergedlist[[j]]<-merge(x=mylist[[j]],y=mylist2[[j]],by="id",all.x=TRUE)
  38. }else if(nrow(mylist[[1]])<nrow(mylist2[[1]])){
  39. mergedlist[[j]]<-merge(x=mylist[[j]],y=mylist2[[j]],by="id",all.y=TRUE)
  40. }else if(nrow(mylist[[1]])==nrow(mylist2[[1]])){
  41. mergedlist[[j]]<-merge(x=mylist[[j]],y=mylist2[[j]],by="id",all=TRUE)
  42. }
  43. mylist[[j]]<-mergedlist[[j]]
  44. }
  45.  
  46. }
  47. }
  48.  
  49. wb<-loadWorkbook(choose.files())
  50. writeData(wb,sheet=5,mergedlist[[1]])
  51. writeData(wb,sheet=6,mergedlist[[2]])
  52. writeData(wb,sheet=7,mergedlist[[3]])
  53.  
  54.  
  55. saveWorkbook(wb,choose.files(),overwrite = TRUE)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement