Importar datos

options (scipen = 999) #para mostrar todos los decimales :)
library(dplyr)
library(readxl)
#Datos
load("~/Metodos/bases de datos_comercio/base_comercio_new/Nelson Jesús Carrillo Paniagua - data_parcial_1_2021.RData")

#Pegar nombres paises
nombre_archivo <- "~/Metodos/bases de datos_comercio/base_comercio_new/nombres_iso_paises.xlsx"
nombres_iso_paises <- read_excel(nombre_archivo)
data_comercio_exterior %>%
  left_join(nombres_iso_paises,
            by = c("pais" = "nom_pais_esp")) -> data_comercio_exterior

#Seleccionar Años
anios_ranking<-2017:2020
data_comercio_exterior %>% 
  filter(anio %in% anios_ranking) ->data_ranking

Cálculo de ranking & porcentajes

data_ranking %>% 
  group_by(anio,iso_3) %>% 
  summarise(total=sum(valor_fob)) %>% mutate(percent=round(prop.table(total)*100,2)) %>% 
  slice_max(n = 5,order_by = total) %>% 
  as.data.frame() %>% 
  group_by(anio) %>% 
  mutate(rank = row_number(),
         data=paste(iso_3,"|",percent,sep = "")) %>% 
  select(anio,data,rank) %>% as.data.frame() -> insumo_reporte
## `summarise()` has grouped output by 'anio'. You can override using the
## `.groups` argument.
print(insumo_reporte)
##    anio      data rank
## 1  2017 USA|44.52    1
## 2  2017 HND|13.83    2
## 3  2017 GTM|13.75    3
## 4  2017  NIC|7.48    4
## 5  2017  CRI|4.55    5
## 6  2018 USA|44.07    1
## 7  2018 HND|15.34    2
## 8  2018 GTM|14.36    3
## 9  2018  NIC|6.87    4
## 10 2018  CRI|4.39    5
## 11 2019 USA|41.88    1
## 12 2019 GTM|15.95    2
## 13 2019 HND|15.91    3
## 14 2019  NIC|6.68    4
## 15 2019   CRI|4.5    5
## 16 2020  USA|36.8    1
## 17 2020 GTM|16.92    2
## 18 2020 HND|15.34    3
## 19 2020  NIC|7.52    4
## 20 2020  CRI|5.08    5

Tabla en formato “RAW”

library(tidyr)
insumo_reporte %>% 
pivot_wider(names_from = rank,values_from = data)->mi_tabla
print(mi_tabla)
## # A tibble: 4 × 6
##    anio `1`       `2`       `3`       `4`      `5`     
##   <dbl> <chr>     <chr>     <chr>     <chr>    <chr>   
## 1  2017 USA|44.52 HND|13.83 GTM|13.75 NIC|7.48 CRI|4.55
## 2  2018 USA|44.07 HND|15.34 GTM|14.36 NIC|6.87 CRI|4.39
## 3  2019 USA|41.88 GTM|15.95 HND|15.91 NIC|6.68 CRI|4.5 
## 4  2020 USA|36.8  GTM|16.92 HND|15.34 NIC|7.52 CRI|5.08

Tabla en Formato Requerido

library(kableExtra)
mi_tabla %>%
  kable(caption = paste("Top",5,"de Exportaciones periodo",
    alin="c", min(anios_ranking),"-",max(anios_ranking))) %>%
  add_footnote(label = "Elaboración propia con base en datos del BCR",
               notation = "symbol") %>%  kable_styling()
Top 5 de Exportaciones periodo c 2017 - 2020
anio 1 2 3 4 5
2017 USA|44.52 HND|13.83 GTM|13.75 NIC|7.48 CRI|4.55
2018 USA|44.07 HND|15.34 GTM|14.36 NIC|6.87 CRI|4.39
2019 USA|41.88 GTM|15.95 HND|15.91 NIC|6.68 CRI|4.5
2020 USA|36.8 GTM|16.92 HND|15.34 NIC|7.52 CRI|5.08
* Elaboración propia con base en datos del BCR

Implementación de funciones personalizadas

# 1. Tabla en formato RAW
tabla_top_k_raw<-function(df_comercio,top_k=5,anios_ranking,decimales=2
){
library(dplyr)
library(tidyr)
df_comercio %>% 
  filter(anio %in% anios_ranking) %>% 
  group_by(anio,iso_3) %>% 
  summarise(total=sum(valor_fob)) %>%           
  mutate(percent=round(prop.table(total)*100,decimales)) %>% 
  slice_max(n = top_k,order_by = total) %>% 
  as.data.frame() %>% 
  group_by(anio) %>% 
  mutate(rank = row_number(),
         data=paste(iso_3,"|",percent,sep = "")) %>% 
  select(anio,data,rank) %>% as.data.frame() %>% rename(`Año`=anio) %>%
  pivot_wider(names_from = rank,values_from = data)->mi_tabla
  print(mi_tabla)
}
#2. Tabla en formato requerido
tabla_top_k_pretty<-function(df_comercio,
                             top_k=5,
                             anios_ranking,
                             decimales=2,
                             pie_pagina= "Elaboración propia con base en datos del BCR"){
library(dplyr)
library(tidyr)
library(kableExtra)
df_comercio %>% 
  filter(anio %in% anios_ranking) %>% 
  group_by(anio,iso_3) %>% 
  summarise(total=sum(valor_fob)) %>%           
  mutate(percent=round(prop.table(total)*100,decimales)) %>% 
  slice_max(n = top_k,order_by = total) %>% 
  as.data.frame() %>% 
  group_by(anio) %>% 
  mutate(rank = row_number(),
         data=paste(iso_3,"|",percent,sep = "")) %>% 
  select(anio,data,rank) %>% as.data.frame() %>% rename(`Año`=anio) %>% 
  pivot_wider(names_from = rank,values_from = data) %>% as.data.frame() %>%   kable(caption = paste("Top",top_k,"de Exportaciones periodo",
    min(anios_ranking),"-",max(anios_ranking))) %>%
  add_footnote(label = pie_pagina)
}

Ejemplos de uso de las Funciones:

tabla_top_k_raw(df_comercio = data_comercio_exterior,
                anios_ranking =2017:2020,
                top_k = 8,
                decimales = 2 )
## `summarise()` has grouped output by 'anio'. You can override using the
## `.groups` argument.
## # A tibble: 4 × 9
##     Año `1`       `2`       `3`       `4`      `5`      `6`      `7`      `8`   
##   <dbl> <chr>     <chr>     <chr>     <chr>    <chr>    <chr>    <chr>    <chr> 
## 1  2017 USA|44.52 HND|13.83 GTM|13.75 NIC|7.48 CRI|4.55 PAN|2.11 MEX|1.96 DOM|1…
## 2  2018 USA|44.07 HND|15.34 GTM|14.36 NIC|6.87 CRI|4.39 MEX|2.44 PAN|2.08 DOM|1…
## 3  2019 USA|41.88 GTM|15.95 HND|15.91 NIC|6.68 CRI|4.5  MEX|2.53 PAN|2.05 DOM|1…
## 4  2020 USA|36.8  GTM|16.92 HND|15.34 NIC|7.52 CRI|5.08 MEX|2.42 PAN|1.97 DOM|1…
tabla_top_k_pretty(df_comercio = data_comercio_exterior,
                   anios_ranking =c(2017,2019),
                   top_k = 3,
                   decimales = 5,
                   pie_pagina = "Mi texto Personalizado" )
## `summarise()` has grouped output by 'anio'. You can override using the
## `.groups` argument.
Top 3 de Exportaciones periodo 2017 - 2019
Año 1 2 3
2017 USA|44.521 HND|13.83431 GTM|13.75393
2019 USA|41.87525 GTM|15.95499 HND|15.9118
a Mi texto Personalizado