Seite 1 von 1

Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Mo Jul 08, 2019 8:07 pm
von Luisa
Liebe R-Experten,

Ich stehe vor dem Problem der Datensatztransformation. Ich hoffe, dass ihr mir weiterhelfen könnt und bedanke mich bereits vorab für eure Unterstützung.

Meine Ausgangslage ist wie folgt: Ich habe in Excel (Format xlsx) einen Datensatz, der für 214 Unternehmen über einen Zeitraum von 7 Jahren Daten für 12 Variablen bereitstellt. Dabei befinden sich die Daten für jede Variable auf einem eigenen Tabellenblatt. Jedes Tabellenblatt trägt den Namen der jeweiligen Variable und ist ansonsten exakt gleich aufgebaut.

Die Zeilenbeschriftung in Zeile 1 sieht in jedem Blatt für die ersten 10 Spalten wie folgt aus: number name shortCut year1 year2 year3 year4 year5 year6 year7
Die Daten sind pro Unternehmen in einer Zeile aufgeführt, sodass ich 214 Datenzeilen pro Tabellenblatt habe.

Nun suche ich nach einer Lösung in R um den Paneldatensatz nach folgendem Schema zu erzeugen:
ShortCut year x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12
a. Year1
a. Year2
a. Year3
a. Year4
a. Year5
a. Year6
a. Year7
b. Year1
b. Year2
. .
. .
. .

Gibt es eine Möglichkeit diese Transformation zu erstellen? Da ich noch weitere solcher Dateien analysieren muss, wäre ich über eine R-Lösung sehr dankbar.

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Di Jul 09, 2019 8:27 am
von jogo
Hallo Luisa,

willkommen im Forum!
Ja, es gibt eine Lösung in R. Am Ende könnte auch ein reshape von breit nach lang stehen.
https://stackoverflow.com/questions/218 ... ong-format

Hast Du denn die einzelnen Datenblätter schon eingelesen? (Bitte mal den Code zeigen.)
Wie sieht die Struktur der eingelesenen Objekte aus? ( str(...) )

Gruß, Jörg

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Di Jul 09, 2019 10:23 am
von student
Hallo Luisa,

dazu habe ich auch noch etwas: gather des tidyr-Paketes

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Di Jul 09, 2019 9:38 pm
von Luisa
Hallo Jörg,

vielen Dank für deine Antwort.

Ich bin noch R-Neuling. Daher würde ich mich freuen, wenn ihr mir bereits bei den ersten Schritten helfen würdet.
Aktuell bin ich unsicher, ob read_xlsx für meinen Datensatz die beste Lösung ist

Ich habe erst einmal wie folgt gestartet (aufgrund der Vertraulichkeit, sind die Variablen umbenannt worden):

Code: Alles auswählen

y<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 1)
x1<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 2)
x2<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 3)
x3<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 4)
x3<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 5)
x4<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 6)
x5<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 7)
x6<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 8)
x7<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 9)
x8<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 10)
x9<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 11)
x10<-read_xlsx("Speicherort.xlsx", skip=0, sheet = 14)
Die Struktur sieht wie folgt aus:

Code: Alles auswählen

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':	214 obs. of  10 variables:
 $ number         : num  1 2 3 4 5 6 7 8 9 10 ...
 $ Name: chr  "Unternehmen1" "Unternehmen2" "Unternehmen3" "Unternehmen4" ...
 $ shortcut : chr  "U1" "U2" "U3" "U4" ...
 $ 2015           : num    ...
 $ 2014           : num   ...
 $ 2013           : num   ...
 $ 2012           : num  ...
 $ 2011           : num  ...
 $ 2010           : num   ...
 $ 2009           : num   ...
Aktuell bin ich noch überfragt, wie die Zusammenführung der einzelnen Daten erfolgen kann.

Ich danke euch sehr für eure Unterstützung.

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Mi Jul 10, 2019 8:27 am
von jogo
Hallo Luisa,

für Beispieldaten bin ich soweit:

Code: Alles auswählen

I <- iris[c(5, 1:4)]
I <- data.frame(ID=paste0("id", rownames(I)), I, stringsAsFactors = FALSE) 

d1 <- data.frame(id=I$ID, Jahr=rep(names(I)[3:6], each=nrow(I)), x1=c(as.matrix(I[3:6])), stringsAsFactors = FALSE)
Die numerischen Werte aus den vier Spalten des Dataframes I landen in einer Spalte des Dataframes d1.
Jetzt fehlt nur noch die Übertragung für Deine Daten.
Das könnte so aussehen:

Code: Alles auswählen

Dat <- data.frame(stringsAsFactors = FALSE, U=y$shortcut, Jahr=rep(names(y)[4:10], each=nrow(y)), 
                 y=c(as.matrix(y[4:10])),
                 x1=c(as.matrix(x1[4:10])),
                 x2=c(as.matrix(x2[4:10])),
                 x3=c(as.matrix(x3[4:10])),
                 x4=c(as.matrix(x4[4:10])),
                 x5=c(as.matrix(x5[4:10])),
                 x6=c(as.matrix(x6[4:10])),
                 x7=c(as.matrix(x7[4:10])),
                 x8=c(as.matrix(x8[4:10])),
                 x9=c(as.matrix(x9[4:10])),
                 x10=c(as.matrix(x10[4:10]))
)
Bitte beachte beim Schreiben Deiner Nachrichten die Formatierung: viewtopic.php?f=20&t=29
(diesmal habe ich Deine Nachricht nachgebessert)

Gruß, Jörg

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: So Jul 14, 2019 3:27 pm
von Luisa
Lieber Jörg,

vielen lieben Dank. Das hat super geklappt!

Ich danke dir für deine Unterstützung :)

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: So Jul 14, 2019 6:45 pm
von Athomas
jogo hat geschrieben: Di Jul 09, 2019 8:27 am Am Ende könnte auch ein reshape von breit nach lang stehen.
Hallo Jörg, ich finde die Idee, die Daten zu "reshapen" naheliegend und gut - warum bist Du davon wieder abgekommen?

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Mo Jul 15, 2019 10:23 am
von jogo
Hallo Thomas,

das ist diesmal ein handgestricktes reshapen. Ursprünglich habe ich versucht, mal wieder reshape() aus base R einzusetzen. Dazu müsste man aber erst sie Spalten des Dataframes umbenennen. Da ich auch schon vorher ähnliche Erfahrungen mit der Widerspenstigkeit von base::reshape() gemacht habe (ja, die entsprechenden Funktionen in anderen Paketen sind etwas handlicher, aber dann müsste man mit Paketen auf Spatzen schießen), war meine Geduld schnell am Ende.
Für diesen Fall (mit eine überschaubaren Anzahl von Spalten) mündete der Frust in einer Trotzreaktion nach dem Motto:
"das kann ich auch selber" ...

ansonsten sähe das so aus:

Code: Alles auswählen

I <- iris[c(5, 1:4)]
colnames(I)[-1] <- paste0("var#", colnames(I)[-1])
reshape(I, sep='#', dir="long", varying=2:5)
Gruß, Jörg
Athomas hat geschrieben: So Jul 14, 2019 6:45 pm
jogo hat geschrieben: Di Jul 09, 2019 8:27 am Am Ende könnte auch ein reshape von breit nach lang stehen.
Hallo Jörg, ich finde die Idee, die Daten zu "reshapen" naheliegend und gut - warum bist Du davon wieder abgekommen?

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Di Jul 16, 2019 12:05 pm
von Athomas
... aber dann müsste man mit Paketen auf Spatzen schießen
Ja, so ist es wohl!

Weil Du mit reshape nicht so glücklich bist: da ich data.table intensiv nutze, verwende ich bei solchen Gelegenheiten dcast.data.table und bin damit rundum zufrieden. Handlich (so finde ich) und natürlich - wie alles in data.table - blitzschnell, gerade bei riesigen Datenmengen :D !

Re: Formatierung von Exceldaten in das Paneldatenformat

Verfasst: Do Jul 18, 2019 1:24 pm
von jogo
Athomas hat geschrieben: Di Jul 16, 2019 12:05 pm
... aber dann müsste man mit Paketen auf Spatzen schießen
Ja, so ist es wohl!

Weil Du mit reshape nicht so glücklich bist: ...
naja, ich probier es gelegentlich immer mal wieder.
Und wie Du siehst: Kaum macht man es richtig - schon funktioniert es. :D
da ich data.table intensiv nutze, verwende ich bei solchen Gelegenheiten dcast.data.table und bin damit rundum zufrieden. Handlich (so finde ich) und natürlich - wie alles in data.table - blitzschnell, gerade bei riesigen Datenmengen :D !
ja, das wäre sonst auch mein Favorit gewesen.
Aber es muss ja auch mit der base-Funktion gehen ...
Bei entsprechenden Datenmengen ist data.table ein Muss.

Gruß, Jörg