Diagramme mit Daten aus Datenbanken in R: dbplot

Diagrammerstellung mit großen Datenmengen aus Datenbanken kann herausfordernd sein. Wie geht das möglichst effizient?

Ad-hoc-Datenbank im Arbeitsspeicher

Wir nutzen Daten über weltweite Chart-Erfolge von Songs und Alben und packen sie in eine Ad-hoc-Datenbank im Arbeitsspeicher. Das genügt, um die Ideen zu demonstrieren, und erspart Aufwand mit der Infrastruktur. Wie effizient die Diagrammerstellung erfolgt, hängt wesentlich davon ab, welche Datenmengen in den Arbeitsspeicher von R verschoben werden müssen. Viele Visualisierungen basieren auf aggregierten Daten. Daher ist es sinnvoll, diese Aggregation möglichst in der Datenbank auszuführen.

Die Daten stammen von der Webseite tsort.info und können auch von meinem github-Profil heruntergeladen werden. Dort liegt auch der R-Code in Form einer xaringan-Präsentation: Diagramme-aus-Datenbanken.Rmd.

Die Daten dürfen frei genutzt werden, wenn man die Quelle angibt, zu tsort.info verlinkt und die Versionsnummer nennt: hier ist es die 2-8-0044.

library(tidyverse)
library(dbplot)
library(DBI)
library(knitr)
library(ggthemes)

music <- readRDS("musicdata.rds")

Die ad-hoc-Datenbank (SQLite) wird so angelegt – entscheidend ist „:memory:“:

con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

dplyr::copy_to(con, music, "musicdb",
    temporary = FALSE,
    indexes = list(
        c("year", "score")
    )
)

Zunächst beschränke ich mich auf die Top 5 Künstler / Bands im Sinne der größten Gesamtpunktzahl, wobei Punkte den Erfolg in den weltweiten Charts widerspiegeln.

Balkendiagramm: Die ineffiziente Art

Hier ein erstes Balkendiagramm:

top5 <- musicdb %>% 
  filter(artist != "Original Soundtrack") %>% 
  group_by(artist) %>% 
  summarise(total = sum(score)) %>% 
  slice_max(order_by = total, n = 5) %>% 
  pull(artist) 

theme_set(theme_economist(base_size = 14))

musicdb %>% 
  filter(artist %in% top5) %>% 
  ggplot(aes(x = fct_infreq(artist))) +
    geom_bar() +
    labs(title = "Top 5: # Songs and Albums",
         x = "")

Der Vektor top5 enthält die 5 Bands / Künstler mit den höchsten Punktzahlen.

Das Vorgehen hier ist ineffizient: Alle Songs / Alben der Top 5 werden von der Datenbank nach R transferiert, dort erfolgt die Aggregation, d. h. Berechnung der Balkenhöhen durch geom_bar().

Manuelle Berechnung der Aggregation in der Datenbank

Effizienter ist es, die Aggregation in der Datenbank auszuführen. Um das obenstehende Diagramm zu erhalten, genügen 5 Datenpunkte:

musicdb %>% 
  filter(artist %in% top5) %>% 
  group_by(artist) %>%   
  tally() %>%   
  arrange(desc(n)) %>% 
  collect() %>% 
  ggplot(aes(x = fct_inorder(artist))) +
    geom_col(aes(y = n)) +
    labs(title = "Top 5: # Songs and Albums",
         x = "")

Eine Stärke des dplyr-Pakets aus dem tidyverse ist, dass man Daten in einer Datenbank genau so ansprechen kann wie Daten im R-Arbeitsspeicher. Das dbplyr-Paket übersetzt den dplyr-Code in SQL und kommuniziert mit der Datenbank. Dabei wird die Ausführung des SQL-Codes so lange wie möglich verzögert (lazy evaluation), um den SQL-Code zu optimieren und unnötige Wartezeiten zu vermeiden. Mit collect() werden die Ergebnisse der vorhergehenden Berechnungen explizit angefordert. fct_inorder() aus dem forcats-Paket definiert die factor levels nach Reihenfolge im Datensatz – zuvor wurde absteigend sortiert.

Im ggplot-Aufruf ersetzt nun geom_col() das zuvor genutzte geom_bar(): ersteres greift auf aggregierte Daten zu (die Häufigkeiten sind in der Variable n abgebildet), letzteres führt die Aggregation selbst durch.

Das dbplot-Paket: Diagramme aus Datenbanken in R

Nachteil der oben gezeigten Methode: Der Anwender muss sich in manuellen Arbeitsschritten um die Datenaggregation kümmern. Einfacher ist es, auf vordefinierte Funktionen zuzugreifen, die das übernehmen. Genau das ist der Zweck des dbplot-Pakets von Edgar Ruiz.

Hier ein kürzerer Weg zu einem Balkendiagramm, bei dem ebenfalls die Aggregation in der Datenbank ausgeführt wird:

musicdb %>% 
  filter(artist %in% top5) %>% 
  dbplot_bar(artist) +
     labs(title = "Top 5: # Songs and Albums",
         x = "", y = "n")

dbplot_bar() kümmert sich um die Details!

Leider sind die Balken nun nicht mehr absteigend nach Häufigkeiten sortiert. Bei diesem Ansatz stehen nicht alle Funktionen zur Datenbearbeitung zur Verfügung, so etwa das oben genutzte forcats::fct_inorder().

Möchte man mehr Flexibilität erreichen und dabei gleichzeitig Arbeit gegenüber dem Ansatz ohne dbplot sparen, kann man auf spezielle Aggregationsfunktionen zugreifen:

musicdb %>% 
  filter(artist %in% top5) %>% 
  db_compute_count(artist) %>%
  arrange(desc(`n()`)) %>% 
  ggplot(aes(x = fct_inorder(artist), y = `n()`)) +
     geom_col() +
     labs(title = "Top 5: # Songs and Albums",
         x = "", y = "n")

Hier habe ich beides: Eine etwas kürzere Syntax und absteigend sortierte Balken. Die Aggregationsfunktionen in dbplot beginnen mit db_compute. Auch hier werden nur fünf Datenpunkte aus der Datenbank nach R verschoben.

Histogramm mit dbplot

Bei anderen Diagrammtypen ist es einfacher, wenn man sich nicht um Details wie Sortierung der Kategorien kümmern muss. Hier ein Histogramm:

musicdb %>% 
  dbplot_histogram(year, bins = 40) +
  labs(title = "Histogram of Years",
       subtitle = "Publication of Songs / Albums")

Mit wenig Code erhält man ein Histogramm, wobei erneut nur die aggregierten Daten (Anzahl pro Kategorie) nach R transferiert werden – die Berechnung der Kategoriengrenzen und Häufigkeiten erfolgt innerhalb der Datenbank.

Streudiagramm vs. Raster Plot

Ein herausfordernder Diagrammtyp ist das Streu- oder Punktdiagramm (scatterplot): da jeder Datenpunkt dargestellt wird, muss auch jeder Datenpunkt dem Grafikprogramm, in unserem Fall R, zur Verfügung stehen. Hier untersuchen wir den Zusammenhang zwischen den Punktzahlen der Songs / Alben (y-Achse) und dem Veröffentlichungsjahr (x-Achse), um zeitabhängige Trends zu prüfen:

musicdb %>% 
  ggplot(aes(x = year, y = score)) +
  geom_point(alpha = 0.1) +
  labs(title = "Scores by Year",
       subtitle = "Scatterplot")

Mit der hohen Transparenz von alpha = 0.1 wirken wir dem Overplotting entgegen: die Daten enthalten gerade im niedrigen Punktebereich sehr viele sich überlappende Datenpunkte, hier durch die Schwarzfärbung erkennbar im Gegensatz zu den helleren Bereichen mit wenigen Datenpunkten bei höheren Punktzahlen.

Eine effizientere Alternative stellt das Raster Plot dar: Ähnlich dem Histogramm fasst es Datenbereiche in Kategorien zusammen und berechnet aggregierte Werte pro Kategorie. Im Unterschied zum Histogramm geschieht das hier zweidimensional: Eine Kategorie wird durch zwei Achsen (x und y) definiert. Hier ein Beispiel:

musicdb %>% 
  dbplot_raster(x = year, y = score,
                resolution = 40) +
  labs(title = "Scores by Year",
       subtitle = "Raster Plot") +
  theme(legend.key.width = unit(3, "cm"),
        legend.position = "bottom")

Dank dbplot findet die Aggregation wiederum in der Datenbank statt und es müssen nun wesentlich weniger Datenpunkte nach R transferiert und von ggplot2 dargestellt werden.

Diese zugegeben gröbere Darstellung reicht aus, um anhand der Hellfärbung im unteren Diagrammbereich entlang der x-Achse die Regionen zu erkennen, in die sehr viele Songs / Alben fallen. Ähnlich wie mit den bins beim Histogramm empfiehlt es sich, gröbere und feinere Raster zu vergleichen mit dem Parameter resolution (Auflösung).

Fazit: Stärken und Schwächen von dbplot

dbplot kann die Diagrammerstellung mit Daten aus Datenbanken sehr viel effizienter gestalten. Es stehen vordefinierte Funktionen zur Verfügung, die Datenaggregation in der Datenbank ausführen und somit den Datentransfer von der Datenbank zu R wesentlich minimieren. Dabei gibt es zwei Gruppen von Funktionen: für die Diagrammerstellung sowie für Berechnungen (Aggregation). Letzteres ist sinnvoll, wenn man die berechneten Daten erst selbst weiterverarbeiten und nicht unmittelbar visualisieren will.

Allerdings ist die Funktionalität gegenüber ggplot2 stark eingeschränkt, es werden nur wenige häufig genutzte Diagrammtypen unterstützt:

• dbplot_bar()
• dbplot_histogram()
• dbplot_line()
• dbplot_raster()
• dbplot_boxplot()

Folgende Berechnungsfunktionen stehen zur Verfügung:

• db_compute_count()
• db_compute_bins()
• db_compute_raster()
• db_compute_raster2(): ergänzt Koordinaten der x / y-Boxen
• db_compute_boxplot()

Wer hier fündig wird, verfügt über eine effiziente Alternative zum umfangreichen Datentransfer von der Datenbank zu R. Das ist besonders hilfreich, wenn die Daten zu groß für den Arbeitsspeicher werden.

Wie sind Eure Erfahrungen mit R und Datenbanken?

Mehr Informationen zu R und Datenbanken gibt es bei RStudio. Dort gibt es auch aufschlussreiche Webinare / Videos.

ggplot2: Elegant Graphics for Data Analysis (Use R!)

R for Data Science

Storytelling mit Daten – Cole Nussbaumer Knaflic