R / SQL

Rafael Zambrano

Librerías

library(RMariaDB) # Para base de datos Mysql
library(tidyverse) #Para manipulacion de datos
library(plotly) # Graficos dinamicos
library(ggsci) #paletas de colores
library(flextable) #Tablas con formato informe academico

Conexion

conexion <- dbConnect(RMariaDB::MariaDB(),
                       user ='root',
                       password ="",
                       dbname ='CLASES_BD',
                       host ='localhost')

• Para ver el listado de tablas de la base de datos:

dbListTables(conexion)

 [1] "COVID_CASOS_ARG"             "COVID_CLASIFICACION"         "COVID_CLASIFICACION_DETALLE"
 [4] "COVID_DEPARTAMENTO"          "COVID_ORIGEN_FINANCIAMIENTO" "COVID_PAIS"                 
 [7] "COVID_PROVINCIA"             "COVID_SEXO"                  "COVID_SINO"                 
[10] "COVID_TIPO_EDAD"            

Lectura de Tablas de Mysql

COVID_CASOS_ARG = tbl(conexion,"COVID_CASOS_ARG")
COVID_CLASIFICACION = tbl(conexion,"COVID_CLASIFICACION")
COVID_CLASIFICACION_DETALLE = tbl(conexion,"COVID_CLASIFICACION_DETALLE")
COVID_DEPARTAMENTO = tbl(conexion,"COVID_DEPARTAMENTO")
COVID_ORIGEN_FINANCIAMIENTO = tbl(conexion,"COVID_ORIGEN_FINANCIAMIENTO")
COVID_PAIS = tbl(conexion,"COVID_PAIS")
COVID_PROVINCIA = tbl(conexion,"COVID_PROVINCIA")
COVID_SEXO = tbl(conexion,"COVID_SEXO")
COVID_SINO = tbl(conexion,"COVID_SINO")
COVID_TIPO_EDAD = tbl(conexion,"COVID_TIPO_EDAD")

Actividades: Realizar las consultas para obtener la siguiente información:

• Armar 9 grupos etarios (el primero de 0 a 9 años, el segundo de 10 a 19, …, el anteúltimo de 70 a 79 y el último para las personas mayores a 80 años). Calcular la cantidad de casos confirmados, fallecidos y tasa de letalidad para cada grupo.

COVID_CASOS_ARG %>% 
  left_join(COVID_TIPO_EDAD) %>% 
  left_join(COVID_CLASIFICACION) %>% 
  left_join(COVID_CLASIFICACION_DETALLE %>% 
              select(-ID_CLASIFICACION)) %>% 
  mutate(EDAD = ifelse(TIPO_EDAD == "Meses",EDAD/12,EDAD)) %>% 
  mutate(Edad_rangos = case_when(EDAD < 10 ~ "1",
                                 EDAD < 20 ~ "2",
                                 EDAD < 30 ~ "2",
                                 EDAD < 40 ~ "3",
                                 EDAD < 50 ~ "4",
                                 EDAD < 60 ~ "5",
                                 EDAD < 70 ~ "6",
                                 EDAD < 80 ~ "7",
                                 T ~ "8")) %>% 
  group_by(Edad_rangos) %>% 
  summarise(confiramdos = sum(CLASIFICACION == "Confirmado"),
            fallecidos = sum(CLASIFICACION_DETALLE == "Caso confirmado - Fallecido")) %>% 
  mutate(letalidad = fallecidos/confiramdos)

• Para poder guardar la salida en una variable solo hace falta agregar la funcion “collect()” al final de la sentencia, y asignar un nombre a la data, en este caso “pregunta_1”

pregunta_1 = COVID_CASOS_ARG %>% 
  left_join(COVID_TIPO_EDAD) %>% 
  left_join(COVID_CLASIFICACION) %>% 
  left_join(COVID_CLASIFICACION_DETALLE %>% 
              select(-ID_CLASIFICACION)) %>% 
  mutate(EDAD = ifelse(TIPO_EDAD == "Meses",EDAD/12,EDAD)) %>% 
  mutate(Edad_rangos = case_when(EDAD < 10 ~ "1",
                                 EDAD < 20 ~ "2",
                                 EDAD < 30 ~ "2",
                                 EDAD < 40 ~ "3",
                                 EDAD < 50 ~ "4",
                                 EDAD < 60 ~ "5",
                                 EDAD < 70 ~ "6",
                                 EDAD < 80 ~ "7",
                                 T ~ "8")) %>% 
  group_by(Edad_rangos) %>% 
  summarise(confiramdos = sum(CLASIFICACION == "Confirmado"),
            fallecidos = sum(CLASIFICACION_DETALLE == "Caso confirmado - Fallecido")) %>% 
  mutate(letalidad = fallecidos/confiramdos) %>% 
  collect()

flextable(pregunta_1)

Edad_rangos	confiramdos	fallecidos	letalidad
5	4659	123	0.0264
4	6861	58	0.0085
3	8643	23	0.0027
2	11155	10	0.0009
7	1476	226	0.1531
6	2478	189	0.0763
8	1581	349	0.2207
1	2704	1	0.0004

p1 = pregunta_1 %>% 
  mutate(Edad_rangos = as.factor(Edad_rangos)) %>% 
  pivot_longer(-Edad_rangos, names_to = "Estatus", values_to = "Casos") %>% 
  ggplot(aes(fill = Edad_rangos, y = Casos, x = Edad_rangos)) + 
  geom_bar(position="stack", stat="identity") +
  theme_minimal() +
  scale_fill_jco() +
  facet_wrap(~Estatus, scales = "free") +
  ggtitle("Confirmados/Fallecidos/Letalidad") +
  theme(legend.position = "none") +
  xlab("Rangos de Edad")

ggplotly(p1,width = 900, height = 500)

¿Como R consulta los datos en una base estructurada sin escribir en SQL ?

• A través del universo de paquetes de tidyverse específicamente “dplyr” y “dbplyr” se traduce automáticamente la sintaxis R a SQL, a continuación se muestra la traducción a SQL que realiza R bajo el capó, simplemente agregando la función “show_query” en lugar de “collect” al final de la sentencia. En la salida podrán notar que se enumeran todos los campos en lugar de usar "*"

COVID_CASOS_ARG %>% 
  left_join(COVID_TIPO_EDAD) %>% 
  left_join(COVID_CLASIFICACION) %>% 
  left_join(COVID_CLASIFICACION_DETALLE %>% 
              select(-ID_CLASIFICACION)) %>% 
  mutate(EDAD = ifelse(TIPO_EDAD == "Meses",EDAD/12,EDAD)) %>% 
  mutate(Edad_rangos = case_when(EDAD < 10 ~ "1",
                                 EDAD < 20 ~ "2",
                                 EDAD < 30 ~ "2",
                                 EDAD < 40 ~ "3",
                                 EDAD < 50 ~ "4",
                                 EDAD < 60 ~ "5",
                                 EDAD < 70 ~ "6",
                                 EDAD < 80 ~ "7",
                                 T ~ "8")) %>% 
  group_by(Edad_rangos) %>% 
  summarise(confiramdos = sum(CLASIFICACION == "Confirmado"),
            fallecidos = sum(CLASIFICACION_DETALLE == "Caso confirmado - Fallecido")) %>% 
  mutate(letalidad = fallecidos/confiramdos) %>% 
  show_query()

<SQL>
SELECT `Edad_rangos`, `confiramdos`, `fallecidos`, `fallecidos` / `confiramdos` AS `letalidad`
FROM (SELECT `Edad_rangos`, SUM(`CLASIFICACION` = 'Confirmado') AS `confiramdos`, SUM(`CLASIFICACION_DETALLE` = 'Caso confirmado - Fallecido') AS `fallecidos`
FROM (SELECT `ID_CASO`, `ID_SEXO`, `EDAD`, `ID_TIPO_EDAD`, `ID_PAIS_RESIDENCIA`, `ID_PROVINCIA_RESIDENCIA`, `ID_DEPARTAMENTO_RESIDENCIA`, `ID_PROVINCIA_CARGA`, `FECHA_INICIO_SINTOMAS`, `FECHA_APERTURA`, `SEMANA_APERTURA`, `FECHA_INTERNACION`, `CUIDADO_INTENSIVO`, `FECHA_CUIDADO_INTENSIVO`, `FALLECIDO`, `FECHA_FALLECIMIENTO`, `ASISTENCIA_RESPIRATORIA_MECANICA`, `ID_ORIGEN_FINANCIAMIENTO`, `ID_CLASIFICACION`, `ID_CLASIFICACION_DETALLE`, `FECHA_DIAGNOSTICO`, `TIPO_EDAD`, `CLASIFICACION`, `CLASIFICACION_DETALLE`, CASE
WHEN (`EDAD` < 10.0) THEN ('1')
WHEN (`EDAD` < 20.0) THEN ('2')
WHEN (`EDAD` < 30.0) THEN ('2')
WHEN (`EDAD` < 40.0) THEN ('3')
WHEN (`EDAD` < 50.0) THEN ('4')
WHEN (`EDAD` < 60.0) THEN ('5')
WHEN (`EDAD` < 70.0) THEN ('6')
WHEN (`EDAD` < 80.0) THEN ('7')
ELSE ('8')
END AS `Edad_rangos`
FROM (SELECT `ID_CASO`, `ID_SEXO`, CASE WHEN (`TIPO_EDAD` = 'Meses') THEN (`EDAD` / 12.0) WHEN NOT(`TIPO_EDAD` = 'Meses') THEN (`EDAD`) END AS `EDAD`, `ID_TIPO_EDAD`, `ID_PAIS_RESIDENCIA`, `ID_PROVINCIA_RESIDENCIA`, `ID_DEPARTAMENTO_RESIDENCIA`, `ID_PROVINCIA_CARGA`, `FECHA_INICIO_SINTOMAS`, `FECHA_APERTURA`, `SEMANA_APERTURA`, `FECHA_INTERNACION`, `CUIDADO_INTENSIVO`, `FECHA_CUIDADO_INTENSIVO`, `FALLECIDO`, `FECHA_FALLECIMIENTO`, `ASISTENCIA_RESPIRATORIA_MECANICA`, `ID_ORIGEN_FINANCIAMIENTO`, `ID_CLASIFICACION`, `ID_CLASIFICACION_DETALLE`, `FECHA_DIAGNOSTICO`, `TIPO_EDAD`, `CLASIFICACION`, `CLASIFICACION_DETALLE`
FROM (SELECT `LHS`.`ID_CASO` AS `ID_CASO`, `LHS`.`ID_SEXO` AS `ID_SEXO`, `LHS`.`EDAD` AS `EDAD`, `LHS`.`ID_TIPO_EDAD` AS `ID_TIPO_EDAD`, `LHS`.`ID_PAIS_RESIDENCIA` AS `ID_PAIS_RESIDENCIA`, `LHS`.`ID_PROVINCIA_RESIDENCIA` AS `ID_PROVINCIA_RESIDENCIA`, `LHS`.`ID_DEPARTAMENTO_RESIDENCIA` AS `ID_DEPARTAMENTO_RESIDENCIA`, `LHS`.`ID_PROVINCIA_CARGA` AS `ID_PROVINCIA_CARGA`, `LHS`.`FECHA_INICIO_SINTOMAS` AS `FECHA_INICIO_SINTOMAS`, `LHS`.`FECHA_APERTURA` AS `FECHA_APERTURA`, `LHS`.`SEMANA_APERTURA` AS `SEMANA_APERTURA`, `LHS`.`FECHA_INTERNACION` AS `FECHA_INTERNACION`, `LHS`.`CUIDADO_INTENSIVO` AS `CUIDADO_INTENSIVO`, `LHS`.`FECHA_CUIDADO_INTENSIVO` AS `FECHA_CUIDADO_INTENSIVO`, `LHS`.`FALLECIDO` AS `FALLECIDO`, `LHS`.`FECHA_FALLECIMIENTO` AS `FECHA_FALLECIMIENTO`, `LHS`.`ASISTENCIA_RESPIRATORIA_MECANICA` AS `ASISTENCIA_RESPIRATORIA_MECANICA`, `LHS`.`ID_ORIGEN_FINANCIAMIENTO` AS `ID_ORIGEN_FINANCIAMIENTO`, `LHS`.`ID_CLASIFICACION` AS `ID_CLASIFICACION`, `LHS`.`ID_CLASIFICACION_DETALLE` AS `ID_CLASIFICACION_DETALLE`, `LHS`.`FECHA_DIAGNOSTICO` AS `FECHA_DIAGNOSTICO`, `LHS`.`TIPO_EDAD` AS `TIPO_EDAD`, `LHS`.`CLASIFICACION` AS `CLASIFICACION`, `RHS`.`CLASIFICACION_DETALLE` AS `CLASIFICACION_DETALLE`
FROM (SELECT `LHS`.`ID_CASO` AS `ID_CASO`, `LHS`.`ID_SEXO` AS `ID_SEXO`, `LHS`.`EDAD` AS `EDAD`, `LHS`.`ID_TIPO_EDAD` AS `ID_TIPO_EDAD`, `LHS`.`ID_PAIS_RESIDENCIA` AS `ID_PAIS_RESIDENCIA`, `LHS`.`ID_PROVINCIA_RESIDENCIA` AS `ID_PROVINCIA_RESIDENCIA`, `LHS`.`ID_DEPARTAMENTO_RESIDENCIA` AS `ID_DEPARTAMENTO_RESIDENCIA`, `LHS`.`ID_PROVINCIA_CARGA` AS `ID_PROVINCIA_CARGA`, `LHS`.`FECHA_INICIO_SINTOMAS` AS `FECHA_INICIO_SINTOMAS`, `LHS`.`FECHA_APERTURA` AS `FECHA_APERTURA`, `LHS`.`SEMANA_APERTURA` AS `SEMANA_APERTURA`, `LHS`.`FECHA_INTERNACION` AS `FECHA_INTERNACION`, `LHS`.`CUIDADO_INTENSIVO` AS `CUIDADO_INTENSIVO`, `LHS`.`FECHA_CUIDADO_INTENSIVO` AS `FECHA_CUIDADO_INTENSIVO`, `LHS`.`FALLECIDO` AS `FALLECIDO`, `LHS`.`FECHA_FALLECIMIENTO` AS `FECHA_FALLECIMIENTO`, `LHS`.`ASISTENCIA_RESPIRATORIA_MECANICA` AS `ASISTENCIA_RESPIRATORIA_MECANICA`, `LHS`.`ID_ORIGEN_FINANCIAMIENTO` AS `ID_ORIGEN_FINANCIAMIENTO`, `LHS`.`ID_CLASIFICACION` AS `ID_CLASIFICACION`, `LHS`.`ID_CLASIFICACION_DETALLE` AS `ID_CLASIFICACION_DETALLE`, `LHS`.`FECHA_DIAGNOSTICO` AS `FECHA_DIAGNOSTICO`, `LHS`.`TIPO_EDAD` AS `TIPO_EDAD`, `RHS`.`CLASIFICACION` AS `CLASIFICACION`
FROM (SELECT `LHS`.`ID_CASO` AS `ID_CASO`, `LHS`.`ID_SEXO` AS `ID_SEXO`, `LHS`.`EDAD` AS `EDAD`, `LHS`.`ID_TIPO_EDAD` AS `ID_TIPO_EDAD`, `LHS`.`ID_PAIS_RESIDENCIA` AS `ID_PAIS_RESIDENCIA`, `LHS`.`ID_PROVINCIA_RESIDENCIA` AS `ID_PROVINCIA_RESIDENCIA`, `LHS`.`ID_DEPARTAMENTO_RESIDENCIA` AS `ID_DEPARTAMENTO_RESIDENCIA`, `LHS`.`ID_PROVINCIA_CARGA` AS `ID_PROVINCIA_CARGA`, `LHS`.`FECHA_INICIO_SINTOMAS` AS `FECHA_INICIO_SINTOMAS`, `LHS`.`FECHA_APERTURA` AS `FECHA_APERTURA`, `LHS`.`SEMANA_APERTURA` AS `SEMANA_APERTURA`, `LHS`.`FECHA_INTERNACION` AS `FECHA_INTERNACION`, `LHS`.`CUIDADO_INTENSIVO` AS `CUIDADO_INTENSIVO`, `LHS`.`FECHA_CUIDADO_INTENSIVO` AS `FECHA_CUIDADO_INTENSIVO`, `LHS`.`FALLECIDO` AS `FALLECIDO`, `LHS`.`FECHA_FALLECIMIENTO` AS `FECHA_FALLECIMIENTO`, `LHS`.`ASISTENCIA_RESPIRATORIA_MECANICA` AS `ASISTENCIA_RESPIRATORIA_MECANICA`, `LHS`.`ID_ORIGEN_FINANCIAMIENTO` AS `ID_ORIGEN_FINANCIAMIENTO`, `LHS`.`ID_CLASIFICACION` AS `ID_CLASIFICACION`, `LHS`.`ID_CLASIFICACION_DETALLE` AS `ID_CLASIFICACION_DETALLE`, `LHS`.`FECHA_DIAGNOSTICO` AS `FECHA_DIAGNOSTICO`, `RHS`.`TIPO_EDAD` AS `TIPO_EDAD`
FROM `COVID_CASOS_ARG` AS `LHS`
LEFT JOIN `COVID_TIPO_EDAD` AS `RHS`
ON (`LHS`.`ID_TIPO_EDAD` = `RHS`.`ID_TIPO_EDAD`)
) `LHS`
LEFT JOIN `COVID_CLASIFICACION` AS `RHS`
ON (`LHS`.`ID_CLASIFICACION` = `RHS`.`ID_CLASIFICACION`)
) `LHS`
LEFT JOIN (SELECT `ID_CLASIFICACION_DETALLE`, `CLASIFICACION_DETALLE`
FROM `COVID_CLASIFICACION_DETALLE`) `RHS`
ON (`LHS`.`ID_CLASIFICACION_DETALLE` = `RHS`.`ID_CLASIFICACION_DETALLE`)
) `dbplyr_053`) `dbplyr_054`) `dbplyr_055`
GROUP BY `Edad_rangos`) `dbplyr_056`