Unión de objetos con “llave” exacta

En muchas ocasiones nos enfrentamos a casos donde queremos unir dos conjunto de datos (dataframes) por ejemplo las encuestas de hogares tipicamente separan la información de los miembros del hogar (edad, sexo, escolaridad, etc.) de la información de la vivienda (cuartos, equipamiento, etc.). Tipicamente tenemos algun tipo de identificador que funcione como una llave y que conecta a ambos objetos:

Objetos que queremos unir

DF Registros Variables
Hogares 2 3
Miembros 6 3

Las llaves pueden ser numeros, caractéres, factores, etc. R nos permite unir objetos siempre y cuando las llaves sean del mismo “tipo”.

Hogares<-tibble(
  ID_Hogar = 1:2, 
  Ingreso = c(1000,5000), 
  Color = c("Azul","Rojo")
)
Miembros <-tibble(
  ID_Hogar = c(1,1,1,2,2,3), 
  ID_Persona = c("01","02","03","01","02","01"), 
  Edad = seq(10, 60, by = 10)
)

En este caso supongamos que estamos interesados en conocer cuantas personas viven en casas azules. Podriamos utilizar la libreria dplyr y la llave “ID Hogar” para asignarle a cada individuo (ID Persona). Lo que buscamos es usar la variable “ID Hogar” que se encuentra en el df Hogares, para asignarle a los miembros del hogar la información de la vivienda.

knitr::kable(Hogares, col.names = gsub("[.]", " ", names(Hogares)),caption = "Hogares")
Hogares
ID_Hogar Ingreso Color
1 1000 Azul
2 5000 Rojo

El df Miembros también cuenta con una variable llamada ID_Hogar y ésta será la que nos ayude a unir la información de Hogares en Miembros.

knitr::kable(Miembros, col.names = gsub("[.]", " ", names(Miembros)),caption = "Miembros")
Miembros
ID_Hogar ID_Persona Edad
1 01 10
1 02 20
1 03 30
2 01 40
2 02 50
3 01 60

El comando que usaremos es left_join (hay otras variantes), pero en general lo que nos indica es que se unira utilizando la información del objeto a la izquierda de la función, para “empatar”.

Miembros %>% left_join(Hogares,by="ID_Hogar")
## # A tibble: 6 x 5
##   ID_Hogar ID_Persona  Edad Ingreso Color
##      <dbl> <chr>      <dbl>   <dbl> <chr>
## 1        1 01            10    1000 Azul 
## 2        1 02            20    1000 Azul 
## 3        1 03            30    1000 Azul 
## 4        2 01            40    5000 Rojo 
## 5        2 02            50    5000 Rojo 
## 6        3 01            60      NA <NA>

Como podemos ver, el individuo que habita en el hogar numero 3 no cuenta con su correspondiente registro en el df Hogares. Si hubieramos realizado un right_join, éste ultimo registro se hubiera eliminado, ya que lo que queremos preservar son los registros de Hogares.

Unión de objetos sin “llave” exacta

El ejemplo anterior mostró como se pueden unir objetos cuando sabemos que la llave es la misma para los diferentes elementos que queremos unir. Sin embargo ¿qué sucede cuando esto no es el caso?. Vamos a ver un ejemplo muy práctico de estadisticas de homicidio en México. El INEGI publica de manera regular esta información, perso al descargarla solamente nos indica el nombre de los municipios y estados donde ocurrieron los eventos. No facilita su clave geostadística (Muy mal!) que nos permita concatenar esta información con otros indicadores.

Lo primero que vamos a hacer es cargar nuestra base de datos de homicidios. Removemos los casos donde no sabemos en donde ocurrieron los eventos y excluimos las repeticiones (esta es una serie de tiempo municipal de homicidios registrados mensualmente en cada municipio para el periodo de 1990-2018).

data <- haven::read_dta("INEGI_hom_byage.dta")%>% 
  rename(ent=state, mun=municipio) %>% filter(mun!="No especificado")
data %>% group_by(ent) %>% tally()
## # A tibble: 32 x 2
##    ent                      n
##    <chr>                <int>
##  1 Aguascalientes        4176
##  2 Baja California       2088
##  3 Baja California Sur   2088
##  4 Campeche              4176
##  5 Chiapas              42804
##  6 Chihuahua            23664
##  7 Ciudad de México      5916
##  8 Coahuila de Zaragoza 13572
##  9 Colima                3828
## 10 Durango              13920
## # … with 22 more rows
homicides<- data %>% select(mun,ent) %>% unique()

Como podemos observar, el df cuenta con cinco variables, en donde un municipio se presenta en más de una ocasión. De manera más precisa hay 843,552 registros o alrededor de 340 observaciones por municipio.

knitr::kable(head(data), "markdown")
mun ent total anos0 anos1_4 anos10_14 anos15_19 anos5_9 year month
Aguascalientes Aguascalientes 1 NA NA NA NA NA 1990 1
Aguascalientes Aguascalientes 1 NA NA 1 NA NA 1990 2
Aguascalientes Aguascalientes 3 NA NA NA NA NA 1990 3
Aguascalientes Aguascalientes 4 NA NA NA 1 NA 1990 4
Aguascalientes Aguascalientes 3 NA NA NA NA NA 1990 5
Aguascalientes Aguascalientes 5 NA NA NA NA NA 1990 6

De igual manera cargamos una base (también del INEGI), donde tenemos ya cargados los catalogos con las claves geoestadísticas, así como sus nombres respectivos. Vemos la distribución de los municipios por entidad:

inegi<-read.dta13("INEGI_entmun.dta", generate.factors = T) 
inegi %>% group_by(NOM_ENT) %>% tally()
## # A tibble: 32 x 2
##    NOM_ENT                 n
##    <chr>               <int>
##  1 Aguascalientes         11
##  2 Baja California         5
##  3 Baja California Sur     5
##  4 Campeche               11
##  5 Chiapas               123
##  6 Chihuahua              67
##  7 Ciudad de México       16
##  8 Coahuila               38
##  9 Colima                 10
## 10 Durango                39
## # … with 22 more rows

Como podemos observar, existen entidades con nombres diferentes. Tal es el caso de Coahuila, en el df data tenemos al estado como “Coahuila”, en tanto que en inegi se tiene guardado como “Coahuila de Zaragoza”. Una posible solución es reemplazar el nombre de las entidades de acuerdo a un formato en especial:

data %>% group_by(ent) %>% tally()
## # A tibble: 32 x 2
##    ent                      n
##    <chr>                <int>
##  1 Aguascalientes        4176
##  2 Baja California       2088
##  3 Baja California Sur   2088
##  4 Campeche              4176
##  5 Chiapas              42804
##  6 Chihuahua            23664
##  7 Ciudad de México      5916
##  8 Coahuila de Zaragoza 13572
##  9 Colima                3828
## 10 Durango              13920
## # … with 22 more rows
data$ent[data$ent %in% "Michoacán De Ocampo" ]<-  "Michoacán"
data$ent[data$ent %in% "Coahuila de Zaragoza"] <-  "Coahuila"
data$ent[data$ent %in% "Veracruz De Ignacio De La Llave" ]<-  "Veracruz"

Paquete “stringdist”

Facil ¿verdad?, pero ahora tenemos que “homologar” de igual forma los nombres de municipios. De forma más específica, tendríamos que revisar más de 2400 registros “a mano”, lo cual podria incrementar el riesgo de errores de captura o recodificación. Una solución a esto es el paquete de fuzzyjoin que utiliza el motor de Mark P.J. van der Loo, 2014. Su paper es muy recomendable, ya que ayuda a comprender las diferentes métricas del “approximate string matching”.

Primero vamos a entender que es lo que hace el motor stringdist.

Lo primero que vamos a hacer es un matching o union exacta. Utilizaremos para ello el nombre de las entidades y luego de entidades y municipios de manera simultánea.

  • Exact matching estatal
homicides %>% left_join(inegi %>% select(NOM_ENT,CVE_ENT) %>% 
                          unique(),by=c("ent"="NOM_ENT")) %>% group_by(CVE_ENT) %>% tally()
## # A tibble: 30 x 2
##    CVE_ENT     n
##    <chr>   <int>
##  1 01         12
##  2 02          6
##  3 03          6
##  4 04         12
##  5 06         11
##  6 07        123
##  7 08         68
##  8 09         17
##  9 10         40
## 10 11         47
## # … with 20 more rows

En este caso podemos ver que hay 30 filas, 29 que se codificaron correctamente y una fila con NA.

  • Exact matching municipal
homicides %>% left_join(inegi %>% select(NOMGEO,CVE_MUN,NOM_ENT,CVE_ENT) %>% 
                          unique(),by=c("mun"="NOMGEO","ent"="NOM_ENT" )) %>% 
  mutate(entmun=paste0(CVE_ENT,CVE_MUN)) %>% group_by(entmun) %>% tally()
## # A tibble: 2,019 x 2
##    entmun     n
##    <chr>  <int>
##  1 01001      1
##  2 01002      1
##  3 01003      1
##  4 01004      1
##  5 01005      1
##  6 01006      1
##  7 01007      1
##  8 01008      1
##  9 01009      1
## 10 01010      1
## # … with 2,009 more rows

En este caso, aunque hay más de 2400 municipios solamente tenemos 2019 uniones exactas. Es decir cerca de 450 nombres de municipios no son exactamente iguales en ambos df. ¿Cuál será nuestra salida? stringdist_join. Veamos el caso más sencillo con la información a nivel estatal.

inegi %>% select(NOM_ENT,CVE_ENT) %>% unique()
##                  NOM_ENT CVE_ENT
## 1         Aguascalientes      01
## 12       Baja California      02
## 17   Baja California Sur      03
## 22              Campeche      04
## 33              Coahuila      05
## 71                Colima      06
## 81               Chiapas      07
## 204            Chihuahua      08
## 271     Ciudad de México      09
## 287              Durango      10
## 326           Guanajuato      11
## 372             Guerrero      12
## 453              Hidalgo      13
## 537              Jalisco      14
## 662               México      15
## 787            Michoacán      16
## 900              Morelos      17
## 933              Nayarit      18
## 953           Nuevo León      19
## 1004              Oaxaca      20
## 1574              Puebla      21
## 1791           Querétaro      22
## 1809        Quintana Roo      23
## 1820     San Luis Potosí      24
## 1878             Sinaloa      25
## 1896              Sonora      26
## 1968             Tabasco      27
## 1985          Tamaulipas      28
## 2028            Tlaxcala      29
## 2088            Veracruz      30
## 2300             Yucatán      31
## 2406           Zacatecas      32
stringdist_left_join(
  data %>% select(ent) %>% rename(NOM_ENT=ent) %>% unique(), 
  inegi %>% select(NOM_ENT,CVE_ENT) %>% unique(),
  method = "soundex",  by ="NOM_ENT", distance_col = NULL)
## Warning in do_dist(a = b, b = a, method = method, weight = weight, q = q, : Non-
## printable ascii or non-ascii characters in soundex. Results may be unreliable.
## See ?printable_ascii.
## # A tibble: 34 x 3
##    NOM_ENT.x           NOM_ENT.y           CVE_ENT
##    <chr>               <chr>               <chr>  
##  1 Aguascalientes      Aguascalientes      01     
##  2 Campeche            Campeche            04     
##  3 Baja California Sur Baja California     02     
##  4 Baja California Sur Baja California Sur 03     
##  5 Baja California     Baja California     02     
##  6 Baja California     Baja California Sur 03     
##  7 Coahuila            Coahuila            05     
##  8 Chiapas             Chiapas             07     
##  9 Chihuahua           Chihuahua           08     
## 10 Colima              Colima              06     
## # … with 24 more rows

Veamos que seguimos teniendo problemas en la unión. Una forma más sistémica es separar las uniones exactas de las no exactas. Posteriormente procedemos a la parte medular de esta nota.

Fuzzy nested matching o unión de objetos anidados

El problema anterior es abordado a partir de las librearias que Primero y dado que esto busca ser un ejemplo escalable, cambiemos los nombres de las variables siguiendo una estructura anidada:

  • C1ID: Coded id level 1
  • C2ID: Coded id level 2
  • C1Name: Coded name 1
  • C2Name: Coded name 2
  • U1Name: Uncoded name 1
  • U2Name: Uncoded name 2

De igual manera hacemos una ligera limpieza para contabilizar solamente las observaciones en municipios validos. Posteriormente separamos el df principal en dos, Coded que contiene aquellas observaciones donde se realizó un matching exacto a dos niveles. Esto es, hubo una correspondencia de entidad y municipio en ambos df. El df con el que vamos a trabajar es Uncoded y corresponde a los casos donde no hubo un matching exacto, por lo que procederemos a realizar un “fuzzy matching”.

Coded<-inegi %>%  rename(C1ID=CVE_ENT,C2ID=CVE_MUN,C1Name=NOM_ENT,C2Name=NOMGEO) %>% 
  select(C2ID,C1ID,C1Name,C2Name) 

Uncoded<-data %>% filter(mun!="Total" & is.na(mun)!=T)  %>%rename(U1Name=ent,U2Name=mun) %>% select(U1Name,U2Name) %>% unique()
as_tibble(Coded)->Coded
as_tibble(Uncoded)->Uncoded

Matched<-inner_join(Coded,Uncoded,by=c("C1Name"="U1Name","C2Name"="U2Name"))
Unmatched<-anti_join(Uncoded,Coded,by=c("U1Name"="C1Name","U2Name"="C2Name"))

Una parte importante de este ejercicio es desarrollar el emparejamiento de manera escalonada. Esto es, para cada nivel se debe realizar primero un matching exacto y posteriormente un fuzzy matching. De esta manera, del df de Unmatched se analiza primero en su nivel más alto de agregaciñon. Esto es, previo a realizar el fuzzy matching en el nivel de anidamiento más bajo, lo que buscamos separar con el comando regex_inner_join.

Unmatched<-regex_inner_join(Unmatched,Coded %>% select("C1Name") %>% unique(),by =c("U1Name"="C1Name"))
Matched<-bind_rows(Matched,
                  inner_join(Coded,Unmatched,by=c("C1Name"="C1Name","C2Name"="U2Name"))
                  )
Matched<-Matched%>% mutate(U1Name=if_else(is.na(U1Name)==T,C1Name,U1Name))
Unmatched<-anti_join(Unmatched,Coded,by=c("C1Name"="C1Name","U2Name"="C2Name")) 

X<-Unmatched # Uncoded data
Y<-Coded # Coded data

varsX <- c(Name = "C1Name")
varsY <- c(Name = "C1Name", ID ="C1ID")

X %>% select(!!varsX) %>%  distinct() %>% mutate(Name=as.character(Name)) ->x
Y %>% select(!!varsY) %>%  distinct() %>% mutate(Name=as.character(Name)) ->y

Z<-stringdist_join(x, y, by = "Name") %>% 
  select(-Name.y) %>% 
  rename(U1Name=Name.x)

Z<-left_join(X,Z, by="U1Name")
rm(varsX,varsY,x,y)

varsX2 <- c(Name = "U2Name", "U1Name")
varsY2 <- c(Name = "C2Name", ID ="C1ID", "C2ID")

z<-Z %>% pull(ID) %>% unique()

df <- data.frame()
for (x in 1:length(z)){
  Z %>% filter(ID==z[[x]])->W
  Y %>% filter(C1ID==z[[x]])->w
  W %>% select(!!varsX2) %>%  distinct() %>% mutate(Name=as.character(Name)) ->W
  w %>% select(!!varsY2) %>%  distinct() %>% mutate(Name=as.character(Name)) ->w
  Ww<-stringdist_join(W,w, mode="inner",method = "soundex", by ="Name")
  df <- bind_rows(df, Ww)
  rm(w,W,Ww)
}

df %>%select(Name.x,ID,C2ID,U1Name)  %>% distinct() %>% rename(U2Name=Name.x) ->df
`%notin%` <- Negate(`%in%`)
df %>% group_by(U2Name,ID,U1Name) %>% tally() %>% filter(n>1) %>% pull(U2Name)->df2
df %>% group_by(U2Name,ID,U1Name,C2ID) %>% filter(U2Name%notin%df2) %>% rename (C1ID=ID)->Unmatched

Al final no identificamos los siguientes muncipios:

  • San Martín de Bolańos
  • San Pedro Mixtepec
  • Tlajomulco de Zúńiga
  • San Juan Mixtepec
  • Briseńas
Match_Unmatch<-bind_rows(Unmatched,Matched %>% rename(U2Name=C2Name) %>% select(-C1Name))
Match_Unmatch %>% rename(ent=U1Name,mun=U2Name,CVE_MUN=C2ID,CVE_ENT=C1ID)->Match_Unmatch
df<-full_join(data,Match_Unmatch)
## Joining, by = c("mun", "ent")
## Warning: Column `mun` has different attributes on LHS and RHS of join
## Warning: Column `ent` has different attributes on LHS and RHS of join
df %>% mutate(CVE_MUN=if_else(mun=="Briseńas","011",CVE_MUN),
              CVE_MUN=if_else(mun=="San Martín de Bolańos","076",CVE_MUN),
              CVE_MUN=if_else(mun=="San Pedro Mixtepec","319",CVE_MUN),
              CVE_MUN=if_else(mun=="Tlajomulco de Zúńiga","097",CVE_MUN)) %>% 
        mutate(CVE_ENT=if_else(mun=="Briseńas","016",CVE_ENT),
              CVE_ENT=if_else(mun=="San Martín de Bolańos","014",CVE_ENT),
              CVE_ENT=if_else(mun=="San Pedro Mixtepec","020",CVE_ENT),
              CVE_ENT=if_else(mun=="Tlajomulco de Zúńiga","014",CVE_ENT))->df
library(foreign)
write.dta(df, "homicides_minors.dta")