Kapitel 61 Daten fusionieren (Merge / Join)
Oft haben wir zwei oder mehr Datensätze, die wir zusammenfügen wollen. Dies tun wir mit einer Schlüsselvariable - oder auch mehreren Schlüsselvariablen. So dass die Daten auf einer Linie sind, die auch zusammengehören.
Wir erstellen zuerst zwei Data Frames.
=123
set.seed<-data.frame(ID=1:10, Alter=abs(round(rnorm(10, 50,12))), BMI=abs(round(rnorm(10,25,3))))
data_1<-data.frame(ID=3:14, Schmerz=abs(round(rnorm(12, 4,2))), Funktion=abs(round(rnorm(12,50,3)))) data_2
Die Schlüsselvariable ist hier die Variable ID. (Die Schlüsselvariablen sind jeweils grün markiert in den Tabellen auf dieser Seite.)
ID | Alter | BMI |
---|---|---|
1 | 44 | 27 |
2 | 66 | 29 |
3 | 60 | 23 |
4 | 41 | 24 |
5 | 42 | 20 |
6 | 46 | 31 |
7 | 41 | 23 |
8 | 52 | 27 |
9 | 47 | 31 |
10 | 47 | 27 |
/
ID | Schmerz | Funktion |
---|---|---|
3 | 5 | 56 |
4 | 4 | 45 |
5 | 4 | 46 |
6 | 6 | 49 |
7 | 8 | 52 |
8 | 4 | 52 |
9 | 2 | 51 |
10 | 0 | 47 |
11 | 1 | 48 |
12 | 5 | 48 |
13 | 6 | 47 |
14 | 6 | 51 |
Wir sehen, dass die beiden Datenblätter nicht alle Teilnehmenden beinhalten. Im ersten Datenblatt fehlen die Teilnehmenden 11 bis 14, im zweiten Datenblatt fehlen die Teilnehmenden 1 und 2.
Jetzt können wir die beiden Data Frames mit der Schlüsselvariable ID zusammenfügen.
Wir müssen uns jetzt überlegen, welche Teilnehmenden wir im zusammengefügten Data Frame haben wollen:
- Alle (Kombination von Teilnehmenden aus Datenball 1 und Datenblatt 2) full_join()
- Alle aus Datenblatt 1: Teilnehmende 1 bis 10 left_join()
- Alle aus Datenblatt 2: Teilnehmende 3 bis 14 right_join()
- Nur diejenigen, die in beiden Datenblätter vorhanden sind: Teilnehmende 3 bis 10. inner_join()
- Alle aus Datenblatt 1, die nicht in Datenblatt 2 sind: Teilnehmende 13 und 14 anti_join()
- Alle in Datenbaltt 1 die auch in Datenblatt 2 sind, aber nur Daten aus Datenblatt 1. semi_join()
<-full_join(data_1, data_2, by="ID")
Alle::kbl(Alle, caption="Alle Teilnehmenden, die in einem der beiden Datenblättern sind.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI | Schmerz | Funktion |
---|---|---|---|---|
1 | 44 | 27 | NA | NA |
2 | 66 | 29 | NA | NA |
3 | 60 | 23 | 5 | 56 |
4 | 41 | 24 | 4 | 45 |
5 | 42 | 20 | 4 | 46 |
6 | 46 | 31 | 6 | 49 |
7 | 41 | 23 | 8 | 52 |
8 | 52 | 27 | 4 | 52 |
9 | 47 | 31 | 2 | 51 |
10 | 47 | 27 | 0 | 47 |
11 | NA | NA | 1 | 48 |
12 | NA | NA | 5 | 48 |
13 | NA | NA | 6 | 47 |
14 | NA | NA | 6 | 51 |
<-left_join(data_1, data_2, by="ID")
Alle_aus_Data_1::kbl(Alle_aus_Data_1, caption="Alle Teilnehmenden, die im Datenblatt 1 sind.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI | Schmerz | Funktion |
---|---|---|---|---|
1 | 44 | 27 | NA | NA |
2 | 66 | 29 | NA | NA |
3 | 60 | 23 | 5 | 56 |
4 | 41 | 24 | 4 | 45 |
5 | 42 | 20 | 4 | 46 |
6 | 46 | 31 | 6 | 49 |
7 | 41 | 23 | 8 | 52 |
8 | 52 | 27 | 4 | 52 |
9 | 47 | 31 | 2 | 51 |
10 | 47 | 27 | 0 | 47 |
<-right_join(data_1, data_2, by="ID")
Alle_aus_Data_2::kbl(Alle_aus_Data_2, caption="Alle Teilnehmenden, die im Datenblatt 2 sind. ") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI | Schmerz | Funktion |
---|---|---|---|---|
3 | 60 | 23 | 5 | 56 |
4 | 41 | 24 | 4 | 45 |
5 | 42 | 20 | 4 | 46 |
6 | 46 | 31 | 6 | 49 |
7 | 41 | 23 | 8 | 52 |
8 | 52 | 27 | 4 | 52 |
9 | 47 | 31 | 2 | 51 |
10 | 47 | 27 | 0 | 47 |
11 | NA | NA | 1 | 48 |
12 | NA | NA | 5 | 48 |
13 | NA | NA | 6 | 47 |
14 | NA | NA | 6 | 51 |
<-inner_join(data_1, data_2, by="ID")
Alle_die_in_beiden_Daten_sind::kbl(Alle_die_in_beiden_Daten_sind, caption="Alle Teilnehmenden, die in beiden Datenblättern sind.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI | Schmerz | Funktion |
---|---|---|---|---|
3 | 60 | 23 | 5 | 56 |
4 | 41 | 24 | 4 | 45 |
5 | 42 | 20 | 4 | 46 |
6 | 46 | 31 | 6 | 49 |
7 | 41 | 23 | 8 | 52 |
8 | 52 | 27 | 4 | 52 |
9 | 47 | 31 | 2 | 51 |
10 | 47 | 27 | 0 | 47 |
<-anti_join(data_1, data_2, by="ID")
Alle_in_1_die_nicht_in_2_sind_nur_Daten_aus_1::kbl(Alle_in_1_die_nicht_in_2_sind_nur_Daten_aus_1, caption="Alle Teilnehmenden, die in 1, aber nicht in 2 sind. Nur Daten aus 1.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI |
---|---|---|
1 | 44 | 27 |
2 | 66 | 29 |
<-semi_join(data_1, data_2, by="ID")
Alle_in_1_die_auch_in_2_sind_nur_Daten_1::kbl(Alle_in_1_die_auch_in_2_sind_nur_Daten_1, caption="Alle Teilnehmenden, die in 1 und 2 sind, nur Daten aus 1.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI |
---|---|---|
3 | 60 | 23 |
4 | 41 | 24 |
5 | 42 | 20 |
6 | 46 | 31 |
7 | 41 | 23 |
8 | 52 | 27 |
9 | 47 | 31 |
10 | 47 | 27 |
So weit so einfach. Wenn wir nun aber pro Teilnehmende:n zwei oder mehr Zeilen haben, zum Beispiel Daten zu zwei Gelenken, so wird es etwas komplexer. Wir benötigen dann zwei Schlüsselvariablen - oder müssen aus beiden eine erstellen.
Erstellen wir einmal zwei Data Frames=123
set.seed<-data.frame(ID=1:10, Alter=abs(round(rnorm(10, 50,12))), BMI=abs(round(rnorm(10,25,3))), Gelenk="Schulter")
data_1<-data.frame(ID=3:14, Schmerz=abs(round(rnorm(12, 4,2))), Funktion=abs(round(rnorm(12,50,3))), Gelenk="Schulter")
data_2
<-data.frame(ID=1:10, Alter=data_1$Alter, BMI=data_1$BMI, Gelenk="Knie")
data_1b<-data.frame(ID=3:14, Schmerz=abs(round(rnorm(12, 4,2))), Funktion=abs(round(rnorm(12,50,3))), Gelenk="Knie")
data_2b
<-bind_rows(data_1, data_1b)
data_1
<-bind_rows(data_2, data_2b) data_2
Wenn wir die Daten jetzt einfach mit der ID Variable zusammenfügen würden, bekämen wir doppelt so viele Zeilen.
Wir sollten am Schluss maximal 28 Zeilen haben. Doch mit dem folgenden Code bekommen wir 44 Zeilen.
Wir sehen auch, dass R für die Variable Gelenk zwei Variablen Gelenk.x und Gelenk.y gebildet hat, da die Inhalte dieser Zellen nicht übereingestimmt haben - klar, sind ja zwei unterschiedliche Gelenke. Da der Schlüssel falsch war, wurde jede mögliche Kombination zusammengefügt, also ID 1 Gelenk Schulter wurde zu ID 1 Gelenk Knie zusammengefügt, also wurde ID 1 verdoppelt.
<-full_join(data_1, data_2, by="ID") %>%
falsch_zusammengefuegtarrange(ID)
::kbl(falsch_zusammengefuegt, caption="Falsch! Wir haben den Schlüssen ungenügend gewählt. Die ID alleine ist nicht eindeutig identifizierende für jede Zeile.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI | Gelenk.x | Schmerz | Funktion | Gelenk.y |
---|---|---|---|---|---|---|
1 | 51 | 27 | Schulter | NA | NA | NA |
1 | 51 | 27 | Knie | NA | NA | NA |
2 | 57 | 28 | Schulter | NA | NA | NA |
2 | 57 | 28 | Knie | NA | NA | NA |
3 | 55 | 30 | Schulter | 1 | 55 | Schulter |
3 | 55 | 30 | Schulter | 4 | 46 | Knie |
3 | 55 | 30 | Knie | 1 | 55 | Schulter |
3 | 55 | 30 | Knie | 4 | 46 | Knie |
4 | 56 | 22 | Schulter | 3 | 49 | Schulter |
4 | 56 | 22 | Schulter | 3 | 50 | Knie |
4 | 56 | 22 | Knie | 3 | 49 | Schulter |
4 | 56 | 22 | Knie | 3 | 50 | Knie |
5 | 50 | 23 | Schulter | 4 | 51 | Schulter |
5 | 50 | 23 | Schulter | 6 | 49 | Knie |
5 | 50 | 23 | Knie | 4 | 51 | Schulter |
5 | 50 | 23 | Knie | 6 | 49 | Knie |
6 | 51 | 24 | Schulter | 3 | 52 | Schulter |
6 | 51 | 24 | Schulter | 7 | 47 | Knie |
6 | 51 | 24 | Knie | 3 | 52 | Schulter |
6 | 51 | 24 | Knie | 7 | 47 | Knie |
7 | 55 | 26 | Schulter | 2 | 50 | Schulter |
7 | 55 | 26 | Schulter | 5 | 51 | Knie |
7 | 55 | 26 | Knie | 2 | 50 | Schulter |
7 | 55 | 26 | Knie | 5 | 51 | Knie |
8 | 57 | 24 | Schulter | 3 | 52 | Schulter |
8 | 57 | 24 | Schulter | 8 | 48 | Knie |
8 | 57 | 24 | Knie | 3 | 52 | Schulter |
8 | 57 | 24 | Knie | 8 | 48 | Knie |
9 | 46 | 26 | Schulter | 6 | 49 | Schulter |
9 | 46 | 26 | Schulter | 4 | 49 | Knie |
9 | 46 | 26 | Knie | 6 | 49 | Schulter |
9 | 46 | 26 | Knie | 4 | 49 | Knie |
10 | 34 | 25 | Schulter | 3 | 47 | Schulter |
10 | 34 | 25 | Schulter | 6 | 56 | Knie |
10 | 34 | 25 | Knie | 3 | 47 | Schulter |
10 | 34 | 25 | Knie | 6 | 56 | Knie |
11 | NA | NA | NA | 2 | 55 | Schulter |
11 | NA | NA | NA | 3 | 53 | Knie |
12 | NA | NA | NA | 7 | 50 | Schulter |
12 | NA | NA | NA | 4 | 48 | Knie |
13 | NA | NA | NA | 1 | 53 | Schulter |
13 | NA | NA | NA | 3 | 53 | Knie |
14 | NA | NA | NA | 6 | 51 | Schulter |
14 | NA | NA | NA | 3 | 46 | Knie |
Wir müssen für den Schlüssel alle Variablen benutzen, die die Zeilen eindeutig identifizieren. Das war mit ID nicht so, da ja jede:r Teilnehmende:r zweimal vorkommt.
Wir können im Befehl einfach by=c(“ID”, “Gelenk”) anfügen, so wird der Schlüssel eindeutig.
<-full_join(data_1, data_2, by=c("ID", "Gelenk")) %>%
richtig_zusammengefuegtarrange(ID)
::kbl(richtig_zusammengefuegt, caption="Richtg! Mit der Kombination von ID und Gelenk bilden wir einen eindeutigen Schlüssel") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
ID | Alter | BMI | Gelenk | Schmerz | Funktion |
---|---|---|---|---|---|
1 | 51 | 27 | Schulter | NA | NA |
1 | 51 | 27 | Knie | NA | NA |
2 | 57 | 28 | Schulter | NA | NA |
2 | 57 | 28 | Knie | NA | NA |
3 | 55 | 30 | Schulter | 1 | 55 |
3 | 55 | 30 | Knie | 4 | 46 |
4 | 56 | 22 | Schulter | 3 | 49 |
4 | 56 | 22 | Knie | 3 | 50 |
5 | 50 | 23 | Schulter | 4 | 51 |
5 | 50 | 23 | Knie | 6 | 49 |
6 | 51 | 24 | Schulter | 3 | 52 |
6 | 51 | 24 | Knie | 7 | 47 |
7 | 55 | 26 | Schulter | 2 | 50 |
7 | 55 | 26 | Knie | 5 | 51 |
8 | 57 | 24 | Schulter | 3 | 52 |
8 | 57 | 24 | Knie | 8 | 48 |
9 | 46 | 26 | Schulter | 6 | 49 |
9 | 46 | 26 | Knie | 4 | 49 |
10 | 34 | 25 | Schulter | 3 | 47 |
10 | 34 | 25 | Knie | 6 | 56 |
11 | NA | NA | Schulter | 2 | 55 |
11 | NA | NA | Knie | 3 | 53 |
12 | NA | NA | Schulter | 7 | 50 |
12 | NA | NA | Knie | 4 | 48 |
13 | NA | NA | Schulter | 1 | 53 |
13 | NA | NA | Knie | 3 | 53 |
14 | NA | NA | Schulter | 6 | 51 |
14 | NA | NA | Knie | 3 | 46 |
61.1 Zusammenfügen von Datenblättern, die die gleichen Variablen enthalten.
Manchmal hat man Daten zu den gleichen Variablen aus unterschiedlichen Quellen - wobei es manchmal aus einer Quelle fehlende Werte hat. Hier möchte man einfach die nicht-fehlenden Werte übernehmen. Mit den normalen join Befehlen geht dies nicht so einfach, bei fehlenden Werten werden zwei Variablen, z.B. Gender.y und Gender.x gebildet. Man kann diese dann kontrollieren und zum Beispiel mit dem Befehl mutate(Gender=coalesce(Gender.y, Gender.x)) zusammenfügen.
Ein Datenwissenschaftler hat dazu die Funktion coalesce_join geschrieben: https://alistaire.rbind.io/blog/coalescing-joins/.
<-as.character(1:100)
key<-(rep(sample(letters[1:26], size=5, replace=TRUE), times=100))
name
<- function(x, y,
coalesce_join by = NULL, suffix = c(".x", ".y"),
join = dplyr::full_join, ...) {
<- join(x, y, by = by, suffix = suffix, ...)
joined # names of desired output
<- union(names(x), names(y))
cols
<- names(joined)[!names(joined) %in% cols]
to_coalesce <- suffix[ifelse(endsWith(to_coalesce, suffix[1]), 1, 2)]
suffix_used # remove suffixes and deduplicate
<- unique(substr(
to_coalesce
to_coalesce, 1,
nchar(to_coalesce) - nchar(suffix_used)
))
<- purrr::map_dfc(to_coalesce, ~dplyr::coalesce(
coalesced paste0(.x, suffix[1])]],
joined[[paste0(.x, suffix[2])]]
joined[[
))names(coalesced) <- to_coalesce
::bind_cols(joined, coalesced)[cols]
dplyr }
<-richtig_zusammengefuegt[1:4]
data_1<-richtig_zusammengefuegt[,c(1:2,4:6)]
data_2
<-data_2 %>%
data_2mutate(Alter=ifelse(Alter>50,NA, Alter))
Hier die zwei Datentabellen:
::kbl(data_1, caption="Data 1, Schlüssel-Variablen in Grün") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(c(1,4), bold = T, color = "#67857D", background = "chartreuse")%>%
kable_styling(full_width = F, font_size = 10)
ID | Alter | BMI | Gelenk |
---|---|---|---|
1 | 51 | 27 | Schulter |
1 | 51 | 27 | Knie |
2 | 57 | 28 | Schulter |
2 | 57 | 28 | Knie |
3 | 55 | 30 | Schulter |
3 | 55 | 30 | Knie |
4 | 56 | 22 | Schulter |
4 | 56 | 22 | Knie |
5 | 50 | 23 | Schulter |
5 | 50 | 23 | Knie |
6 | 51 | 24 | Schulter |
6 | 51 | 24 | Knie |
7 | 55 | 26 | Schulter |
7 | 55 | 26 | Knie |
8 | 57 | 24 | Schulter |
8 | 57 | 24 | Knie |
9 | 46 | 26 | Schulter |
9 | 46 | 26 | Knie |
10 | 34 | 25 | Schulter |
10 | 34 | 25 | Knie |
11 | NA | NA | Schulter |
11 | NA | NA | Knie |
12 | NA | NA | Schulter |
12 | NA | NA | Knie |
13 | NA | NA | Schulter |
13 | NA | NA | Knie |
14 | NA | NA | Schulter |
14 | NA | NA | Knie |
::kbl(data_2, caption="Data 2, Schlüssel-Variablen in Grün") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(c(1,3), bold = T, color = "#67857D", background = "chartreuse")%>%
kable_styling(full_width = F, font_size = 10)
ID | Alter | Gelenk | Schmerz | Funktion |
---|---|---|---|---|
1 | NA | Schulter | NA | NA |
1 | NA | Knie | NA | NA |
2 | NA | Schulter | NA | NA |
2 | NA | Knie | NA | NA |
3 | NA | Schulter | 1 | 55 |
3 | NA | Knie | 4 | 46 |
4 | NA | Schulter | 3 | 49 |
4 | NA | Knie | 3 | 50 |
5 | 50 | Schulter | 4 | 51 |
5 | 50 | Knie | 6 | 49 |
6 | NA | Schulter | 3 | 52 |
6 | NA | Knie | 7 | 47 |
7 | NA | Schulter | 2 | 50 |
7 | NA | Knie | 5 | 51 |
8 | NA | Schulter | 3 | 52 |
8 | NA | Knie | 8 | 48 |
9 | 46 | Schulter | 6 | 49 |
9 | 46 | Knie | 4 | 49 |
10 | 34 | Schulter | 3 | 47 |
10 | 34 | Knie | 6 | 56 |
11 | NA | Schulter | 2 | 55 |
11 | NA | Knie | 3 | 53 |
12 | NA | Schulter | 7 | 50 |
12 | NA | Knie | 4 | 48 |
13 | NA | Schulter | 1 | 53 |
13 | NA | Knie | 3 | 53 |
14 | NA | Schulter | 6 | 51 |
14 | NA | Knie | 3 | 46 |
Jetzt haben wir die Variable Alter in beiden Datensätzen, jedoch fehlen im Datensatz 2 die Alters-Werte bei den Teilnehmenen mit einem Alter über 50 Jahren.
Fügen wir nun die beiden Datensätze mit coalesce_join zusammen, so wird das fehlende Alter im Datensatz 2 durch das Alter im Datensatz 1 ersetzt.
<-coalesce_join(data_1, data_2, by=c("ID", "Gelenk"))
data_coalesce
::kbl(data_coalesce, caption="Data 2") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(c(1,4), bold = T, color = "#67857D", background = "chartreuse")%>%
kable_styling(full_width = F, font_size = 10)
ID | Alter | BMI | Gelenk | Schmerz | Funktion |
---|---|---|---|---|---|
1 | 51 | 27 | Schulter | NA | NA |
1 | 51 | 27 | Knie | NA | NA |
2 | 57 | 28 | Schulter | NA | NA |
2 | 57 | 28 | Knie | NA | NA |
3 | 55 | 30 | Schulter | 1 | 55 |
3 | 55 | 30 | Knie | 4 | 46 |
4 | 56 | 22 | Schulter | 3 | 49 |
4 | 56 | 22 | Knie | 3 | 50 |
5 | 50 | 23 | Schulter | 4 | 51 |
5 | 50 | 23 | Knie | 6 | 49 |
6 | 51 | 24 | Schulter | 3 | 52 |
6 | 51 | 24 | Knie | 7 | 47 |
7 | 55 | 26 | Schulter | 2 | 50 |
7 | 55 | 26 | Knie | 5 | 51 |
8 | 57 | 24 | Schulter | 3 | 52 |
8 | 57 | 24 | Knie | 8 | 48 |
9 | 46 | 26 | Schulter | 6 | 49 |
9 | 46 | 26 | Knie | 4 | 49 |
10 | 34 | 25 | Schulter | 3 | 47 |
10 | 34 | 25 | Knie | 6 | 56 |
11 | NA | NA | Schulter | 2 | 55 |
11 | NA | NA | Knie | 3 | 53 |
12 | NA | NA | Schulter | 7 | 50 |
12 | NA | NA | Knie | 4 | 48 |
13 | NA | NA | Schulter | 1 | 53 |
13 | NA | NA | Knie | 3 | 53 |
14 | NA | NA | Schulter | 6 | 51 |
14 | NA | NA | Knie | 3 | 46 |
61.2 Updating / Ersetzen von Werten mit Werten aus zweiter Datenquelle
Manchmal wollen wir - falls die Werte aus zwei Quellen nicht übereinstimmen, die Werte aus einer der beiden Quellen übernehmen.
Auch hier gibt es eine Funktion, diesemal in einem Paket
Schauen wir uns dies mit drei Datensätzen an:
Hier erstellen wir die drei Datensätz:
<-data.frame(Name=c("John", "Paul", "George", "Ringo", "Keith", "Mick", "Bill", "Charlie", "Ron", "Mick", "Brian", "Ian", "Bill"),
Data_frame_ALastname=c("Lennon", "McCartney", "Harrison", "Starr", "Richards","Jagger", "Wyman", "Watts", "Wood", "Taylor", "Jones","Stewart", "Preston"))
<-data.frame(Lastname=c("Lennon", "McCartney", "Harrison", "Starr", "Richards","Jagger", "Wyman", "Watts", "Wood", "Taylor", "Jones","Stewart", "Keys"),
Data_frame_BName=c("John", "Paul", "George", "Ringo", "Keith", "Mick", "Bill", "Charlie", "Ron", "Mick", "Brian", "Ian", "Bobby"),
Instrument=c("Guitar", "Bass", "Guitar", "Drums", "Guitar", "Guitar", "Bass", "Drums", "Guitar", "Guitar", "Guitar", "Keyboards", "Saxophon"))
<-data.frame(Lastname=c("Lennon", "McCartney", "Harrison", "Starr", "Richards","Jagger", "Wyman", "Watts", "Wood", "Taylor", "Jones","Stewart", "Keys", "Preston"),
Data_frame_CName=c("John", "Paul", "George", "Ringo", "Keith", "Mick", "Bill", "Charlie", "Ron", "Mick", "Brian", "Ian", "Bobby", "Bill")) %>%
mutate(Instrument=case_when(
=="Bobby"&Lastname=="Keys"~"Saxophone",
Name=="Bill"&Lastname=="Preston"~"Keyboards")) Name
Jetzt schauen wir uns die drei Datentabellen an:
::kbl(Data_frame_A, caption="Tabelle A") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtrakable_styling(full_width = F, font_size = 10)
Name | Lastname |
---|---|
John | Lennon |
Paul | McCartney |
George | Harrison |
Ringo | Starr |
Keith | Richards |
Mick | Jagger |
Bill | Wyman |
Charlie | Watts |
Ron | Wood |
Mick | Taylor |
Brian | Jones |
Ian | Stewart |
Bill | Preston |
::kbl(Data_frame_B, caption="Tabelle B") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtrakable_styling(full_width = F, font_size = 10)
Lastname | Name | Instrument |
---|---|---|
Lennon | John | Guitar |
McCartney | Paul | Bass |
Harrison | George | Guitar |
Starr | Ringo | Drums |
Richards | Keith | Guitar |
Jagger | Mick | Guitar |
Wyman | Bill | Bass |
Watts | Charlie | Drums |
Wood | Ron | Guitar |
Taylor | Mick | Guitar |
Jones | Brian | Guitar |
Stewart | Ian | Keyboards |
Keys | Bobby | Saxophon |
::kbl(Data_frame_C, caption="Tabelle C") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtrakable_styling(full_width = F, font_size = 10)
Lastname | Name | Instrument |
---|---|---|
Lennon | John | NA |
McCartney | Paul | NA |
Harrison | George | NA |
Starr | Ringo | NA |
Richards | Keith | NA |
Jagger | Mick | NA |
Wyman | Bill | NA |
Watts | Charlie | NA |
Wood | Ron | NA |
Taylor | Mick | NA |
Jones | Brian | NA |
Stewart | Ian | NA |
Keys | Bobby | Saxophone |
Preston | Bill | Keyboards |
Wir wollen, dass die fehlenden Werte durch die Werte aus der anderen Datentabelle ersetzt werden. Left_join tut dies jedoch nicht. Es erstellt zwei Spalten für Instrumente, eine aus der linken Datentabell, die andere aus der rechten.
<-left_join(Data_frame_A, Data_frame_B, by=c("Name", "Lastname"))
data_joined_1<-left_join(data_joined_1, Data_frame_C, by=c("Name", "Lastname"))
data_joined_2
::kbl(data_joined_2, caption="Stimmen die Werte nicht überein, werden zwei Spalten erstellt.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument.x | Instrument.y |
---|---|---|---|
John | Lennon | Guitar | NA |
Paul | McCartney | Bass | NA |
George | Harrison | Guitar | NA |
Ringo | Starr | Drums | NA |
Keith | Richards | Guitar | NA |
Mick | Jagger | Guitar | NA |
Bill | Wyman | Bass | NA |
Charlie | Watts | Drums | NA |
Ron | Wood | Guitar | NA |
Mick | Taylor | Guitar | NA |
Brian | Jones | Guitar | NA |
Ian | Stewart | Keyboards | NA |
Bill | Preston | NA | Keyboards |
Auch der Befehl merge hilft nicht
<-merge(data_joined_1, Data_frame_C, by=c("Name", "Lastname"), all=TRUE)
data_joined_merge_all
::kbl(data_joined_merge_all, caption="Auch mit merge werden wieder zwei Spalten erstellt, falls die Zellinhalte nicht übereinstimmen.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument.x | Instrument.y |
---|---|---|---|
Bill | Preston | NA | Keyboards |
Bill | Wyman | Bass | NA |
Bobby | Keys | NA | Saxophone |
Brian | Jones | Guitar | NA |
Charlie | Watts | Drums | NA |
George | Harrison | Guitar | NA |
Ian | Stewart | Keyboards | NA |
John | Lennon | Guitar | NA |
Keith | Richards | Guitar | NA |
Mick | Jagger | Guitar | NA |
Mick | Taylor | Guitar | NA |
Paul | McCartney | Bass | NA |
Ringo | Starr | Drums | NA |
Ron | Wood | Guitar | NA |
Manchmal ist der Grund nur, dass es in einem Datenblatt fehlende Werte gibt. Hier können wir mit dem coalesce Befehl die fehlenden Werte ersetzen. Der coalesce Befehl nimmt den ersten nicht-fehlenden Wert der genannten Variablen.
<-data_joined_merge_all %>%
variante_1mutate(Instrument=coalesce(Instrument.x, Instrument.y))
Nach einer Kontrolle können die Variablen Instrumt.x und Instrument.y noch gelöscht werden
Schauen wir uns die Tabelle wieder an:
::kbl(variante_1, caption="Die fehlenden Werte wurden mit dem coalesce Befehl ersetzt. Natürlich würden wir die Variablen Instrument.x und Instrument.y noch löschen. Hier sind zur nur noch zur Kontrolle da.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument.x | Instrument.y | Instrument |
---|---|---|---|---|
Bill | Preston | NA | Keyboards | Keyboards |
Bill | Wyman | Bass | NA | Bass |
Bobby | Keys | NA | Saxophone | Saxophone |
Brian | Jones | Guitar | NA | Guitar |
Charlie | Watts | Drums | NA | Drums |
George | Harrison | Guitar | NA | Guitar |
Ian | Stewart | Keyboards | NA | Keyboards |
John | Lennon | Guitar | NA | Guitar |
Keith | Richards | Guitar | NA | Guitar |
Mick | Jagger | Guitar | NA | Guitar |
Mick | Taylor | Guitar | NA | Guitar |
Paul | McCartney | Bass | NA | Bass |
Ringo | Starr | Drums | NA | Drums |
Ron | Wood | Guitar | NA | Guitar |
Der folgende Code erstellt die Funktion coalesce_join. Wenn wird den folgenden Code ausführen, können wir danach Daten mit dem Befehl coalesce_join zusammenführen. (Das haben wir ja auch schon weiter oben getan).
# This function is [from here: https://alistaire.rbind.io/blog/coalescing-joins/](https://alistaire.rbind.io/blog/coalescing-joins/)
<-as.character(1:100)
key<-(rep(sample(letters[1:26], size=5, replace=TRUE), times=100))
name
<- function(x, y,
coalesce_join by = NULL, suffix = c(".x", ".y"),
join = dplyr::full_join, ...) {
<- join(x, y, by = by, suffix = suffix, ...)
joined # names of desired output
<- union(names(x), names(y))
cols
<- names(joined)[!names(joined) %in% cols]
to_coalesce <- suffix[ifelse(endsWith(to_coalesce, suffix[1]), 1, 2)]
suffix_used # remove suffixes and deduplicate
<- unique(substr(
to_coalesce
to_coalesce, 1,
nchar(to_coalesce) - nchar(suffix_used)
))
<- purrr::map_dfc(to_coalesce, ~dplyr::coalesce(
coalesced paste0(.x, suffix[1])]],
joined[[paste0(.x, suffix[2])]]
joined[[
))names(coalesced) <- to_coalesce
::bind_cols(joined, coalesced)[cols]
dplyr }
Hier ein Beispiel, wie der Befehl angewandt werden kann:
<-coalesce_join(data_joined_1, Data_frame_C, by=c("Name", "Lastname"))
data_joined_merge_with_coalesce_join
::kbl(data_joined_merge_with_coalesce_join, caption="Die fehlenden Werte wurden mit dem coalesce Befehl ersetzt.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument |
---|---|---|
John | Lennon | Guitar |
Paul | McCartney | Bass |
George | Harrison | Guitar |
Ringo | Starr | Drums |
Keith | Richards | Guitar |
Mick | Jagger | Guitar |
Bill | Wyman | Bass |
Charlie | Watts | Drums |
Ron | Wood | Guitar |
Mick | Taylor | Guitar |
Brian | Jones | Guitar |
Ian | Stewart | Keyboards |
Bill | Preston | Keyboards |
Bobby | Keys | Saxophone |
Stellen Sie sich nun die Situation vor, dass wir einige fehlende Werte in einem neuen Datensatz haben, den wir mit einem alten Datensatz zusammenführen möchten, der einige falsche Werte enthält.
Zuerst fügen wir dem neuen Datensatz ein paar fehlende Werte hinzu:
<-data_joined_merge_with_coalesce_join
Data_frame_new$Instrument[Data_frame_new$Instrument=="Guitar"]<-NA
Data_frame_new::kbl(Data_frame_new, caption="Hier der Datensatz, in den wir ein paar fehlende Werte eingefügt haben.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument |
---|---|---|
John | Lennon | NA |
Paul | McCartney | Bass |
George | Harrison | NA |
Ringo | Starr | Drums |
Keith | Richards | NA |
Mick | Jagger | NA |
Bill | Wyman | Bass |
Charlie | Watts | Drums |
Ron | Wood | NA |
Mick | Taylor | NA |
Brian | Jones | NA |
Ian | Stewart | Keyboards |
Bill | Preston | Keyboards |
Bobby | Keys | Saxophone |
Hier erstellen wir den “alten” Datensatz mit den falschen Angaben.
<-data_joined_merge_with_coalesce_join
Data_frame_old
$Instrument[Data_frame_old$Instrument=="Bass"]<-"Bagpipes"
Data_frame_old
::kbl(Data_frame_old, caption="Hier der falsche Datensatz, in dem fälschlicherweise steht, dass Bill Wyman und Paul McCartney Dudelsack als Hauptinstrument spielen.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument |
---|---|---|
John | Lennon | Guitar |
Paul | McCartney | Bagpipes |
George | Harrison | Guitar |
Ringo | Starr | Drums |
Keith | Richards | Guitar |
Mick | Jagger | Guitar |
Bill | Wyman | Bagpipes |
Charlie | Watts | Drums |
Ron | Wood | Guitar |
Mick | Taylor | Guitar |
Brian | Jones | Guitar |
Ian | Stewart | Keyboards |
Bill | Preston | Keyboards |
Bobby | Keys | Saxophone |
Was ist, wenn im alten Datenblatt falsche Informationen enthalten sind und Sie bei Unstimmigkeiten nur die Informationen des neueren Datenblattes verwenden möchten?
Jetzt wollen wir die Werte aus dem neuen Datenblatt übernehmen, wenn es Diskrepanzen gibt. Bei fehlenden Werten wollen wir die Werte aus der alten Datentabelle übernehmen.
<- rquery::natural_join(Data_frame_new,Data_frame_old,
data_corrected by = c("Name", "Lastname"),
jointype = "FULL")
::kbl(data_corrected, caption="Wenn es fehlende Werte hat, nimmt es die nicht-fehlenden Werte des anderen Datensatzes. Wenn die Werte nicht übereinstimmen, nimmt es die Werte des neuen (im Befehl links aufgeführten) Datensatzes") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument |
---|---|---|
Bill | Preston | Keyboards |
Bill | Wyman | Bass |
Bobby | Keys | Saxophone |
Brian | Jones | Guitar |
Charlie | Watts | Drums |
George | Harrison | Guitar |
Ian | Stewart | Keyboards |
John | Lennon | Guitar |
Keith | Richards | Guitar |
Mick | Jagger | Guitar |
Mick | Taylor | Guitar |
Paul | McCartney | Bass |
Ringo | Starr | Drums |
Ron | Wood | Guitar |
Wenn wir es falsch machen - zum Beispiel wenn wir im Befehl *natural_join den Datensatz mit den falschen Werten zuerst (links) aufführen, so wird die zusammengeführte Tabelle natürlich auch falsch sein:
<- rquery::natural_join(Data_frame_old,Data_frame_new,
data_wrongly_merged by = c("Name", "Lastname"),
jointype = "FULL")
::kbl(data_wrongly_merged, caption="Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Instrument |
---|---|---|
Bill | Preston | Keyboards |
Bill | Wyman | Bagpipes |
Bobby | Keys | Saxophone |
Brian | Jones | Guitar |
Charlie | Watts | Drums |
George | Harrison | Guitar |
Ian | Stewart | Keyboards |
John | Lennon | Guitar |
Keith | Richards | Guitar |
Mick | Jagger | Guitar |
Mick | Taylor | Guitar |
Paul | McCartney | Bagpipes |
Ringo | Starr | Drums |
Ron | Wood | Guitar |
Falls Sie noch mehr wissen möchten, finden Sie hier noch ein Video:
61.3 Hinzufügen von Zeilen / Fällen (Rows)
Wenn wir nur neue Fälle, respektive neue Zeilen hinzufügen möchten, ohne dass es eine Überlappung gibt, so können wir auch bind_rows benutzen. Hier erstellen wir zwei Datensätze, beatles und stones mit den Namen der jeweiligen Musikern.
<-Data_frame_A %>%
beatlesfilter(Name %in% c("John", "Paul", "George", "Ringo")) %>%
mutate(Band="Beatles")
<-Data_frame_A %>%
stonesfilter(Name %in% c("Mick", "Charlie", "Keith", "Bill", "Brian", "Ron")) %>%
mutate(Band="Rolling Stones")
::kbl(beatles, caption="Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse") %>%
kable_styling(full_width = F, font_size = 10)
Name | Lastname | Band |
---|---|---|
John | Lennon | Beatles |
Paul | McCartney | Beatles |
George | Harrison | Beatles |
Ringo | Starr | Beatles |
::kbl(stones, caption="Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")%>%
kable_styling(full_width = F, font_size = 10)
Name | Lastname | Band |
---|---|---|
Keith | Richards | Rolling Stones |
Mick | Jagger | Rolling Stones |
Bill | Wyman | Rolling Stones |
Charlie | Watts | Rolling Stones |
Ron | Wood | Rolling Stones |
Mick | Taylor | Rolling Stones |
Brian | Jones | Rolling Stones |
Bill | Preston | Rolling Stones |
Nun können wir die beiden Datensätze zusammenfügen mit bind_rows.
<-bind_rows(beatles, stones)
combined_bands
::kbl(combined_bands, caption="Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.") %>% kable_classic(full_width = F, html_font = "Cambria") kableExtra
Name | Lastname | Band |
---|---|---|
John | Lennon | Beatles |
Paul | McCartney | Beatles |
George | Harrison | Beatles |
Ringo | Starr | Beatles |
Keith | Richards | Rolling Stones |
Mick | Jagger | Rolling Stones |
Bill | Wyman | Rolling Stones |
Charlie | Watts | Rolling Stones |
Ron | Wood | Rolling Stones |
Mick | Taylor | Rolling Stones |
Brian | Jones | Rolling Stones |
Bill | Preston | Rolling Stones |
Es gibt auch noch einen bind_cols Befehl, der ähnlich wie die join Befehle funktioniert. Nehmen wir an, wir hätten noch eine Variable mit der Information des Rankings der Bands und möchten die danach hinzufügen. Wir haben hier aber nicht die gleiche Kontrolle wie bei den join Befehlen. Wenn die beiden Datensätze falsch sortiert sind, oder in einem Datensatz zusätzliche Zeilen vorkommen, so würden sie falsch zusammengesetzt. Ausserdem haben wir in unserem Beispiel in beiden Datensätzen die Variable Band, die kommt nun zweimal vor. Sie wird von R automatisch umgetauft.
<-data.frame(Band=c(rep("Beatles", times=4), rep("Rolling Stones", times=8)),Ranking=c(rep("Beste Band der Welt", times=4), rep("Zweitbeste Band der Welt", times=8)))
Ranking
<-bind_cols(combined_bands, Ranking, .name_repair="unique") combined_bands_variante_1
## New names:
## • `Band` -> `Band...3`
## • `Band` -> `Band...4`
::kbl(combined_bands_variante_1, caption="Datensatz nach dem Zusammenfügen mit bind_cols. Gefährlich, da wir keine Schlüsselvariable angeben. Falls der Datensatz nicht korrekt sortiert ist, würden die Daten falsch zusammengefügt.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Band…3 | Band…4 | Ranking |
---|---|---|---|---|
John | Lennon | Beatles | Beatles | Beste Band der Welt |
Paul | McCartney | Beatles | Beatles | Beste Band der Welt |
George | Harrison | Beatles | Beatles | Beste Band der Welt |
Ringo | Starr | Beatles | Beatles | Beste Band der Welt |
Keith | Richards | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Mick | Jagger | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Bill | Wyman | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Charlie | Watts | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Ron | Wood | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Mick | Taylor | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Brian | Jones | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Bill | Preston | Rolling Stones | Rolling Stones | Zweitbeste Band der Welt |
Da wir nicht in beiden Datensätzen vernünftige Schlüsselvariablen haben, wäre das zusammenfügen mit den join Befehlen nicht sinnvoll, da wir dann plötzlich 80 Zeilen haben.
<-left_join(combined_bands, Ranking, by=c("Band"))
combined_band_variante_2
nrow(combined_band_variante_2)
## [1] 80
Wir könnten das Problem jedoch lösen, wenn wir die Duplikate im Ranking Datensatz löschen:
<-distinct(Ranking) Ranking_distinct
Nun können wir mit join fusionieren:
<-full_join(combined_bands, Ranking_distinct, by=c("Band"))
combined_band_variante_3
::kbl(combined_band_variante_3, caption="Zusammengefügter Datensatz nach dem Löschen der Duplikate im Datensatz Ranking.") %>% kable_classic(full_width = F, html_font = "Cambria") %>%
kableExtracolumn_spec(3, bold = T, color = "#67857D", background = "chartreuse")
Name | Lastname | Band | Ranking |
---|---|---|---|
John | Lennon | Beatles | Beste Band der Welt |
Paul | McCartney | Beatles | Beste Band der Welt |
George | Harrison | Beatles | Beste Band der Welt |
Ringo | Starr | Beatles | Beste Band der Welt |
Keith | Richards | Rolling Stones | Zweitbeste Band der Welt |
Mick | Jagger | Rolling Stones | Zweitbeste Band der Welt |
Bill | Wyman | Rolling Stones | Zweitbeste Band der Welt |
Charlie | Watts | Rolling Stones | Zweitbeste Band der Welt |
Ron | Wood | Rolling Stones | Zweitbeste Band der Welt |
Mick | Taylor | Rolling Stones | Zweitbeste Band der Welt |
Brian | Jones | Rolling Stones | Zweitbeste Band der Welt |
Bill | Preston | Rolling Stones | Zweitbeste Band der Welt |
So, das war’s für dieses Kapitel.