r - 将大列（4137行）拆分并转置为与R中的行（196行，21列）长度相同的几行
问题描述
我对 R 有问题。
我创建了以下两个表。
表 1：“long_strategic_return_event_window”
| 日期 | 相对日 | daily_return_acquirer | CUSIPID |
|---|---|---|---|
| 2011-03-14 | -10 | 0.00000000 | 00187E203280 |
| 2011-03-15 | -9 | 0.00000000 | 00187E203280 |
| 2011-03-16 | -8 | 0.02400000 | 00187E203280 |
| 2011-03-17 | -7 | -0.02343750 | 00187E203280 |
| …… | …… | …… | …… |
| …… | …… | …… | …… |
| 2011-04-07 | 8 | -0.03488372 | 00187E203280 |
| 2011-04-08 | 9 | -0.04953146 | 00187E203280 |
| 2011-04-11 | 10 | -0.00422535 | 00187E203280 |
| 2016-01-15 | -10 | -0.01362530 | 0028241001499 |
| 2016-01-19 | -9 | -0.00271337 | 0028241001499 |
| 2016-01-20 | -8 | -0.01162503 | 0028241001499 |
| 2016-01-21 | -7 | -0.01201201 | 0028241001499 |
| …… | …… | …… | …… |
| …… | …… | …… | …… |
表 2：“strategic_realizedreturn”
| 行名 | cusip_acquirer | RR(-10) | RR(-9) | RR(-8) | RR(-7) | RR(-6) | RR(-5) | RR(-4) | RR(-3) | RR(-2) | RR(-1) | RR(0) | RR(1) | RR(2) | RR(3) | RR(4) | RR(5) | RR(6) | RR(7) | RR(8) | RR(9) | RR(10) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 00187E203280 | 00187E203 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 |
| 0028241001499 | 002824100 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 |
| 00287Y1092353 | 00287Y109 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 |
| 00430U10332 | 00430U103 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 |
| 004498101906 | 004498101 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 | 不适用 |
如果工作表 1 的 CUSIPID 等于工作表 2 的行名，我正拼命尝试获取 sheet1$daily_return_acquirer 的列并将其转置到 sheet2 的正确行中。
工作表 1 上的每个 CUSIPID 恰好有 21 个 daily_return_acquirer 值，它们适合工作表 2 的 21 列。 Sheet1$Relative_day 应该等于工作表 2 上的行，因此：-10 = RR(-10), -9 = RR( -9), ..., 10=RR(10)
我为两张纸创建了一个最小的可重现示例，以使其更易于理解。我希望你能帮帮我！
Sheet 1: "long_strategic_return_event_window"
structure(list(Date = structure(c(15047, 15048, 15049, 15050,
15051, 15054, 15055, 15056, 15057, 15058, 15061, 15062, 15063,
15064, 15065, 15068, 15069, 15070, 15071, 15072, 15075, 16815,
16819, 16820, 16821, 16822, 16825, 16826, 16827, 16828, 16829,
16832, 16833, 16834, 16835, 16836, 16839, 16840, 16841, 16842,
16843, 16847, 18057, 18058, 18059, 18060, 18061, 18064, 18065,
18066, 18067, 18068, 18071, 18072, 18073, 18074, 18075, 18078,
18079, 18080, 18082, 18085, 18086, 14687, 14690, 14691, 14692,
14693, 14694, 14697, 14698, 14699, 14700, 14704, 14705, 14706,
14707, 14708, 14711, 14712, 14713, 14714, 14715, 14718, 15957,
15958, 15959, 15960, 15961, 15964, 15965, 15966, 15967, 15968,
15971, 15972, 15973, 15974, 15975, 15978, 15979, 15980, 15981,
15982, 15985), class = "Date"), relative_day = c(-10L, -9L, -8L,
-7L, -6L, -5L, -4L, -3L, -2L, -1L, 0L, 1L, 2L, 3L, 4L, 5L, 6L,
7L, 8L, 9L, 10L, -10L, -9L, -8L, -7L, -6L, -5L, -4L, -3L, -2L,
-1L, 0L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, -10L, -9L,
-8L, -7L, -6L, -5L, -4L, -3L, -2L, -1L, 0L, 1L, 2L, 3L, 4L, 5L,
6L, 7L, 8L, 9L, 10L, -10L, -9L, -8L, -7L, -6L, -5L, -4L, -3L,
-2L, -1L, 0L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, -10L,
-9L, -8L, -7L, -6L, -5L, -4L, -3L, -2L, -1L, 0L, 1L, 2L, 3L,
4L, 5L, 6L, 7L, 8L, 9L, 10L), daily_return_acquirer = c(0, 0,
0.0240000000000001, -0.0234375000000001, -0.00799999999999997,
0.0161290322580645, 0.0857142857142857, -0.00584795321637427,
0.125, 0.0261437908496731, -0.0140127388535031, -0.0116279069767442,
0.0457516339869281, 0.01875, -0.049079754601227, 0.0129032258064516,
0, -0.0140127388535031, -0.0348837209302326, -0.0495314591700134,
-0.00422535211267597, -0.0136253041362531, -0.00271336951159347,
-0.0116250309176354, -0.0120120120120121, 0.0139311043566364,
-0.00924306769922569, 0.0126071608673727, 0.00771912350597615,
-0.0929083271559179, 0.0310542086624898, 0.0158520475561427,
-0.0148244473342003, 0.0036958817317846, -0.000263019463440429,
-0.015785319652723, 0.00133654103180979, -0.012279765082755,
0.0056756756756757, -0.0233808116097822, 0.0217391304347826,
0.0231618637220576, -0.00619914761720269, 0.0158544509421702,
0.00511705257771513, 0.00483645157184688, -0.00329322355921476,
-0.00050832380226194, -0.00508582326764152, -0.00817891373801918,
0.0094060043808788, 0.00561654327291291, -0.00418888042650417,
-0.162523900573614, 0.0350076103500761, 0.0294117647058824, 0.0388571428571428,
0.00935093509350944, 0.0113079019073569, 0.00983429880102371,
-0.0262806830309498, -0.0242498972461981, 0.000280819994383544,
0.00761035007610347, -0.00755287009063441, 0.004566210045662,
-0.0212121212121212, -0.00928792569659437, 0.0046874999999999,
-0.0171073094867806, -0.0348101265822786, 0.00983606557377057,
0.0551948051948052, 0.0784615384615384, 0.00713266761768899,
-0.00141643059490082, 0.00425531914893621, 0.00423728813559326,
-0.0295358649789029, 0.0159420289855072, 0.00427960057061345,
0.0198863636363636, -0.0027855153203342, 0.00139664804469271,
0.00590511097536142, 0.00485829959514174, 0.0394842868654311,
-0.00523255813953494, 0.00331190337034876, -0.00349514563106796,
0.0134450506625097, 0.00596039223226307, -0.000382262996941956,
0.011281070745698, 0.00226885989790126, 0.00811167704206753,
0.00243263473053897, 0.00746686578308752, 0.00222345747637585,
-0.00055463117027179, 0.0177580466148724, 0.00308978553253353,
-0.0108715346983148, -0.00696098186481045, -0.0084855192768862
), CusipID = c("00187E203280", "00187E203280", "00187E203280",
"00187E203280", "00187E203280", "00187E203280", "00187E203280",
"00187E203280", "00187E203280", "00187E203280", "00187E203280",
"00187E203280", "00187E203280", "00187E203280", "00187E203280",
"00187E203280", "00187E203280", "00187E203280", "00187E203280",
"00187E203280", "00187E203280", "0028241001499", "0028241001499",
"0028241001499", "0028241001499", "0028241001499", "0028241001499",
"0028241001499", "0028241001499", "0028241001499", "0028241001499",
"0028241001499", "0028241001499", "0028241001499", "0028241001499",
"0028241001499", "0028241001499", "0028241001499", "0028241001499",
"0028241001499", "0028241001499", "0028241001499", "00287Y1092353",
"00287Y1092353", "00287Y1092353", "00287Y1092353", "00287Y1092353",
"00287Y1092353", "00287Y1092353", "00287Y1092353", "00287Y1092353",
"00287Y1092353", "00287Y1092353", "00287Y1092353", "00287Y1092353",
"00287Y1092353", "00287Y1092353", "00287Y1092353", "00287Y1092353",
"00287Y1092353", "00287Y1092353", "00287Y1092353", "00287Y1092353",
"00430U10332", "00430U10332", "00430U10332", "00430U10332", "00430U10332",
"00430U10332", "00430U10332", "00430U10332", "00430U10332", "00430U10332",
"00430U10332", "00430U10332", "00430U10332", "00430U10332", "00430U10332",
"00430U10332", "00430U10332", "00430U10332", "00430U10332", "00430U10332",
"00430U10332", "004498101906", "004498101906", "004498101906",
"004498101906", "004498101906", "004498101906", "004498101906",
"004498101906", "004498101906", "004498101906", "004498101906",
"004498101906", "004498101906", "004498101906", "004498101906",
"004498101906", "004498101906", "004498101906", "004498101906",
"004498101906", "004498101906")), row.names = c(NA, -105L), class = c("data.table",
"data.frame"), .internal.selfref = <pointer: 0x0000022eca291ef0>)
Sheet 2: "strategic_realizedreturn"
structure(list(cusip_acquirer = structure(1:5, .Label = c("00187E203",
"002824100", "00287Y109", "00430U103", "004498101"), class = "factor"),
`RR(-10)` = c("NA", "NA", "NA", "NA", "NA"), `RR(-9)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(-8)` = c("NA", "NA", "NA", "NA",
"NA"), `RR(-7)` = c("NA", "NA", "NA", "NA", "NA"), `RR(-6)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(-5)` = c("NA", "NA", "NA", "NA",
"NA"), `RR(-4)` = c("NA", "NA", "NA", "NA", "NA"), `RR(-3)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(-2)` = c("NA", "NA", "NA", "NA",
"NA"), `RR(-1)` = c("NA", "NA", "NA", "NA", "NA"), `RR(0)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(1)` = c("NA", "NA", "NA", "NA",
"NA"), `RR(2)` = c("NA", "NA", "NA", "NA", "NA"), `RR(3)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(4)` = c("NA", "NA", "NA", "NA",
"NA"), `RR(5)` = c("NA", "NA", "NA", "NA", "NA"), `RR(6)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(7)` = c("NA", "NA", "NA", "NA",
"NA"), `RR(8)` = c("NA", "NA", "NA", "NA", "NA"), `RR(9)` = c("NA",
"NA", "NA", "NA", "NA"), `RR(10)` = c("NA", "NA", "NA", "NA",
"NA")), row.names = c("00187E203280", "0028241001499", "00287Y1092353",
"00430U10332", "004498101906"), class = "data.frame")
解决方案
除非我弄错了（我可能是因为您没有在所需输出的 shell 中填写任何值），否则这只是pivot_wider从输出 shell 添加行名的组合：
long_strategic_return_event_window %>%
select(-Date) %>%
pivot_wider(
names_from=relative_day,
names_prefix="RR",
values_from=daily_return_acquirer
) %>%
add_column(
`ROW NAME`=rownames(strategic_realizedreturn),
.before=1
)
给予
# A tibble: 5 x 23
`ROW NAME` CusipID `RR-10` `RR-9` `RR-8` `RR-7` `RR-6` `RR-5` `RR-4` `RR-3` `RR-2` `RR-1` RR0 RR1 RR2 RR3 RR4 RR5 RR6 RR7
<chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 00187E203… 00187E… 0 0 0.024 -0.0234 -0.00800 1.61e-2 0.0857 -0.00585 1.25e-1 0.0261 -0.0140 -0.0116 0.0458 1.88e-2 -0.0491 1.29e-2 0 -0.0140
2 002824100… 002824… -0.0136 -0.00271 -0.0116 -0.0120 0.0139 -9.24e-3 0.0126 0.00772 -9.29e-2 0.0311 0.0159 -0.0148 0.00370 -2.63e-4 -0.0158 1.34e-3 -0.0123 0.00568
3 00287Y109… 00287Y… -0.00620 0.0159 0.00512 0.00484 -0.00329 -5.08e-4 -0.00509 -0.00818 9.41e-3 0.00562 -0.00419 -0.163 0.0350 2.94e-2 0.0389 9.35e-3 0.0113 0.00983
4 00430U103… 00430U… 0.00761 -0.00755 0.00457 -0.0212 -0.00929 4.69e-3 -0.0171 -0.0348 9.84e-3 0.0552 0.0785 0.00713 -0.00142 4.26e-3 0.00424 -2.95e-2 0.0159 0.00428
5 004498101… 004498… 0.00591 0.00486 0.0395 -0.00523 0.00331 -3.50e-3 0.0134 0.00596 -3.82e-4 0.0113 0.00227 0.00811 0.00243 7.47e-3 0.00222 -5.55e-4 0.0178 0.00309
# … with 3 more variables: RR8 <dbl>, RR9 <dbl>, RR10 <dbl>
R-10虽然，就个人而言，我会为etc选择稍微更用户友好的列名......
推荐阅读
- json - 使用 jq 按键值过滤嵌套的 json
- css - 我怎样才能使这个字体大小顺利变小而没有跳跃？
- r - 如何将 R 的 read.table 与未定义数量的标题行一起使用？
- php - 毫秒格式 php
- javascript - http post请求的表单输入
- html - 我们可以在 HTML5 中使用大量的 div 标签吗
- java - 使用 Java 注释在方法中生成一些代码
- vim - UltiSnips 不会自动重新加载对片段文件的更改
- python - 带有时区错误的 Pandas date_range，可能的错误？
- javascript - setInterval 不适用于我的功能