R遍历数据框的各列以根据开始结束年份创建新列

santoku 发表于 Dev

三德

我有这样的开始/结束年份的租赁数据框架

region=c("a","b","c","d")
lease=c("x","y","z","k")
startyr=c(2000,2001,2003,2002)
endyr=c(2004,2004,2006,2005)
annualAmt=c(7000,8500,6000,5500)
df=data.frame(region,lease,startyr,endyr,annualAmt)

我想通过将数据框重塑为所需的输出来扩展年份：

region lease 2000 2001 2002 ... 2006
a x 7000 7000 7000 7000 7000 0 0
b y 0 8500 8500 8500 8500 0 0

逻辑是，如果租约涵盖2000-2004年，则其amt将计入2000,2001..2004 col

最好的方法是什么？如果编写循环，该如何命名新创建的year cols 2000-2006？还是应该使用申请？

A5C1D2H2I1M1N2O1R2T1

这是一个替代方案，主要涉及基本的加法和减法：

Rows <- df$endyr - df$startyr               # How many times to repeat rows?
df <- df[rep(rownames(df), Rows), ]         # Repeat the rows
df$year <- df$startyr + sequence(Rows) - 1  # Add a new "year" variable
reshape(df, direction = "wide",             # Reshape, long to wide
        idvar = c("region", "lease"),       # idvars are the first two cols
        timevar = "year",                   # timevar is the new year col
        drop = c("startyr", "endyr"))       # and drop the start/endyr cols
#   region lease annualAmt.2000 annualAmt.2001 annualAmt.2002
# 1      a     x           7000           7000           7000
# 2      b     y             NA           8500           8500
# 3      c     z             NA             NA             NA
# 4      d     k             NA             NA           5500
#   annualAmt.2003 annualAmt.2004 annualAmt.2005
# 1           7000             NA             NA
# 2           8500             NA             NA
# 3           6000           6000           6000
# 4           5500           5500             NA

另外，您可以使用“ data.table”，如下所示：

library(data.table)
## Start with your original df
dt <- data.table(df)
dcast.data.table(
  DT[, list(year = seq(startyr, endyr), 
            annualAmt), 
     by = list(region, lease)], 
  region + lease ~ year, 
  value.var = "annualAmt", fill = 0)
#    region lease 2000 2001 2002 2003 2004 2005 2006
# 1:      a     x 7000 7000 7000 7000 7000    0    0
# 2:      b     y    0 8500 8500 8500 8500    0    0
# 3:      c     z    0    0    0 6000 6000 6000 6000
# 4:      d     k    0    0 5500 5500 5500 5500    0

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。