r - 计算面板数据中一个时期到另一个时期的匹配观察百分比

Question

我有一个按以下方式构建的时间序列面板数据集:有多个基金，每个基金拥有多只股票，我们有一个股票值(value)列。如您所见，面板不平衡。我的实际数据集非常大，每个基金至少有 500 只股票，并且代表不同的季度，其中一些缺少季度值。

df <- data.frame(
  fund_id = c(1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2),
  stock_id = c(1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,3,3,3,3),
  year_q = c("2011-03","2011-06","2011-09","2011-12","2012-03","2012-06","2011-12","2012-03","2012-06","2012-09",
           "2012-12","2013-03","2013-06","2014-09","2015-03","2013-03","2013-06","2013-09","2013-12"),
  value = c(1,2,1,3,4,2,1,2,3,4,2,1,3,1,1,3,2,3,1)
)


> df
   fund_id stock_id  year_q value
1        1        1 2011-03     1
2        1        1 2011-06     2
3        1        1 2011-09     1
4        1        1 2011-12     3
5        1        1 2012-03     4
6        1        1 2012-06     2
7        1        2 2011-12     1
8        1        2 2012-03     2
9        1        2 2012-06     3
10       1        2 2012-09     4
11       1        2 2012-12     2
12       1        2 2013-03     1
13       1        2 2013-06     3
14       2        1 2014-09     1
15       2        1 2015-03     1
16       2        3 2013-03     3
17       2        3 2013-06     2
18       2        3 2013-09     3
19       2        3 2013-12     1

我想为每个基金计算当前季度持有的股票占前一到三个季度持有的股票的百分比。所以基本上对于每个基金和每个日期，我希望有 3 个列，分别是过去的第 1 季度、过去的 2 季度和过去的 3 季度，这些列显示了在过去的每个季度中也存在该日期持有的股票的百分比。
结果应该是这样的:

result <- data.frame(
  fund_id = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2),
  year_q = c("2011-03","2011-06","2011-09","2011-12","2012-03","2012-06","2012-09","2012-12","2013-03","2013-06",
               "2013-03","2013-06","2013-09","2013-12","2014-03","2014-06","2014-09","2014-12","2015-03"),
  past_1Q = c("NA",1,1,0.5,1,1,0.5,1,1,1,"NA",1,1,1,"NA","NA","NA","NA","NA"),
  past_2Q = c("NA","NA",1,0.5,0.5,1,0.5,0.5,1,1,"NA","NA",1,1,"NA","NA","NA","NA","NA"),
  past_3Q = c("NA","NA","NA",0.5,0.5,0.5,0.5,0.5,0.5,1,"NA","NA","NA",1,"NA","NA","NA","NA","NA")
)

> result
   fund_id  year_q past_1Q past_2Q past_3Q
1        1 2011-03      NA      NA      NA
2        1 2011-06       1      NA      NA
3        1 2011-09       1       1      NA
4        1 2011-12     0.5     0.5     0.5
5        1 2012-03       1     0.5     0.5
6        1 2012-06       1       1     0.5
7        1 2012-09     0.5     0.5     0.5
8        1 2012-12       1     0.5     0.5
9        1 2013-03       1       1     0.5
10       1 2013-06       1       1       1
11       2 2013-03      NA      NA      NA
12       2 2013-06       1      NA      NA
13       2 2013-09       1       1      NA
14       2 2013-12       1       1       1
15       2 2014-03      NA      NA      NA
16       2 2014-06      NA      NA      NA
17       2 2014-09      NA      NA      NA
18       2 2014-12      NA      NA      NA
19       2 2015-03      NA      NA      NA

我尝试使用 rollapply 执行此操作，但无法获得正确的结果。我知道这可能不是最好的样本数据，但在我的真实数据中，每个基金通常有 500 多只股票，我预计一个时期与过去时期的匹配股票的百分比平均约为 0.95。

这是我必须获得前两个结果列的内容(归功于@r2evans):

result <- df %>%
  group_by(fund_id) %>%
  mutate(miny = min(year_q), maxy = max(year_q)) %>%
  distinct(fund_id, miny, maxy) %>%
  group_by(fund_id) %>%
  mutate(across(c(miny, maxy), ~ as.Date(paste0(., "-01")))) %>%
  transmute(year_q = purrr::map2(miny, maxy, ~ format(seq(.x, .y, by = "3 months"), format = "%Y-%m")))  %>%
  tidyr::unnest(year_q) %>%
  full_join(df, by = c("fund_id", "year_q")) %>%
  distinct(fund_id, year_q) %>%
  arrange(fund_id, year_q)

Answer 1

library(tidyverse)

df %>%
  mutate(year_q = as.Date(paste0(year_q, '-01'))) %>%
  group_by(fund_id, year_q) %>%
  summarise(stock_id = list(unique(stock_id))) %>%
  complete(year_q = seq(min(year_q), max(year_q), by = "3 months")) %>%
  reduce(.init = ., 1:3, ~ mutate(.x, "past_{.y}Q" := map(1:n(), \(N) unlist(stock_id[pmax(N-.y, 0)])))) %>%
  mutate(across(contains("past"), \(past) map2_dbl(stock_id, past, ~ mean(.x %in% .y)) %>% replace_na(0))) %>%
  ungroup()

# A tibble: 19 × 6
   fund_id year_q     stock_id  past_1Q past_2Q past_3Q
     <dbl> <date>     <list>      <dbl>   <dbl>   <dbl>
 1       1 2011-03-01 <dbl [1]>     0       0       0  
 2       1 2011-06-01 <dbl [1]>     1       0       0  
 3       1 2011-09-01 <dbl [1]>     1       1       0  
 4       1 2011-12-01 <dbl [2]>     0.5     0.5     0.5
 5       1 2012-03-01 <dbl [2]>     1       0.5     0.5
 6       1 2012-06-01 <dbl [2]>     1       1       0.5
 7       1 2012-09-01 <dbl [1]>     1       1       1  
 8       1 2012-12-01 <dbl [1]>     1       1       1  
 9       1 2013-03-01 <dbl [1]>     1       1       1  
10       1 2013-06-01 <dbl [1]>     1       1       1  
11       2 2013-03-01 <dbl [1]>     0       0       0  
12       2 2013-06-01 <dbl [1]>     1       0       0  
13       2 2013-09-01 <dbl [1]>     1       1       0  
14       2 2013-12-01 <dbl [1]>     1       1       1  
15       2 2014-03-01 <NULL>        0       0       0  
16       2 2014-06-01 <NULL>        0       0       0  
17       2 2014-09-01 <dbl [1]>     0       0       0  
18       2 2014-12-01 <NULL>        0       0       0  
19       2 2015-03-01 <dbl [1]>     0       1       0