Hej Leute,

in diesem Artikel geht es um die Struktur von Datentabellen, also wie eine Tabelle aufgebaut ist. Was gibt es denn darüber zu erzählen, denkt ihr euch vielleicht. Eine Tabelle hat Zeilen und Spalten. Das stimmt natürlich, aber dennoch gibt es zwei Varianten, die beide ihre Vor- und Nachteile haben, so dass auch beide ihre Daseins-Berechtigung haben.

Im ersten Teil des Artikels erfahrt ihr also, was ein Quer-Datensatz und was ein Längs-Datensatz ist. Dann gehen wir auf die Vor- und Nachteile der beiden Strukturen ein, welche auch erklären, wann man besser Längs oder Quer verwendet. Nun kommt der Praxisteil: Wie konvertiert man denn nun zwischen den beiden Formaten? Das erkläre ich Euch in einer bebilderten Schritt-für-Schritt-Anleitung in Excel. Für die R-Nutzer unter euch gehe ich auf die Funktionen aus den Packages tidyr, reshape2 und data.table ein, natürlich mit Code-Beispielen, damit ihr sofort loslegen könnt.

Der Quer-Datensatz – viele Spalten

Der Quer-Datensatz (im Englischen Wide oder seltener un-stacked) ist die normale Tabelle, wie wir sie kennen. Jede Zeile steht für einen Eintrag, also zum Beispiel einen Kunden oder ein Produkt. Die Spalten entsprechen den Attributen. Bei Kunden könnte das Name, Adresse, Alter, Geschlecht sein, beim Produkt vielleicht Länge, Breite, Höhe, Gewicht, Farbe, Warenkategorie.

Datentabelle im Querformat - Jedes Attribut eine Spalte

Quer-Datenstruktur: Spalte entspricht Variable

Auch bei einer Befragung ist der Quer-Datensatz typisch. Dort entspricht jede Zeile einem ausgefüllten Fragebogen, die Antworten auf die Fragen stehen in den Spalten

Beispiel Fragebogendatensatz

Der Längs-Datensatz – viele Zeilen

Der Längs-Datensatz oder auch Kontenmodell (im Englischen Long, Tall oder seltener stacked) hat nur wenige Spalten, dafür aber viele Zeilen. Denn jedes Attribut bekommt eine eigene Zeile. Im Extremfall benötigt man nur drei Spalten:

  1. Die ID-Spalte(n), welche einen Identifikator (z.B. für einen Fragebogen, ein Produkt, einen Kunden …) enthält
  2. Die Variable-Spalte, welche die Bezeichnung des Attributs / der Variablen enthält
  3. Die Value-Spalte, welche die tatsächlichen Werte enthält

datentabelle im Längsformat - Variablenspalte und Wertespalte

Beispiel für Längsstruktur von Daten - drei Spalten

Die Vor- und Nachteile der beiden Strukturen

Die Vorteile der Quer-Datenstruktur

Der Quer-Datensatz hat den Vorteil, dass er die leichter lesbare und damit auch verbreitetste Struktur ist. Jede Spalte hat ihr Format (integer, float, string, Datum). Diese lassen sich gut auswerten, denn es ist ja alles schön nach Spalten getrennt.

Die Nachteile der Quer-Datenstruktur

Der Quer-Datensatz hat aber zwei Nachteile. Zum einen ist es nicht sehr speicherschonend, denn für jede ID wird für jedes Attribut ein Platz reserviert. Wenn nun die Zeilen sehr unterschiedlich sind, dann ist es nicht effizient, die Daten so zu speichern. Enthält zum Beispiel eine Tabelle zwei Fragebögen, wobei der eine 5 Fragen und der andere 100 Fragen enthält, dann muss die Tabelle trotzdem mindestens 101 (ID und 100 Variablen) Spalten haben. In den Zeilen mit dem kurzen Fragebögen sind halt nur 6 Spalten befüllt.

Der zweite Nachteil ist die Unflexibilität. Wenn man ein weiteres Attribut benötigt, dann benötigt man also eine neue Spalte. Erstmal kein Problem, wenn man nur für sich arbeitet. Aber wenn dahinter eine große Tabelle auf einer Datenbank liegt, muss diese auch angepasst werden, was Zeit kostet. Wenn nun die Daten über mehrere Systeme fließen, muss alles an die neue Struktur angepasst werden.

Die Vorteile der Längs-Datenstrukur

So kommt die Längs-Datenstruktur ins Spiel, denn diese zwei Nachteile hat sie nicht. Zum einen gibt es nur eine Zeile, wenn das Attribut auch da ist. In dem Fragebogenbeispiel kommen für einen kurzen Fragebogen maximal 5 Zeilen hinzu, für einen langen maximal 100 Zeilen. Maximal, denn werden Fragen nicht beantwortet, tauchen sie im Datensatz auch nicht unbedingt auf.

Beispiel Fragebogenauswertung - Längsstruktur eines Datensatzes

Der zweite Vorteil ist die Flexibilität, denn kommt noch ein Attribut hinzu, dann kommt eine Zeile hinzu. Fällt eine Variable weg (z.B. neue Version eines Fragebogens), dann ändert sich nichts. Die Struktur der Tabelle bleibt identisch.

Die Nachteile der Längs-Datenstruktur

OK, aber natürlich hat so ein Längs-Datensatz auch ein paar Nachteile. Zum einen gestalten sich die Auswertungen als nicht ganz so intuitiv, insbesondere, wenn man mit fehlenden Werten zu tun hat.

Zum anderen machen verschiedene Datentypen ein Problem. Denn hat man ein numerisches Attribut und ein Text-Attribut, muss man die Value-Spalte als String definieren. Das verlangsamt wiederum die Auswertungen, denn der String muss ja wieder in eine Zahl umgewandelt werden, wenn man zum Beispiel einen Mittelwert berechnen will.

Dieses Problem kann man aber umgehen, indem man für jeden Datentyp eine eigene Value-Spalte definiert, also eine Spalte für numerische Werte, eine für Text und eine für Datumsangaben. Aber auch das Vorgehen hat Nachteile, denn nun sind ja wieder viele Felder leer.

Längsdatensatz mit drei Spalten für verschiedene Datentypen: numerisch, Strings, Datum

Wann benutze ich welche Struktur?

Eigentlich ergeben sich die Anwendungen automatisch aus den Vor- und Nachteilen. Die erste Regel ist, dass bei der Weitergabe an jemanden nicht IT-affinen die Quer-Struktur (viele Spalten) verwendet werden sollte. Generell ist meistens die Quer-Struktur vorzuziehen, denn diese ist intuitiver und die meisten Programme sind darauf ausgelegt. Ist aber der Inhalt der Daten variabel bzw. unterliegt häufiger mal Änderungen, die Struktur aber nicht ohne weiteres zu ändern (z.B. SQL-Tabellen), dann sollte man die Längs-Struktur verwenden. Auch bei späterer interaktiver Pivotierung, z.B. in Excel, kann man einfacher mit der Längs-Struktur arbeiten.

Zum Glück kann man relativ problemlos, je nach Größe des Datensatzes, einen Quer-Datensatz in einen Längs-Datensatz und umgekehrt konvertieren.

Umwandeln in Excel

Von Längs zu Quer in Excel mittels Pivot

Einen Längs-Datensatz in einen Quer-Datensatz umzuwandeln, ist in Excel ganz einfach. Aber nur, wenn nur numerische Werte (oder Datumswerte) vorhanden sind. Dafür erzeugt man eine Pivot-Tabelle.

  1. Zuerst wählt ihr den Datensatz aus und erzeugt einen PivotTable
  2. Die ID-Variable (hier BogenNr) gehört in die Zeilenbeschreibung, die Variable-Spalte in die Spaltenbeschreibung und die Wert-Spalte in Werte
  3. Nun noch die Aggregation umstellen von Anzahl auf irgendetwas anderes (Summe, Mittelwert, Min, Max). Es sollte eh nur einen Wert pro Zelle geben, deshalb ist es egal. Nur eben nicht die Standard-Einstellung Anzahl.
  4. Jetzt noch das Datumsfeld korrekt formatieren (Shortcut Strg+1)
Excel-laengs-zu-quer-pivot
Excel-laengs-zu-quer-pivot-02
Excel-laengs-zu-quer-pivot-03
Excel-laengs-zu-quer-pivot-04
Excel-laengs-zu-quer-pivot-05
Excel-laengs-zu-quer-pivot-06
Excel-laengs-zu-quer-pivot-07
previous arrow
next arrow
 

Von Längs zu Quer in Excel mittels Formeln

Wir sehen, dass die Wörter (Strings) im Pivot nicht korrekt übertragen werden. Es gibt ein paar Varianten, wie man das erreichen kann. Ich zeige euch die einfachste, die aber mit etwas Handarbeit verbunden ist.

  1. Zuerst fügt man eine Spalte an den Längs-Datensatz an, nämlich einen Schlüssel. Das ist einfach die Kombination der zwei Spalten BogenNr und Variable, getrennt durch das Zeichen #. So kann jede Zeile eindeutig identifiziert werden.
  2. Nun bauen wir das Gerüst der Tabelle. Ihr könntet die Ausprägungen der BogenNr und die Ausprägungen der Variable abtippen. Weniger Arbeit ist es allerdings, die Werte zu kopieren und dann die Duplikate zu entfernen. Bei den Spalten kopiert ihr das Ergebnis nochmal, fügt es aber transponiert ein.
  3. Nun bauen wir uns die Formel per INDEX-VERGLEICH. Die Kombination der beiden Befehle funktioniert wie SVERWEIS, ist allerdings etwas flexibler. Beim SVERWEIS hätten wir den Schlüssel in die erste Spalte schreiben müssen.
  4. Wir ergänzen noch eine kleine Abfrage mit WENNFEHLER, damit fehlende Werte leere Felder sind. Dieser Schritt ist nicht unbedingt nötig und eigentlich ist es auch korrekter, die fehlenden Werte mit #NV zu bezeichnen. Aber die Tabelle sieht etwas netter aus.
  5. Letzter Schritt: Noch das Datumsfeld wieder vernünftig formatieren, also auf Datumsformat umstellen.
Excel-laengs-zu-quer-formel-01
Excel-laengs-zu-quer-formel-02
Excel-laengs-zu-quer-formel-02b
Excel-laengs-zu-quer-formel-03
Excel-laengs-zu-quer-formel-04
Excel-laengs-zu-quer-formel-05
Excel-laengs-zu-quer-formel-06
previous arrow
next arrow
 

Man kann nun noch den Prozess, die Quer-Tabelle aufzubauen, auch in Formeln packen. Meiner Meinung nach ist es den Aufwand aber nicht wert. Denn wenn ich etwas automatisieren möchte, dann benutze ich direkt R oder Python.

Von Quer zu Längs in Excel mittels Formeln

Die umgekehrte Richtung, also von Querformat zu Längsformat können wir auch mit ein bisschen Formelarbeit in Excel realisieren. Allerdings ist es nicht die platzsparende Version, in der nicht ausgefüllte Felder gar nicht erst auftauchen. Aber das kann man im Nachhinein durch Sortierung und dann Löschen schnell erreichen.

  1. Zuerst benötigen wir eine Durchnummerierung der Zeilen. Der (vollständige) Längsdatensatz hat genau (Anzahl Zeilen) * (Anzahl Spalten-1) des Querdatensatzes an Zeilen.
  2. In der zweiten Spalte kommt der Identifikator (hier BogenNr). Hier werden alle BogenNr einfach wiederholt, bis die Tabelle voll ist. Ich habe das durch Modulo-Rechnung (Excel-Befehl REST) gelöst.
  3. Nun kommt die Spalte mit der Variablenbezeichnung. Dazu zählen wir, wie häufig die BogenNr schon vorgekommen ist (Excel-Befehl ZAEHLENWENN) und picken dementsprechend die entsprechenden Spaltennamen aus dem Querdatensatz raus.
  4. Als letzter Schritt kommt noch der tatsächliche Zelleneintrag in die Werte-Spalte. Das lässt sich einfach per INDEX-VERGLEICH lösen. Wir brauchen eben zwei Vergleiche, einen für die Zeile, einen für die Spalte.
Excel-quer-zu-laengs-formel-01
Excel-quer-zu-laengs-formel-02
Excel-quer-zu-laengs-formel-03
Excel-quer-zu-laengs-formel-04
Excel-quer-zu-laengs-formel-05
previous arrow
next arrow
 

Wir sehen, auch diese Umwandlung ist nicht perfekt, denn das Datum wird nun wieder in der internen Darstellung (als ganze Zahl der Tage seit dem Ursprungsdatum, 01.01.1900 entspricht der 1) angezeigt.

Umwandeln in R

In R gibt es mehrere Funktionen, um aus einem Längs- ein Querdatensatz und umgekehrt zu machen, da mehrere Packages entsprechende Funktionen bereitstellen.

Also soweit man das nachvollziehen kann, war reshape2 von Hadley Wickam das erste Package, welches die Konvertierung längs-quer und quer-längs (Befehle melt und dcast) implementiert hat. Wickam hat aber im Package tidyr andere Funktionen (spread und gather) entwickelt, welche mehr zur Syntax des tidyverse passen. Insofern dürfte reshape2 nicht weiterentwickelt werden. Die Konvertierungsfunktionen in data.table (auch melt und dcast) haben die gleiche Syntax wie in reshape2 und rufen die Funktionen aus reshape2 auf, sofern die Daten nur als data.frame und nicht als data.table vorliegen. Sind die Daten allerdings ein data.table, dann werden eigene, für große Datenmengen optimierte, Funktionen aufgerufen.

spread und gather aus dem Package tidyr

Im tidyverse, genauer im R-Package tidyr, gibt es die beiden Funktionen spread und gatherspread breitet die Daten aus, also von längs zu quer. gather sammelt die Daten ein, also von quer zu längs.

Bei spread gibt man als Parameter neben dem Datensatz noch key und value an. Key ist die Spalte im Längsdatensatz, welche den Variablen-/Attributnamen enthält. Value ist die Spalte, welche die tatsächlichen Werte enthält.

Bei gather gibt man auch key und value an, hier sind es aber die Namen, welche der konvertierte Datensatz haben soll. Dann übergibt man noch die Spalten, welche zusammengefasst werden sollen. In unserem Beispiel sind das Frage 1 bis Frage 5. Alternativ und deutlich kürzer schließt man mit Minus die Spalten aus, die erhalten bleiben sollen, in unserem Fall BogenNr.

library(tidyr)
 
Laengs <- data.frame(stringsAsFactors=FALSE,
     BogenNr = c(1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4, 5),
    Variable = c("Frage 1", "Frage 2", "Frage 3", "Frage 4", "Frage 5",
                 "Frage 1", "Frage 2", "Frage 1", "Frage 4", "Frage 5",
                 "Frage 1", "Frage 2", "Frage 3", "Frage 4", "Frage 1"),
        Wert = c("20", "gut", "22.05.2019", "ja", "immer", "25", "schlecht",
                 "12", "nein", "selten", "18", "teils/teils", "06.12.2018",
                 "ja", "27")
)
 
# Umwandeln in Querdatensatz mittels der Funktion spread
# Parameter sind key: Spalte mit Attribut-/Variablenname und value: die Spalte mit den Werten
Quer <- spread(Laengs, key = Variable, value = Wert)
# Alternativ mit Pipe-Schreibweise
Quer <- Laengs %>% spread(key = Variable, value = Wert)
 
# Umwandeln von Quer nach Längs
gather(Quer, key = Variable, value = Wert, -BogenNr)
# Alternativ mit Pipe-Schreibweise
Quer %>% gather(key = Variable, value = Wert, -BogenNr)

Mehr Informationen zum Package tidyr findet ihr unter https://tidyr.tidyverse.org.

melt und dcast aus dem Package data.table

Im Package data.table gibt es die Funktionen dcast und melt. dcast konvertiert einen data.frame oder data.table von längs zu quer. melt ist die Umkehrung, konvertiert als von quer zu längs.

Sind die Daten in einem data.frame, dann ruft auch data.table die entsprechenden Funktionen aus reshape2 auf. Sind die Daten aber ein data.table (mit setDT einfach umzuwandeln), dann werden die performanteren dcast- und melt-Funktionen aus data.table verwendet. Zudem gibt es bei diesen die Möglichkeit, die Variablen-Spalte nicht als Faktor zurückzubekommen.

Bei melt werden als Parameter id.vars die ID-Variable(n) übergeben. Alle anderen Spalten werden zusammen konvertiert. Zudem können die Spaltennamen der Variablenspalte und der Werte-Spalte übergeben werden. Wird der Parameter variable.factor auf FALSE gesetzt, ist die Variablenspalte vom Typ chr (String), andernfalls vom Typ factor. Achtung: Der Parameter variable.factor funktioniert nur, wenn tatsächlich ein data.table vorliegt. Bei einem data.frame geht das nicht.

Bei dcast wird eine Formel übergeben, die die Konvertierung definiert. Auf der linken Seite ist die ID-Variable, rechts von der Tilde steht die Spalte mit den Variablen-/Attributnamen. Zusätzlich kann man mit dem Parameter value.var bestimmen, welche Spalte(n) die Werte enthalten. Ist dieser Parameter nicht angegeben, dann werden alle verbleibenden Spalten verwendet.

# melt und dcast vom Package data.table
library(data.table)
 
# Umwandeln von Quer zu Längs mittels melt
Laengs <- melt(Quer, id.vars = "BogenNr", variable.name = "Variable", 
               value.name = "Wert")
 
# damit die performante melt-Funktion zum Einsatz kommt, muss ein data.table vorliegen
QuerDT <- setDT(Quer)
LaengsDT <- melt(QuerDT, id.vars = "BogenNr",variable.name = "Variable", 
                 value.name = "Wert", variable.factor = FALSE)
# explizite Angabe der umzuwandelnden Spalten (measure.vars)
LaengsDT <- melt(QuerDT, id.vars = "BogenNr", 
                 measure.vars = c("Frage 1","Frage 2", "Frage 3", "Frage 4", "Frage 5"),
                 variable.name = "Variable", value.name = "Wert")
 
# Umwandeln von Längs zu Quer mittels dcast
QuerDT <- dcast(LaengsDT, BogenNr~Variable, value.var = "Wert")

Mehr Informationen zum Package data.table, welches viele Funktionen und Strukturen bereitstellt, um super schnell mit großen Datenmengen umzugehen, findet ihr unter https://github.com/Rdatatable/data.table/wiki/Getting-started.

Speziell zur Umwandlung der beiden Datenformate gibt es auch noch genaueres unter https://cran.r-project.org/web/packages/data.table/vignettes/datatable-reshape.html.

Performance-Unterschiede tidyr vs data.table

In unserem kleinen Beispiel haben beide bei jeweils einer Aufgabe die Nase vorn: spread war schneller als dcast und melt war schneller als gather. Der Fairness halber muss man aber sagen, dass dcast mehr Möglichkeiten bietet, so kann gleichzeitig eine Aggregation/Gruppierung stattfinden.

library(rbenchmark)
benchmark(
  spread = spread(Laengs, key = Variable, value = Wert),
  dcast = dcast(LaengsDT, BogenNr~Variable, value.var = "Wert"),
  replications = 1000
  )
benchmark(
  gather = gather(Quer, key = Variable, value = Wert, -BogenNr),
  melt = melt(QuerDT, id.vars = "BogenNr", variable.name = "Variable", 
              value.name = "Wert", variable.factor = FALSE),
  replications=1000
)

Auch in einem größeren Beispiel zeigen sich die gleichen Unterschiede. Allerdings wird dcast immer besser, je größer der Datensatz ist. Bei 100.000 Zeilen ist dcast schneller. Interessanterweise sind die ursprünglichen melt- und dcast-Funktionen aus reshape2 deutlich schneller als die aus dem Package data.table. Das wundert mich doch sehr. Seht ihr, warum das so ist?

library(rbenchmark)
library(data.table)
library(tibble)
library(tidyr)
n = 1000000
set.seed(93)
BspQuer <- data.frame(ID=1:n,x = rnorm(n), y = runif(n), z = rbinom(n,1,0.3))
BspQuerDT <- setDT(BspQuer)
BspQuerTibble <- as_tibble(BspQuer)
BspLaengsDT <- melt(BspQuer, id.vars = "ID",variable.name = "Variable", 
                    value.name = "Wert", variable.factor = FALSE)
BspLaengsTibble <- gather(BspQuer, key = Variable, value = Wert, -ID)
 
benchmark(
  spread = spread(BspLaengsTibble, key = Variable, value = Wert),
  dcastDT = dcast(BspLaengsDT, ID~Variable, value.var = "Wert"),
  dcastReshape2 = dcast(BspLaengs, ID~Variable, value.var = "Wert"),
  replications = 100
)
 
benchmark(
  gather = gather(BspQuerTibble, key = Variable, value = Wert, -ID),
  meltReshape2 = melt(BspQuer, id.vars = "ID",variable.name = "Variable", 
                      value.name = "Wert"),
  meltDT = melt(BspQuerDT, id.vars = "ID",variable.name = "Variable", 
              value.name = "Wert", variable.factor = FALSE),
  replications = 500
)

Fazit

Habt ihr bis zum Ende durchgehalten? Perfekt! Ich hoffe, die Unterschiede in der Struktur zwischen einem Datensatz im Quer- oder Längs-Format sind nun klar. Ihr versteht, wann ihr welche Struktur einsetzt, weil ihr die Vor- und Nachteile kennt. Und die Schritt-für-Schritt-Anleitung in Excel und die R-Befehle waren hilfreich, so dass ihr einsatzbereit seid.

Happy Coding,

Euer Holger