Dieser Artikel ist Teil 2 von 2 aus der Artikelserie R Data Frames meistern mit dplyr.
Noch mehr Datenbank-Features
Im ersten Teil dieser Artikel-Serie habe ich die Parallelen zwischen Data Frames in R und Relationen in SQL herausgearbeitet und gezeigt, wie das Paket dplyr eine Reihe von SQL-analogen Operationen auf Data Frames standardisiert und optimiert. In diesem Teil möchte ich nun drei weitere Analogien aufzeigen. Es handelt sich um die
- Window Functions in dplyr als Entsprechung zu analytischen Funktionen in SQL,
- Joins zwischen Data Frames als Pendant zu Tabellen-Joins
- Delegation von Data Frame-Operationen zu einer bestehenden SQL-Datenbank
Window Functions
Im letzten Teil habe ich gezeigt, wie durch die Kombination von group_by() und summarise() im Handumdrehen Aggregate entstehen. Das Verb group_by() schafft dabei, wie der Name schon sagt, eine Gruppierung der Zeilen des Data Frame anhand benannter Schlüssel, die oft ordinaler oder kategorialer Natur sind (z.B. Datum, Produkt oder Mitarbeiter).
Ersetzt man die Aggregation mit summarise() durch die Funktion mutate(), um neue Spalten zu bilden, so ist der Effekt des group_by() weiterhin nutzbar, erzeugt aber „Windows“, also Gruppen von Datensätzen des Data Frames mit gleichen Werten der Gruppierungskriterien. Auf diesen Gruppen können nun mittels mutate() beliebige R-Funktionen angewendet werden. Das Ergebnis ist im Gegensatz zu summarise() keine Verdichtung auf einen Datensatz pro Gruppe, sondern eine Erweiterung jeder einzelnen Zeile um neue Werte. Das soll folgendes Beispiel verdeutlichen:
library(dplyr)
set.seed(42)
df <- data.frame(id = 1:20,
a=sample(c("Hund","Katze","Maus","Tiger"),20,replace=T),
b=sample(1:10,20, replace = T))
df
id a b
1 1 Maus 7
2 2 Hund 3
3 3 Katze 3
4 4 Maus 4
5 5 Tiger 10
6 6 Maus 10
7 7 Hund 8
8 8 Hund 8
9 9 Hund 6
10 10 Katze 1
11 11 Maus 7
12 12 Hund 9
13 13 Hund 8
14 14 Tiger 5
15 15 Tiger 6
16 16 Maus 6
17 17 Katze 1
18 18 Maus 4
19 19 Maus 7
20 20 Maus 9
df %>%
group_by(a) %>%
mutate(r = row_number(), # aus dplyr
n_memb = n(), # aus dplyr
n_dist = n_distinct(b), # aus dplyr
ra=rank(desc(b)), # aus base und dplyr
last_b = lag(b), # aus dplyr
next_b = lead(b), # aus dplyr
mb = mean(b), # aus base
cs = cumsum(b) ) # aus base
Source: local data frame [20 x 11]
Groups: a [4]
id a b r n_memb n_dist ra last_b next_b mb cs
1 1 Maus 7 1 8 5 4.0 NA 4 6.750000 7
2 2 Hund 3 1 6 4 6.0 NA 8 7.000000 3
3 3 Katze 3 1 3 2 1.0 NA 1 1.666667 3
4 4 Maus 4 2 8 5 7.5 7 10 6.750000 11
5 5 Tiger 10 1 3 3 1.0 NA 5 7.000000 10
6 6 Maus 10 3 8 5 1.0 4 7 6.750000 21
7 7 Hund 8 2 6 4 3.0 3 8 7.000000 11
8 8 Hund 8 3 6 4 3.0 8 6 7.000000 19
9 9 Hund 6 4 6 4 5.0 8 9 7.000000 25
10 10 Katze 1 2 3 2 2.5 3 1 1.666667 4
11 11 Maus 7 4 8 5 4.0 10 6 6.750000 28
12 12 Hund 9 5 6 4 1.0 6 8 7.000000 34
13 13 Hund 8 6 6 4 3.0 9 NA 7.000000 42
14 14 Tiger 5 2 3 3 3.0 10 6 7.000000 15
15 15 Tiger 6 3 3 3 2.0 5 NA 7.000000 21
16 16 Maus 6 5 8 5 6.0 7 4 6.750000 34
17 17 Katze 1 3 3 2 2.5 1 NA 1.666667 5
18 18 Maus 4 6 8 5 7.5 6 7 6.750000 38
19 19 Maus 7 7 8 5 4.0 4 9 6.750000 45
20 20 Maus 9 8 8 5 2.0 7 NA 6.750000 54
Das group_by() unterteilt den Data Frame nach den 4 gleichen Werten von a. Innerhalb dieser Gruppen berechnen die beispielsweise eingesetzten Funktionen
- row_number(): Die laufende Nummer in dieser Gruppe
- n(): Die Gesamtgröße dieser Gruppe
- n_distinct(b): Die Anzahl verschiedener Werte von b innerhalb der Gruppe
- rank(desc(b)): Den Rang innerhalb der selben Gruppe, absteigend nach b geordnet
- lag(b): Den Wert von b der vorherigen Zeile innerhalb derselben Gruppe
- lead(b): Analog den Wert von b der folgenden Zeile innerhalb derselben Gruppe
- mean(b): Den Mittelwert von b innerhalb der Gruppe
- cumsum(b): Die kumulierte Summe der b-Werte innerhalb der Gruppe.
Wichtig ist hierbei, dass die Anwendung dieser Funktionen nicht dazu führt, dass die ursprüngliche Reihenfolge der Datensätze im Data Frame geändert wird. Hier erweist sich ein wesentlicher Unterschied zwischen Data Frames und Datenbank-Relationen von Vorteil: Die Reihenfolge von Datensätzen in Data Frames ist stabil und definiert. Sie resultiert aus der Abfolge der Elemente auf den Vektoren, die die Data Frames bilden. Im Gegensatz dazu haben Tabellen und Views keine Reihenfolge, auf die man sich beim SELECT verlassen kann. Nur mit der ORDER BY-Klausel über eindeutige Schlüsselwerte erreicht man eine definierte, stabile Reihenfolge der resultierenden Datensätze.
Die Wirkungsweise von Window Functions wird noch besser verständlich, wenn in obiger Abfrage das group_by(a) entfernt wird. Dann wirken alle genannten Funktionen auf der einzigen Gruppe, die existiert, nämlich dem gesamten Data Frame:
df %>%
mutate(r = row_number(), # aus dplyr
n_memb = n(), # aus dplyr
n_dist = n_distinct(b), # aus dplyr
ra=rank(desc(b)), # aus base und dplyr
last_b = lag(b), # aus dplyr
next_b = lead(b), # aus dplyr
mb = mean(b), # aus base
cs = cumsum(b) ) # aus base
id a b r n_memb n_dist ra last_b next_b mb cs
1 1 Maus 7 1 20 9 9.0 NA 3 6.1 7
2 2 Hund 3 2 20 9 17.5 7 3 6.1 10
3 3 Katze 3 3 20 9 17.5 3 4 6.1 13
4 4 Maus 4 4 20 9 15.5 3 10 6.1 17
5 5 Tiger 10 5 20 9 1.5 4 10 6.1 27
6 6 Maus 10 6 20 9 1.5 10 8 6.1 37
7 7 Hund 8 7 20 9 6.0 10 8 6.1 45
8 8 Hund 8 8 20 9 6.0 8 6 6.1 53
9 9 Hund 6 9 20 9 12.0 8 1 6.1 59
10 10 Katze 1 10 20 9 19.5 6 7 6.1 60
11 11 Maus 7 11 20 9 9.0 1 9 6.1 67
12 12 Hund 9 12 20 9 3.5 7 8 6.1 76
13 13 Hund 8 13 20 9 6.0 9 5 6.1 84
14 14 Tiger 5 14 20 9 14.0 8 6 6.1 89
15 15 Tiger 6 15 20 9 12.0 5 6 6.1 95
16 16 Maus 6 16 20 9 12.0 6 1 6.1 101
17 17 Katze 1 17 20 9 19.5 6 4 6.1 102
18 18 Maus 4 18 20 9 15.5 1 7 6.1 106
19 19 Maus 7 19 20 9 9.0 4 9 6.1 113
20 20 Maus 9 20 20 9 3.5 7 NA 6.1 122
Anwendbar sind hierbei sämtliche Funktionen, die auf Vektoren wirken. Diese müssen also wie in unserem Beispiel nicht unbedingt aus dplyr stammen. Allerdings komplettiert das Package die Menge der sinnvoll anwendbaren Funktionen um einige wichtige Elemente wie cumany() oder n_distinct().
Data Frames Hand in Hand…
In relationalen Datenbanken wird häufig angestrebt, das Datenmodell zu normalisieren. Dadurch bekommt man die negativen Folgen von Datenredundanz, wie Inkonsistenzen bei Datenmanipulationen und unnötig große Datenvolumina, in den Griff. Dies geschieht unter anderem dadurch, dass tabellarische Datenbestände aufgetrennt werden Stammdaten- und Faktentabellen. Letztere beziehen sich über Fremdschlüsselspalten auf die Primärschlüssel der Stammdatentabellen. Durch Joins, also Abfragen über mehrere Tabellen und Ausnutzen der Fremdschlüsselbeziehungen, werden die normalisierten Tabellen wieder zu einem fachlich kompletten Resultat denormalisiert.
In den Data Frames von R trifft man dieses Modellierungsmuster aus verschiedenen Gründen weit seltener an als in RDBMS. Dennoch gibt es neben der Normalisierung/Denormalisierung andere Fragestellungen, die sich gut durch Joins beantworten lassen. Neben der Zusammenführung von Beobachtungen unterschiedlicher Quellen anhand charakteristischer Schlüssel sind dies bestimmte Mengenoperationen wie Schnitt- und Differenzmengenbildung.
Die traditionelle R-Funktion für den Join zweier Data Frames lautet merge(). dplyr erweitert den Funktionsumfang dieser Funktion und sorgt für sprechendere Funktionsnamen und Konsistenz mit den anderen Operationen.
Hier ein synthetisches Beispiel:
products <- data.frame(
id = 1:5,
name = c("Desktop", "Laptop", "Maus", "Tablet", "Smartphone"),
preis = c(500, 700, 10, 300, 500)
)
set.seed(1)
(salesfacts <- data.frame(
prod_id = sample(1:5,size = 8,replace = T),
date = as.Date('2017-01-01') + sample(1:5,size = 8,replace = T)
) )
prod_id date
1 2 2017-01-05
2 2 2017-01-02
3 3 2017-01-03
4 5 2017-01-02
5 2 2017-01-05
6 5 2017-01-03
7 5 2017-01-05
8 4 2017-01-04
Nun gilt es, die Verkäufe aus dem Data Frame sales mit den Produkten in products zusammenzuführen und auf Basis von Produkten Bilanzen zu erstellen. Diese Denormalisierung geschieht durch das Verb inner_join() auf zweierlei Art und Weise:
salesfacts %>%
inner_join(products, by = c("prod_id" = "id"))
prod_id date name preis
1 2 2017-01-05 Laptop 700
2 2 2017-01-02 Laptop 700
3 3 2017-01-03 Maus 10
4 5 2017-01-02 Smartphone 500
5 2 2017-01-05 Laptop 700
6 5 2017-01-03 Smartphone 500
7 5 2017-01-05 Smartphone 500
8 4 2017-01-04 Tablet 300
products %>%
inner_join(salesfacts, by = c("id" = "prod_id"))
id name preis date
1 2 Laptop 700 2017-01-05
2 2 Laptop 700 2017-01-02
3 2 Laptop 700 2017-01-05
4 3 Maus 10 2017-01-03
5 4 Tablet 300 2017-01-04
6 5 Smartphone 500 2017-01-02
7 5 Smartphone 500 2017-01-03
8 5 Smartphone 500 2017-01-05
Die Ergebnisse sind bis auf die Reihenfolge der Spalten und der Zeilen identisch. Außerdem ist im einen Fall der gemeinsame Schlüssel der Produkt-Id als prod_id, im anderen Fall als id enthalten. dplyr entfernt also die Spalten-Duplikate der Join-Bedingungen. Letzere wird bei Bedarf im by-Argument der Join-Funktion angegeben. R-Experten erkennen hier einen „Named Vector“, also einen Vektor, bei dem jedes Element einen Namen hat. Diese Syntax verwendet dplyr, um elegant die äquivalenten Spalten zu kennzeichnen. Wird das Argument by weggelassen, so verwendet dplyr im Sinne eines „Natural Join“ automatisch alle Spalten, deren Namen in beiden Data Frames vorkommen.
Natürlich können wir dieses Beispiel mit den anderen Verben erweitern, um z.B. eine Umsatzbilanz pro Produkt zu erreichen:
salesfacts %>%
inner_join(products, by = c("prod_id" = "id")) %>%
group_by(prod_id) %>%
summarise(n_verk = n(), sum_preis = sum(preis), letzt_dat = max(date))
# A tibble: 4 × 4
prod_id n_verk sum_preis letzt_dat
1 2 3 2100 2017-01-05
2 3 1 10 2017-01-03
3 4 1 300 2017-01-04
4 5 3 1500 2017-01-05
dplyr bringt insgesamt 6 verschiedene Join-Funktionen mit: Neben dem bereits verwendeten Inner Join gibt es die linksseitigen und rechtsseitigen Outer Joins und den Full Join. Diese entsprechen genau der Funktionalität von SQL-Datenbanken. Daneben gibt es die Funktion semi_join(), die in SQL etwa folgendermaßen ausgedrückt würde:
SELECT ...
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.a_id = a.id)
Das Gegenteil, also ein NOT EXISTS, realisiert die sechste Join-Funktion: anti_join(). Im folgenden Beispiel sollen alle Produkte ausgegeben werden, die noch nie verkauft wurden:
products %>% anti_join(salesfacts,c("id" = "prod_id"))
id name preis
1 1 Desktop 500
… und in der Datenbank
Wir schon mehrfach betont, hat dplyr eine Reihe von Analogien zu SQL-Operationen auf relationalen Datenbanken. R Data Frames entsprechen Tabellen und Views und die dplyr-Operationen den Bausteinen von SELECT-Statements. Daraus ergibt sich die Möglichkeit, dplyr-Funktionen ohne viel Zutun auf eine bestehende Datenbank und deren Relationen zu deligieren.
Mir fallen folgende Szenarien ein, wo dies sinnvoll erscheint:
- Die zu verarbeitende Datenmenge ist zu groß für das Memory des Rechners, auf dem R läuft.
- Die interessierenden Daten liegen bereits als Tabellen und Views auf einer Datenbank vor.
- Die Datenbank hat Features, wie z.B. Parallelverarbeitung oder Bitmap Indexe, die R nicht hat.
In der aktuellen Version 0.5.0 kann dplyr nativ vier Datenbank-Backends ansprechen: SQLite, MySQL, PostgreSQL und Google BigQuery. Ich vermute, unter der Leserschaft des Data Science Blogs dürfte MySQL (oder der Fork MariaDB) die weiteste Verbreitung haben, weshalb ich die folgenden Beispiele darauf zeige. Allerdings muss man beachten, dass MySQL keine Window Funktionen kennt, was sich 1:1 auf die Funktionalität von dplyr auswirkt.
Im folgenden möchte ich zeigen, wie dplyr sich gegen eine bestehende MySQL-Datenbank verbindet und danach einen bestehenden R Data Frame in eine neue Datenbanktabelle wegspeichert:
mysql_db <- src_mysql(host = "localhost", user = "testuser",
password = "********", dbname = "test")
library(ggplot2)
str(diamonds)
Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 53940 obs. of 10 variables:
$ carat : num 0.23 0.21 0.23 0.29 0.31 0.24 0.24 0.26 0.22 0.23 ...
$ cut : chr "Ideal" "Premium" "Good" "Premium" ...
$ color : chr "E" "E" "E" "I" ...
$ clarity: chr "SI2" "SI1" "VS1" "VS2" ...
$ depth : num 61.5 59.8 56.9 62.4 63.3 62.8 62.3 61.9 65.1 59.4 ...
$ table : num 55 61 65 58 58 57 57 55 61 61 ...
$ price : int 326 326 327 334 335 336 336 337 337 338 ...
$ x : num 3.95 3.89 4.05 4.2 4.34 3.94 3.95 4.07 3.87 4 ...
$ y : num 3.98 3.84 4.07 4.23 4.35 3.96 3.98 4.11 3.78 4.05 ...
$ z : num 2.43 2.31 2.31 2.63 2.75 2.48 2.47 2.53 2.49 2.39 ...
diamonds %>% mutate(cut = as.character(cut),
color = as.character(color),
clarity = as.character(clarity)) -> diamonds
diamonds_mysql <- copy_to(mysql_db, diamonds, name="diamonds",
temporary = FALSE, indexes = list(
c("cut", "color", "clarity"), "carat", "price"))
diamonds_mysql %>% summarise(count = n())
Source: query [?? x 1]
Database: mysql 5.5.54-0ubuntu0.14.04.1 [testuser@localhost:/test]
count
<dbl>
1 53940
Die erste Anweisung verbindet R mit einer bestehenden MySQL-Datenbank. Danach lade ich den Data Frame diamonds aus dem Paket ggplot2. Mit str() wird deutlich, dass drei darin enthaltene Variablen vom Typ Factor sind. Damit dplyr damit arbeiten kann, werden sie mit mutate() in Character-Vektoren gewandelt. Dann erzeugt die Funktion copy_to() auf der MySQL-Datenbank eine leere Tabelle namens diamonds, in die die Datensätze kopiert werden. Danach erhält die Tabelle noch drei Indexe (von dem der erste aus drei Segmenten besteht), und zum Schluß führt dplyr noch ein ANALYSE der Tabelle durch, um die Werteverteilungen auf den Spalten für kostenbasierte Optimierung zu bestimmen.
Meistens aber wird bereits eine bestehende Datenbanktabelle die interessierenden Daten enthalten. In diesem Fall lautet die Funktion zum Erstellen des Delegats tbl():
diamonds_mysql2 <- tbl(mysql_db,"diamonds")
identical(diamonds_mysql,diamonds_mysql2)
[1] TRUE
Die Rückgabewerte von copy_to() und von tbl() sind natürlich keine reinrassigen Data Frames, sondern Objekte, auf die die Operationen von dplyr wirken können, indem sie auf die Datenbank deligiert werden. Im folgenden Beispiel sollen alle Diamanten, die ein Gewicht von mindestens 1 Karat haben, pro Cut, Color und Clarity nach Anzahl und mittlerem Preis bilanziert werden:
bilanz <- diamonds_mysql2 %>%
filter(carat >= 1) %>%
group_by(cut,color,clarity) %>%
summarise(count = n(), mean_price = mean(price))
bilanz
Source: query [?? x 5]
Database: mysql 5.5.54-0ubuntu0.14.04.1 [testuser@localhost:/test]
Groups: cut, color
cut color clarity count mean_price
<chr> <chr> <chr> <dbl> <dbl>
1 Fair D I1 3 9013.667
2 Fair D SI1 26 6398.192
3 Fair D SI2 29 6138.552
4 Fair D VS1 1 7083.000
5 Fair D VS2 7 8553.429
6 Fair D VVS1 1 10752.000
7 Fair D VVS2 2 9639.000
8 Fair E I1 5 2469.800
9 Fair E SI1 28 6407.464
10 Fair E SI2 45 5627.489
# ... with more rows
explain(bilanz)
<SQL>
SELECT `cut`, `color`, `clarity`, count(*) AS `count`, AVG(`price`) AS `mean_price`
FROM (SELECT *
FROM `diamonds`
WHERE (`carat` >= 1.0)) `cttxnwlelz`
GROUP BY `cut`, `color`, `clarity`
<PLAN>
id select_type table type possible_keys key key_len ref rows
1 1 PRIMARY <derived2> ALL <NA> <NA> <NA> <NA> 19060
2 2 DERIVED diamonds ALL diamonds_carat <NA> <NA> <NA> 50681
Extra
1 Using temporary; Using filesort
2 Using where
Die Definition der Variablen bilanz geschieht dabei komplett ohne Interaktion mit der Datenbank. Erst beim Anzeigen von Daten wird das notwendige SQL ermittelt und auf der DB ausgeführt. Die ersten 10 resultierenden Datensätze werden angezeigt. Mittels der mächtigen Funktion explain() erhalten wir das erzeugte SQL-Kommando und sogar den Ausführungsplan auf der Datenbank. SQL-Kundige werden erkennen, dass die verketteten dplyr-Operationen in verschachtelte SELECT-Statements umgesetzt werden.
Zu guter Letzt sollen aber meistens die Ergebnisse der dplyr-Operationen irgendwie gesichert werden. Hier hat der Benutzer die Wahl, ob die Daten auf der Datenbank in einer neuen Tabelle gespeichert werden sollen oder ob sie komplett nach R transferiert werden sollen. Dies erfolgt mit den Funktionen compute() bzw. collect():
compute(bilanz, name = "t_bilanz", temporary = F)
df <- collect(bilanz)
str(df)
Classes ‘grouped_df’, ‘tbl_df’, ‘tbl’ and 'data.frame': 265 obs. of 5 variables:
$ cut : chr "Fair" "Fair" "Fair" "Fair" ...
$ color : chr "D" "D" "D" "D" ...
$ clarity : chr "I1" "SI1" "SI2" "VS1" ...
$ count : num 3 26 29 1 7 1 2 5 28 45 ...
$ mean_price: num 9014 6398 6139 7083 8553 ...
...
Durch diese beiden Operationen wurde eine neue Datenbanktabelle „t_bilanz“ erzeugt und danach der Inhalt der Bilanz als Data Frame zurück in den R-Interpreter geholt. Damit schließt sich der Kreis.
Fazit
Mit dem Paket dplyr von Hadley Wickham wird die Arbeit mit R Data Frames auf eine neue Ebene gehoben. Die Operationen sind konsistent, vollständig und performant. Durch den Verkettungs-Operator %>% erhalten sie auch bei hoher Komplexität eine intuitive Syntax. Viele Aspekte der Funktionalität lehnen sich an Relationale Datenbanken an, sodass Analysten mit SQL-Kenntnissen rasch viele Operationen auf R Data Frames übertragen können.
Zurück zu R Data Frames meistern mit dplyr – Teil 1.
Über Exasol-CEO Martin Golombek
Read this article in English:
