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.

Zwei Datentabellen anhand einer Schlüssevariable - hier die Variable Key_Patient_ID - zusammenfügen.

Wir erstellen zuerst zwei Data Frames.

set.seed=123
data_1<-data.frame(ID=1:10, Alter=abs(round(rnorm(10, 50,12))), BMI=abs(round(rnorm(10,25,3))))
data_2<-data.frame(ID=3:14, Schmerz=abs(round(rnorm(12, 4,2))), Funktion=abs(round(rnorm(12,50,3))))

Die Schlüsselvariable ist hier die Variable ID. (Die Schlüsselvariablen sind jeweils grün markiert in den Tabellen auf dieser Seite.)

Table 61.1: Daten Alter BMI
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

/

Table 22.1: Daten Schmerz und Funktion
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()
Alle<-full_join(data_1, data_2, by="ID")
kableExtra::kbl(Alle, caption="Alle Teilnehmenden, die in einem der beiden Datenblättern sind.") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 43.1: Alle Teilnehmenden, die in einem der beiden Datenblättern sind.
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
Alle_aus_Data_1<-left_join(data_1, data_2, by="ID")
kableExtra::kbl(Alle_aus_Data_1, caption="Alle Teilnehmenden, die im Datenblatt 1 sind.") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 43.2: Alle Teilnehmenden, die im Datenblatt 1 sind.
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
Alle_aus_Data_2<-right_join(data_1, data_2, by="ID")
kableExtra::kbl(Alle_aus_Data_2, caption="Alle Teilnehmenden, die im Datenblatt 2 sind. ") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 1.1: Alle Teilnehmenden, die im Datenblatt 2 sind.
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
Alle_die_in_beiden_Daten_sind<-inner_join(data_1, data_2, by="ID")
kableExtra::kbl(Alle_die_in_beiden_Daten_sind, caption="Alle Teilnehmenden, die in beiden Datenblättern sind.") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 1.2: Alle Teilnehmenden, die in beiden Datenblättern sind.
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
Alle_in_1_die_nicht_in_2_sind_nur_Daten_aus_1<-anti_join(data_1, data_2, by="ID")
kableExtra::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") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 61.2: Alle Teilnehmenden, die in 1, aber nicht in 2 sind. Nur Daten aus 1.
ID Alter BMI
1 44 27
2 66 29
Alle_in_1_die_auch_in_2_sind_nur_Daten_1<-semi_join(data_1, data_2, by="ID")
kableExtra::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") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 58.1: Alle Teilnehmenden, die in 1 und 2 sind, nur Daten aus 1.
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

set.seed=123
data_1<-data.frame(ID=1:10, Alter=abs(round(rnorm(10, 50,12))), BMI=abs(round(rnorm(10,25,3))), Gelenk="Schulter")
data_2<-data.frame(ID=3:14, Schmerz=abs(round(rnorm(12, 4,2))), Funktion=abs(round(rnorm(12,50,3))), Gelenk="Schulter")

data_1b<-data.frame(ID=1:10, Alter=data_1$Alter, BMI=data_1$BMI, Gelenk="Knie")
data_2b<-data.frame(ID=3:14, Schmerz=abs(round(rnorm(12, 4,2))), Funktion=abs(round(rnorm(12,50,3))), Gelenk="Knie")

data_1<-bind_rows(data_1, data_1b)

data_2<-bind_rows(data_2, data_2b)

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.

falsch_zusammengefuegt<-full_join(data_1, data_2, by="ID") %>% 
arrange(ID)
kableExtra::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") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 58.3: Falsch! Wir haben den Schlüssen ungenügend gewählt. Die ID alleine ist nicht eindeutig identifizierende für jede Zeile.
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.

richtig_zusammengefuegt<-full_join(data_1, data_2, by=c("ID", "Gelenk")) %>% 
  arrange(ID)
kableExtra::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") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 58.4: Richtg! Mit der Kombination von ID und Gelenk bilden wir einen eindeutigen Schlüssel
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/.

key<-as.character(1:100)
name<-(rep(sample(letters[1:26], size=5, replace=TRUE), times=100))

coalesce_join <- function(x, y, 
                          by = NULL, suffix = c(".x", ".y"), 
                          join = dplyr::full_join, ...) {
  joined <- join(x, y, by = by, suffix = suffix, ...)
  # names of desired output
  cols <- union(names(x), names(y))
  
  to_coalesce <- names(joined)[!names(joined) %in% cols]
  suffix_used <- suffix[ifelse(endsWith(to_coalesce, suffix[1]), 1, 2)]
  # remove suffixes and deduplicate
  to_coalesce <- unique(substr(
    to_coalesce, 
    1, 
    nchar(to_coalesce) - nchar(suffix_used)
  ))
  
  coalesced <- purrr::map_dfc(to_coalesce, ~dplyr::coalesce(
    joined[[paste0(.x, suffix[1])]], 
    joined[[paste0(.x, suffix[2])]]
  ))
  names(coalesced) <- to_coalesce
  
  dplyr::bind_cols(joined, coalesced)[cols]
}
data_1<-richtig_zusammengefuegt[1:4]
data_2<-richtig_zusammengefuegt[,c(1:2,4:6)]

data_2<-data_2 %>% 
  mutate(Alter=ifelse(Alter>50,NA, Alter))

Hier die zwei Datentabellen:

kableExtra::kbl(data_1, caption="Data 1, Schlüssel-Variablen in Grün") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(c(1,4), bold = T, color = "#67857D", background = "chartreuse")%>% 
  kable_styling(full_width = F, font_size = 10)
Table 61.3: Data 1, Schlüssel-Variablen in Grün
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
kableExtra::kbl(data_2, caption="Data 2, Schlüssel-Variablen in Grün") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(c(1,3), bold = T, color = "#67857D", background = "chartreuse")%>% 
  kable_styling(full_width = F, font_size = 10)
Table 54.1: Data 2, Schlüssel-Variablen in Grün
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.

data_coalesce<-coalesce_join(data_1, data_2, by=c("ID", "Gelenk"))

kableExtra::kbl(data_coalesce, caption="Data 2") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(c(1,4), bold = T, color = "#67857D", background = "chartreuse")%>% 
  kable_styling(full_width = F, font_size = 10)
Table 7.1: Data 2
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_A<-data.frame(Name=c("John", "Paul", "George", "Ringo", "Keith", "Mick", "Bill", "Charlie", "Ron", "Mick", "Brian", "Ian", "Bill"), 
                         Lastname=c("Lennon", "McCartney", "Harrison", "Starr", "Richards","Jagger", "Wyman", "Watts", "Wood", "Taylor", "Jones","Stewart", "Preston"))

Data_frame_B<-data.frame(Lastname=c("Lennon", "McCartney", "Harrison", "Starr", "Richards","Jagger", "Wyman", "Watts", "Wood", "Taylor", "Jones","Stewart", "Keys"),
                        Name=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_C<-data.frame(Lastname=c("Lennon", "McCartney", "Harrison", "Starr", "Richards","Jagger", "Wyman", "Watts", "Wood", "Taylor", "Jones","Stewart", "Keys", "Preston"),
                         Name=c("John", "Paul", "George", "Ringo", "Keith", "Mick", "Bill", "Charlie", "Ron", "Mick", "Brian", "Ian", "Bobby", "Bill")) %>% 
  mutate(Instrument=case_when(
    Name=="Bobby"&Lastname=="Keys"~"Saxophone",
    Name=="Bill"&Lastname=="Preston"~"Keyboards"))


Jetzt schauen wir uns die drei Datentabellen an:

kableExtra::kbl(Data_frame_A, caption="Tabelle A") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  kable_styling(full_width = F, font_size = 10)
Table 58.5: Tabelle A
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
kableExtra::kbl(Data_frame_B, caption="Tabelle B") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  kable_styling(full_width = F, font_size = 10)
Table 61.4: Tabelle B
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
kableExtra::kbl(Data_frame_C, caption="Tabelle C") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  kable_styling(full_width = F, font_size = 10)
Table 61.5: Tabelle C
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.

data_joined_1<-left_join(Data_frame_A, Data_frame_B, by=c("Name", "Lastname"))
data_joined_2<-left_join(data_joined_1, Data_frame_C, by=c("Name", "Lastname"))

kableExtra::kbl(data_joined_2, caption="Stimmen die Werte nicht überein, werden zwei Spalten erstellt.") %>% kable_classic(full_width = F, html_font = "Cambria") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.6: Stimmen die Werte nicht überein, werden zwei Spalten erstellt.
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

data_joined_merge_all<-merge(data_joined_1, Data_frame_C, by=c("Name", "Lastname"), all=TRUE)

kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.7: Auch mit merge werden wieder zwei Spalten erstellt, falls die Zellinhalte nicht übereinstimmen.
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
Wir sollten immer überprüfen, warum es Unterschiede in den Inhalten gibt und die Variablen entsprechend bearbeiten.

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.

variante_1<-data_joined_merge_all %>% 
  mutate(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:

kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.8: 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.
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
Es gibt eine Lösung, die dies automatisch beim join Prozess durchführt. Dies ist vor allem hilfreich, wenn wir viele solche Variablen haben. Man sollte jedoch zuerst den normalen join Prozess durchführen, um eine Kontrolle zu haben, bei welchen Variablen es unterschiedliche Werte hat - und vor allem sollte man verstehen, warum es Unterschiede gibt. Beschreibung der Lösung:https://community.rstudio.com/t/merging-2-dataframes-and-replacing-na-values/32123/2.

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/)


key<-as.character(1:100)
name<-(rep(sample(letters[1:26], size=5, replace=TRUE), times=100))
        
coalesce_join <- function(x, y, 
                          by = NULL, suffix = c(".x", ".y"), 
                          join = dplyr::full_join, ...) {
  joined <- join(x, y, by = by, suffix = suffix, ...)
  # names of desired output
  cols <- union(names(x), names(y))
  
  to_coalesce <- names(joined)[!names(joined) %in% cols]
  suffix_used <- suffix[ifelse(endsWith(to_coalesce, suffix[1]), 1, 2)]
  # remove suffixes and deduplicate
  to_coalesce <- unique(substr(
    to_coalesce, 
    1, 
    nchar(to_coalesce) - nchar(suffix_used)
  ))
  
  coalesced <- purrr::map_dfc(to_coalesce, ~dplyr::coalesce(
    joined[[paste0(.x, suffix[1])]], 
    joined[[paste0(.x, suffix[2])]]
  ))
  names(coalesced) <- to_coalesce
  
  dplyr::bind_cols(joined, coalesced)[cols]
}

Hier ein Beispiel, wie der Befehl angewandt werden kann:

data_joined_merge_with_coalesce_join<-coalesce_join(data_joined_1, Data_frame_C, by=c("Name", "Lastname"))

kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.9: Die fehlenden Werte wurden mit dem coalesce Befehl ersetzt.
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_frame_new<-data_joined_merge_with_coalesce_join
Data_frame_new$Instrument[Data_frame_new$Instrument=="Guitar"]<-NA
kableExtra::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") %>% 
  column_spec(1, bold = T, color = "#67857D", background = "chartreuse")
Table 61.10: Hier der Datensatz, in den wir ein paar fehlende Werte eingefügt haben.
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_frame_old<-data_joined_merge_with_coalesce_join 

Data_frame_old$Instrument[Data_frame_old$Instrument=="Bass"]<-"Bagpipes"


kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.11: Hier der falsche Datensatz, in dem fälschlicherweise steht, dass Bill Wyman und Paul McCartney Dudelsack als Hauptinstrument spielen.
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.

data_corrected <- rquery::natural_join(Data_frame_new,Data_frame_old, 
                                 by = c("Name", "Lastname"),
                                 jointype = "FULL") 

kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.12: 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
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:

data_wrongly_merged <- rquery::natural_join(Data_frame_old,Data_frame_new, 
                                 by = c("Name", "Lastname"),
                                 jointype = "FULL") 

kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.13: Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.
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.

beatles<-Data_frame_A %>% 
  filter(Name %in% c("John", "Paul", "George", "Ringo")) %>% 
  mutate(Band="Beatles")

stones<-Data_frame_A %>% 
  filter(Name %in% c("Mick", "Charlie", "Keith", "Bill", "Brian", "Ron")) %>% 
  mutate(Band="Rolling Stones")
kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse") %>% 
  kable_styling(full_width = F, font_size = 10)
Table 61.14: Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.
Name Lastname Band
John Lennon Beatles
Paul McCartney Beatles
George Harrison Beatles
Ringo Starr Beatles
kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")%>% 
  kable_styling(full_width = F, font_size = 10)
Table 61.15: Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.
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.

combined_bands<-bind_rows(beatles, stones)

kableExtra::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") 
Table 61.16: Hier haben wir die Tabelle mit den falschen Werten links (d.h. zuerst) im Befehl aufgeführt, so nimmt es hier die falschen Werte.
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.

Ranking<-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)))

combined_bands_variante_1<-bind_cols(combined_bands, Ranking, .name_repair="unique")     
## New names:
## • `Band` -> `Band...3`
## • `Band` -> `Band...4`
kableExtra::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") %>% 
  column_spec(1:2, bold = T, color = "#67857D", background = "chartreuse")
Table 61.17: 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.
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.

combined_band_variante_2<-left_join(combined_bands, Ranking, by=c("Band"))

nrow(combined_band_variante_2)
## [1] 80

Wir könnten das Problem jedoch lösen, wenn wir die Duplikate im Ranking Datensatz löschen:

Ranking_distinct<-distinct(Ranking)

Nun können wir mit join fusionieren:

combined_band_variante_3<-full_join(combined_bands, Ranking_distinct, by=c("Band"))

kableExtra::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") %>% 
  column_spec(3, bold = T, color = "#67857D", background = "chartreuse")
Table 61.18: Zusammengefügter Datensatz nach dem Löschen der Duplikate im Datensatz Ranking.
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.