I have raw panel data in the following format and would like to reshape it into the classic panel data format to then use it in panel data regression.
The column names that start with numbers are the time in the excel date format. After reshaping the data, there should be a firm and time column as well as columns for the dataitems listed in the "Variable" column.
From R:
dput(head(df.example))
structure(list(Firm = c("ABB LTD N", "Facebook", "Nestle", "ABB LTD N",
"Facebook", "Nestle"), Variable = c("Price", "Price", "Price",
"Market Value", "Market Value", "Market Value"), `32508` = c(110.67,
162500, 14.355, 809735, 9.647, 2223.87), `32538` = c(110.35,
162000, 14.355, 809735, 8.836, 2036.94), `32568` = c(115.29,
16925, 14.355, 809735, 10.556, 2433.36), `32598` = c(130.61,
19175, 14.355, 809735, 11.744, 2707.32), `32628` = c(146.34,
4130, 14.355, 809735, 12.975, 162000)), row.names = c(NA, -6L
), class = c("tbl_df", "tbl", "data.frame"))
I tried to do it with the reshape2::melt and reshape2::cast functions to no avail. I was not able to find a similar problem. Thanks a lot.
I am not sure about your column names with numbers, but a way to solve is using data.table. (I am leaving the date considering the Excel origin ("1900-01-01") as documented in ?as.Date)
library(data.table)
df2 <- melt(df,id = c("Firm","Variable"), variable.name = "date")
setDT(df2)[,date := as.Date(as.numeric(paste(date)), origin = "1900-01-01"))]
df2
#returns
Firm Variable date value
1: ABB LTD N Price 1989-01-02 110.670
2: Facebook Price 1989-01-02 162500.000
3: Nestle Price 1989-01-02 14.355
4: ABB LTD N Market Value 1989-01-02 809735.000
5: Facebook Market Value 1989-01-02 9.647
6: Nestle Market Value 1989-01-02 2223.870
7: ABB LTD N Price 1989-02-01 110.350
8: Facebook Price 1989-02-01 162000.000
9: Nestle Price 1989-02-01 14.355
10: ABB LTD N Market Value 1989-02-01 809735.000
11: Facebook Market Value 1989-02-01 8.836
12: Nestle Market Value 1989-02-01 2036.940
13: ABB LTD N Price 1989-03-03 115.290
14: Facebook Price 1989-03-03 16925.000
15: Nestle Price 1989-03-03 14.355
16: ABB LTD N Market Value 1989-03-03 809735.000
17: Facebook Market Value 1989-03-03 10.556
18: Nestle Market Value 1989-03-03 2433.360
19: ABB LTD N Price 1989-04-02 130.610
20: Facebook Price 1989-04-02 19175.000
21: Nestle Price 1989-04-02 14.355
22: ABB LTD N Market Value 1989-04-02 809735.000
23: Facebook Market Value 1989-04-02 11.744
24: Nestle Market Value 1989-04-02 2707.320
25: ABB LTD N Price 1989-05-02 146.340
26: Facebook Price 1989-05-02 4130.000
27: Nestle Price 1989-05-02 14.355
28: ABB LTD N Market Value 1989-05-02 809735.000
29: Facebook Market Value 1989-05-02 12.975
30: Nestle Market Value 1989-05-02 162000.000
Firm Variable date value