Wide to Long Format mit großen Excel-Datensatz

[Pete]

Hallo zusammen,

ich bin neu hier im Forum und habe ein großes Problem mit meiner Masterarbeit.
Ich muss mit Daten, die ich aus Refinitv Eikon extrahiert habe, eine Datenanalyse mit R durchführen. Allerdings entspricht das Format des Excel-Datensatzes aus Refinitv Eikon nicht dem Format, das ich für die Verarbeitung mit R benötige.
Der Datensatz aus Eikon ist im Wide Format, wie in Abbildung 1 zu sehen ist. Ich habe insgesamt 9 Variablen, die alle den Zeitraum von 2010 bis 2023 abdecken. Der Datensatz ist also riesig.

Für die Verarbeitung mit R benötige ich den Datensatz im Format wie in Abbildung 2 dargestellt, mit dem Zusatz, dass ich eine neue Spalte mit den Jahren 2010-2023 benötige.

Meine Versuche, den Datensatz mit R zu bearbeiten, sehen wie folgt aus:

Code: Alles auswählen

dfNEU <- reshape(data = df,
                 varying = c("ESG Score(FY2010)", "ESG Score(FY2011)", "ESG Score(FY2012)", "ESG Score(FY2013)", "ESG Score(FY2014)", "ESG Score 			(FY2015)", "ESG Score (FY2016)", "ESG Score(FY2017)", "ESG Score(FY2018)", "ESG Score(FY2019)", "ESG Score(FY2020)", "ESG Score(FY2021)", "ESG Score(FY2022)", "ESG Score(FY2023)", "Total Assets(FY2010, EUR)", "Total Assets(FY2011, EUR)", "Total Assets(FY2012, EUR)", "Total Assets(FY2013, EUR)", "Total Assets(FY2014, EUR)", "Total Assets(FY2015, EUR)", "Total Assets(FY2016, EUR)", "Total Assets(FY2017, EUR)", "Total Assets(FY2018, EUR)", "Total Assets(FY2019, EUR)", "Total Assets(FY2020, EUR)", "Total Assets(FY2021, EUR)", "Total Assets(FY2022, EUR)", "Total Assets(FY2023, EUR)", "Debt - Total(FY2010, EUR)", "Debt - Total(FY2011, EUR)", "Debt - Total(FY2012, EUR)", "Debt - Total(FY2013, EUR)", "Debt - Total (FY2014, EUR)", "Debt - Total(FY2015, EUR)", "Debt - Total(FY2016, EUR)", "Debt - Total(FY2017, EUR)", "Debt - Total(FY2018, EUR)", "Debt - Total (FY2019, EUR)", "Debt - Total(FY2020, EUR)", "Debt - Total(FY2021, EUR)", "Debt - Total(FY2022, EUR)", "Debt - Total(FY2023, EUR)", "Income before Taxes(FY2010, EUR)", "Income before Taxes(FY2011, EUR)", "Income before Taxes(FY2012, EUR)", "Income before Taxes(FY2013, EUR)", "Income before Taxes(FY2014, EUR)", "Income before Taxes(FY2015, EUR)", "Income before Taxes(FY2016, EUR)", "Income before Taxes(FY2017, EUR)", "Income before Taxes(FY2018, EUR)", "Income before Taxes(FY2019, EUR)", "Income before Taxes(FY2020, EUR)", "Income before Taxes(FY2021, EUR)", "Income before Taxes(FY2022, EUR)", "Income before Taxes(FY2023, EUR)", "Inventories - Total(FY2010, EUR)", "Inventories - Total(FY2011, EUR)", "Inventories - Total(FY2012, EUR)", "Inventories - Total (FY2013, EUR)", "Inventories - Total(FY2014, EUR)", "Inventories - Total(FY2015, EUR)", "Inventories - Total(FY2016, EUR)", "Inventories - Total (FY2017, EUR)", "Inventories - Total(FY2018, EUR)", "Inventories - Total(FY2019, EUR)", "Inventories - Total(FY2020, EUR)", "Inventories - Total (FY2021, EUR)", "Inventories - Total(FY2022, EUR)", "Inventories - Total(FY2023, EUR)", "Property Plant & Equipment - Net - Total(FY2010, EUR)", "Property Plant & Equipment - Net - Total(FY2011, EUR)", "Property Plant & Equipment - Net - Total(FY2012, EUR)", "Property Plant & Equipment - Net - Total(FY2013, EUR)", "Property Plant & Equipment - Net - Total (FY2014, EUR)"	"Property Plant & Equipment - Net - Total(FY2015, EUR)", "Property Plant & Equipment - Net - Total(FY2016, EUR)", "Property Plant & Equipment - Net - Total(FY2017, EUR)", "Property Plant & Equipment - Net - Total(FY2018, EUR)", "Property Plant & Equipment - Net - Total (FY2019, EUR)", "Property Plant & Equipment - Net - Total(FY2020, EUR)", "Property Plant & Equipment - Net - Total(FY2021, EUR)", "Property Plant & Equipment - Net - Total(FY2022, EUR)", "Property Plant & Equipment - Net - Total(FY2023, EUR)", "Research & Development Expense(FY2010, EUR)", "Research & Development Expense(FY2011, EUR)", "Research & Development Expense(FY2012, EUR)", "Research & Development Expense(FY2013, EUR)", "Research & Development Expense(FY2014, EUR)", "Research & Development Expense(FY2015, EUR)", "Research & Development Expense(FY2016, EUR)", "Research & Development Expense(FY2017, EUR)"	"Research & Development Expense(FY2018, EUR)", "Research & Development Expense(FY2019, EUR)", "Research & Development Expense(FY2020, EUR)", "Research & Development Expense(FY2021, EUR)", "Research & Development Expense(FY2022, EUR)", "Research & Development Expense (FY2023, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2010, EUR)", "Income Taxes - Payable - Long-Term & Short-Term (FY2011, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2012, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2013, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2014, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2015, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2016, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2017, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2018, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2019, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2020, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2021, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2022, EUR)", "Income Taxes - Payable - Long-Term & Short-Term(FY2023, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2010, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2011, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2012, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2013, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2014, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2015, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2016, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2017, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2018, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2019, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2020, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2021, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2022, EUR)", "Earnings before Interest & Taxes (EBIT)(FY2023, EUR)",
                 idvar = c("Identifier", "Company Name", "Country of Headquarters", "TRBC Industry Name"),
                 timevar = "year",
                 v.names = c("ESG", "Total Assets", "Total Debt", "Income Before Taxes", "Inventories", "PPE", "R&D Expense", "Income Taxes Payable", "EBIT"),
                 times = c(2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023),
                 direction = "long",
                 )

Die Ausgabe, die ich mit diesem Code erhalte, ist nicht zufriedenstellend, da die Daten in den Zeilen seltsam verschoben sind. D.h. die Daten von 2018 werden komischerweise in der Spalte von 2010 angezeigt usw.
Ich hoffe ich konnte mein Problem erklären.

Ich freue mich auf rege Teilnahme und auf eure Nachrichten.

Viele Grüße
Pete

[schubbiaschwilli]

Gude!

"Der Datensatz ist also riesig." - Naja, schauen wir mal. xl konnte mal nur 65.536 und jetzt kann es 1.048.576 Zeilen, also...
Fangen wir mal vorne an: In welchem Dateiformat liegen die Daten den vor bzw. kannst du die Datei in xl öffnen? Wie hast du die Datei in R geöffnet?

Dank&Gruß Schubbiaschwilli

[Pete]

Hallo,

vielen Dank für deine Nachricht.

Der Excel-Datensatz hat 45.630 Zellen und liegt im ganz normalen xlsx Dateiformat vor. Ich habe ihn über "Import Dataset" in R eingefügt. Über Library habe ich auch (readxl) installiert.

Rausgeworfen habe ich den bearbeiteten Datensatz in R wie folgt:

Code: Alles auswählen

write.xlsx(dfNEU, file = "dfNEU")

Das Problem mit den verschobenen Daten habe ich ja bereits oben beschrieben.

Viele Grüße
Pete

[schubbiaschwilli]

Gude!

Wie genau hast du die Daten importiert? Ich nehme an, mit dem R-Befehl "write.xlsx", oder?

Abgesehen davon, dass das Codebeispiel mit dem Reshape-Befehl bei mir Fehler wirft.

Dann kenne ich weder die Datei, noch die Daten, und die Screenshots sind ja auch keine richtigen.

Da es aber sowas wie eine Logik in den Spaltennamen gibt, würde ich die ursprüngliche Tabelle gemäß den Spalten mit den Jahreszahlen trennen und dann zeilenweise anhängen, und da es die Jahre 2010-2023 sind, diese sind also bekannt, und die Spaltennamen logisch aufgebaut sind kann man das über eine Schleife bzw. Funktion (oder im Zweifel auch manuell mit Suchen&Ersetzen oder sogar in xl) lösen.

Dank&Gruß
Schubbiaschwill

[Pete]

Hallo zusammen,

der Excel-Datensatz wurde, wie oben schon erwähnt, über "Import Dataset" in R Studio importiert.
Der Code kann nicht funktionieren, wenn man die Excel-Datei mit den eingefügten Variablen nicht hat, oder?
Er dient nur zur Veranschaulichung des von mir verwendeten Codes, der mir nicht die richtige Formatierung für die ausgeworfene Excel-Datei gegeben hat.

Die Excel-Datei ist wie aus dem Beispiel in Abbildung 1 aufgebaut. Anbei noch ein Screenshot der Excel-Datei zum besseren Verständnis.

Neben "ESG Score(2010)...ESG Score(2023)" gibt es noch weitere Variablen, die genauso aufgebaut sind wie der ESG Score "Total Assets(2010, EUR)...Total Assets(2023, EUR)" ... etc.

Ich darf die Daten nicht in Excel bearbeiten, da für die Masterarbeit geplant ist, die Daten mit R zu bearbeiten.

Ich wäre sehr dankbar, wenn jemand einen Code für die Umformatierung in das Long-Format, wie in dem Beispiel aus Abbildung 2, hätte.

Viele Grüße
Pete

[schubbiaschwilli]

Gude!

Nun, da du deine Daten ja kennst, und weißt wie sie aufgebaut sind, ich aber sie aber nicht kenne und leider nicht habe, kann ich sagen, das sowas ganz einfach zu bauen ist.

Nehmen wir mal die Fischer-Iris-Daten, die man sich mit

Code: Alles auswählen

iris

ansehen kann. Mit

Code: Alles auswählen

colnames(iris)

kommt man an die Spaltennamen, und die kann man natürlich mit

Code: Alles auswählen

colnames(iris)[grepl("Sepal", colnames(iris))]
colnames(iris)[grepl(".Length", colnames(iris))]

durchsuchen oder sich mit

Code: Alles auswählen

iris[,grepl("Sepal", colnames(iris))]
iris[,grepl(".Length", colnames(iris))]

anzeigen lassen. Nun noch mit

Code: Alles auswählen

c()

die fehlenden Spalten des Schlüssels dazufügen (das Jahr nicht vergessen!), und das ganze mit

Code: Alles auswählen

rbind()

zeilenweise anfügen.

Geht doch ganz einfach.

[student]

Hallo Pete,

klappt nicht der ganz einfache Weg über eine CSV-Datei? Du kannst in Excel die Daten als CSV-Datei speichern und mit read.csv2() (Gebietsschema DE) einlesen. Das ist m. E. der einfachste und sicherste Weg....

[bigben]

Hallo,

Ich bin spät für die Party, möchte aber kurz was ergänzen:

Erstens

Code: Alles auswählen

c("ESG Score(FY2010)", "ESG Score(FY2011)", "ESG Score(FY2012)", "ESG Score(FY2013)", "ESG Score(FY2014)", 

Spaltennamen mit Leerzeichen sind in R keine gute Idee. Auch solche mit Bindestrichen oder die mit Ziffern anfangen nicht. Ich gewöhne mir daher gerade an, alle aus Excel eingelesenen Daten erstmal durch die Funktion clean_names aus dem Paket janitor laufen zu lassen. Kann ich jedem nur empfehlen. Das gilt von Anfang an.

Zweitens
Es ist kein Geheimnis, dass die Funktion reshape und ihre Dokumentation keinen Spaß machen und User deshalb auf Pakete wie tidyr oder data.table ausweichen. So eine wichtige Aufgabe wie das Umformen von Daten ist es wert, sich gründlich damit zu beschäftigen. Ich empfehle das hier als Einstiegslektüre: https://tidyr.tidyverse.org/articles/pivot.html

Da kommen dann tibbles heraus statt Dataframes, aber die lassen sich leicht in Dataframes umwandeln.

Drittens
Du hast natürlich Recht, dass Dein Code nur laufen kann, wenn man passende Beispieldaten dazu hat. Wessen Aufgabe könnte es sein, die zur Verfügung zu stellen? Ich kann nur sagen, dass ein lauffähiges, reproduzierbares Minimalbeispiel der aussichtsreichste Weg ist, wenn man auch Code aus dem Forum hofft. Siehe dazu z. B. viewtopic.php?t=11

Viertens

D.h. die Daten von 2018 werden komischerweise in der Spalte von 2010 angezeigt usw. Ich hoffe ich konnte mein Problem erklären

Das ist das Ergebnis einer Fehlbedienung und so etwas ohne reproduzierbares Minkmalbeispiel aufzuklären macht keinen Spaß. Wir haben das einfach schon zu oft gehabt, dass man sich den Kopf zermartert und die Ursache war dann irgendwas zuvor gar nicht erwähntes.

Fünften
In Deinem Screenshot kommen Kommas anstelle von Dezimalpjnkten vor. Das deutet nichts gutes an. Werden die Zahlenspalten in R als Zahlen erkannt oder als Text?

VG,
Bernhard