1.- Carga de librerias

Preparacion del entorno de trabajo cargando los paquetes necesarios para el manejo de datos, formato de tablas y visualizacion de graficas.

library(readxl)
library(dplyr)
library(gt)
library(ggplot2)
library(scales)
library(e1071)

2.- Leer datos

# Seleccion del archivo Excel (se abre ventana emergente)
ruta_archivo <- file.choose()

# guess_max = Inf evita que readxl "adivine" mal el tipo de columna cuando
# los primeros valores validos aparecen muy abajo (aqui, Quantity (converted)
# tiene sus primeras ~8,300 filas vacias, y con el guess_max=1000 por defecto
# readxl detecta la columna como logica/vacia y descarta los numeros reales).
Datos <- read_excel(ruta_archivo, guess_max = Inf)

cat("Dataset cargado exitosamente. Total de variables importadas:", ncol(Datos), "\n")
## Dataset cargado exitosamente. Total de variables importadas: 32

3.- Seleccion de la variable

Se extrae y limpia la variable Quantity (converted) (Cantidad de reservas/produccion, ya estandarizada a unidades homogeneas: millones de barriles, millones de m³ o millones de boe segun el tipo de combustible). Se aseguran los formatos numericos (reemplazando comas por puntos) y se omiten los valores nulos para garantizar la exactitud de los calculos.

valores_limpios <- gsub(",", ".", Datos$`Quantity (converted)`)
Variable <- na.omit(as.numeric(valores_limpios))
N <- length(Variable)

cat("Variable analizada: Quantity (converted)\n")
## Variable analizada: Quantity (converted)
cat("Total de observaciones utiles (n):", N, "\n")
## Total de observaciones utiles (n): 40877
cat("Minimo:", round(min(Variable), 4), "\n")
## Minimo: -0.0212
cat("Maximo:", round(max(Variable), 4), "\n")
## Maximo: 49837180

4.- Conteo (frecuencias)

Se realiza el calculo de intervalos y el conteo de frecuencias usando la regla de Sturges. Se genera una distribucion con limites ajustados a multiplos de 10 (enteros).

BASE <- 10
min_int <- floor(min(Variable) / BASE) * BASE
max_int <- ceiling(max(Variable) / BASE) * BASE
k_int_sug <- floor(1 + 3.322 * log10(N))

Rango_int <- max_int - min_int
Amplitud_int <- ceiling((Rango_int / k_int_sug) / 10) * 10
if (Amplitud_int == 0) Amplitud_int <- 10

cortes_int <- seq(from = min_int, by = Amplitud_int, length.out = k_int_sug + 1)
if (max(cortes_int) < max(Variable)) cortes_int <- c(cortes_int, max(cortes_int) + Amplitud_int)
while (length(cortes_int) > 2 && cortes_int[length(cortes_int) - 1] >= max(Variable)) {
  cortes_int <- cortes_int[-length(cortes_int)]
}
K_real <- length(cortes_int) - 1

inter_int <- cut(Variable, breaks = cortes_int, include.lowest = TRUE, right = FALSE)
ni_int <- as.vector(table(inter_int))

# Calculos unitarios y porcentuales
hi_int_unit <- ni_int / N
hi_int_perc <- hi_int_unit * 100

TDF_Enteros <- data.frame(
  Li = cortes_int[1:K_real],
  Ls = cortes_int[2:(K_real + 1)],
  MC = (cortes_int[1:K_real] + cortes_int[2:(K_real + 1)]) / 2,
  ni = ni_int,
  hi_unit = hi_int_unit,
  hi_perc = hi_int_perc,
  Ni_asc = cumsum(ni_int),
  Ni_desc = rev(cumsum(rev(ni_int))),
  Hi_asc_unit = cumsum(hi_int_unit),
  Hi_desc_unit = rev(cumsum(rev(hi_int_unit))),
  Hi_asc_perc = cumsum(hi_int_perc),
  Hi_desc_perc = rev(cumsum(rev(hi_int_perc)))
)

cat("Clases enteras ajustadas (k):", K_real, "\n")
## Clases enteras ajustadas (k): 16

5.- Tabla de distribucion de frecuencias

Se estructura la matriz de resultados. Esta tabla incluye las frecuencias relativas en formato unitario (\(h_i\)) y porcentual (%).

fuente_nota <- paste("n =", format(N, big.mark = ","), "| Fuente: Global Energy Monitor - GOGET 2023")

TDF_Int_gt <- TDF_Enteros
fila_total_int <- data.frame(
  Li = NA, Ls = NA, MC = NA,
  ni = sum(TDF_Int_gt$ni),
  hi_unit = sum(TDF_Int_gt$hi_unit),
  hi_perc = sum(TDF_Int_gt$hi_perc),
  Ni_asc = NA, Ni_desc = NA,
  Hi_asc_unit = NA, Hi_desc_unit = NA,
  Hi_asc_perc = NA, Hi_desc_perc = NA
)

bind_rows(TDF_Int_gt, fila_total_int) %>%
  gt() %>%
  tab_header(
    title = md("**Tabla N°1**"),
    subtitle = "Distribucion de frecuencias de la cantidad (Quantity converted) (Limites enteros)"
  ) %>%
  cols_label(
    Li = "Lim. Inf", Ls = "Lim. Sup",
    MC = "Marca clase", ni = md("n~i~"),
    hi_unit = md("h~i~ (Unit)"), hi_perc = md("h~i~ (%)"),
    Ni_asc = md("N~i~ (\u2191)"), Ni_desc = md("N~i~ (\u2193)"),
    Hi_asc_unit = md("H~i~ (Unit \u2191)"), Hi_desc_unit = md("H~i~ (Unit \u2193)"),
    Hi_asc_perc = md("H~i~ (% \u2191)"), Hi_desc_perc = md("H~i~ (% \u2193)")
  ) %>%
  cols_align(align = "center", columns = everything()) %>%
  fmt_number(columns = c(Li, Ls, MC, hi_perc, Hi_asc_perc, Hi_desc_perc), decimals = 2, use_seps = TRUE) %>%
  fmt_number(columns = c(hi_unit, Hi_asc_unit, Hi_desc_unit), decimals = 4) %>%
  fmt_number(columns = c(ni, Ni_asc, Ni_desc), decimals = 0, use_seps = TRUE) %>%
  tab_source_note(fuente_nota) %>%
  tab_style(
    style = cell_fill(color = "#F5F5F5"),
    locations = cells_body(rows = nrow(TDF_Int_gt) + 1)
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_body(rows = nrow(TDF_Int_gt) + 1)
  ) %>%
  sub_missing(columns = everything(), missing_text = "") %>%
  tab_options(heading.background.color = "#EBF5FB", column_labels.font.weight = "bold")
Tabla N°1
Distribucion de frecuencias de la cantidad (Quantity converted) (Limites enteros)
Lim. Inf Lim. Sup Marca clase ni hi (Unit) hi (%) Ni (↑) Ni (↓) Hi (Unit ↑) Hi (Unit ↓) Hi (% ↑) Hi (% ↓)
−10.00 3,114,820.00 1,557,405.00 40,829 0.9988 99.88 40,829 40,877 0.9988 1.0000 99.88 100.00
3,114,820.00 6,229,650.00 4,672,235.00 29 0.0007 0.07 40,858 48 0.9995 0.0012 99.95 0.12
6,229,650.00 9,344,480.00 7,787,065.00 8 0.0002 0.02 40,866 19 0.9997 0.0005 99.97 0.05
9,344,480.00 12,459,310.00 10,901,895.00 2 0.0000 0.00 40,868 11 0.9998 0.0003 99.98 0.03
12,459,310.00 15,574,140.00 14,016,725.00 3 0.0001 0.01 40,871 9 0.9999 0.0002 99.99 0.02
15,574,140.00 18,688,970.00 17,131,555.00 2 0.0000 0.00 40,873 6 0.9999 0.0001 99.99 0.01
18,688,970.00 21,803,800.00 20,246,385.00 0 0.0000 0.00 40,873 4 0.9999 0.0001 99.99 0.01
21,803,800.00 24,918,630.00 23,361,215.00 1 0.0000 0.00 40,874 4 0.9999 0.0001 99.99 0.01
24,918,630.00 28,033,460.00 26,476,045.00 2 0.0000 0.00 40,876 3 1.0000 0.0001 100.00 0.01
28,033,460.00 31,148,290.00 29,590,875.00 0 0.0000 0.00 40,876 1 1.0000 0.0000 100.00 0.00
31,148,290.00 34,263,120.00 32,705,705.00 0 0.0000 0.00 40,876 1 1.0000 0.0000 100.00 0.00
34,263,120.00 37,377,950.00 35,820,535.00 0 0.0000 0.00 40,876 1 1.0000 0.0000 100.00 0.00
37,377,950.00 40,492,780.00 38,935,365.00 0 0.0000 0.00 40,876 1 1.0000 0.0000 100.00 0.00
40,492,780.00 43,607,610.00 42,050,195.00 0 0.0000 0.00 40,876 1 1.0000 0.0000 100.00 0.00
43,607,610.00 46,722,440.00 45,165,025.00 0 0.0000 0.00 40,876 1 1.0000 0.0000 100.00 0.00
46,722,440.00 49,837,270.00 48,279,855.00 1 0.0000 0.00 40,877 1 1.0000 0.0000 100.00 0.00

40,877 1.0000 100.00





n = 40,877 | Fuente: Global Energy Monitor - GOGET 2023

6.- Graficos de distribucion de frecuencias

color_barras <- "#2E86C1"
color_ojiva1 <- "#2E86C1"
color_ojiva2 <- "#C03928"

tema_base <- theme_minimal(base_size = 12) +
  theme(
    legend.position = "none",
    plot.title = element_text(face = "bold", size = 13),
    plot.caption = element_text(color = "#888888", size = 9, hjust = 0),
    axis.title = element_text(face = "bold", size = 11),
    axis.text.x = element_text(angle = 25, hjust = 1, size = 10),
    panel.grid.major.x = element_blank(),
    panel.grid.major.y = element_line(color = "#EEEEEE"),
    panel.grid.minor = element_blank(),
    plot.background = element_rect(fill = "white", color = NA)
  )

df_graf <- TDF_Enteros %>%
  mutate(intervalo = factor(paste(format(Li, big.mark = ","), "-", format(Ls, big.mark = ",")),
                             levels = paste(format(Li, big.mark = ","), "-", format(Ls, big.mark = ","))))

6.1 Histograma - Frecuencia Absoluta

ggplot(df_graf, aes(x = intervalo, y = ni)) +
  geom_col(fill = color_barras, color = "white", width = 0.95) +
  geom_text(aes(label = format(ni, big.mark = ",")), vjust = -0.4, size = 3.5, fontface = "bold") +
  scale_y_continuous(labels = label_comma(), expand = expansion(mult = c(0, 0.12))) +
  labs(title = "Histograma de Frecuencia Absoluta",
       x = "Quantity (converted)", y = "Frecuencia Absoluta (ni)",
       caption = fuente_nota) +
  tema_base

6.2 Histograma - Frecuencia Relativa Porcentual

ggplot(df_graf, aes(x = intervalo, y = hi_perc)) +
  geom_col(fill = color_barras, color = "white", width = 0.95) +
  geom_text(aes(label = paste0(round(hi_perc, 2), "%")), vjust = -0.4, size = 3.5, fontface = "bold") +
  scale_y_continuous(labels = function(x) paste0(x, "%"), expand = expansion(mult = c(0, 0.12))) +
  labs(title = "Histograma de Frecuencia Relativa Porcentual",
       x = "Quantity (converted)", y = "Frecuencia Relativa (%)",
       caption = fuente_nota) +
  tema_base

6.3 Diagrama de Cajas (Boxplot)

ggplot(data.frame(x = Variable), aes(x = x)) +
  geom_boxplot(fill = color_barras, color = "#1A5276", outlier.color = "#C03928", outlier.size = 1.5) +
  labs(title = "Diagrama de Cajas (Boxplot)",
       x = "Quantity (converted)", y = "",
       caption = fuente_nota) +
  theme_minimal(base_size = 12) +
  theme(
    axis.text.y = element_blank(),
    axis.ticks.y = element_blank(),
    plot.title = element_text(face = "bold", size = 13),
    plot.caption = element_text(color = "#888888", size = 9, hjust = 0),
    plot.background = element_rect(fill = "white", color = NA)
  )

6.4 Ojivas Ascendente y Descendente

ojiva_df <- data.frame(
  x = c(TDF_Enteros$Ls, TDF_Enteros$Li),
  y = c(TDF_Enteros$Hi_asc_perc, TDF_Enteros$Hi_desc_perc),
  tipo = rep(c("Ascendente", "Descendente"), each = K_real)
)

ggplot(ojiva_df, aes(x = x, y = y, color = tipo, group = tipo)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 2.5) +
  scale_color_manual(values = c("Ascendente" = color_ojiva1, "Descendente" = color_ojiva2)) +
  scale_x_continuous(labels = label_comma()) +
  scale_y_continuous(labels = function(x) paste0(x, "%"), limits = c(0, 105), expand = expansion(mult = c(0, 0))) +
  labs(title = "Ojivas Ascendente y Descendente",
       x = "Quantity (converted)", y = "Frecuencia Acumulada (%)",
       color = NULL, caption = fuente_nota) +
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", size = 13),
    plot.caption = element_text(color = "#888888", size = 9, hjust = 0),
    legend.position = "bottom",
    plot.background = element_rect(fill = "white", color = NA)
  )

7.- Indicadores estadisticos

La variable Quantity (converted) es cuantitativa continua de escala de razon, por lo que se calculan todos los indicadores: tendencia central, dispersion, forma y valores atipicos.

media    <- mean(Variable)
mediana  <- median(Variable)
moda_val <- TDF_Enteros$MC[which.max(TDF_Enteros$ni)]
varianza <- var(Variable)
sd_val   <- sd(Variable)
CV       <- (sd_val / abs(media)) * 100

asim <- skewness(Variable, type = 2)
kurt <- kurtosis(Variable)

Q1      <- quantile(Variable, 0.25)
Q3      <- quantile(Variable, 0.75)
IQR_val <- Q3 - Q1
outliers_data <- Variable[Variable < (Q1 - 1.5 * IQR_val) | Variable > (Q3 + 1.5 * IQR_val)]
num_out <- length(outliers_data)

out_txt <- if (num_out > 0) {
  paste0(num_out, " [", round(min(outliers_data), 2), "; ", round(max(outliers_data), 2), "]")
} else {
  "0 [Sin outliers]"
}

data.frame(
  Variable = "Quantity (converted)",
  Rango    = paste0("[", round(min(Variable), 2), "; ", format(round(max(Variable), 2), big.mark = ","), "]"),
  Media    = round(media, 2),
  Mediana  = round(mediana, 2),
  Moda     = round(moda_val, 2),
  Varianza = round(varianza, 2),
  Desv_Est = round(sd_val, 2),
  CV       = round(CV, 2),
  Asimetria = round(asim, 4),
  Curtosis  = round(kurt, 4),
  Outliers  = out_txt,
  check.names = FALSE
) %>%
  gt() %>%
  tab_header(title = md("**Tabla N°2: Indicadores Estadisticos de Quantity (converted)**")) %>%
  cols_label(
    Variable  = "Variable",
    Rango     = "Rango",
    Media     = md("Media (X\u0304)"),
    Mediana   = "Mediana (Me)",
    Moda      = "Moda (Mo)",
    Varianza  = md("Varianza (S\u00B2)"),
    Desv_Est  = "Desv. Est. (S)",
    CV        = "C.V. (%)",
    Asimetria = "Asimetria (As)",
    Curtosis  = "Curtosis (K)",
    Outliers  = "Outliers [Intervalo]"
  ) %>%
  tab_source_note("Autor: Grupo 5") %>%
  tab_options(
    table.width                       = pct(100),
    table.font.size                   = px(12),
    table.font.names                  = "Arial",
    heading.align                     = "center",
    heading.title.font.size           = px(13),
    heading.background.color          = "#AAAAAA",
    heading.border.bottom.color       = "#AAAAAA",
    column_labels.border.bottom.color = "#CCCCCC",
    column_labels.border.bottom.width = px(1),
    table_body.border.bottom.color    = "#CCCCCC",
    table_body.border.bottom.width    = px(1),
    table.border.top.color            = "#AAAAAA",
    table.border.top.width            = px(1),
    table.border.bottom.color         = "#AAAAAA",
    table.border.bottom.width         = px(1),
    source_notes.font.size            = px(11),
    source_notes.border.lr.color      = "transparent",
    data_row.padding                  = px(5)
  ) %>%
  tab_style(
    style     = cell_text(color = "white", weight = "bold"),
    locations = cells_title(groups = "title")
  ) %>%
  tab_style(
    style     = cell_text(color = "#333333", align = "center"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style     = cell_text(color = "#333333", align = "center"),
    locations = cells_body(columns = everything())
  )
Tabla N°2: Indicadores Estadisticos de Quantity (converted)
Variable Rango Media (X̄) Mediana (Me) Moda (Mo) Varianza (S²) Desv. Est. (S) C.V. (%) Asimetria (As) Curtosis (K) Outliers [Intervalo]
Quantity (converted) [-0.02; 49,837,180] 19547.41 14.97 1557405 176497509865 420116.1 2149.22 66.6226 6157.63 7771 [675; 49837179.67]
Autor: Grupo 5

8.- Conclusiones

Los valores de la cantidad (Quantity converted) de los yacimientos fluctuan entre -0.02 y 49,837,180 (millones de unidades convertidas) y giran en torno a 14.97 (mediana), con una desviacion estandar de 420,116.1, presentando 7771 valores atipicos (entre 675 y 49,837,180), siendo un conjunto de datos heterogeneo (C.V. = 2,149.22%), cuyos valores se agrupan fuertemente en la parte baja de la cantidad. Por lo anterior, el comportamiento es perjudicial, ya que la altisima dispersion y asimetria (pocos yacimientos con volumenes extremadamente grandes frente a la inmensa mayoria con volumenes pequenos) refleja una alta concentracion del recurso en muy pocas unidades, lo que incrementa el riesgo operativo y de dependencia sobre esos pocos activos de gran escala.