r - 有没有办法 pivot_wider 某些值而不是特定列中的其他值？

Question

我有什么:

Symbol Date Value

A. 07/20 10

A. 09/20. 12

B. 07/20. 15

B. 08/20. 19

VFINX. 07/20. 22

VFINX 08/20. 20

我需要的:

Symbol Date Value. VFINX

A. 07/20 10. 22

A. 09/20. 12. 20

B. 07/20. 15. 22

B. 08/20. 19. 20

图书馆

library(tidyverse)
library(magrittr)
library(tidyr)
library(dplyr)
library(tidyquant)
library(purrr)
library(PerformanceAnalytics)
library(psych)
library(broom)
library(ustyc)
library(quantmod)

1. 在这里，我下载了一些股票数据，并尝试使用 broom 包对其进行设置以进行回归分析。我已经能够以一种方式做到这一点，但想看看我是否不能以另一种方式做到这一点。
2. 这是第一种方式。

'''

tickers <- c("A",
             "AAL",
             "AAP",
             "AAPL",
             "ADI",
             "ABBV",
             "ABC",
             "VFINX")
 returns <- tickers %>%
  tq_get( from = "2017-01-01", to ="2019-12-01", get="stock.prices") %>%
  group_by(symbol) %>%
  tq_transmute(adjusted, periodReturn, period = "monthly") %>%
  mutate(RF = .02/12,
         Excess_Return = (monthly.returns-RF)*100)

View(returns)


finalized <- returns %>% 
  select(symbol, date, Excess_Return) %>%
  pivot_wider(names_from = symbol, values_from = Excess_Return)

View(finalized)


steps <- finalized[-1] %>%
  select(everything()) %>%
  gather(Var, Val, -VFINX) %>% #here we are just pivot_longer-ing the data besides the VFINX and just creating two columns (Var, and Val)
  nest(data= c(Val, VFINX)) %>% #nesting the value (the return for each month of the stock) and the VFINX return for that same month
  mutate(
    model = map(data, ~lm(Val~VFINX, data= .)), #here we're applying the same formula to each of the nested data
    tidied = map(model, tidy) # here we are tidying the data for each of the nested here
  ) %>%
  unnest(tidied)
)

'''

1. 理想情况下，我想要的是跳过必须先 pivot_wider 所有内容和 VFINX，然后再将 VFINX 之外的所有内容旋转回来。相反，我只想将符号列中 VFINX 所在的值作为轴心。如果我这样做，它不应该为每只股票重复序列吗？在这里我指定了我想要的特定符号 [which(returns2$symbol == VFINX)]。相反，我收到了这个错误

错误:无法对不存在的列进行子集化。x 列 VFINX, VFINX, VFINX, VFINX, VFINX 等'存在。

'''

 returns2 <- tickers %>%
      tq_get( from = "2017-01-01", to ="2019-12-01", get="stock.prices") %>%
      group_by(symbol) %>%
      tq_transmute(adjusted, periodReturn, period = "monthly") %>%
      mutate(RF = .02/12,
             Excess_Return = (monthly.returns-RF)*100)
    View(returns2)
    
    returns2<- returns2 %>%
      pivot_wider(names_from = returns2$symbol[which(returns2$symbol=="VFINX")], values_from=Excess_Return)
      
'''

Answer 1

IIUC，这让你从“我有什么”到“我需要什么”:

returns %>% 
  select(symbol, date, Excess_Return) %>% 
  group_by(date) %>% 
  mutate(vfinx = Excess_Return[symbol == "VFINX"]) %>% 
  ungroup

# A tibble: 280 x 4
   symbol date       Excess_Return   vfinx
   <chr>  <date>             <dbl>   <dbl>
 1 A      2017-01-31         5.17   0.856 
 2 A      2017-02-28         4.59   3.80  
 3 A      2017-03-31         3.15  -0.0721
 4 A      2017-04-28         3.96   0.851 
 5 A      2017-05-31         9.44   1.23  
 6 A      2017-06-30        -1.65   0.445 
 7 A      2017-07-31         0.643  1.88  
 8 A      2017-08-31         8.08   0.127 
 9 A      2017-09-29        -0.970  1.89  
10 A      2017-10-31         6.02   2.15  

您可以通过 symbol(省略 symbol == 'VFINX')拟合回归，如下所示:

returns %>% 
  select(symbol, date, Excess_Return) %>% 
  group_by(date) %>% 
  mutate(vfinx = Excess_Return[symbol == "VFINX"]) %>% 
  ungroup() %>%
  filter(symbol != "VFINX") %>% 
  group_by(symbol) %>% 
  summarise(fit = list(lm(Excess_Return ~ vfinx, data = .) %>% tidy)) %>% 
  unnest(fit)

输出

# A tibble: 14 x 6
   symbol term        estimate std.error statistic  p.value
   <chr>  <chr>          <dbl>     <dbl>     <dbl>    <dbl>
 1 A      (Intercept)   -0.234     0.467    -0.501 6.17e- 1
 2 A      vfinx          1.24      0.129     9.63  8.51e-19
 3 AAL    (Intercept)   -0.234     0.467    -0.501 6.17e- 1
 4 AAL    vfinx          1.24      0.129     9.63  8.51e-19
 5 AAP    (Intercept)   -0.234     0.467    -0.501 6.17e- 1
 6 AAP    vfinx          1.24      0.129     9.63  8.51e-19
 7 AAPL   (Intercept)   -0.234     0.467    -0.501 6.17e- 1
 8 AAPL   vfinx          1.24      0.129     9.63  8.51e-19
 9 ABBV   (Intercept)   -0.234     0.467    -0.501 6.17e- 1
10 ABBV   vfinx          1.24      0.129     9.63  8.51e-19
11 ABC    (Intercept)   -0.234     0.467    -0.501 6.17e- 1
12 ABC    vfinx          1.24      0.129     9.63  8.51e-19
13 ADI    (Intercept)   -0.234     0.467    -0.501 6.17e- 1
14 ADI    vfinx          1.24      0.129     9.63  8.51e-19