Power BI in Zeiten einer Pandemie

Was macht ein Power BI-Designer ohne eine sinnvolle Beschäftigung, wenn er z. B. aufgrund einer Pandemie über zu viel freie Zeit verfügt? Da mir nichts besseres einfällt, habe ich mir die frei verfügbaren Zahlen zur “Corona”-Pandemie im Internet angesehen und daraus versucht Power BI-Datasets zu basteln. Die Herausforderung hier war das vorhandene Zahlenmaterial aus verschiedensten Quellen zu sichten und so zu konsolidieren, dass man sinnvolle Auswertungen aufsetzen kann.

Nachdem ich nicht der einzige mit ein wenig freier Zeit und konstruktiven Ideen bin, gab es bereits einige Vorreiter, die Implementierungen mit Power BI erstellt haben, wie z. B.

Die offizielle Webseite zu diesem Thema der John Hopkins University enthält auch ein visuelle Darstellung: https://coronavirus.jhu.edu/map.html (auf Basis von Arcgis https://www.arcgis.com/index.html – aber leider nicht Power BI). Weitere Dashboards findet man bspw.. auch unter https://github.com/CSSEGISandData/COVID-19/issues/576. Eine meiner Meinung nach sehr interessante Infografik findet ihr https://informationisbeautiful.net/visualizations/covid-19-coronavirus-infographic-datapack/. Die Daten dazu ist in einem Google-Document unter https://docs.google.com/spreadsheets/d/1g_YxmDfQx7aOU2DKzNZo9b-NTk62Bju6X3z6OuCa6gw/edit#gid=515684451 zusammengestellt.

Für Deutschland bietet das RKI, ähnlich wie die Johns Hopkins University, ein interaktives Dashboard mit den aktuellen Fallzahlen unter https://npgeo-corona-npgeo-de.hub.arcgis.com/ an:
image

Nachdem viele Bezeichnungen zu dieser Pandemie um Umlauf sind, möchte ich als Laie versuchen einige Klarheit in die Verwirrung zu bringen: Mit Corona bezeichnet man eine Gruppe von Viren, zu denen neben dem derzeitigen Virus SARS-CoV-2 (severe acute respiratory syndrome coronavirus 2 oder frei übersetzt: Schweres akutes Atemwegssyndrom Coronavirus 2”) auch Middle East respiratory syndrome coronavirus (MERS-CoV) oder SARS-assoziierten Coronavirus (SARS-CoV, gelegentlich auch als SARS-CoV-1 bezeichnet). Die Wirkung auf Wirbeltiere, zu denen ja auch der Mensch zählt, ist sehr unterschiedlich – von leichten Erkältungssymptomen bis zu einer schweren Lungenentzündung, die zum Tode führen kann.

Zur Erstellung der Datasets und Berichte habe ich Power BI Desktop in der aktuellen Version (vom 13. März 2020) verwendet. Doch spricht aus meiner Sicht auch nichts dagegen, dies auch mit einer älteren (oder, wenn verfügbar, neueren) Version umzusetzen. Ich habe versucht für die Erstellung auf die neuesten oder sogar nur als Preview verfügbaren Features zu verzichten.

Ich werde mich in diesem Beitrag schwerpunktmäßig mit den Ausgangsdaten zu der Viruspandemie für mögliche Berichte beschäftigen. Hierbei lasse ich mögliche weitere Daten zu r wissenschaftlichen Analyse wie Bevölkerungszahlen, Altersstrukturen außer acht. Ebenfalls werde ich mich noch nicht um die visuelle Darstellung innerhalb eines Berichts kümmern – dies werde ich ggf. in einem weiteren Beitrag beschreiben.

Datenquelle

Voraussetzung für die Erstellung von eigenen Datasets ist natürlich das erforderliche Zahlenmaterial in eine verarbeitbare Form zu bekommen. Im Internet gibt es einige Quellen mit brauchbaren Zahlenmaterial, wie z. B.

Noch ein paar persönliche Worte zu diesem Zahlenmaterial: Alle vorhandenen Daten beruhen auf Meldungen zu bestätigten Fällen von Personen, bei denen das Virus SARS-CoV-2 nachgewiesen werden konnte. Auch Todesfälle lassen sich nicht eindeutig diesem Virus zuschreiben, da einerseits nicht jeder Todesfall dahingehend untersucht werden kann, ob das Virus tatsächlich die Ursache dafür war oder er auf eine andere Erkrankung zurückzuführen ist.
Des Weiteren werden Personen nur nach bestimmten Auswahlverfahren getestet. Somit geht man allgemein von einer Dunkelziffer an Infizierten und als Folge von Todesfällen aus. Außerdem sollte berücksichtigt werden, dass Personen, die bspw. heute negativ auf das Virus getestet wurden, bereits morgen infiziert sein könnten.
Besonderheiten zwischen den einzelnen Datasets gibt es in Bezug auf die Erfassung der Daten. So geht bspw. die Johns Hopkins University den Weg das Informationen aus Sozial Media mit den Daten von öffentlichen Stellen weltweit abgeglichen werden: “To identify new cases, we monitor various twitter feeds, online news services, and direct communication sent through the dashboard. Before manually updating the dashboard, we confirm the case numbers using regional and local health departments, namely the China CDC (
CCDC), Hong Kong Department of Health, Macau Government, Taiwan CDC, European CDC (ECDC), the World Health Organization (WHO), as well as city and state level health authorities. For city level case reports in the U.S., Australia, and Canada, which we began reporting on February 1, we rely on the US CDC, Government of Canada, Australia Government Department of Health and various state or territory health authorities. All manual updates (outside mainland China) are coordinated by a team at JHU.” Quelle https://systems.jhu.edu/research/public-health/ncov/
Dadurch und durch unterschiedliche Erhebungs-/Meldezeitpunkte kann es zwischen den Zahlen der einzelnen Datasets zu weiteren Abweichungen kommen – die wir uns aber gerne anhand der Datasets zu einem späteren Zeitpunkt noch einmal ansehen können.
Einige Dataset liefern auch Zahlen zu genesenen (recovered) Personen. Auch diese Zahlenangabe ist kritisch zu betrachten, denn nur Personen die nachweislich erkrankt waren und zu einem späteren Zeitpunkt noch einmal auf dieses Virus ohne Befund getestet worden sind, werden als geheilt bezeichnet. Nun ist es jedoch so, dass nach heutigen Erkenntnissen eine gewisse Immunität gegen das Virus besteht, wenn eine Person einmal erkrankt war. So sind auch solche Zahlen mit einer gewissen Vorsicht zu sehen.

Wer mit den deutschen Zahlenmaterial arbeiten möchte, sollte das vom RKI aufbereitete auf Basis der Gesundheitsämter und Landesministerien verwenden –> hierzu ein Zitat: “bitte zitiert nicht die Johns Hopkins University (JHU), wenn Ihr die aktuellen Fallzahlen von bestätigten COVID-19-Infektionen für Deutschland berichtet.
Die Zahlen selbst sind gut. Die Quelle “JHU” jedoch sorgt für ziemlich viel Verwirrung. Bitte zitiert stattdessen unsere Gesundheitsämter und Landesministerien: gleiche Zahlen, korrekte Quelle, weniger Verwirrung.” Quelle: https://gehrcke.de/2020/03/ard-zdf-covid-19-fallzahlen/

Noch eine persönliche Bitte an alle die, die solches Zahlenmaterial zur Verfügung stellen: Ich verstehe es schon, dass es möglicherweise hilfreich ist, sich durch die Bereitstellung solcher Zahlen zu profilieren. Doch muss das wirklich sein! Wäre es nicht einfacher, alle Zahlen weltweit an einer Stelle konsolidiert (und validiert) zur Verfügung zu stellen, so dass jeder in die Lage ist, aktuelle Auswertungen durchzuführen.

Doch nun genug der Theorie, schauen wir uns dieses Virus in Zahlen in Power BI an. Bei den folgenden Datasets spare ich mir eine detaillierte Beschreibung der einzelnen Schritte, wie ich zu dem Ergebnis gekommen bin. Ich beschränke mich in diesem Beitrag darauf, euch den Power Query-Code zur Verfügung zu stellen – die ausführliche Beschreibung der Vorgehensweise würde hier zu weit führen. Solltet ihr Fragen zu meinen Abfragen oder Verbesserungsvorschläge haben, freue ich mich sehr auf eure Kommentare und Hinweise.

Der in diesem Beitrag eingebauten Sourcecode mit den Power Query-Abfragen, sollte euch ermöglichen die Datasets sehr schnell selbst nachzubauen. Voraussetzung dafür ist, dass ihr den Power BI-Desktop gestartet habt und eine (oder mehrere) neue Abfragen erstellt:

image

Daraufhin startet der Power Query-Editor

image

…und über die Schaltfläche “Erweiterter Editor” könnt ihr dann die von mir bereitgestellten Abfragen einfügen.

image

Dazu markiert ihr den bereits im Editor vorhandenen Text (mit STRG-A) und fügt die Abfrage aus dem Blog (mit STRG-C) ein. Im Ergebnis sollte dies dann bspw. so aussehen:

image

Noch ein kleiner technischer Hinweis: Einige der genannten Datasets haben in der URL/im Dateinamen einen zeitabhängigen Faktor. Um jeweils den aktuellen Tag beziehen zu können, habe ich einen neue Abfrage mit folgendem Aufbau definiert:


let
    Source = DateTime.ToText(DateTime.LocalNow(),"yyyy-MM-dd")
in
    Source

image

Bitte beachtet, dass einige Dateien der folgenden Datasets möglicherweise aus anderen Zeitzonen kommen bzw. zeitlich versetzt bereitgestellt werden. Deshalb kann es sein, dass eine Datei noch nicht bereitsteht, obwohl in Deutschland bereits der neue Tag angebrochen ist. Um zu verhindern, dass beim Abholen der Daten Fehler auftreten, könnt ihr die Power Query-Abfragen um eine entsprechende Fehlerbehandlung erweitern.

image

Details dazu findet ihr unter https://docs.microsoft.com/de-de/powerquery-m/error-handling. Um diesen Beitrag übersichtlich zu halten, habe ich in der ersten Version darauf verzichtet, Error handling in die Power Query-Abfragen einzuarbeiten. Sollte jedoch jemand entsprechende Vorschläge habe, freue ich mich über Kommentare oder Mails zu diesem Beitrag.

Dataset des ECDC

Das ECDC stellt die Daten im Excel-Format (xlsx) zur Verfügung, so dass eine Auswertung bereits mit Excel möglich wäre:

image

Die Daten für die Anzahl der geprüften Corona-Infektionen und der Todesfälle aufgrund der Infektion sind nach Ländern und Tage zusammengestellt. Nachdem Power BI bereits eine Zeit-Dimension zur Verfügung stellt, kann man aus den Daten die Angaben für Tag, Monat und Jahr löschen. Des Weiteren kann man mit dem zweistelligen  Landeskennzeichen in Power BI auch nicht viel anfangen. Deshalb spricht nichts dagegen, auch diese Angabe zu löschen. Somit lassen sie sich auch gut mit Power Query z. B. mit folgender “Abfrage” analysieren:

 

let
    Source = Excel.Workbook(Web.Contents("<a href='https://www.ecdc.europa.eu/sites/default/files/documents/COVID-19-geographic-disbtribution-worldwide-"'>https://www.ecdc.europa.eu/sites/default/files/documents/COVID-19-geographic-disbtribution-worldwide-"</a> &amp; DateToday &amp; ".xlsx"), null, true),
    dataSheet = Source{[Item="COVID-19-geographic-disbtributi",Kind="Sheet"]}[Data],
    HeaderDefinition = Table.PromoteHeaders(dataSheet, [PromoteAllScalars=true]),
    RemovedColumns = Table.RemoveColumns(HeaderDefinition,{"Day", "Month", "Year", "GeoId"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"DateRep", type date}, {"Cases", Int64.Type}, {"Deaths", Int64.Type}, {"Countries and territories", type text}})
in
    ChangedType

image

Hinweis: Die Daten stehen auf der Webseite auch in dem alten Excel-Format (xls) zur Verfügung. Jedoch endet deren Bereitstellung am 16.3.2020 – aus mir unbekannten Gründen.

Data Repository by Johns Hopkins CSSE

Dieses Data Repository wurde von dem Center for Systems Science and Engineering (CSSE) der Johns Hopkins University auf Github zur allgemeinen Nutzung bereitgestellt. In dem Repository befinden sich die aktuellen Daten im CSV-Format unter https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data. Dieses Verzeichnis unterteilt sich in zwei Unterbereiche:

Möchtet ihr euch ein eigenes Dataset mit allen Informationen zusammenstellen, könnt ihr entweder die drei Dateien mit dem zeitlichen Verlauf für die einzelnen Fälle kombinieren oder die Berichte über die einzelnen Tage zusammenführen. Als erstes Beispiel auf Basis der Daten verwende ich die drei Dateien. Die Power Query-Abfrage dazu sieht wie folgt aus:


let
    Source1 = Csv.Document(Web.Contents("<a href='https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv"),&#91;Delimiter=","'>https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv"),[Delimiter=","</a>, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Headers1 = Table.PromoteHeaders(Source1, [PromoteAllScalars=true]),
    UnpivotedColumns1 = Table.UnpivotOtherColumns(Headers1, {"Province/State", "Country/Region", "Lat", "Long"}, "Attribut", "Wert"),
     RenamedColumns1 = Table.RenameColumns(UnpivotedColumns1,{{"Wert", "ConfirmedCases"}, {"Attribut", "DateValue"}}),
    AddDeaths1 = Table.AddColumn(RenamedColumns1, "Deaths", each 0),
    AddRecovered1 = Table.AddColumn(AddDeaths1, "Recovered", each 0),

    Source2 = Csv.Document(Web.Contents("<a href='https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Deaths.csv"),&#91;Delimiter=","'>https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Deaths.csv"),[Delimiter=","</a>, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Headers2 = Table.PromoteHeaders(Source2, [PromoteAllScalars=true]),
    UnpivotedColumns2 = Table.UnpivotOtherColumns(Headers2, {"Province/State", "Country/Region", "Lat", "Long"}, "Attribut", "Wert"),
    RenamedColumns2 = Table.RenameColumns(UnpivotedColumns2,{{"Wert", "Deaths"}, {"Attribut", "DateValue"}}),
    AddConfirmed2 = Table.AddColumn(RenamedColumns2, "ConfirmedCases", each 0),
    AddRecovered2 = Table.AddColumn(AddConfirmed2, "Recovered", each 0),

    Source3 = Csv.Document(Web.Contents("<a href='https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Recovered.csv"),&#91;Delimiter=","'>https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Recovered.csv"),[Delimiter=","</a>, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Headers3 = Table.PromoteHeaders(Source3, [PromoteAllScalars=true]),
    UnpivotedColumns3 = Table.UnpivotOtherColumns(Headers3, {"Province/State", "Country/Region", "Lat", "Long"}, "Attribut", "Wert"),
    RenamedColumns3 = Table.RenameColumns(UnpivotedColumns3,{{"Wert", "Recovered"}, {"Attribut", "DateValue"}}),
    AddConfirmed3 = Table.AddColumn(RenamedColumns3, "ConfirmedCases", each 0),
    AddDeath3 = Table.AddColumn(AddConfirmed3, "Deaths", each 0),

    Source = Table.Combine({AddRecovered1, AddRecovered2, AddDeath3}),
    ChangedType = Table.TransformColumnTypes(Source,{{"ConfirmedCases", Int64.Type}, {"Deaths", Int64.Type}, {"Recovered", Int64.Type}}),
    CovertDateValue = Table.TransformColumnTypes(ChangedType, {{"DateValue", type date}}, "en-US"),
    GroupedRows = Table.Group(CovertDateValue, {"Province/State", "Country/Region", "Lat", "Long", "DateValue"}, {{"ConfirmedCases", each List.Sum([ConfirmedCases]), type number}, {"Deaths", each List.Sum([Deaths]), type number}, {"Recovered", each List.Sum([Recovered]), type number}})
in
    GroupedRows

und als Ergebnis sollte dann so aussehen:

image

Den Aufbau der Power Query-Abfrage für den csse_covid_19_daily_reports liefere ich euch noch nach. Zum Ausprobieren sollte diese Abfrage bereits hilfreich sein.

Datasets für deutsche Bundesländer (RKI)

Leider habe ich bisher noch kein sinnvolles frei verfügbares offizielles Dataset zu Fallzahlen in den Bundesländern und/oder auf Landkreis/Stadt-Ebene gefunden. Deshalb habe ich mir aus der Web-Seite des RKI (https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html) die tagaktuellen Zahlen extrahiert – zwar nicht schön, doch leider aktuell nicht zu ändern. Die Power Query-Abfrage dazu sieht wie folgt aus:


let
    Source = Web.BrowserContents("<a href='https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html")'>https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html")</a>,
    ExtractedTableFromHtml = Html.Table(Source, {{"Column1", "TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(3), TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6), TABLE &gt; * &gt; TR &gt; TD[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6)"}, {"Column2", "TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(3) + TH[colspan=""4""][rowspan=""1""]:nth-child(2):nth-last-child(2), TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5), TABLE &gt; * &gt; TR &gt; TD[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TD[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5)"}, {"Column3", "TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(3) + TH[colspan=""4""][rowspan=""1""]:nth-child(2):nth-last-child(2), TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4), TABLE &gt; * &gt; TR &gt; TD[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TD[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TD[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4)"}, {"Column4", "TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(3) + TH[colspan=""4""][rowspan=""1""]:nth-child(2):nth-last-child(2), TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(3), TABLE &gt; * &gt; TR &gt; TD[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TD[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TD[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4) + TD[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(3)"}, {"Column5", "TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(3) + TH[colspan=""4""][rowspan=""1""]:nth-child(2):nth-last-child(2), TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(3) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(2), TABLE &gt; * &gt; TR &gt; TD[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TD[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TD[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4) + TD[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(3) + TD[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(2)"}, {"Column6", "TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(3) + TH[colspan=""4""][rowspan=""1""]:nth-child(2):nth-last-child(2) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(1), TABLE &gt; * &gt; TR &gt; TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(3) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(2) + TH[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(1), TABLE &gt; * &gt; TR &gt; TD[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(6) + TD[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(5) + TD[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(4) + TD[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(3) + TD[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(2) + TD[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(1)"}}, [RowSelector="TABLE &gt; * &gt; TR"]),
    PromotedHeaders = Table.PromoteHeaders(ExtractedTableFromHtml, [PromoteAllScalars=true]),
    RemovedBottomRows = Table.RemoveLastN(PromotedHeaders,1),
    RemovedTopRows = Table.Skip(RemovedBottomRows,1),
    RenamedColumns = Table.RenameColumns(RemovedTopRows,{{"Elektro­nisch über­mittelte Fälle", "Anzahl_Faelle_gesamt"}, {"Elektro­nisch über­mittelte Fälle_1", "Anzahl_Faelle_Aenderung"}, {"Elektro­nisch über­mittelte Fälle_2", "Faelle_pro_1000Einwohner"}, {"Elektro­nisch über­mittelte Fälle_3", "Todesfaelle"}}),
    RemovedColumns = Table.RemoveColumns(RenamedColumns,{"Beson­ders be­trof­fene Gebiete in Deutsch­land"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"Anzahl_Faelle_gesamt", Int64.Type}, {"Anzahl_Faelle_Aenderung", Int64.Type}, {"Faelle_pro_1000Einwohner", Int64.Type}, {"Todesfaelle", Int64.Type}})
in
    ChangedType

was schlussendlich zu dem folgenden Ergebnis führt:

image

Update (30.03.2020)

Wie es aussieht, bin nicht ich der einzige der anmahnt, dass die Verfügbarkeit des Zahlenmaterials verbessert werden sollte, wie z. B. https://www.govdata.de/web/guest/neues/-/blogs/covid-19. Dieser Herausforderung scheint man sich das Robert Koch Institut nun gestellt zu haben, denn es bietet parallel zu dem Dashboard, auch das dem Dashboard zugrundeliegende Datenmaterial für

Für diese Datasets werden entsprechende Web-APIs angeboten, mit denen man sich auch dem Datenbestand eigene Abfragen z. B. auch für Power BI zusammenbauen kann. Nachdem wir es hier mit einem speziellen Abfragetyp zu tun haben, möchte ich euch, wie im Folgenden die Power Queries dazu liefern, wie ihr mit diesem Zahlenmaterial arbeiten könnt. Im ersten Schritt benötigt ihr die Abfrage, die ihr euch direkt auf der Webseite zum jeweiligen Dataset zusammenbauen könnt. Wählt dazu API-Explorer aus:

image

und dann seht ihr auf der rechten Seite die Abfrage-URL, die ihr als Basis für die Power Query verwenden könnt.

Hinweis: Wie ihr oberhalb des API-Explorer-Buttons sehen könnt, beinhaltet dieses Dataset derzeit 23.941 Datensätze. Irgendwie schaffe ich es nicht mehr als 2.000 Datensätze per Power Query in Power BI zu importieren. Die genaue Ursache dafür haben ich noch nicht gefunden – kann nur mit einigen Vermutungen dienen, wie bspw., dass die Ausgabe ohne Anmeldung auf 2.000 Datensätze begrenzt ist. Mit ein wenig Aufwand lässt sich mit Power Query eine Abfrage bauen, die die Web-Abfrage über einen Parameter, wie z. B. das Datum, mehrfach ausführt und die Ergebnisse zusammenführt.

Die Selektion von bestimmten Datensätzen lässt sich über die Webseite direkt steuern. Dafür einen entsprechenden Parameter mit Klick auf das Plus-Symbol hinter “wobei gilt” hinzufügen

image

und anschließend unter wie, den entsprechenden Parameterwert eingeben:

image

Nach Bestätigung mit der Enter-Taste wird der Parameter in die Abfrage-URL übernommen und ihr könnt ihn dann in Power BI verwenden:

image

Zusätzlich bietet die Web-API noch vielfältige Möglichkeiten die Abfrage-URL besser auf die geforderten Ergebnisse zuzuschneiden. Eine Beschreibung dieser “Abfragesprache” findet ihr unter https://developers.arcgis.com/rest/services-reference/query-feature-service-layer-.htm.

Mit ein wenig Power Query-Programmierung kommt man bspw. für das “Gesamt”-Dataset auf folgendes Ergebnis:


let
    Source = Json.Document(Web.Contents("<a href='https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&amp;outFields=*&amp;outSR=4326&amp;f=json"))'>https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&amp;outFields=*&amp;outSR=4326&amp;f=json"))</a>,
    features = Source[features],
    convertedtoTable = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedColumn = Table.ExpandRecordColumn(convertedtoTable, "Column1", {"attributes"}, {"Column1.attributes"}),
    expandedAttributes = Table.ExpandRecordColumn(expandedColumn, "Column1.attributes", {"IdBundesland", "Bundesland", "Landkreis", "Altersgruppe", "Geschlecht", "AnzahlFall", "AnzahlTodesfall", "ObjectId", "Meldedatum", "IdLandkreis", "Datenstand", "NeuerFall", "NeuerTodesfall"}, {"IdBundesland", "Bundesland", "Landkreis", "Altersgruppe", "Geschlecht", "AnzahlFall", "AnzahlTodesfall", "ObjectID", "Meldedatum", "IdLandkreis", "Datenstand", "NeuerFall", "NeuerTodesfall"}),
    ChangedType = Table.TransformColumnTypes(expandedAttributes,{{"IdBundesland", Int64.Type}, {"AnzahlFall", Int64.Type}, {"AnzahlTodesfall", Int64.Type}, {"IdLandkreis", Int64.Type}, {"Datenstand", type datetime}, {"NeuerFall", Int64.Type}, {"NeuerTodesfall", Int64.Type}}),
    AddedCustom = Table.AddColumn(ChangedType, "Benutzerdefiniert", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 8, 0,[Meldedatum]/1000)),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"Meldedatum"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Benutzerdefiniert", "Meldedatum"}})&nbsp;&nbsp;&nbsp;
in
    RenamedColumns

Er außer diesem “Gesamt”-Dataset auch noch die aktuellen Datasets für Bundesland bzw. Landkreis ausprobieren will, wird sehr schnell merken, dass die Entwickler hier ein wenig für Verwirrung sorgen wollten, da der Aufbau inkl. der Feldnamen komplett unterschiedlich ist – sonst wäre es ja auch zu einfach. Deshalb hier die Power Query-Abfragen für die Dataset für Bundesländer und Landkreise:

Bundesland


let
    Source = Json.Document(Web.Contents("<a href='https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/Coronaf%C3%A4lle_in_den_Bundesl%C3%A4ndern/FeatureServer/0/query?where=1%3D1&amp;outFields=*&amp;outSR=4326&amp;f=json"))'>https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/Coronaf%C3%A4lle_in_den_Bundesl%C3%A4ndern/FeatureServer/0/query?where=1%3D1&amp;outFields=*&amp;outSR=4326&amp;f=json"))</a>,
     features = Source[features],
    convertedtoTable = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedColumn = Table.ExpandRecordColumn(convertedtoTable, "Column1", {"attributes"}, {"Column1.attributes"}),
    expandedAttributes = Table.ExpandRecordColumn(expandedColumn, "Column1.attributes", {"OBJECTID_1", "LAN_ew_AGS", "LAN_ew_GEN", "LAN_ew_BEZ", "LAN_ew_EWZ", "OBJECTID", "Fallzahl", "Aktualisierung", "AGS_TXT", "GlobalID", "faelle_100000_EW", "Shape__Area", "Shape__Length", "Death"}, {"OBJECTID_1", "LAN_ew_AGS", "LAN_ew_GEN", "LAN_ew_BEZ", "LAN_ew_EWZ", "OBJECTID", "Fallzahl", "Aktualisierung", "AGS_TXT", "GlobalID", "faelle_100000_EW", "Shape__Area", "Shape__Length", "Death"}),
    AddedCustom = Table.AddColumn(expandedAttributes, "Benutzerdefiniert", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 8, 0,[Aktualisierung]/1000)),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"Aktualisierung"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Benutzerdefiniert", "Aktualisierung"}})&nbsp;&nbsp;&nbsp;
in
    RenamedColumns

Landkreis


let
    Source = Json.Document(Web.Contents("<a href='https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?where=1%3D1&amp;outFields=*&amp;outSR=4326&amp;f=json"))'>https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?where=1%3D1&amp;outFields=*&amp;outSR=4326&amp;f=json"))</a>,
    features = Source[features],
    convertedtoTable = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedColumn = Table.ExpandRecordColumn(convertedtoTable, "Column1", {"attributes"}, {"Column1.attributes"}),
    expandedAttributes = Table.ExpandRecordColumn(expandedColumn, "Column1.attributes", {"ADE", "AGS", "AGS_0", "BEM", "BEZ", "BL", "BL_ID", "BSG", "cases", "cases_per_100k", "cases_per_population", "county", "death_rate", "deaths", "DEBKG_ID", "EWZ", "FK_S3", "GEN", "GF", "IBZ", "KFL", "last_update", "NBD", "NUTS", "OBJECTID", "RS", "RS_0", "SDV_RS", "Shape__Area", "Shape__Length", "SN_G", "SN_K", "SN_L", "SN_R", "SN_V1", "SN_V2", "WSK"}, {"ADE", "AGS", "AGS_0", "BEM", "BEZ", "BL", "BL_ID", "BSG", "cases", "cases_per_100k", "cases_per_population", "county", "death_rate", "deaths", "DEBKG_ID", "EWZ", "FK_S3", "GEN", "GF", "IBZ", "KFL", "last_update", "NBD", "NUTS", "OBJECTID", "RS", "RS_0", "SDV_RS", "Shape__Area", "Shape__Length", "SN_G", "SN_K", "SN_L", "SN_R", "SN_V1", "SN_V2", "WSK"})
in
    expandedAttributes

In Power Query habe ich alle Felder der beiden Datasets übernommen. Möglicherweise macht es ja Sinn, dort nur solche Felder zu verwenden, die ihr für eure Aufgabenstellung benötigt.

Summary

Auch wenn die vorhandenen Datenquellen nur sehr spärlich und deren Exaktheit möglicherweise zu wünschen übrig lässt, so sollten wir Data Analysts/Data Scientists nicht davor zurückschrecken, diese Zahlen weiter zu analysieren. Möglicherweise erkennt ja jemand Zusammenhänge, die dazu beitragen die Erkennungsrate dieses Virus zu verbessern, die Todesrate zu reduzieren oder sogar die Pandemie einzudämmen. Unterscheidet jedoch dabei zwischen einem reinem Zusammenhang, d.h. einer Korrelation zwischen zwei Variablen, und einer tatsächlichen Auswirkung von einer auf die andere Variable, d.h. einer Kausalität, besteht noch ein großer Unterschied, der in diesem Artikel behandelt wird –> um dies an einem Beispiel für alle Nicht-Statistiker zu verdeutlichen: Die Größe der Population von Störche und der Geburtenrate des Menschen kann unter Umständen für bestimmte Gebiete korrelieren ohne das dazwischen wirklich eine Kausalität besteht (außer natürlich für die, die immer noch an die Geschichte vom Klapperstorch glauben) –> siehe dazu auch http://www3.math.uni-paderborn.de/~agbiehler/sis/sisonline/struktur/jahrgang21-2001/heft2/Langfassungen/2001-2_Matth.pdf.

Über

Die IT-Welt wird immer komplexer und zwischen den einzelnen Komponenten gibt es immer mehr Abhängigkeiten. Nachdem ich durch meine tägliche Arbeit immer wieder vor der Herausforderung stehe, komplexe Probleme zu lösen, möchte ich diese Seite dafür verwenden, Euch den einen oder anderen Tipp zu geben, wenn Ihr vor ähnlichen Aufgabenstellungen steht.

Veröffentlicht in Allgemein

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Follow Sylvio's Infobox on WordPress.com
März 2020
M D M D F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
%d Bloggern gefällt das: