Dicho esto veamos podemos analizar las estadísticas sobre el Coronavirus usando Power Query y PowerPivot.
Mi objetivo es combinar los datos que por lo general aparecen en páginas Web que se ocupan del tema, con otros datos como población (tamaño y densidad) y edad promedio. También generar indicadores como porcentaje de defunciones del total por millón habitantes, porcentaje de sanados del total de confirmados, porcentaje de defunciones del total de confirmados y otras más.
En este post no voy a mostrar en detalle el desarrollo del modelo, que se puede descargar aquí, sino solamente los lineamentos generales. Quien no esté interesado en los lineamentos del modelo puede ir directamente a la parte final del post para ver algunos de los reportes que se pueden crear.
Aclaración sobre la descarga del archivo: el archivo está ligado dinámicamente a las fuentes de datos; cada vez que se abre el archivo los datos se actualizan por lo que la apertura del archivo puede durar un poco más de los esperado. El cuaderno fue desarrollado usando Excel 365; en otras versiones puede no funcionar o no actualizar los datos.
1 - Las fuentes de los datos.
- Wikipedia: en esta página de Wikipedia se encuentra la tabla por localidad con los casos confirmados, los recuperados y las defunciones.
- Worldmeter: en esta página encontramos una tabla con datos de población, superficie, edad promedio y más por país.
La preparación y elaboración de los datos la hacemos con Power Query.
Los últimos dos pasos (#"Consultas combinadas" y #"Columnas quitadas1") los agregamos después de haber creado la tabla en el tercer paso (ver más adelante).
Guardamos la consulta como Solo conexión y la cargamos al modelo de datos
Segundo paso: creamos una tabla con los datos de la población de los países.
Creamos una consulta a la tabla en la página de Worldmeter y aplicamos esta serie de pasos para realizar las transformaciones necesarias:
Los últimos dos pasos (#"Consultas combinadas" y #"Columnas quitadas1") los agregamos después de haber creado la tabla en el tercer paso (ver más adelante).
Guardamos la consulta como Solo conexión y la cargamos al modelo de datos
Tercer paso: creamos una lista de países y continentes.
Los países que aparecen en ambas listas no coinciden en su totalidad. Por ejemplo, Wikepdia incluye localidades como el crucero Diamond Princess (por eso el campo de la tabla se llama "localidad" y no país); los nombres de los países pueden diferir (idioma, abreviación, etc.).
Mi decisión fue crear una tabla con los países comunes a ambas listas (después de comprobar que los países o localidades que quedan fuera de la lista no son significativos).
Para crear la tabla aplicamos el método que mostré en la nota sobre como comparar listas con la función List.Intersect.
Antes de cargar la tabla a una consulta en Power Query agregamos el campo [Continente], tarea que realicé manualmente (195 filas, no es el fin del mundo) enriqueciendo a su vez mis conocimientos de geografía.
Primer paso: creamos una tabla con los datos de la pandemia.
Creamos una consulta a la tabla en la página de Wikipedia; transformamos los datos con un serie de pasos (quitamos columnas innecesarias, cambiamos el tipo de datos, etc.). El código de los pasos aplicados es el siguiente
let
Origen = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Template:2019%E2%80%9320_coronavirus_pandemic_data#covid19-container")),
Data0 = Origen{0}[Data],
#"Encabezados con nivel disminuido" = Table.DemoteHeaders(Data0),
#"Columnas quitadas" = Table.RemoveColumns(#"Encabezados con nivel disminuido",{"Column1", "Column6"}),
#"Filas superiores quitadas" = Table.Skip(#"Columnas quitadas",1),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Filas superiores quitadas", [PromoteAllScalars=true]),
#"Filas inferiores quitadas" = Table.RemoveLastN(#"Encabezados promovidos",2),
#"Valor reemplazado" = Table.ReplaceValue(#"Filas inferiores quitadas","–",null,Replacer.ReplaceValue,{"Cases", "Deaths", "Recov."}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Valor reemplazado",{{"Cases", Int64.Type}, {"Deaths", Int64.Type}, {"Recov.", Int64.Type}, {"Countries and territories", type text}}),
#"Filas superiores quitadas1" = Table.Skip(#"Tipo cambiado",1),
#"Consultas combinadas" = Table.NestedJoin(#"Filas superiores quitadas1", {"Countries and territories"}, paises, {"Paises"}, "paises", JoinKind.RightOuter),
#"Columnas quitadas1" = Table.RemoveColumns(#"Consultas combinadas",{"paises"})
in
#"Columnas quitadas1"
Los últimos dos pasos (#"Consultas combinadas" y #"Columnas quitadas1") los agregamos después de haber creado la tabla en el tercer paso (ver más adelante).
Guardamos la consulta como Solo conexión y la cargamos al modelo de datos
Segundo paso: creamos una tabla con los datos de la población de los países.
Creamos una consulta a la tabla en la página de Worldmeter y aplicamos esta serie de pasos para realizar las transformaciones necesarias:
let
Origen = Web.Page(Web.Contents("https://www.worldometers.info/world-population/population-by-country")),
Data0 = Origen{0}[Data],
#"Columnas quitadas" = Table.RemoveColumns(Data0,{"Yearly Change", "Net Change", "Density (P/Km²)", "Migrants (net)", "Fert. Rate", "Urban Pop %", "World Share", "#"}),
#"Valor reemplazado" = Table.ReplaceValue(#"Columnas quitadas","N.A.",null,Replacer.ReplaceValue,{"Med. Age"}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Valor reemplazado",{{"Med. Age", Int64.Type}, {"Land Area (Km²)", Int64.Type}, {"Population (2020)", Int64.Type}}),
#"Consultas combinadas" = Table.NestedJoin(#"Tipo cambiado", {"Country (or dependency)"}, paises, {"Paises"}, "paises", JoinKind.RightOuter),
#"Se expandió paises" = Table.ExpandTableColumn(#"Consultas combinadas", "paises", {"Paises"}, {"Paises.1"}),
#"Columnas quitadas1" = Table.RemoveColumns(#"Se expandió paises",{"Paises.1"})
in
#"Columnas quitadas1"
Los últimos dos pasos (#"Consultas combinadas" y #"Columnas quitadas1") los agregamos después de haber creado la tabla en el tercer paso (ver más adelante).
Guardamos la consulta como Solo conexión y la cargamos al modelo de datos
Tercer paso: creamos una lista de países y continentes.
Los países que aparecen en ambas listas no coinciden en su totalidad. Por ejemplo, Wikepdia incluye localidades como el crucero Diamond Princess (por eso el campo de la tabla se llama "localidad" y no país); los nombres de los países pueden diferir (idioma, abreviación, etc.).
Mi decisión fue crear una tabla con los países comunes a ambas listas (después de comprobar que los países o localidades que quedan fuera de la lista no son significativos).
Para crear la tabla aplicamos el método que mostré en la nota sobre como comparar listas con la función List.Intersect.
Antes de cargar la tabla a una consulta en Power Query agregamos el campo [Continente], tarea que realicé manualmente (195 filas, no es el fin del mundo) enriqueciendo a su vez mis conocimientos de geografía.
A partir de aquí vamos a usar PowerPivot para crear los informes. El primer paso antes de crear las tablas dinámicas con nuestros informes es establecer las relaciones entre las tres tablas que hemos cargado al modelo de datos.
El segundo paso es crear una serie de medidas que usaremos en las tablas dinámicas (nuevamente, no entraré aquí en detalles técnicos suponiendo que mis lectores tienen conocimientos básicos de Power Query, Power Pivot y DAX).
Primero creamos "medidas básicas". En la tabla wikipedia creamos esta medidas:
- Confirmados = SUM(wikipedia[Cases])
- Sanados = SUM(wikipedia[Recov.])
- Defunciones = SUM(wikipedia[Deaths])
En la tabla World_Pop creamos
- Poblacion = SUM(World_Pop[Population (2020)])
- Edad Promedio = AVERAGE(World_Pop[Med. Age])
- Superficie = SUM(World_Pop[Land Area (Km²)])
A partir de estas medidas podemos crear "medidas compuestas" (combinan datos de tablas distintas). En wikipedia creamos:
- Confirmados x 1M hab = [Confirmados]/[Poblacion]*1000000
- Defunciones x 1M hab = [Defunciones]/[Poblacion]*1000000
- Sanados x 1M hab = [Sanados]/[Poblacion]*1000000
- Sanados/Confirmados % = [Sanados]/[Confirmados]
- Defunciones/Confirmados % = [Defunciones]/[Confirmados]
En World_Pop creamos
- Densidad km2 = [Poblacion]/[Superficie]
Ahora creamos nuestros reportes dinámicos usando los valores de la tabla paises en el campo de las filas. Esto nos permite combinar los datos de las distintas tablas de datos, tarea que sería imposible con tablas dinámicas "clásicas".
Veamos algunos de los reportes que podemos crear (todo el cuaderno se puede descargar aquí)
1 - Datos por continente
En este informe combinamos datos de la pandemia con datos de la población. Es interesante notar que en continente con el mayor número de casos confirmados y de decesos, Europa Occidental, es a su vez el continente con la mayor edad promedio.
2 - Medidas por millón habitantes por continente
3 - Medidas relativas al número de confirmados, por continente
Por supuesto podemos crear informes detallados por país poniendo [Paises] de la tabla paises en lugar de [Continente].
En la captura de pantalla se puede ver que he agregado un slicer (segmentación de datos) por continente.
Y ahora un reflexión personal. Viendo los datos resulta evidente la problemática de la confiabilidad de los datos. Los datos de ciertos países no parecen confiables, ya sea por incapacidad de recolectar los datos o por intención de minimizar el impacto de la pandemia en el país.
Por otro lado la cantidad de casos confirmados tiene relación directa con número de personas examinadas; algunos países, como Corea del Sur, han tenido una política de realizar análisis de contagio a gran parte de la población, otros, como los países africanos, no cuentan con esta capacidad.
Buen análisis construido.
ResponderBorrar