使用data.table统计符合条件的分组观测值：股票收益统计需求

数据情况 #

我用以下代码生成了模拟的小时级股票收益数据（共4只股票，每只覆盖24小时，包含4个交易日的收益）：

set.seed(1)
dt <- data.table(stock = c(rep("a",24),rep("b",24),rep("c",24),rep("d",24)), 
                 hour = rep(1:24,4), 
                 day1 = sample(-5:5,96,replace = TRUE), 
                 day2 = sample(-10:-1,96,replace = TRUE), 
                 day3 = sample(0:10,96,replace = TRUE), 
                 day4 = 0)

数据样例如下：

统计需求 #

我需要按每个交易日（day1-day4）统计以下5类股票的数量：

1. 所有小时收益均为负的股票数量
2. 所有小时收益均为正的股票数量
3. 24小时内收益正负混合的股票数量
4. 24小时内至少有一次收益为0的股票数量
5. 24小时内收益全为0的股票数量

期望输出 #

最终希望得到如下格式的统计结果：

解决方案 #

可以利用data.table的分组和列操作来实现，具体代码如下：

# 按股票分组，计算每个股票在各交易日的特征指标
stock_stats <- dt[, .(
  day1_all_neg = all(day1 < 0),
  day1_all_pos = all(day1 > 0),
  day1_mixed = any(day1 < 0) & any(day1 > 0),
  day1_at_least_one_zero = any(day1 == 0),
  day1_all_zero = all(day1 == 0),
  
  day2_all_neg = all(day2 < 0),
  day2_all_pos = all(day2 > 0),
  day2_mixed = any(day2 < 0) & any(day2 > 0),
  day2_at_least_one_zero = any(day2 == 0),
  day2_all_zero = all(day2 == 0),
  
  day3_all_neg = all(day3 < 0),
  day3_all_pos = all(day3 > 0),
  day3_mixed = any(day3 < 0) & any(day3 > 0),
  day3_at_least_one_zero = any(day3 == 0),
  day3_all_zero = all(day3 == 0),
  
  day4_all_neg = all(day4 < 0),
  day4_all_pos = all(day4 > 0),
  day4_mixed = any(day4 < 0) & any(day4 > 0),
  day4_at_least_one_zero = any(day4 == 0),
  day4_all_zero = all(day4 == 0)
), by = stock]

# 构建结果表格并填充统计值
result <- data.table(
  counts = c("stocks_where_all_hours_negative",
             "stocks_where_all_hours_positive",
             "stocks_where_mixed_pos_and_neg",
             "stocks_where_at_least_one_zero",
             "stocks_where_all_zero"),
  day1 = c(sum(stock_stats$day1_all_neg),
           sum(stock_stats$day1_all_pos),
           sum(stock_stats$day1_mixed),
           sum(stock_stats$day1_at_least_one_zero),
           sum(stock_stats$day1_all_zero)),
  day2 = c(sum(stock_stats$day2_all_neg),
           sum(stock_stats$day2_all_pos),
           sum(stock_stats$day2_mixed),
           sum(stock_stats$day2_at_least_one_zero),
           sum(stock_stats$day2_all_zero)),
  day3 = c(sum(stock_stats$day3_all_neg),
           sum(stock_stats$day3_all_pos),
           sum(stock_stats$day3_mixed),
           sum(stock_stats$day3_at_least_one_zero),
           sum(stock_stats$day3_all_zero)),
  day4 = c(sum(stock_stats$day4_all_neg),
           sum(stock_stats$day4_all_pos),
           sum(stock_stats$day4_mixed),
           sum(stock_stats$day4_at_least_one_zero),
           sum(stock_stats$day4_all_zero))
)

# 输出结果
print(result)

运行这段代码后，就能得到完全符合期望格式的统计结果。

内容的提问来源于stack exchange，提问作者FG7