Proyecto Costeo Unitario: Informe Merge Ruralidad

Autor/a

Guillermo David Hincapie

Fecha de publicación

6 de marzo de 2026

Resumen

Este documento presenta una descripción del proceso para el merge entre la base de datos SECOP-II y la base de Categoría de ruralidad en Colombia a nivel municipal. Se propone que el proceso sea mediante bucle departamental porque la base de datos SECOP-II sólo dispone de información del NIT de entidad y no del código Divipola.

Palabras clave

Merge, Divipola, SECOP-II, Analisis de datos

Para llevar a cabo el proceso siguiente fueron utilizadas las siguientes librerias de R, cuya descripción se presenta:

readxl — para leer archivos de Excel (.xlsx). Se usa al cargar la base de datos de categorías de ruralidad y el archivo con los NIT municipales.

stringr — para manipulación de cadenas de texto. Aunque se carga, la función principal de limpieza (limpiar_texto) se construye con funciones base de R como tolower() e iconv(), siendo stringr un complemento disponible para operaciones adicionales sobre texto.

arrow — para leer archivos en formato .parquet, que es un formato columnar optimizado para bases de datos grandes. Es clave en este flujo porque la base SECOP-II supera los 4 millones de registros y este formato permite manejarla eficientemente en memoria.

writexl — para exportar resultados a Excel (.xlsx) sin necesidad de Java (a diferencia de xlsx). Se usa para guardar el listado de NIT de municipios no identificados.

1 Carga de bases de datos

Para efectos de introducir la categoría de ruralidad en la base de datos del SECOP-II se propone cargar la base de datos correspondiente a la clasificación de categorías, para el año 2025, información suministrada por el DANE.

1.1 Base de datos de Categoría de Ruralidad.

La base de datos de ruralidad se carga y se encuentra la siguiente estructura general de sus categorías.

                  Categoria Frecuencia
1 Ciudades y aglomeraciones        121
2                Intermedio        372
3                     Rural        373
4            Rural disperso        256

Se procede, así, a conformar una nueva columna para esta base que ponga el nombre de los municipios en minusculas y sin acentos, esto con el fin de procurar un primer merge entre las dos bases en relación con el nombre del municipio.

Mostrar código

library(stringr)
limpiar_texto <- function(x) {
  x <- tolower(x)
  x <- iconv(x, from = "UTF-8", to = "ASCII//TRANSLIT")
  return(x)
}
rural$municipio <- limpiar_texto(rural$Municipio)
rural$depa <- limpiar_texto(rural$Departamento)

1.2 Cargando base de datos secop-II.

Esta base de datos es pesada, tiene mas de 4 millones de filas referentes a contratos realizados por distintas entidades públicas de cada departamento y municipio. Esta base de datos se encuentra en un tipo de archivo llamado .parquet, que frecuentemente se utiliza para cargar bases de datos de gran consumo de memoria. Bajo el siguiente código se carga la base de datos en cuestión:

Mostrar código

library(arrow)
data <- read_parquet("C:/Users/000278969/OneDrive - UPB/Documentos/Consultorias/2026_Proyecto_ART/Datos/SECOP_contratos_cruce_clasificador_productos.parquet")

Ahora, aplicamos la función anterior construída (ver código de la sección 1.1) sobre la variable que contiene la base SECOP-II que se llama “ciudad”, la cual contiene el nombre del municipio en cuestion. Esto tambien se aplica sobre el nombre del departamento-, con el fin de realizar un merge que tenga en cuanta también el nombre del departamento.

Es importante identificar si esta variable tiene problemas, como datos faltantes - NA- o si el proceso de limpia del texto presentó problemas, ya que afectaría el proceso de merge con la base de datos de ruralidad.

Antes de hacer el proceso de merge, conviene identificar si los nombres de los departamentos entre las dos bases de datos son muy diferentes, dado que este es el mecanismo que permite identificar nombres de municipios que son iguales pero pertenencen a departamentos diferentes. De esta manera, se procede a hacer esta revisión general:

De entrada, la base ruralidad tiene 33 departamentos y la base SECOP-II tiene 34, por lo que es importante identificar la naturaleza de esta diferencia. Una forma de hacerlo es revisar si todos los registros de SECOP están en la base ruralidad.

Aqui vemos dos problemas importantes de la base de datos SECOP-II que dificultan el proceso de unión, y es que en primer lugar hay una categoría departamental que indica “no definido” y que representa 27.447 filas de la base de datos SECOP-II. Este tambien podrá ser un problema de la variable que refiere el nombre del municipio. También será importante identificar, entonces, si la variable de nombre de municipio tambien tiene un registro no identificado para esta base.

Mostrar código

sum(is.na(data$nombre_depa) | grepl("no definido|definido", data$nombre_depa, ignore.case = TRUE))

[1] 27447

Mostrar código

sum(is.na(data$nombre_muni) | grepl("no definido|definido", data$nombre_muni, ignore.case = TRUE))

[1] 922529

Lo cual hace dificil este tratamiento. Una forma de soluconar esto es identificar si existen, para estos registros, el NIT de la entidad. Esto puede ayudar a asociar el nombre del municipio y eventualmente el nombre del departamento para aquellos municipios que los tengan. Para llevar a cabo esta confirmación realizamos el siguiente subset para los no identificados en municipios y luego en departamentos:

Mostrar código

no_idenficado_muni <- subset(data,nombre_muni == "no definido")
summary(no_idenficado_muni$nit_entidad)

   Length     Class      Mode 
   922529 character character

Mostrar código

no_nit <- as.data.frame(table(no_idenficado_muni$nit_entidad))
#head(no_nit)
no_idenficado_depa <- subset(data,nombre_depa == "no definido" )
summary(no_idenficado_depa$nit_entidad)

   Length     Class      Mode 
    27447 character character

Mostrar código

no_nit_depa <- as.data.frame(table(no_idenficado_depa$nit_entidad))
#head(no_nit_depa)

# Ojo: las variables aqui están mal nombradas porque sí tienen NIT. 
library(writexl)

Warning: package 'writexl' was built under R version 4.5.2

Mostrar código

write_xlsx(no_nit, "Municipios_nit.xlsx")

De esta manera, puede observarse que los contratos que no tienen un departamento o una ciudad tienen un NIT, y estos NIT hacen referencia a entidades que no necesariamente son municipales o departamentales. Esto tiene sentido porque hay contratos que realizan entidades del gobierno central, como la entidad “Servicio Nacional de Aprendizaje” entre otras. Sin embargo, haciendo una revision de los NIT´s , existen cerca de 86 municipios que sí tienen NIT de entidad municipal. Estos se pueden dividir en:

GOBIERNO MUNICIPAL PRESUPUESTARIO.
GOBIERNO MUNICIPAL EXTRAPRESUPUESTARIO

Por lo que serán entonces considerados adjuntando para ello el nombre del municipio alque pertencen como razón social. Esto permite ganar información relevante. En efecto, municipios como La Union en Antrioquia presentan contratos por 1114 contratos.

Quedan entonces las siguientes acciones:

Homogenizar a Bogota y San Andres en ambas bases.
Ingresar los NIT´s de las entidades que son del orden muncipal.
Categorizar los contratos no definidos en Departamento y Ciudad como contratos del gobierno central o de entidades centralizadas a las que no aplicaría el criterio de ruralidad en principio.

De esta manera, se propone la homogenización:

Mostrar código

data$nombre_depa <- ifelse(
  tolower(data$nombre_depa) == "distrito capital de bogota",
  "bogota, d.c.",
  data$nombre_depa
)

data$nombre_depa <- ifelse(
  tolower(data$nombre_depa) == "archipielago de san andres, providencia y santa catalina",
  "san andres, providencia y santa catalina",
  data$nombre_depa
)

2 Proceso de Aplicación del Merge.

En general se propone el siguiente código en R para establecer el merge entre la base de datos del SECOP-II y la base de datos de ruralidad , teniendo en cuenta que el merge debe repetir varibles que se encuentran en ambas bases. para que el proceso se haga de manera adecuada, se sugiere hacerlo en bloque, considerando coincidencias entre el nombre de los departamentos como mecanísmo control.

Es importante indicar que algunos entidades consideradas con carácter municipal en realidad no lo son, siendo un potencial error de la base o para verificarlo seria necesario revisar a fondo los contratos en sus anexos técnicos.

Ahora bien, con esta homogenización, podemos proceder a identificar los municipios que asi aparezcan como no definidos en nombre de ciudad y departamento, se pueden capturar sus nombres a partir del NIT correspondiente. Se tienen entonces dos variables en la base secop, que corresponden a los municipios -ciudad inicial y luego a nuestro tratamiento de merge con el NIT. Una forma de verificar que el proceso es correcto, consiste en determinar si el número de no identificados en la nueva variable es congruente con lo inicialmente identificado y, además, con la corrección propuesta.

Mostrar código

sum(is.na(merge_1_nit$nombre_muni_f) | grepl("no definido|definido", merge_1_nit$nombre_muni_f, ignore.case = TRUE))

[1] 768899

Por lo tanto, hay una ganancia potencial de 153630 filas que corresponderían a municipios sobre los cuales hay contratos que no contaron con asignación de ciudad pero si de NIT, y que efectivamente son de municipios. Ahora se procede a hacer el merge entre ruralidad y SECOP-II teniendo en cuenta tanto los valores de departamentos como los valores de ciudad, donde esto ayudará a mantener el control deseado.

Mostrar código

merge_f <-merge(merge_1_nit, rural, by.x="nombre_muni_f", by.y="municipio",all.x = TRUE)
sum(is.na(merge_f$nombre_muni_f) | grepl("no definido|definido", merge_f$nombre_muni_f, ignore.case = TRUE))

[1] 768899

Dando el resultado anterior, se confirma que el proceso es correcto en el sentido en que se incorporaron a municipios por la referencia de su NIT, asi no tengan datos asociados de nombre de ciudad y departamento. Podemos concentrarnos, por ejemplo, en el mucipio de la Union, el cual por NIT debe tener un número de 1000 registros, como efectivament muestra el resultado de la siguiente línea de código.

Mostrar código

union <- subset(merge_f, nit_entidad == "890981995")
dim(union)

[1] 1114   61

Con esta confirmación, entonces, se procede a hacer un descriptivo general de la base de datos finalmente conformada, la cual tiene los indicadores de ruralidad, codigo divipola y otros elementos importantes.

--- title: "Proyecto Costeo Unitario: Informe Merge Ruralidad" author: "Guillermo David Hincapie" date: today date-format: long format: html: toc: true toc-depth: 3 toc-title: "Contenido" number-sections: true theme: cosmo code-fold: true code-summary: "Mostrar código" code-tools: true smooth-scroll: true fig-cap-location: bottom tbl-cap-location: top include-in-header: text: | <style> body { text-align: justify; hyphens: auto; } p { text-align: justify; } .abstract { text-align: justify; } </style> abstract: | Este documento presenta una descripción del proceso para el merge entre la base de datos SECOP-II y la base de Categoría de ruralidad en Colombia a nivel municipal. Se propone que el proceso sea mediante bucle departamental porque la base de datos SECOP-II sólo dispone de información del NIT de entidad y no del código Divipola. keywords: - Merge - Divipola - SECOP-II - Analisis de datos lang: es editor: visual --- Para llevar a cabo el proceso siguiente fueron utilizadas las siguientes librerias de R, cuya descripción se presenta: **readxl** — para leer archivos de Excel (.xlsx). Se usa al cargar la base de datos de categorías de ruralidad y el archivo con los NIT municipales. **stringr** — para manipulación de cadenas de texto. Aunque se carga, la función principal de limpieza (`limpiar_texto`) se construye con funciones base de R como `tolower()` e `iconv()`, siendo `stringr` un complemento disponible para operaciones adicionales sobre texto. **arrow** — para leer archivos en formato .parquet, que es un formato columnar optimizado para bases de datos grandes. Es clave en este flujo porque la base SECOP-II supera los 4 millones de registros y este formato permite manejarla eficientemente en memoria. **writexl** — para exportar resultados a Excel (.xlsx) sin necesidad de Java (a diferencia de `xlsx`). Se usa para guardar el listado de NIT de municipios no identificados. ## Carga de bases de datos Para efectos de introducir la categoría de ruralidad en la base de datos del SECOP-II se propone cargar la base de datos correspondiente a la clasificación de categorías, para el año 2025, información suministrada por el DANE. ### Base de datos de Categoría de Ruralidad. La base de datos de ruralidad se carga y se encuentra la siguiente estructura general de sus categorías. ```{r} #| echo: false library(readxl) rural <- read_excel("C:/Users/000278969/OneDrive - UPB/Documentos/Consultorias/2026_Proyecto_ART/Datos/Nueva_Estrategia/Merge_ruralidad/Categorias_ruralidad_2025.xlsx") categorias<-as.data.frame(table(rural$`Categoría de ruralidad 2025`)) colnames(categorias)<- c("Categoria", "Frecuencia") categorias ``` Se procede, así, a conformar una nueva columna para esta base que ponga el nombre de los municipios en minusculas y sin acentos, esto con el fin de procurar un primer merge entre las dos bases en relación con el nombre del municipio. ```{r} #| echo: true #| message: true #| warning: false library(stringr) limpiar_texto <- function(x) { x <- tolower(x) x <- iconv(x, from = "UTF-8", to = "ASCII//TRANSLIT") return(x) } rural$municipio <- limpiar_texto(rural$Municipio) rural$depa <- limpiar_texto(rural$Departamento) ``` ```{r} #| include: false head(rural$municipio) ``` ### Cargando base de datos secop-II. Esta base de datos es pesada, tiene mas de 4 millones de filas referentes a contratos realizados por distintas entidades públicas de cada departamento y municipio. Esta base de datos se encuentra en un tipo de archivo llamado .parquet, que frecuentemente se utiliza para cargar bases de datos de gran consumo de memoria. Bajo el siguiente código se carga la base de datos en cuestión: ```{r} #| echo: true #| message: true #| warning: false library(arrow) data <- read_parquet("C:/Users/000278969/OneDrive - UPB/Documentos/Consultorias/2026_Proyecto_ART/Datos/SECOP_contratos_cruce_clasificador_productos.parquet") ``` Ahora, aplicamos la función anterior construída (ver código de la sección 1.1) sobre la variable que contiene la base SECOP-II que se llama "ciudad", la cual contiene el nombre del municipio en cuestion. Esto tambien se aplica sobre el nombre del departamento-, con el fin de realizar un merge que tenga en cuanta también el nombre del departamento. ```{r} #| include: false names(data) data$nombre_muni <- limpiar_texto(data$ciudad) data$nombre_depa <- limpiar_texto(data$departamento) ``` Es importante identificar si esta variable tiene problemas, como datos faltantes - NA- o si el proceso de limpia del texto presentó problemas, ya que afectaría el proceso de merge con la base de datos de ruralidad. ```{r} #| include: false names(data) summary(data$nombre_muni) summary(data$nombre_depa) sum(is.na(data$nombre_muni)) sum(is.na(data$nombre_depa)) # No tiene NA´s, por lo tanto el problema es de merge ``` Antes de hacer el proceso de merge, conviene identificar si los nombres de los departamentos entre las dos bases de datos son muy diferentes, dado que este es el mecanismo que permite identificar nombres de municipios que son iguales pero pertenencen a departamentos diferentes. De esta manera, se procede a hacer esta revisión general: ```{r} #| include: false departamentos_ruralidad <- as.data.frame(table(rural$depa)) departamentos_secop <- as.data.frame(table(data$nombre_depa)) ``` De entrada, la base ruralidad tiene 33 departamentos y la base SECOP-II tiene 34, por lo que es importante identificar la naturaleza de esta diferencia. Una forma de hacerlo es revisar si todos los registros de SECOP están en la base ruralidad. ```{r} #| include: false departamentos_secop$Var1 %in% departamentos_ruralidad$Var1 ``` Aqui vemos dos problemas importantes de la base de datos SECOP-II que dificultan el proceso de unión, y es que en primer lugar hay una categoría departamental que indica "no definido" y que representa 27.447 filas de la base de datos SECOP-II. Este tambien podrá ser un problema de la variable que refiere el nombre del municipio. También será importante identificar, entonces, si la variable de nombre de municipio tambien tiene un registro no identificado para esta base. ```{r} #| include: true sum(is.na(data$nombre_depa) | grepl("no definido|definido", data$nombre_depa, ignore.case = TRUE)) sum(is.na(data$nombre_muni) | grepl("no definido|definido", data$nombre_muni, ignore.case = TRUE)) ``` Lo cual hace dificil este tratamiento. Una forma de soluconar esto es identificar si existen, para estos registros, el NIT de la entidad. Esto puede ayudar a asociar el nombre del municipio y eventualmente el nombre del departamento para aquellos municipios que los tengan. Para llevar a cabo esta confirmación realizamos el siguiente subset para los no identificados en municipios y luego en departamentos: ```{r} #| echo: true no_idenficado_muni <- subset(data,nombre_muni == "no definido") summary(no_idenficado_muni$nit_entidad) no_nit <- as.data.frame(table(no_idenficado_muni$nit_entidad)) #head(no_nit) no_idenficado_depa <- subset(data,nombre_depa == "no definido" ) summary(no_idenficado_depa$nit_entidad) no_nit_depa <- as.data.frame(table(no_idenficado_depa$nit_entidad)) #head(no_nit_depa) # Ojo: las variables aqui están mal nombradas porque sí tienen NIT. library(writexl) write_xlsx(no_nit, "Municipios_nit.xlsx") ``` De esta manera, puede observarse que los contratos que no tienen un departamento o una ciudad tienen un NIT, y estos NIT hacen referencia a entidades que no necesariamente son municipales o departamentales. Esto tiene sentido porque hay contratos que realizan entidades del gobierno central, como la entidad "Servicio Nacional de Aprendizaje" entre otras. Sin embargo, haciendo una revision de los NIT´s , existen cerca de 86 municipios que sí tienen NIT de entidad municipal. Estos se pueden dividir en: - GOBIERNO MUNICIPAL PRESUPUESTARIO. - GOBIERNO MUNICIPAL EXTRAPRESUPUESTARIO Por lo que serán entonces considerados adjuntando para ello el nombre del municipio alque pertencen como razón social. Esto permite ganar información relevante. En efecto, municipios como La Union en Antrioquia presentan contratos por 1114 contratos. Quedan entonces las siguientes acciones: 1. Homogenizar a Bogota y San Andres en ambas bases. 2. Ingresar los NIT´s de las entidades que son del orden muncipal. 3. Categorizar los contratos no definidos en Departamento y Ciudad como contratos del gobierno central o de entidades centralizadas a las que no aplicaría el criterio de ruralidad en principio. De esta manera, se propone la homogenización: ```{r} #| echo: true data$nombre_depa <- ifelse( tolower(data$nombre_depa) == "distrito capital de bogota", "bogota, d.c.", data$nombre_depa ) data$nombre_depa <- ifelse( tolower(data$nombre_depa) == "archipielago de san andres, providencia y santa catalina", "san andres, providencia y santa catalina", data$nombre_depa ) ``` ## Proceso de Aplicación del Merge. En general se propone el siguiente código en R para establecer el merge entre la base de datos del SECOP-II y la base de datos de ruralidad , teniendo en cuenta que el merge debe repetir varibles que se encuentran en ambas bases. para que el proceso se haga de manera adecuada, se sugiere hacerlo en bloque, considerando coincidencias entre el nombre de los departamentos como mecanísmo control. ```{r} #| include: false data_nit <- read_excel("C:/Users/000278969/OneDrive - UPB/Documentos/Consultorias/2026_Proyecto_ART/Datos/Nueva_Estrategia/Merge_ruralidad/Municipios_con_nit.xlsx") # Realizando un merge con la base de datos data con respecto al NIT merge_1_nit <- merge(data, data_nit, by.x = "nit_entidad", by.y = "Var1", all.x = TRUE) merge_1_nit$razon_social <- limpiar_texto(merge_1_nit$razon_social) ``` Es importante indicar que algunos entidades consideradas con carácter municipal en realidad no lo son, siendo un potencial error de la base o para verificarlo seria necesario revisar a fondo los contratos en sus anexos técnicos. ```{r} #| include: false merge_1_nit$nombre_muni_f <- ifelse( tolower(merge_1_nit$nombre_muni) == "no definido" & !is.na(merge_1_nit$razon_social), merge_1_nit$razon_social, merge_1_nit$nombre_muni ) merge_1_nit$nombre_depa <- ifelse( tolower(merge_1_nit$nombre_depa) == "san andres, providencia y santa catalina", "archipielago de san andres, providencia y santa catalina", merge_1_nit$nombre_depa ) departamentos_ruralidad <- as.data.frame(table(rural$depa)) departamentos_secop <- as.data.frame(table(merge_1_nit$nombre_depa)) ``` Ahora bien, con esta homogenización, podemos proceder a identificar los municipios que asi aparezcan como no definidos en nombre de ciudad y departamento, se pueden capturar sus nombres a partir del NIT correspondiente. Se tienen entonces dos variables en la base secop, que corresponden a los municipios -ciudad inicial y luego a nuestro tratamiento de merge con el NIT. Una forma de verificar que el proceso es correcto, consiste en determinar si el número de no identificados en la nueva variable es congruente con lo inicialmente identificado y, además, con la corrección propuesta. ```{r} sum(is.na(merge_1_nit$nombre_muni_f) | grepl("no definido|definido", merge_1_nit$nombre_muni_f, ignore.case = TRUE)) ``` Por lo tanto, hay una ganancia potencial de 153630 filas que corresponderían a municipios sobre los cuales hay contratos que no contaron con asignación de ciudad pero si de NIT, y que efectivamente son de municipios. Ahora se procede a hacer el merge entre ruralidad y SECOP-II teniendo en cuenta tanto los valores de departamentos como los valores de ciudad, donde esto ayudará a mantener el control deseado. ```{r} merge_f <-merge(merge_1_nit, rural, by.x="nombre_muni_f", by.y="municipio",all.x = TRUE) sum(is.na(merge_f$nombre_muni_f) | grepl("no definido|definido", merge_f$nombre_muni_f, ignore.case = TRUE)) ``` Dando el resultado anterior, se confirma que el proceso es correcto en el sentido en que se incorporaron a municipios por la referencia de su NIT, asi no tengan datos asociados de nombre de ciudad y departamento. Podemos concentrarnos, por ejemplo, en el mucipio de la Union, el cual por NIT debe tener un número de 1000 registros, como efectivament muestra el resultado de la siguiente línea de código. ```{r} union <- subset(merge_f, nit_entidad == "890981995") dim(union) ``` Con esta confirmación, entonces, se procede a hacer un descriptivo general de la base de datos finalmente conformada, la cual tiene los indicadores de ruralidad, codigo divipola y otros elementos importantes.