Archivo de la categoría: Trucos

Mapa España por Comunidades Autónomas con Google Studio

Continúo evaluando métodos para crear mapas con software que no sea de pago e intentando que la dificultad sea mínima. En este caso quería mostraros y poner a vuestra disposición un mapa de España por Comunidades Autónomas, además os dejo acceso libre a los datos que usa el mapa para que vosotros mismos podáis realizar el trabajo. Voy a pasaros 2 enlaces uno con los datos donde tenemos datos de pruebas PCR por 100.000 habitantes a nivel de Comunidad Autónoma (por representar algo) es ahí donde incluís los datos que deseáis representar gráficamente. Y el otro enlace es el dashboard simple hecho con Data Studio que véis al inicio de la entrada que contiene un mapa con el formato que en este momento necesito. Este trabajo es meramente experimental porque pongo a disposición de todos tanto mapa como Hoja de Google, veremos lo que tarda en dejar de funcionar.

El mapa se ha llevado a cabo según las instrucciones de este video:

Si necesitáis realizar el mismo proceso para México ahí lo tenéis muy bien explicado, en mi caso voy a pasar los link para generar el mapa y vosotros mismos cambiando los datos de la google sheet deberíais poder materializar el gráfico. Para no dejar abiertos al público vuestros mapas os pediría copiar o duplicar tanto la Google Sheet Seguir leyendo Mapa España por Comunidades Autónomas con Google Studio

Leer una tabla en PDF con Excel (a través de R)

Hay situaciones en las que tenemos datos en pdf y los necesitamos exportar a Excel para graficar o cruzar esos datos. En ocasiones es mejor meter esos datos a mano, otras veces disponemos de un software de pago que nos permite realizar esa tarea y también hay páginas web que nos permiten cambiar el formato del pdf. En nuestro caso simplemente necesitamos una tabla que está en formato pdf para disponer de esos datos en Excel, más sencillo, copiar del pdf y pegar en Excel esa tabla. Si está en texto el pdf se puede complicar y si está en modo imagen más. Si empleas windows en tu esta entrada puede ser de utilidad ya que usando de R podrás hacer está tarea de copiar pdf y pegar Excel de un modo más rápido, te cuento paso por paso en video.

El primer paso será la instalación de R y RStudio:

Una vez hemos disponemos de R y Rstudio en nuestro equipo descarga este programa en R que te permitirá exportar una tabla a R con las instrucciones que resumo a continuación:

Esta entrada del blog quiero que sea dinámica, me gustaría mejorar el proceso porque tiene posibilidades de dar a conocer R a personas sin conocimientos de programación. Podemos crear desde una aplicación hasta leer url. Saludos.

Trucos Excel. Convertir texto en un resultado o fórmula

Es posible que en Excel tengamos fórmulas que provengan de la concatenación de algunas celdas y necesitemos ejecutar o crear una fórmula. En este caso he encontrado una función muy sencilla que podemos añadir a nuestro libro de macros personal o directamente a nuestro libro. La función es genial y sencilla y proviene de este foro:

https://www.mrexcel.com/board/threads/eval-function-without-the-morefunc-add-in.62067/

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

Tiene muchos años pero podéis comprobar que funciona perfectamente. Esta solución me parece más elegante que otras, aunque es probable que MS haya optado por incluir una función que haga esta labor, lo desconozco. Saludos.

Expandir un data frame de R con tidyr

En alguna entrada del blog ya he tratado sobre la expansión de un conjunto de datos pero quería tener una entrada específica. Es algo que se puede programar mediante bucles (tarda una vida) o bien podemos usar la función expand del paquete tydyr. Viendo un ejemplo y los conjuntos de datos generados vais a entender el propósito de la expansión de tablas, se trata de un inicio y un fin y deseamos que se genere una secuencia de observaciones sucesivas dado ese inicio y ese fin. A modo de ejemplo ilustrativo:

library(tidyverse)

clientes <- 100 

cliente <- data.frame(id_cliente = seq(1, clientes))
cliente %>% mutate( inicio = rpois(nrow(cliente), 2),
                    fin = inicio + rpois(nrow(cliente), 4)) ->
  cliente

cliente_expand <- cliente %>% group_by(id_cliente) %>% expand(entrada=inicio:fin) %>% as_tibble()

Con este programa pasamos de un data frame con un registro por id a otro data frame con tantos registros por id como longitud tenga la secuencia entre el campo de inicio y el campo fin:

Esta función expand de rstats me está siendo especialmente útil para trabajar con horas. Saludos.

Identificar los municipios costeros y limítrofes de España con R.

Otro ejercicio con spatial data R Rstats y data sciense para el trabajo con objetos espaciales en el ecosistema big data. Empiezo con frase ilógica y ridícula para mejorar las búsquedas de Google pero el tema que traigo hoy creo que puede ser útil para aquellos que, dado un spatial data, tienen que identificar los polígonos que bordean ese objeto, en este caso vamos a identificar los municipios que bordean España, pueden ser limítrofes con Francia y Portugal o bien municipios costeros. No se plantean algoritmos complicados, como en entradas anteriores nos centramos en la extracción de mapas de GADM:

Obtención de los mapas necesarios

library(maptools)
library(raster)
library(maps)
library(tidyverse)
library(sqldf)

Espania < - getData('GADM', country='Spain', level=0)
Espania$name = Espania$NAME_1
Espania2 <- getData('GADM', country='Spain', level=4)
Espania2$name = Espania$NAME_1

Por un lado obtenemos el mapa de España sin división territorial que en GADM es el nivel 0 y por otro lado el municipal que es nivel 4. Un tipo brillante sería capaz de encontrar un algoritmo que identificara que polígonos no tienen adyacencia, pero un tipo mediocre pensaría "si cruzo el borde con los municipios, los objetos que crucen son el exterior"

Municipios del contorno

contorno < - map_data(Espania) %>% mutate(lat2=round(lat,1), long2=round(long,1)) %>% select(long2,lat2) 

municipios < - map_data(Espania2) %>% mutate(lat2=round(lat,1), long2=round(long,1))  %>% select(long2,lat2,region) 

contorno < - inner_join(municipios, contorno) 

En este punto hay aspectos claramente mejorables, el cruce se realiza por latitud y longitud, Seguir leyendo Identificar los municipios costeros y limítrofes de España con R.

Datos agrupados en R con dplyr

Entrada rápida para ilustrar como crear un campo autonumérico por un factor, es una duda que me plantean, tienen datos de clientes y fechas y necesitan crear un autonumérico en R que les diga el número de orden de los eventos de una fecha. Algo parecido a lo que hacemos con el retain de R. Vamos a ilustrar la tarea con un ejemplo:

clientes = 100
id_cliente = rpois(clientes,10)
fecha = rpois(clientes, today()-rpois(clientes,5) )

eventos <- cbind.data.frame(id_cliente,fecha)

eventos$fecha <- as.Date(eventos$fecha, origin="1970-01-01")
eventos <- eventos %>% arrange(id_cliente,fecha) 

100 clientes que aparecen una o n veces con fechas asociadas, el primer paso que sugiero hacer es eliminar duplicados con dplyr:

eventos <- eventos %>% group_by(id_cliente, fecha) %>% 
  filter(row_number()==1) %>% as_tibble()

Agrupamos por cliente y fecha y nos quedamos con el primer registro, en este caso da igual quedarse con el primero que con el último. Ahora que no tenemos duplicados la agrupación ya no es por cliente y por fecha, como vamos a crear un valor agrupado por cliente haremos el group_by solo por cliente:

eventos <- eventos %>% group_by(id_cliente) %>% 
  mutate(evento=row_number()) %>% as_tibble()

Cada evento irá numerado de 1 a n gracias a row_number(), el mismo se reinicia a 1 cada vez que cambia el valor del group_by.

Truco Excel. Calculadora desde la barra de herramientas de acceso rápido

Hace tiempo que no ponía trucos de Excel y el de hoy es muy sencillo pero que a mi me ha sido de especial utilidad, un acceso a la calculadora para esos cálculos rápidos que siempre he hecho con la calculadora de sobre mesa. Se trata de añadir un acceso a la Calculadora de Windows desde la barra de herramientas de acceso rápido. Vamos a personalizar la barra:

Y desde ese menú nos vamos a Más comandos. Se abre un cuadro de diálogo que nos sugiere elementos a añadir pero la calculadora saldrá si queremos ver todos los elementos disponibles:

Seleccionamos la calculadora y ya tenemos nuestro acceso rápido. Evidentemente podéis añadir muchos más accesos a esa barra, en este caso problemas con mi vieja calculadora han hecho que sea necesario el uso de la calculadora de Windows.

Ejecutar un código al iniciar la sesión de R

A raíz de una conversación en Twitter os traigo un pequeño truco de R para aquellos que tenéis funciones predefinidas y que tenéis que cargarlas al iniciar las sesiones de R, es como ejecutar el código nada más abrir R. En mi caso el código que quiero ejecutar son una serie de utilidades que tengo guardadas en C:\carpeta, con source(“C:/carpeta/Utils.R”, encoding=”UTF-8″) R cargaría todo el código R alojado en ese script de R y necesito que se ejecute el script al inicial la sesión de R, no quiero poner esa línea al principio de cada programa. Lo primero que tenemos que hacer es buscar donde tenemos instalado R, una vez hallamos accedido a la correspondiente carpeta vamos a la subcarpeta /etc y tenemos un archivo llamado Rprofile.site lo abrimos con un editor de texto:

# Things you might want to change

# options(papersize="a4")
# options(editor="notepad")
# options(pager="internal")

# set the default help type
# options(help_type="text")
  options(help_type="html")

# set a site library
# .Library.site <- file.path(chartr("\\", "/", R.home()), "site-library")

# set a CRAN mirror
# local({r <- getOption("repos")
#       r["CRAN"] <- "http://my.local.cran"
#       options(repos=r)})

# Give a fortune cookie, but only to interactive sessions
# (This would need the fortunes package to be installed.)
#  if (interactive()) 
#    fortunes::fortune()

source("C:/carpeta/Utils.R", encoding="UTF-8")

Pues en ese archivo ponemos source("C:/carpeta/Utils.R", encoding="UTF-8") y cada vez que abramos nuestro R, desde RStudio por ejemplo, se ejecutará el script con nuestras utilidades.

Medias ponderadas en Excel. Crear tu propia función

Hace años conocí a una persona que no sabía hacer medias ponderadas con Excel, hoy esa persona es una referencia dentro de este ecosistema de Inteligencia Artificial, Big Data, Machine Learning, Unsupervised Learning,… total, una referencia en la venta de humo porque me imagino que seguirá sin saber hacer una media ponderada en Excel con el SUMAPRODUCTO y por eso realizo esta entrada en homenaje a esas grandes locomotoras que echan humo y más humo pero que ahí siguen. Además también es útil para varias cosas más como:

  • Crear nuestra propia función en Excel
  • Emplear rangos en funciones de Excel
  • Crear sumas acumuladas con un bucle en nuestra función
  • Emplear funciones propias de Excel en nuestra función de visual basic

La función es sencilla y replica la forma habitual de hacer medias ponderadas en Excel con el SUMAPRODUCTO del dato del que deseamos calcular la media por el campo de ponderación dividido por la suma del campo de ponderación:

Public Function MEDIAPONDERADA(Valor As Range, Ponderacion As Range)

 If Valor.Rows.Count <> Ponderacion.Rows.Count Then
 Sample = "Tamaños distintos de rango"
 Exit Function
 
 ElseIf Valor.Rows.Count = 1 Then
 Sample = "Solo un número no se puede"
 Exit Function
 End If

 acum = 0
 For i = 1 To Valor.Rows.Count
   dato = Valor(i) * Ponderacion(i)
   acum = acum + dato
 Next
 
 MEDIAPONDERADA = acum / Excel.WorksheetFunction.Sum(Ponderacion)

End Function

Esta función la ponéis en el libro de macros personal y cuando escribáis =MEDIAPONDERADA ya la tendréis a vuestra disposición en todas las sesiones de Excel. Saludos.

Truco Excel. Producto cartesiano de dos campos

Hacía tiempo que no ponía trucos en Excel y hoy os traigo un truco que puede ser de utilidad cuando tienes que hacer combinaciones. Se trata de realizar el producto cartesiano mediante una macro de Excel, además os pongo el enlace al propio Excel para que podáis rellenar los campos a cruzar. No creo que haga falta describir que es un producto cartesiano de dos campospero de forma resumida se puede decir que es crear el total de pares de esos dos campos, un todos con todos, es útil cuando quieres hacer combinaciones (como ya he dicho). La macro en Visual Basic se podrá hacer mejor, pero a mi se me ha ocurrido hacer un triple bucle, probablemente se pueda hacer con n campos pero si tenéis que realizar productos cartesianos más complejos es preferible que lo hagáis con otra herramienta. El código empleado es este:

Sub ProductoCartesiano()
    
L1 = Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count
L2 = Range(Range("B2"), Range("B2").End(xlDown)).Rows.Count

Cells(1, 4) = Cells(1, 1)
Cells(1, 5) = Cells(1, 2)

i = 2
j = 1


While i <= L1 * L2
While j <= L1
k = 1
While k <= L2

Cells(i, 4) = Cells(j + 1, 1)
Cells(i, 5) = Cells(k + 1, 2)

k = k + 1
If k > L2 Then j = j + 1
i = i + 1

Wend
Wend
Wend
End Sub

Nada emocionante pero tiene su “talento”. Si no queréis complicaros la vida directamente podéis descargar del siguiente enlace la hoja del cálculo que realiza este proceso:

Producto_cartesiano_excelV0

Saludos.