r - dplyr 和间隔 : count observations and sum data without using loops

Question

我想使用 R dplyr 包在不使用循环的情况下计算以下与区间相关的问题:

我想计算每个间隔(绝对和相对间隔端点)中的观察值

我想对每个区间(绝对和相对区间端点)中的观测数据求和

间隔端点来自 df_abs$interval 和 df_rel$interval 列。例如

间隔:(-inf,-60]

间隔:(-60,-30]

间隔:(-30,0]

带有数据和间隔的数据框如下所示:

library(dplyr)

# ----------{ data and interval ----------
df_data <- data.frame(varA = NA,
                      varB = NA, 
                      varC = c(-81.0, -14.3, 29.6, 42.7, 46.4, 57.7, 15.3, 256.3,  20.3, -25.1, -23.1, -17.5))

df_abs <- data.frame(interval = c(-Inf, -60, -30, 0, 30, 60, 100, 200, Inf),
                     count = NA,
                     sum = NA)

df_rel <- data.frame(interval = c(0,5,15,50,75,95,100),
                     count = NA,
                     sum = NA)
# ---------- data and interval }----------


# ----------{ calculation ----------    
# absolute data frame
for (i in 1 : nrow(df_abs)-1) {  
  # count observation between interval
  df_abs$count[i+1]  <- summarise(df_data, sum(df_abs$interval[i] < varC &   varC <= df_abs$interval[i+1]))

  # sum between interval
  df_abs$sum[i+1]  <- sum(df_data$varC[df_abs$interval[i] < df_data$varC &   df_data$varC <= df_abs$interval[i+1]])  
}


# relative data frame
df_data_arranged <- df_data %>%
                      arrange(varC) %>%
                      mutate(observationPercent = c(1:nrow(df_data)) * 100/length(df_data$varC))


for (i in 1 : nrow(df_rel)-1) {  
  # count observation between interval
  df_rel$count[i+1]  <- summarise(df_data_arranged, sum(df_rel$interval[i] < observationPercent &   observationPercent <= df_rel$interval[i+1])) 

  # sum between interval
  df_rel$sum[i+1]  <- sum(df_data_arranged$varC[df_rel$interval[i] < df_data_arranged$observationPercent & df_data_arranged$observationPercent <= df_rel$interval[i+1]]) 
}    
# ---------- calculation }----------

答案应该是这样的:

df_abs <- data.frame(interval = c(-Inf, -60, -30, 0, 30, 60, 100, 200, Inf),
                     count = c(0,1,0,4,3,3,0,0,1),
                     sum = c(0,-81,0,-80,65.2,146.8,0,0,256.3))    

df_rel <- data.frame(interval = c(0,5,15,50,75,95,100),
                     count = c(0,0,1,4,3,2,1),
                     sum = c(0,0,-81,-39.6,92.6,104.1,256.3))

据我了解 dplyr 包，对于这两个问题中的每一个都应该有一个相当简短和直接的解决方案，而根本不必使用循环。

Answer 1

这可以按如下方式完成:

创建一个新列( mutate )来标识哪个观察属于哪个区间(通过 base::cut )

按间隔 ( group_by )

对您的观察进行分组

对结果应用您的操作( summarise 和 dplyr 的 n() 和常见的 sum 在这里)

如下:

df_abs <- mutate(df_data, interval = cut(varC, df_abs$interval)) %>% 
  group_by(interval) %>% 
  summarise(count=n(), sum=sum(varC))
#     interval count   sum
#1 (-Inf,-60]     1 -81.0
#2    (-30,0]     4 -80.0
#3     (0,30]     3  65.2
#4    (30,60]     3 146.8
#5 (200, Inf]     1 256.3

df_rel <- mutate(df_data_arranged, 
                 interval = cut(observationPercent, df_rel$interval)) %>% 
  group_by(interval) %>% 
  summarise(count=n(), sum=sum(varC))
#  interval count   sum
#1   (5,15]     1 -81.0
#2  (15,50]     5 -64.7
#3  (50,75]     3  92.6
#4  (75,95]     2 104.1
#5 (95,100]     1 256.3