🔝To change the language of this blog post, choose it from the Google box at the top left.
En el artículo anterior publiqué un mapa interactivo con datos climáticos fijos, que puedes descargar desde aquí:
Mapa del clima español | #ExcelPedroWave
A continuación explicaré cómo consultar los datos climáticos de un día desde la Agencia Estatal de Meteorología - AEMET, necesarios para visualizarlos en el mapa descargado del artículo anterior, y cómo almacenar un histórico del clima de varios días.
Para seguir la explicación tendrás que descargar el archivo que adjunto al final del artículo y abrirlo por la hoja 'ConsultaFecha':
Para descargar los datos climáticos de un día hay que seleccionar la fecha con el desplegable de la celda E2 y, en la cinta de opciones, seleccionar: Datos > Actualizar todo.
También se puede actualizar todo con la combinación de teclas: Control + Alt + F5
A continuación explicaré cómo extraer, transformar y cargar los datos climáticos con Power Query en Excel, ¡la mejor herramienta ETL del mercado!
Pulsa la flecha para pasar de página: ➡️
A continuación explicaré detalladamente estas 20 preguntas que me he hecho y las 20 respuestas que he dado para obtener los datos climáticos.
Información elaborada utilizando, entre otras, la obtenida de la Agencia Estatal de Meteorología - AEMET.
La AEMET permite consultar infinidad de datos climáticos, tanto del tiempo de los últimos días como de la predicción del tiempo para los próximos días.
En esta ocasión extraeremos datos históricos del tiempo acaecido en los últimos 7 días, aunque también se pueden consultar los datos climatológicos registrados durante el día de hoy, desde esta página:
El Tiempo. Hoy y últimos días - Resúmenes diarios anteriores - Tabla - Agencia Estatal de Meteorología - AEMET. Gobierno de España
Esta página contiene varias tablas con el clima en España durante el día de ayer:
Se pueden ver 2 botones para exportar dos tipos de archivos:
https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.xls?k=&datos=det&w=2&f=&x=d07
https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=2&f=&x=d07
En el resto del artículo plantearé unas cuantas preguntas y las respuestas que he dado para solucionar el problema de extraer, transformar y cargar los datos climáticos de AEMET con Power Query en Excel, partiendo de un archivo con un "formato poco amigable".
Pregunta 1: ¿Qué herramienta ETL usar para datos AEMET?
Respuesta 1: ¡Power Query sin duda!
En Excel 2010 para Windows, apareció por primera vez Power Query como un complemento gratuito descargable desde aquí: Download Microsoft Power Query para Excel from Official Microsoft Download Center
A partir de Excel 2016 Power Query en Excel está integrado en la cinta de opciones: Datos > Obtener datos > Iniciar Editor de Power Query...
Teniendo esta poderosa herramienta ETL en Excel, un analista de datos ¿para qué va a usar otra distinta?
Pregunta 2: ¿De qué archivo se descargan los datos?
Respuesta 2: Del archivo con formato CSV
Los dos tipos de archivos que se pueden descargar desde AEMET son: XLS y CSV.
Para extraer datos de un archivo XLS (formato binario de Excel 97-2003) es necesario tener instalado Access Database Engine OLEDB (ACE): conector de Power Query Excel - Power Query | Microsoft Docs
Para extraer datos desde cualquier versión de Excel es mejor conectar a un archivo OpenXML (como XLSX), lo que obligaría a convertir el archivo XLS en XLSX, antes de importarlo con Power Query.
Como AEMET también suministra el archivo CSV, es mejor usar directamente este formato para extraer los datos climáticos, aunque es un archivo con un "formato poco amigable".
Pregunta 3: ¿Qué datos climáticos se pueden extraer?
Respuesta 3: Temperaturas, viento y precipitaciones
Estos son los datos de los archivos CSV descargados de AEMET y abiertos en Excel:
Son 809 registros de todas las estaciones controladas cada día por AEMET, con 12 campos:
Es un archivo con un "formato poco amigable" al incluir entre paréntesis la hora del registro y por poner puntos (.) en lugar de comas (,) para los valores decimales. Por ejemplo, "26.0 (09:30)" para informar la temperatura máxima y su hora entre paréntesis, lo que obliga a cambiar el signo decimal y a separar cada campo en dos campos, uno para el valor y otro para la hora.
Al mapa solo se llevan los datos máximos o mínimos, por lo que los valores marcados como "NO USADO" no se han tenido en cuenta como datos para el mapa climático, aunque la temperatura media se podría incluir en próximas versiones, si este mapa tiene muchos lectores...
Pregunta 4: ¿De qué fechas se extraen datos?
Respuesta 4: De hoy y de los 7 días anteriores
AEMET mantiene solamente los archivos de hoy y de los 7 días anteriores.
Los datos de AEMET se pueden descargar desde este enlace:
https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=2&f=&x=d07
Modificando el argumento final x se obtiene:
También publica los registros de las estaciones meteorológicas durante el día de hoy, que se pueden descargar desde este enlace:
https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=1&f=
Estos datos difieren un poco de los datos de los 7 días anteriores.
Son los mismos datos usados al descargar los 7 días anteriores, excepto la última columna con la hora de "Datos hasta", que indica cuando se registraron los datos por última vez en el día de hoy.
ATENCIÓN: Solamente interesa descargar los datos de hoy si se quieren obtener los datos meteorológicos actuales. Para obtener valores históricos es conveniente que la fecha del registro sea de días anteriores que son actualizados con datos más confiables. Por ejemplo, el día 8 de julio se pueden obtener datos fiables de los días 1 al 4 de julio, pues los datos de los 3 últimos días aún puede modificarlos la AEMET.
Pregunta 5: ¿Cómo se seleccionan las fechas?
Respuesta 5: Mediante un desplegable de 8 días
En la celda E2 con validación de datos se selecciona la fecha con un desplegable en el rango E7:E14, con la lista de fechas de hoy y de los 7 días anteriores.
En la celda J2 se calcula la ruta del archivo CSV a consultar, con el nombre definido: miRuta, y con la fórmula:
="https://www.aemet.es/es/eltiempo/observacion/ultimosdatos_espana_resumenes-diarios-anteriores.csv?k=&datos=det&w=" & $F$6 & "&f=" & SI($G$6=0;"";"&x=d0" & $G$6)
Esta ruta es la que debe leer Power Query para consultar los datos climáticos.
Pregunta 6: ¿Cómo pasar "miRuta" a Power Query?
Respuesta 6: Con una referencia a Excel
Se abre el Editor de Power Query desde la cinta de opciones con: Datos > Obtener datos > Iniciar Editor de Power Query...
Se selecciona: Nuevo origen > Otros orígenes > Consulta en blanco
y se cambia el nombre de la consulta: ConsultaAEMET
En la ventana de fórmulas se escribe:
= Excel.CurrentWorkbook(){[Name="miRuta"]}[Content]{0}[Column1]
Con lo que se obtiene la URL del archivo CSV a extraer desde AEMET:
Pregunta 7: ¿Cómo extraer los datos de la ruta?
Respuesta 7: Como documento CSV codificado en español
Insertar un paso después y editar la fórmula:
= Csv.Document(Web.Contents(Origen),[Delimiter=",", Columns=8, Encoding=1252])
Que extrae los datos climáticos del archivo CSV:
Se extraen únicamente 8 columnas del archivo CSV separado por comas, y con el argumento Encoding=1252 para traducir el texto al idioma español, indicando como Origen de archivo: 1252: Europeo occidental (Windows), lo que se puede ver haciendo clic en la rueda de configuración de este segundo paso:
Esto pasaría sin Encoding=1252, que no entendería algunas letras en español, como la letra ñ:
Encoding: es la codificación de texto del archivo. Su valor predeterminado es: 65001 (UTF-8), que no soporta el idioma español, por lo que hay que cambiarlo a 1252: Europeo occidental (Windows). Enlace aquí: Csv.Document - PowerQuery M | Microsoft Docs
Pregunta 8: ¿Decimales con puntos en lugar de comas?
Respuesta 8: AEMET obliga a convertir los decimales
Los datos decimales de AEMET están separados por puntos en lugar de comas. ¿Por qué será?
En el paso 3 es necesario convertir los puntos "." en comas "," con esta fórmula:
= Table.ReplaceValue(Personalizado1,".",",",Replacer.ReplaceText,{"Column3", "Column4", "Column5", "Column6", "Column7", "Column8"})
Pregunta 9: ¿Crear tabla o solo conexión a AEMET?
Respuesta 9: Crear solo conexión
Es mejor: Crear únicamente la conexión para la ConsultaAEMET, pues esta conexión se usará en dos consultas:
Pregunta 10: ¿Cómo separar los datos de la fecha?
Respuesta 10: Con dos referencias a la ConsultaAEMET
Desde Inicio > Administrar > Referencia, se crea una consulta nueva que hace referencia a ConsultaAEMET, creando dos consultas diferentes:
También se pueden crear, haciendo clic con el botón derecho del ratón sobre la ConsultaAEMET, y seleccionando en el menú contextual: Referencia
Pregunta 11: ¿Cómo obtener la fecha de la consulta?
Respuesta 11: Conservando las filas superiores de la consulta
Con 4 pasos se obtiene la fecha en ConsultaFecha:
Pasos en el editor avanzado del lenguaje M de Power Query:
Con lo que se obtiene la fecha y hora oficial de actualización y la fecha del tiempo meteorológico.
Pregunta 12: ¿Cómo obtener los datos climáticos?
Respuesta 12: Quitando las filas superiores de la consulta
Con unos cuantos pasos se obtiene la fecha en ConsultaTiempo:
No voy a explicar todos los pasos, pues son muchos, aunque se pueden ver en el editor avanzado del lenguaje M de Power Query:
Esta consulta se carga en la tabla con la ConsultaTiempo, con los datos climáticos necesarios para el mapa y con el tipo de dato adecuado.
En esta tabla están los datos climáticos de un día:
Pregunta 13: ¿Cómo resumir los datos por provincia?
Respuesta 13: Con una tabla dinámica
La tabla anterior, obtenida con Power Query, se resume con una tabla dinámica en una nueva hoja de Excel denominada: TD_Tiempos, para obtener los valores máximos y la temperatura mínima de cada provincia en un día.
En el área de Filas se coloca la Provincia y los demás campos se resumen en el área de Valores:
El resultado es la siguiente tabla con máximos y mínimos de datos climáticos por provincia:
Pregunta 14: ¿Cómo transformo los datos climáticos?
Respuesta 14: Mediante una tabla auxiliar
En la hoja 'Tiempo' se ha diseñado una tabla auxiliar, denominada TablaTiempo, con los datos climáticos transformados, para que cada dato climático de una provincia en el día seleccionado incluya su valor máximo o mínimo, su hora y su estación meteorológica, excepto para las precipitaciones que son durante las 24 horas de un día, por lo que no llevan hora.
Los cálculos se hacen con los datos de la tabla ConsultaTiempo y con los campos de la tabla dinámica resumida anteriormente en la hoja 'TD_Tiempos', gracias a la función: IMPORTARDATOSDINAMICOS.
Esta tabla tiene 52 filas, una por cada provincia, y 26 columnas:
=SI.ERROR(FECHANUMERO('ConsultaFecha'!$B$4);FECHANUMERO(EXTRAE('ConsultaFecha'!$B$3;1;ENCONTRAR(" a las";'ConsultaFecha'!$B$3))))
y en A3 y siguientes: =$A$2
=IMPORTARDATOSDINAMICOS("Máx. de Temperatura máxima (ºC)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])
=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Máxima (ºC)]]=ConsultaTiempo[Temperatura máxima (ºC)])*FILA(ConsultaTiempo[Provincia]))-1
=INDICE(ConsultaTiempo[Estación];[@[Fila Máxima]])
=INDICE(ConsultaTiempo[Hora máxima];[@[Fila Máxima]])
=IMPORTARDATOSDINAMICOS("Máx. de Temperatura mínima (ºC)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])
=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Mínima máx. (ºC)]]=ConsultaTiempo[Temperatura mínima (ºC)])*FILA(ConsultaTiempo[Provincia]))-1
=INDICE(ConsultaTiempo[Estación];[@[Fila Mínima máx.]])
=INDICE(ConsultaTiempo[Hora mínima];[@[Fila Mínima máx.]])
=IMPORTARDATOSDINAMICOS("Mín. de Temperatura mínima (ºC)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])
=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Mínima (ºC)]]=ConsultaTiempo[Temperatura mínima (ºC)])*FILA(ConsultaTiempo[Provincia]))-1
=INDICE(ConsultaTiempo[Estación];[@[Fila Mínima]])
=INDICE(ConsultaTiempo[Hora mínima];[@[Fila Mínima]])
=IMPORTARDATOSDINAMICOS("Máx. de Racha (km/h)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])
=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Racha (km/h)]]=ConsultaTiempo[Racha (km/h)])*FILA(ConsultaTiempo[Provincia]))-1
=INDICE(ConsultaTiempo[Estación];[@[Fila Racha]])
=INDICE(ConsultaTiempo[Hora racha];[@[Fila Racha]])
=IMPORTARDATOSDINAMICOS("Máx. de Velocidad máxima (km/h)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])
=MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Velocidad máxima (km/h)]]=ConsultaTiempo[Velocidad máxima (km/h)])*FILA(ConsultaTiempo[Provincia]))-1
=INDICE(ConsultaTiempo[Estación];[@[Fila Velocidad]])
=INDICE(ConsultaTiempo[Hora Velocidad máxima];[@[Fila Velocidad]])
=IMPORTARDATOSDINAMICOS("Máx. de Precipitación 00-24h (mm)";TD_Tiempos!$A$1;"Provincia";[@Provincia2])
=SI([@[Precipitación (mm)]]=0;0;MAX(([@Provincia2]=ConsultaTiempo[Provincia])*([@[Precipitación (mm)]]=ConsultaTiempo[Precipitación 00-24h (mm)])*FILA(ConsultaTiempo[Provincia]))-1)
=SI([@[Fila Precipitación]]=0;"";INDICE(ConsultaTiempo[Estación];[@[Fila Precipitación]]))
Este es el resultado de la tabla TablaTiempo en la hoja 'Tiempo':
Pregunta 15: ¿Cómo extraer solo los datos climáticos?
Respuesta 15: Con una consulta en Power Query
La tabla anterior se abre en Power Query seleccionando en la cinta de opciones: Datos > Obtener datos > De otras fuentes > De una tabla o rango
Con lo que se muestra en el Editor de Power Query:
Estos son los pasos aplicados:
= Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Provincia", type text}, {"Provincia2", type text}, {"Máxima (ºC)", type number}, {"Fila Máxima", Int64.Type}, {"Estación Máxima", type text}, {"Hora Máxima", type time}, {"Mínima máx. (ºC)", type number}, {"Fila Mínima máx.", Int64.Type}, {"Estación Mínima máx.", type text}, {"Hora Mínima máx.", type time}, {"Mínima (ºC)", type number}, {"Fila Mínima", Int64.Type}, {"Hora Mínima", type time}, {"Racha (km/h)", Int64.Type}, {"Fila Racha", Int64.Type}, {"Velocidad máxima (km/h)", Int64.Type}, {"Fila Velocidad", Int64.Type}, {"Fila Precipitación", Int64.Type}, {"Hora Racha", type time}, {"Hora Velocidad", type time}, {"Precipitación (mm)", type number}, {"Estación Velocidad", type text}, {"Estación Precipitación", type text}, {"Estación Racha", type text}, {"Estación Mínima", type text}})
= Table.RemoveColumns(#"Tipo cambiado",{"Fila Máxima", "Fila Mínima máx.", "Fila Mínima", "Fila Racha", "Fila Velocidad", "Fila Precipitación"})
Esta consulta TablaTiempo se guarda como solo conexión. Más adelante se anexará a una nueva tabla con el historial del tiempo meteorológico de varios días...
Pregunta 16: ¿Cómo pasar "miFecha" a Power Query?
Respuesta 16: Con una referencia a Excel
Se abre el Editor de Power Query desde la cinta de opciones con: Datos > Obtener datos > Iniciar Editor de Power Query...
Se selecciona: Nuevo origen > Otros orígenes > Consulta en blanco
y se cambia el nombre de la consulta: ExtraeFecha
En la ventana de fórmulas se escribe:
= Excel.CurrentWorkbook(){[Name="miFecha"]}[Content]{0}[Column1]
Se crea un segundo paso con la fórmula:
= DateTime.Date(Origen)
O seleccionando en la pestaña: Transformar > Fecha > Solo fecha, con lo que la fórmula será:
= Date.From(Origen)
Con lo que se obtiene la fecha elegida para la consulta del tiempo en la celda E2 (miFecha) de la hoja 'ConsultaFecha':
Pregunta 17: ¿Cómo crear un historial del clima?
Respuesta 17: Con una tabla de historial del tiempo
Como la consulta del tiempo es de un día, hace falta crear una tabla con el historial del tiempo meteorológico de varios días.
La nueva tabla HistorialTiempo se crea en la hoja 'HistorialTiempo', con las mismas columnas que la consulta solo conexión que hemos creado antes en Power Query: TablaTiempo
Esta tabla contendrá los datos climáticos de cada provincia para cada día en que se hayan consultado datos.
Pregunta 18: ¿Cómo anexar el tiempo de varias fechas?
Respuesta 18: Anexando filas con Power Query
Solo queda concatenar las filas de la tabla anterior HistorialTiempo con la consulta solo conexión TablaTiempo en la tabla con el historial del tiempo HistorialTiempo, anexando cada consulta que se haga al historial según la fecha del tiempo.
La tabla anterior HistorialTiempo se abre en Power Query seleccionando en la cinta de opciones: Datos > Obtener datos > De otras fuentes > De una tabla o rango
Con lo que se muestra en el Editor de Power Query:
Estos son los pasos aplicados:
= Excel.CurrentWorkbook(){[Name="HistorialTiempo"]}[Content]
= Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Provincia", type text}, {"Provincia2", type text}, {"Máxima (ºC)", type number}, {"Estación Máxima", type text}, {"Hora Máxima", type time}, {"Mínima máx. (ºC)", type number}, {"Estación Mínima máx.", type text}, {"Hora Mínima", type time}, {"Hora Mínima máx.", type time}, {"Mínima (ºC)", type number}, {"Estación Mínima", type text}, {"Racha (km/h)", Int64.Type}, {"Estación Racha", type text}, {"Hora Racha", type time}, {"Velocidad máxima (km/h)", Int64.Type}, {"Estación Velocidad", type text}, {"Hora Velocidad", type time}, {"Precipitación (mm)", type number}, {"Estación Precipitación", type text}})
= Table.SelectRows(#"Tipo cambiado", each [Fecha] <> ExtraeFecha)
= Table.Combine({#"Filas filtradas", TablaTiempo})
Este último paso se inserta desde el menú: Inicio > Combinar > Anexar consultas
Si en el historial existían filas con la fecha de la consulta, primero se filtran y luego se añaden las filas para esa fecha con los datos climáticos actualizados. Esto es así porque AEMET va actualizando los datos de los días anteriores progresivamente cuando tiene datos rigurosos de todas las estaciones meteorológicas, lo que puede ser al cabo de unos días.
Pregunta 19: ¿Cómo cargar el historial del tiempo en el mapa?
Respuesta 19: Con una tabla dinámica
Los datos históricos del clima español se conectan a la plantilla con el mapa del tiempo mediante una tabla dinámica.
Ya está explicado en la sección: Cómo actualizar los datos climáticos de este artículo:
Mapa del clima español | #ExcelPedroWave
por lo que no lo explicaré aquí. El resultado es la hoja 'Tiempo' con la tabla dinámica con el historial del tiempo:
Pregunta 20: ¿Cómo visualizar el tiempo en el mapa?
Respuesta 20: Con una mapa coroplético
La explicación vuelve a estar en este artículo desde el que se puede descargar el mapa:
Mapa del clima español | #ExcelPedroWave
y se puede ver en acción el mapa interactivo del clima español:
Puedes descargar la consulta del tiempo AEMET v1, con datos climáticos desde el 15 de junio de 2022 hasta ayer, desde estos enlaces:
Abre la plantilla con una versión de Excel de escritorio a partir de Excel 2013 y presiona el botón: Habilitar edición
Aparece una ADVERTENCIA DE SEGURIDAD: Se han deshabilitado las conexiones de datos externos. Presiona el botón: Habilitar contenido
Para usarla seleccionar un día en la celda E2 de la hoja 'ConsultaFecha' y en la cinta de opciones seleccionar: Datos > Actualizar todo, o la combinación de teclas: Control + Alt + F5
Fin de este extenso análisis de datos climáticos. Si ves que ha quedado algo pendiente de explicar, puedes hacer un comentario.
No Response to "Datos climáticos con Power Query"
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.