daily pastebin goal
85%
SHARE
TWEET

Untitled

a guest Sep 23rd, 2018 109 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ### NOVO PROCEDIMENTO PARA DADOS
  2.  
  3. # ALUNOS / MATRÍCULAS / QUALIFICAÇÕES
  4.  
  5. setwd("/home/hudson/minitrab/app")
  6.  
  7. library(dplyr)
  8. library(data.table)
  9. library(RPostgreSQL)
  10. library(bit64)
  11. library(stringr)
  12.  
  13. #Carregando o Driver
  14. drv <- dbDriver("PostgreSQL")
  15.  
  16. #Conectando com a Base de dados
  17. con <- dbConnect(drv, dbname = "mtb",
  18.                  host = "10.1.0.4", port = 5432,
  19.                  user = "radar", password = "RadaR_@5112@18")
  20.  
  21.  
  22. #Lendo as tabelas em datraframes                 
  23. mdl_user <- dbGetQuery(con, "SELECT * FROM public.mdl_user")
  24. mdl_user_enrolments <- dbGetQuery(con, "SELECT * FROM public.mdl_user_enrolments")
  25. mdl_enrol <- dbGetQuery(con, "SELECT * FROM public.mdl_enrol")
  26. mdl_course <- dbGetQuery(con, "SELECT *  FROM public.mdl_course")
  27. mdl_course_categories <- dbGetQuery(con, "SELECT * FROM public.mdl_course_categories")
  28. mdl_badge_issued <- dbGetQuery(con, "SELECT * FROM public.mdl_badge_issued")
  29.  
  30. dim_estudante <- fread("dados/dim_estudante.csv")
  31.  
  32. #Disconectando
  33. dbDisconnect(con)
  34.  
  35. #Descarregando o Driver
  36. dbUnloadDriver(drv)
  37.  
  38. ##############################
  39. # ALUNOS
  40.  
  41. # Selecionando apenas as colunas utilizáveis
  42. mdl_user <- select(mdl_user, c("id", "auth", "confirmed", "username", "idnumber", "firstname", "lastname", "email", "country", "lang", "calendartype", "timezone", "firstaccess", "lastaccess", "lastlogin", "currentlogin", "lastip", "timecreated", "alternatename"))
  43.  
  44. #Renomeando 'timecreated' para 'tempocadastro' , 'alternatename' para 'cpf' e "id" para "userid"
  45. mdl_user <- rename(mdl_user, tempocadastro = timecreated)
  46. mdl_user <- rename(mdl_user, cpf = alternatename)
  47. mdl_user <- rename(mdl_user, userid = id)
  48.  
  49. # NÚMERO DE ALUNOS POR CPF
  50. alunos <- mdl_user[!is.na(mdl_user$cpf),]
  51. alunos <- alunos[alunos$cpf != "",]
  52. alunos <- alunos[!duplicated(alunos$cpf),]
  53.  
  54. #### ADICIONANDO MAIS INFORMAÇÕES SOBRE UF
  55.  
  56. #Manipulacoes CPF em 'dim_estudante'
  57. dim_estudante$cpf <- str_pad(dim_estudante$cpf, 11, pad = "0")
  58. dim_estudante$cpf <- as.character(dim_estudante$cpf)
  59.  
  60. #Manipulacoes CPF em 'alunos'
  61. #Retirando 'pontuação'
  62. alunos$cpf <- gsub("[[:punct:]]", "", alunos$cpf)
  63. alunos$cpf <- as.character(alunos$cpf)
  64.  
  65. #Juntando 'dim_estudante' com 'matriculas_curso' por CPF
  66. alunos <- left_join(alunos, dim_estudante, by='cpf')
  67.  
  68. #SUBSTITUINDO NA's por "NI"
  69. alunos[is.na(alunos)] <- c("NI")
  70.  
  71. ##############################
  72. # MATRÍCULAS EM CURSOS
  73. matriculas_curso <- mdl_user_enrolments
  74.  
  75. # Joins para obter courseid, e fullname (nome do curso)
  76. mdl_enrol <- rename(mdl_enrol, enrolid = id)
  77. mdl_course <- rename(mdl_course, courseid = id)
  78. mdl_enrol <- rename(mdl_enrol, enrolname = name)
  79. mdl_course_categories <- rename(mdl_course_categories, categoryName = name)
  80.  
  81. course_enrol_join <- mdl_course
  82. course_enrol_join <- merge(course_enrol_join, mdl_enrol, by.x = "courseid", by.y = "courseid", all.y = TRUE)
  83. course_enrol_join <- merge(course_enrol_join, mdl_course_categories, by.x = "category", by.y = "id")
  84.  
  85. # Seleção de colunas a manter
  86. keep = c(
  87.   "courseid",
  88.   "fullname",
  89.   "enrolid",
  90.   "categoryName"
  91. )
  92. course_enrol_join <- subset(course_enrol_join, select = keep)
  93. course_enrol_join <- distinct(course_enrol_join)
  94.  
  95. matriculas_curso <- left_join(mdl_user_enrolments, course_enrol_join, by = "enrolid")
  96. matriculas_curso <- select(matriculas_curso, -c("id", "status", "modifierid", "timemodified"))
  97.  
  98. # Adição de informação dos alunos através do join com a tabela alunos
  99. matriculas_curso <- left_join(matriculas_curso, alunos, by = "userid")
  100.  
  101. # Remoção de CPFs duplicados para o mesmo curso
  102. matriculas_curso <- distinct(matriculas_curso, cpf, courseid, .keep_all = TRUE)
  103.  
  104.  
  105. ##############################
  106. # QUALIFICAÇÕES
  107.  
  108. # Join por 'userid' com 'alunos'
  109. qualificacoes_cpf <- distinct(mdl_badge_issued)
  110. qualificacoes_cpf <- left_join(qualificacoes_cpf, alunos, by = "userid")
  111.  
  112. # Substituindo NA por NI
  113. qualificacoes_cpf[is.na(qualificacoes_cpf)] <- c("NI")
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top