向R中的data.table添加新行，方法是对两行进行差异

Question

我有一个数据表，如下：

Month    Portfolio     ExcessReturn
196306   Portfolio 1    -0.303536
196306   Portfolio 10    -1.250765
196307   Portfolio 1     3.2952484
196307   Portfolio 10    6.4787957

我想为每个月添加一行，将投资组合列中的值作为投资组合LS，并将超额收益列中的值作为投资组合10和投资组合1的超额收益之间的差值。

任何帮助都将不胜感激！

Answer 1

我们可以通过'Month'，'Portfolio‘(作为新值)进行分组，得到'ExcessReturn’的输出结果来对数据集求和(‘diff’)，然后用原始数据集进行输出，并对'Month‘进行order

library(data.table)
out <- setDT(df1)[, .(ExcessReturn = diff(ExcessReturn)),
       .(Month)][, Portfolio  := 'Portfolio LS']
rbind(df1, out)[order(Month)]

-output

#    Month    Portfolio ExcessReturn
#1: 196306  Portfolio 1    -0.303536
#2: 196306 Portfolio 10    -1.250765
#3: 196306 Portfolio LS    -0.947229
#4: 196307  Portfolio 1     3.295248
#5: 196307 Portfolio 10     6.478796
#6: 196307 Portfolio LS     3.183547

数据

df1 <- structure(list(Month = c(196306L, 196306L, 196307L, 196307L), 
    Portfolio = c("Portfolio 1", "Portfolio 10", "Portfolio 1", 
    "Portfolio 10"), ExcessReturn = c(-0.303536, -1.250765, 3.2952484, 
    6.4787957)), class = "data.frame", row.names = c(NA, -4L))

Answer 2

使用dplyr，您可以执行以下操作：

library(dplyr)

df %>%
  group_by(Month) %>%
  summarise(ExcessReturn = ExcessReturn[Portfolio == 'Portfolio 10'] - 
                           ExcessReturn[Portfolio == 'Portfolio 1'], 
            Portfolio = 'Portfolio LS') %>%
  bind_rows(df) %>%
  arrange(Month, Portfolio)

#   Month ExcessReturn Portfolio   
#   <int>        <dbl> <chr>       
#1 196306       -0.304 Portfolio 1 
#2 196306       -1.25  Portfolio 10
#3 196306       -0.947 Portfolio LS
#4 196307        3.30  Portfolio 1 
#5 196307        6.48  Portfolio 10
#6 196307        3.18  Portfolio LS