r - 将时间序列数据重塑为具有多个主题和变量的面板数据
问题描述
我有以下格式的原始面板数据,并希望将其重塑为经典面板数据格式,然后在面板数据回归中使用它。
以数字开头的列名是excel日期格式的时间。重塑数据后,应该有一个公司和时间列以及“变量”列中列出的数据项的列。来自 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"))
我试图用 reshape2::melt 和 reshape2::cast 函数来做,但无济于事。我找不到类似的问题。非常感谢。
解决方案
我不确定您的列名是否带有数字,但一种解决方法是使用data.table
. (考虑到 Excel 来源,我将留下日期,("1900-01-01")
如 中所述?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
推荐阅读
- ansible - YAML:“ansible 模块不支持的参数”
- python - 根据条件更改 matplotlib 的刻度标签的颜色
- typescript - 打字稿 - 合并具有不同界面的对象的重叠属性
- database - 地理查询系统
- jquery - 如何从 jquery 中删除最后选择的复选框的消息
- docker-compose - 是否有任何干净的方法可以将 env_file 附加到自动生成的 docker 文件中?
- excel - 根据用户的客户端语言自动调整英文公式中的“RC”
- python - 如何在电子邮件中定位元素?
- python - 有没有办法在 Pandas DataFrame 的列中查找模式
- logback - 一旦我开始在 logback 中使用谷歌的 LoggingAppender,日志就会在云运行中消失?