Deutsch
Support

2024-10-09

Open-Government-Daten mit duckplyr analysieren

Hannes Mühleisen

Zur duckplyr-Dokumentation: duckplyr.tidyverse.org.

Daten zu wranglen, indem man SQL-Strings darauf wirft, ist nicht der ergonomischste Weg, interaktive Datenanalyse in R zu machen. Seit einer Weile arbeiten wir mit dem dplyr-Projektteam bei Posit (früher RStudio) und Kirill Müller an duckplyr. duckplyr ist ein hochperformanter Drop-in-Ersatz für dplyr, angetrieben von DuckDB. Mehr über duckplyr steht im Ankündigungs-Blogpost. In diesem Beitrag gehen wir einen anspruchsvollen realen Einsatzfall mit duckplyr durch. Wer mitmachen möchte, findet ein Google-Colab-Notebook mit allen Code-Snippets in diesem Beitrag. Die unten berichteten Zeiten stammen ebenfalls aus Colab.

Wie viele amtliche Statistikbehörden stellt Neuseelands „Stats NZ Tatauranga Aotearoa“ dankenswerterweise einige der von ihnen gepflegten Datensätze als Open Data zum Download bereit. Die größte auf der Seite verfügbare Datei enthält „Age and sex by ethnic group (grouped total responses), for census usually resident population counts, 2006, 2013, and 2018 Censuses“, CSV-Zip-Datei.

Wir können diese Datei herunterladen (gespiegelt von unserem CDN, wir wollen arme Stats NZ nicht DDoS-en) und so entpacken:

download.file("https://blobs.duckdb.org/nzcensus.zip", "nzcensus.zip")
unzip("nzcensus.zip")

Schauen wir uns die CSV-Dateien im Zip und ihre Größen an:

file.info(Sys.glob("*.csv"))["size"]
                               size
Data8277.csv              857672667
DimenLookupAge8277.csv         2720
DimenLookupArea8277.csv       65400
DimenLookupEthnic8277.csv       272
DimenLookupSex8277.csv           74
DimenLookupYear8277.csv          67

Wie wir sehen, gibt es eine große (~800 MB) Data-Datei und eine Reihe von Dimen...-Dimensionsdateien. Das ist ein recht übliches Datenlayout, manchmal „Star Schema“ genannt. Daraus ist klar, dass in unserer Zukunft ein paar Joins stehen. Aber zuerst konzentrieren wir uns auf die Hauptdatei, Data8277.csv. Das Lesen großer CSV-Dateien ist nicht trivial und kann sehr frustrierend sein. Aber genug gejammert, wie die Kiwis sagen würden.

Lassen Sie uns zunächst kurz schauen, wie die Datei aussieht:

cat(paste(readLines("Data8277.csv", n=10), collapse="\n"))
Year,Age,Ethnic,Sex,Area,count
2018,000,1,1,01,795
2018,000,1,1,02,5067
2018,000,1,1,03,2229
2018,000,1,1,04,1356
2018,000,1,1,05,180
2018,000,1,1,06,738
2018,000,1,1,07,630
2018,000,1,1,08,1188
2018,000,1,1,09,2157

Bisher sieht das eher zahm aus, es scheinen fünf Spalten zu sein. Dankenswerterweise haben sie Namen. Beim bloßen Anschauen der Spaltenwerte sieht es so aus, als wären sie alle numerisch und sogar Integer-Werte. Der Schein kann aber trügen, und die Spalten Age, Area, count enthalten irgendwo weiter unten Zeichenwerte. Fun Fact: Wir müssen bis Zeile 431 741 warten, bis die Spalte Area einen Nicht-Integer-Wert enthält. Offensichtlich brauchen wir einen guten CSV-Parser. R hat keinen Mangel an CSV-Readern, zum Beispiel enthält das Paket readr einen flexiblen CSV-Parser. Diese Datei mit readr zu lesen dauert etwa eine Minute (auf Colab).

Aber jetzt fangen wir an, DuckDB und duckplyr zu nutzen. Zuerst installieren wir duckplyr (und DuckDB, das eine Abhängigkeit ist):

install.packages("duckplyr")
duckdb:::sql("SELECT version()")

Dieser Befehl gibt die installierte DuckDB-Version aus; zum Zeitpunkt dieses Schreibens ist die neueste Version auf CRAN 1.1.0. Wir können jetzt DuckDBs fortgeschrittene Data-Wrangling-Fähigkeiten nutzen. Zuerst enthält DuckDB wahrscheinlich den weltweit fortschrittlichsten CSV-Parser. Für die Extra-Neugierigen: Hier ist eine Präsentation zu DuckDBs CSV-Parser. Wir nutzen DuckDBs CSV-Reader, um nur die ersten 10 Zeilen aus der CSV-Datei zu lesen:

duckdb:::sql("FROM Data8277.csv LIMIT 10")
   Year Age Ethnic Sex Area count
1  2018 000      1   1   01   795
2  2018 000      1   1   02  5067
3  2018 000      1   1   03  2229
4  2018 000      1   1   04  1356
5  2018 000      1   1   05   180
6  2018 000      1   1   06   738
7  2018 000      1   1   07   630
8  2018 000      1   1   08  1188
9  2018 000      1   1   09  2157
10 2018 000      1   1   12   177

Das dauert nur ein paar Millisekunden, weil DuckDBs CSV-Reader Ergebnisse streaming erzeugt, und weil wir nur 10 Zeilen angefordert haben, sind wir ziemlich schnell fertig.

DuckDB kann mit dem Schlüsselwort DESCRIBE auch das Schema ausgeben, das es aus der CSV-Datei erkannt hat:

duckdb:::sql("DESCRIBE FROM Data8277.csv")
  column_name column_type ...
1        Year      BIGINT ...
2         Age     VARCHAR ...
3      Ethnic      BIGINT ...
4         Sex      BIGINT ...
5        Area     VARCHAR ...
6       count     VARCHAR ...

Wir sehen, dass wir die verschiedenen Datentypen für die Spalten korrekt erkannt haben. Wir können das Schlüsselwort SUMMARIZE nutzen, um verschiedene Zusammenfassungsstatistiken für alle Spalten in der Datei zu berechnen:

duckdb:::sql("SUMMARIZE FROM Data8277.csv")

Das dauert etwas länger, aber die Ergebnisse sind sehr interessant:

# A tibble: 6 × 12
  column_name column_type min   max     approx_unique avg      std   q25   q50
  <chr>       <chr>       <chr> <chr>           <dbl> <chr>    <chr> <chr> <chr>
1 Year        BIGINT      2006  2018                3 2012.33… 4.92… 2006  2013
2 Age         VARCHAR     000   999999            149 NA       NA    NA    NA
3 Ethnic      BIGINT      1     9999               11 930.545… 2867… 3     6
4 Sex         BIGINT      1     9                   3 4.0      3.55… 1     2
5 Area        VARCHAR     001   DHB9999          2048 NA       NA    NA    NA
6 count       VARCHAR     ..C   9999            16825 NA       NA    NA    NA
# ℹ 3 more variables: q75 <chr>, count <dbl>, null_percentage <dbl>

Das zeigt wieder die Spaltennamen und ihre Typen, aber auch die Zusammenfassungsstatistiken für Minimum und Maximum, ungefähre Zahl eindeutiger Werte, Durchschnitt, Standardabweichungen, 25-, 50- und 75-Quantile und den Prozentsatz von NULL/NA-Werten. Man bekommt also einen ziemlich guten Überblick, wie die Daten aussehen.

Aber wir sind nicht hier, um Zusammenfassungsstatistiken anzustarren, wir wollen eine echte Analyse der Daten machen. In diesem Einsatzfall wollen wir die Zahl der Nicht-Europäer zwischen 20 und 40 berechnen, die im Gebiet Auckland leben, anhand der Census-Daten 2018, und die Ergebnisse sollen nach Geschlecht gruppiert sein. Dafür müssen wir die Dimensions-CSV-Dateien mit der Hauptdatendatei joinen, um die Dimensionswerte richtig zu filtern. In SQL, der Lingua Franca der großskaligen Datenanalyse, sieht das so aus:

Zuerst joinen wir alles zusammen:

FROM 'Data8277.csv' data
JOIN 'DimenLookupAge8277.csv' age ON data.Age = age.Code
JOIN 'DimenLookupArea8277.csv' area ON data.Area = area.Code
JOIN 'DimenLookupEthnic8277.csv' ethnic ON data.Ethnic = ethnic.Code
JOIN 'DimenLookupSex8277.csv' sex ON data.Sex = sex.Code
JOIN 'DimenLookupYear8277.csv' year ON data.Year = year.Code

Als Nächstes nutzen wir die SELECT-Projektion, um ein paar grundlegende Umbenennungen und Datenbereinigungen durchzuführen:

SELECT
    year.Description AS year_,
    area.Description AS area_,
    ethnic.Description AS ethnic_,
    sex.Description AS sex_,
    TRY_CAST(replace(age.Description, ' years', '') AS INTEGER) AS age_,
    TRY_CAST(data.count AS INTEGER) AS count_

Der Datensatz enthält verschiedene Totals, die wir entfernen, bevor wir weitermachen:

WHERE count_ > 0
  AND age_ IS NOT NULL
  AND area_ NOT LIKE 'Total%'
  AND ethnic_ NOT LIKE 'Total%'
  AND sex_ NOT LIKE 'Total%'

Wir packen die vorherigen Anweisungen als Common-Table-Expression expanded_cleaned_data und können dann die eigentliche Aggregation mit DuckDB berechnen:

SELECT sex_, sum(count_) AS group_count
FROM expanded_cleaned_data
WHERE age_ BETWEEN 20 AND 40
  AND area_ LIKE 'Auckland%'
  AND ethnic_ <> 'European'
  AND year_ = 2018
GROUP BY sex_
ORDER BY sex_

Das dauert ca. 20 s auf dem begrenzten freien Colab-Tier. Das Ergebnis ist:

    sex_ group_count
1 Female      398556
2   Male      397326

Soweit, so gut. SQL-Abfragen zu schreiben ist aber nicht für jeden. Die Ergonomie, SQL-Strings in einer interaktiven Datenanalyseumgebung wie R zu erzeugen, ist gelinde gesagt fragwürdig. Frameworks wie dplyr haben gezeigt, wie Data-Wrangling-Ergonomie massiv verbessert werden kann. Drücken wir unsere Analyse also mit dplyr aus, nachdem wir die Daten zuerst aus CSV in den RAM gelesen haben:

library(dplyr)


data   <- readr::read_csv("Data8277.csv")
age    <- readr::read_csv("DimenLookupAge8277.csv")
area   <- readr::read_csv("DimenLookupArea8277.csv")
ethnic <- readr::read_csv("DimenLookupEthnic8277.csv")
sex    <- readr::read_csv("DimenLookupSex8277.csv")
year   <- readr::read_csv("DimenLookupYear8277.csv")


expanded_cleaned_data <- data |>
  filter(grepl("^\\d+$", count)) |>
  mutate(count_ = as.integer(count)) |>
  filter(count_ > 0) |>
  inner_join(
    age |>
      filter(grepl("^\\d+ years$", Description)) |>
      mutate(age_ = as.integer(Code)),
    join_by(Age == Code)
  ) |>
  inner_join(area |>
    mutate(area_ = Description) |>
    filter(!grepl("^Total", area_)), join_by(Area == Code)) |>
  inner_join(ethnic |>
    mutate(ethnic_ = Description) |>
    filter(!grepl("^Total", ethnic_)), join_by(Ethnic == Code)) |>
  inner_join(sex |>
    mutate(sex_ = Description) |>
    filter(!grepl("^Total", sex_)), join_by(Sex == Code)) |>
  inner_join(year |> mutate(year_ = Description), join_by(Year == Code))


# create final aggregation, still completely lazily
twenty_till_forty_non_european_in_auckland_area <-
  expanded_cleaned_data |>
  filter(
    age_ >= 20, age_ <= 40,
    grepl("^Auckland", area_),
    year_ == "2018",
    ethnic_ != "European"
  ) |>
  summarise(group_count = sum(count_), .by = sex_) |> arrange(sex_)


print(twenty_till_forty_non_european_in_auckland_area)

Das sieht schöner aus und ist in ca. einer Minute fertig, hat aber mehrere versteckte Probleme. Erstens lesen wir den gesamten Datensatz in den RAM. Für diesen Datensatz ist das wahrscheinlich möglich, weil die meisten Computer mehr als 1 GB RAM haben, für größere Datensätze funktioniert das natürlich nicht. Dann führen wir eine Reihe von dplyr-Verbs aus. dplyr führt diese aber eagerly aus, das heißt es optimiert die Folge von Verbs nicht ganzheitlich. Es kann zum Beispiel nicht sehen, dass wir im letzten Schritt alle nicht-europäischen Ethnizitäten herausfiltern, und berechnet all das trotzdem für das Zwischenergebnis. Dasselbe passiert mit Survey-Jahren, die nicht 2018 sind; erst im letzten Schritt filtern wir die heraus. Wir haben einen teuren Join über alle anderen Jahre umsonst berechnet. Je nach Datenverteilung kann das extrem verschwenderisch sein. Und ja, man kann die Filter manuell herumschieben, das ist aber mühsam und fehleranfällig. Wenigstens ist das Ergebnis genau dasselbe wie die SQL-Version oben:

# A tibble: 2 × 2
  sex_   group_count
  <chr>        <int>
1 Female      398556
2 Male        397326

Jetzt stellen wir dasselbe Skript auf duckplyr um. Statt die CSV-Dateien vollständig mit readr in den RAM zu lesen, nutzen wir die Funktion duckplyr_df_from_csv aus duckplyr:

library("duckplyr")


data   <- duckplyr_df_from_csv("Data8277.csv")
age    <- duckplyr_df_from_csv("DimenLookupAge8277.csv")
area   <- duckplyr_df_from_csv("DimenLookupArea8277.csv")
ethnic <- duckplyr_df_from_csv("DimenLookupEthnic8277.csv")
sex    <- duckplyr_df_from_csv("DimenLookupSex8277.csv")
year   <- duckplyr_df_from_csv("DimenLookupYear8277.csv")

Das dauert genau 0 Sekunden, weil duckplyr eigentlich nicht viel tut. Wir erkennen das Schema der CSV-Dateien mit unserem preisgekrönten „Sniffer“ und legen die sechs Placeholder-Objekte für jede dieser Dateien an. Teil des einzigartigen Designs von duckplyr ist, dass diese Objekte „Heisenbergsche“ sind: Sie verhalten sich wie völlig normale R-data.frames, sobald sie als solche behandelt werden, können aber auch als Lazy-Evaluation-Placeholder wirken, wenn sie an nachgelagerte Analyseschritte übergeben werden. Das wird durch ein wenig bekanntes R-Feature namens ALTREP möglich, das unter anderem erlaubt, R-Vektoren on-demand zu berechnen.

Jetzt führen wir dieselbe dplyr-Pipeline wie oben erneut aus. Nur diesmal sind wir in weniger als einer Sekunde „fertig“. Das liegt daran, dass wir nur lazy einen sogenannten Relation Tree konstruiert haben, der die Gesamtheit der Transformationen kapselt. Das erlaubt ganzheitliche Optimierung, zum Beispiel das Pushen von Jahr und Ethnizität ganz nach unten zum Lesen der CSV-Datei vor dem Joinen. Wir können auch das Lesen von Spalten eliminieren, die in der Abfrage gar nicht genutzt werden.

Erst wenn wir schließlich das Ergebnis ausgeben

print(twenty_till_forty_non_european_in_auckland_area)

wird die eigentliche Berechnung ausgelöst. Das ist in derselben Zeit fertig wie die handgeschriebene SQL-Abfrage oben, nur dass wir diesmal eine deutlich angenehmere Erfahrung durch die dplyr-Syntax hatten. Und dankenswerterweise ist das Ergebnis immer noch genau dasselbe.

Dieser Einsatzfall wurde auch als Teil meiner Keynote auf der diesjährigen posit::conf vorgestellt:

Schließlich sollten wir anmerken, dass duckplyr noch in Entwicklung ist. Wir haben große Sorgfalt darauf verwendet, nichts zu kaputtzumachen, und fallen auf die bestehende dplyr-Implementierung zurück, wenn etwas (noch) nicht in DuckDB laufen kann. Wir würden uns aber freuen, von Ihnen zu hören, wenn etwas nicht wie erwartet funktioniert.