Traducir celdas en hojas de cálculo de Google

Problema

Deseamos traducir rápidamente un rango de celdas en hojas de cálculo de Google.

Solución

1. Utilizamos la función GOOGLETRANSLATE. Consta de 3 argumentos: texto a traducir, idioma de origen e idioma de destino. Solamente el primero es obligatorio.

En nuestro ejemplo:

=GOOGLETRANSLATE(A2,"en","es")

2. Arrastramos hacia abajo el cuadro azul en la esquina inferior derecha.

Resultado

Notas

1. Si queremos detectar automáticamente los idiomas podemos utilizar el argumento "auto" en lugar de los argumentos de idiomas. Esto es especialmene útil si nuestras celdas mezclan varios idiomas.

=GOOGLETRANSLATE(A2,"auto", "es")

o bien

=GOOGLETRANSLATE(A3,DETECTLANGUAGE(A3), "es")

2. Si estamos traduciéndo al inglés, no necesitamos indicar ni argumento de origen ni de destino, simplemente:

=GOOGLETRANSLATE(A3, "auto")

o tan solo:

=GOOGLETRANSLATE(A3)

Entradas relacionadas

En una próxima entrada trataremos en detalle las transformaciones de formato ancho a largo y viceversa. Un tema ya tratado anteriormente, directa o indirectamente, en las siguientes entradas:

• Listas numeradas en Google Docs
• Crear un mapa con Fusion Tables de Google
• Insertar imágenes en una hoja de cálculo de Google Drive
• Google Sets en las hojas de cálculo de Google Drive

Gráficos de 'Credit Card Default Data' en ggplot2

Problema

Queremos crear con ggplot2 los siguientes dos gráficos del libro 'An Introduction to Statistical Learning'. Nos servirá para practicar algunos conceptos de ggplot2.

Solución

1. Gráfico 1: diagrama de dispersión.
Al contrario que en el libro, representamos todos los puntos (10.000). Para evitar un gráfico sobrecargado de puntos —overplotting— añadimos transparencia a los mismos con el argumento alpha. Adicionalmente, cambiamos la forma de los puntos con el argumento shape (el 1 es un círculo y el 2 una cruz). Finalmente, en lugar de dejar un panel de fondo totalmente blanco, dejo líneas de cuadrícula grises (theme_bw) para identificar mejor la ubicación de los puntos.

library(ISLR)
library(ggplot2)

ggplot(data = Default, aes(x = balance, y = income)) +
  geom_point(aes(color = default, shape = default),
             alpha = .5,
             size = 2) +
  scale_shape_manual(values = c(1, 3)) +
  scale_colour_manual(values = c("royalblue", "orangered")) +
  scale_x_continuous(breaks = seq(0, 2500, 500)) +
  theme_bw()


2. Gráfico 2: diagramas de caja.
Como queremos representar en un único panel dos gráficos, uno para balance y otro para income, necesitamos transformar el data frame de formato ancho a largo con la función melt del paquete reshape2. Posteriormente, usamos face_wrap para generar los dos gráficos e indicamos las escalas de los ejes free para que se adapten automáticamente para cada gráfico.

library(reshape2)  
ggplot(data = melt(Default), aes(x = default, y = value)) +
  stat_boxplot(geom = 'errorbar', width = 0.5) +
  geom_boxplot(aes(fill = default)) +
  facet_wrap(~ variable, scales = "free") +
  scale_fill_manual(values = c("Dodgerblue", "orangered"))


• De formato ancho a largo.
Dentro de facet_wrap usamos variable pues es el nombre de la columna clave (key) que recoge el nombre de las dos columnas: balance e income.

melt(Default)[c(1:5, 19996:20000), ]

Using default, student as id variables
      default student variable      value
1          No      No  balance   729.5265
2          No     Yes  balance   817.1804
3          No      No  balance  1073.5492
4          No      No  balance   529.2506
5          No      No  balance   785.6559
19996      No      No   income 52992.3789
19997      No      No   income 19660.7218
19998      No      No   income 58636.1570
19999      No      No   income 36669.1124
20000      No     Yes   income 16862.9523

Entradas relacionadas

En una próxima entrada trataremos en detalle las transformaciones de formato ancho a largo y viceversa. Un tema ya tratado anteriormente, directa o indirectamente, en las siguientes entradas:

• Diagramas de caja de un data frame con ggplot2: una o varias columnas
• Cambiar el orden de un diagrama de barras apiladas con ggplot2
• Agregar datos en R con el paquete reshape2
• Múltiples valores y funciones de agregación con reshape2 y plyr
• Convertir de formato largo a ancho usando una función de agregación en R

Insertar fila de tolales en una tabla de Excel

Problema

En Excel, queremos resumir rápidamente una tabla añadiendo una fila total en la última fila, pudiendo elegir entre diferentes funciones para cada columna.

Solución

1. Partimos de un rango de datos

2. Hacemos clic en cualquier celda y presionamos Ctrl+T

La tabla se habrá formateado automáticamente y mostrará un autofiltro.
3. En Herramientas de tabla, Diseño seleccionamos la casilla de verificación de la Fila de totales.

4. Una fila Total se habrá insertado en la última fila de la tabla.

Para cada columna podemos elegir entre diferentes funciones para resumir los datos. Como se puede apreciar en la barra de fórmulas Excel utiliza la función subtotales, por defecto la suma: =SUBTOTALES(109,[Año 3])

Resultados

Entradas relacionadas

• Importar datos desde la web a Excel
• Resaltar filas alternas en Excel

Mapa de calor en forma de calendario en R

Problema

Queremos representar una serie temporal como un mapa de calor en forma de calendario.

Solución

Ejecutamos el código de Paul Bleicher para crear la función calendarHeat. Hacemos alguna edición menor para poner el título y los días de la semana en castellano.

# Creamos una serie temporal aleatoria para tres años.
library(dplyr)
set.seed(2015)
df <-
  data.frame(fechas = sample(seq(
    as.Date('2017-01-01'),
    as.Date('2019-12-31'),
    by = "day"
  ), 1000))
df <- df %>% mutate(sesiones = floor(runif(nrow(.), 1, 101)) )
# Creamos dos gráficos con dos paletas de colores diferentes.
calendarHeat(df$fechas, df$sesiones, varname = "Sesiones")
calendarHeat(df$fechas, df$sesiones, varname = "Sesiones", ncolors = 99, color = "r2b" )

Resultados

Entradas relacionadas

• Generar un mapa de calor por días y horas con ggplot2
• Entradas de geom_tile

Generar un mapa de calor por días y horas con ggplot2

Problema

Queremos crear un mapa de calor, con teselas coloreadas en función de la frecuencia en ggplot2.

Solución

library(dplyr)
library(lubridate)
library(ggplot2)

# Generamos fechas aleatorias
set.seed(2015)
df <-
  data.frame(fechas = sample(seq(
    as.POSIXct('2018/01/01', tz = "CET"),
    as.POSIXct('2018/12/31', tz = "CET"),
    by = "sec"
  ), 1000))

# Manipulamos el data frame 
# 1. Cambiamos el nombre de los días de la semana
# 2. Agrupamos por horas y días
df %>%
  mutate(Días = factor(
    weekdays(fechas),
    levels = rev(
      c(
        "Monday",
        "Tuesday",
        "Wednesday",
        "Thursday",
        "Friday",
        "Saturday",
        "Sunday"
      )
    ),
    labels = c(
      "Domingo",
      "Sábado",
      "Viernes",
      "Jueves",
      "Miércoles",
      "Martes",
      "Lunes"
    )
  )) %>%
  group_by(Horas = hour(fechas), Días) %>%
  summarise(Sesiones = n()) %>%
# Creamos el gráfico
  ggplot(aes(Horas, Días)) +
  geom_tile(aes(fill = Sesiones), colour = "white") +
  scale_fill_distiller(palette = "YlGnBu", direction = 1) +
  scale_x_continuous(breaks = 0:23) +
  theme_minimal() +
  theme(
    legend.position = "bottom",
    legend.key.width = unit(2, "cm"),
    panel.grid = element_blank()
  ) +
  coord_equal()

Resultado

Referencias

• stackoverflow
• Entradas de geom_tile

Generar fechas aleatorias entre dos momentos en el tiempo con R

Problema

Deseamos generar un vector con fechas aleatorias entre dos momentos en el tiempo.

Solución

• Opción 1: fechas
Con la función seq generamos una secuencia de fechas en incrementos de un día entre los dos momentos en el tiempo . Y posteriormente seleccioamos aleatoriamente 10.

set.seed(1984)
sample(seq(as.Date('2015-01-01'), as.Date('2017-01-01'), by = "day"), 10)

• Opción 2: fechas, horas, minutos y segundos.
Si necesitamos, además de las fechas, generar horas, minutos y segundos aleatorios, usamos as.POSIXct, y cambiamos el incremento especificando segundos (by = "sec") en la secuencia.

set.seed(1984)
sample(seq(as.POSIXct('2015-01-01'), as.POSIXct('2017-01-01'), by = "sec"), 10)

Resultados

Opción 1

[1] "2016-04-27" "2015-11-16" "2015-09-30" "2015-08-30" "2016-06-20"
[6] "2016-09-19" "2015-01-24" "2015-11-21" "2016-08-23" "2015-06-05"

Opción 2

[1] "2016-04-26 15:04:13 CEST" "2015-11-16 10:17:23 CET" 
 [3] "2015-09-30 22:50:41 CEST" "2015-08-30 23:17:49 CEST"
 [5] "2016-06-23 04:49:01 CEST" "2016-09-22 14:37:58 CEST"
 [7] "2015-01-24 17:04:13 CET"  "2015-11-24 07:13:42 CET" 
 [9] "2016-08-29 16:13:13 CEST" "2015-06-06 21:29:18 CEST"

Referencias

• stackoverflow
• Sequence Generation

Filtrar un data frame basado en intervalos de tiempo en R

Problema

Para cada una de las Species queremos quedarnos con la primera imagen (columna ID) de cada intervalo de una hora empezando por la fecha inicial: 2015-03-16 18:42:00. Es decir, para la Specie A, nos queremos quedar con P1, P3 y P4. P2 no la consideraríamos pues está dentro del intervalo de una hora a partir de P1, entre las 18:42 y las 19:41.

  ID Species            DateTime
1 P1       A 2015-03-16 18:42:00
2 P2       A 2015-03-16 19:34:00
3 P3       A 2015-03-16 19:58:00
4 P4       A 2015-03-16 21:02:00
5 P5       B 2015-03-16 21:18:00
6 P6       A 2015-03-16 21:19:00
7 P7       A 2015-03-16 21:33:00
8 P8       B 2015-03-16 21:35:00
9 P9       B 2015-03-16 23:43:00

• Datos

df <- read.table(
  text = 'ID   Species       DateTime
  P1   A            "2015-03-16 18:42:00"
  P2   A             "2015-03-16 19:34:00"
  P3   A             "2015-03-16 19:58:00"
  P4   A             "2015-03-16 21:02:00"
  P5   B             "2015-03-16 21:18:00"
  P6   A             "2015-03-16 21:19:00"
  P7   A             "2015-03-16 21:33:00"
  P8   B             "2015-03-16 21:35:00"
  P9   B             "2015-03-16 23:43:00"',
  stringsAsFactors = FALSE,
  header = TRUE
)

Solución

Creamos una nueva columna con los intervalos cada 60 minutos y nos quedamos con la primera ocurrencia para cada una de las Species. Es importante señalar que dentro de la función cut tenemos que especificar 60 minutos y no una hora ("1 hour"), o de lo contrario el intervalo no tendría en cuenta los minutos sino solamente las horas. Es decir, comenzaría el primer intervalo a las 18:00 y no a las 18:42.

library(dplyr)
df$DateTime <- as.POSIXct(df$DateTime)
df %>%
  mutate(by60 = cut(DateTime, "60 min")) %>%
  group_by(Species, by60) %>%
  slice(1) %>%
  ungroup() %>%
  select(-by60)

Resultados

# A tibble: 5 x 3
  ID    Species DateTime           
                   
1 P1    A       2015-03-16 18:42:00
2 P3    A       2015-03-16 19:58:00
3 P4    A       2015-03-16 21:02:00
4 P5    B       2015-03-16 21:18:00
5 P9    B       2015-03-16 23:43:00

Referencias

• stackoverflow

Cómo exportar en Excel un fichero csv con dobles comillas como calificador de texto

Problema

Queremos exportar un fichero de Excel como un fichero csv (delimitado por comas), y usar las dobles comillas como calificador de texto: "Texto1", "Texto2", "Texto3".

Solución

Excel no nos permite resolver el problema a través de los menús. Tenemos que recurrir a VBA con una subrutina encontrada en la página de soporte de Microsoft.

1. Abrimos el Editor de Microsoft Visual Basic: Alt+F11.
2. Creamos la siguiente subrutina en un módulo.

Sub QuoteCommaExport()
   ' Definición de variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Cuadro de diálogo para obtener la ruta de destino.
   DestFile = InputBox("Introduce la ruta completa del fichero" _
      & Chr(10) & "(Ej: C:\Usuarios\nubededatos\fichero.csv):", "Exportador con comillas")

   ' Obtiene el siguiente número de archivo disponible.
   FileNum = FreeFile()

   ' Desactiva la comprobación de errores.
   On Error Resume Next

   ' Intenta abrir la ruta de destino seleccionada.
   Open DestFile For Output As #FileNum

   ' Si se produce un error, muestra este mensaje.
   If Err <> 0 Then
      MsgBox "No se puede abrir el fichero " & DestFile
      End
   End If

   ' Activamos comprobación de errores.
   On Error GoTo 0

   ' Bucle en cada filade la selección.
   For RowCount = 1 To Selection.Rows.Count

      ' Bucle en cada columna de la selección.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Escribe el texto entrecomillado de la celda actual en el fichero.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Comprueba si es la última columna.
         If ColumnCount = Selection.Columns.Count Then
            ' Si es así, escribe una línea en blanco.
            Print #FileNum,
         Else
            ' De lo contrario, escribe una coma.
            Print #FileNum, ",";
         End If
      ' Comienza la siguiente iteración del bucle de ColumnCount.
      Next ColumnCount
   ' Comienza la siguiente iteración del bucle de RowCount.
   Next RowCount

   ' Cierra el fichero de destino.
   Close #FileNum
End Sub

3. Seleccionamos el rango que deseamos exportar.
4. Ejecutamos la subrutina.
5. En el cuadro de diálogo indicamos la ruta completa donde exportar el fichero.

6. El nuevo fichero se habrá exportado en la ruta indicada en el cuadro de diálogo.Comprobamos que tiene el calificador de texto con las comillas dobles abriendo el fichero con un editor de texto.

Entradas relacionadas

• Añadir una descripción a las funciones definidas por el usuario en Excel
• Convertir texto de URL en enlaces activos
• Abrir ficheros y aplicaciones con cuadro de diálogo e hipervínculo en Excel
• Eliminar todos los hipervínculos de una hoja o libro con VBA
• Comprobar si una celda contiene un hipervínculo

Referencias

• Método Application.MacroOptions (Excel)