使用R语言生成房间使用增量时段数据框以制作热力图

需求说明 #

将房间预订记录转换为按房间、日期、小时维度统计占用次数的宽表，规则为：只要预订时段覆盖了某小时的任意部分（哪怕仅半小时），该小时计为1次占用；需处理2000条记录+50个房间的规模，要求用向量化方法实现高效计算，避免嵌套循环。

原始数据 #

df <- data.frame(id = c(1:4),
                 room = c("room a", "room a", "room a", "room b"),
                 day = c("Mon", "Tue", "Mon", "Wed"),
                 start = c("09:00", "09:00", "10:00", "09:00"),
                 end = c("11:00", "10:00", "14:30", "11:00"))

解决方案代码 #

library(dplyr)
library(tidyr)

# 1. 预处理时间：将时分转为小时数值，方便计算覆盖区间
df_processed <- df %>%
  mutate(
    # 提取小时+分钟转成小数（如14:30 → 14.5）
    start_hour = as.numeric(substr(start, 1, 2)) + as.numeric(substr(start, 4, 5))/60,
    end_hour = as.numeric(substr(end, 1, 2)) + as.numeric(substr(end, 4, 5))/60,
    # 统一房间名称大小写
    room = toupper(room)
  )

# 2. 生成所有需要的维度组合：所有房间 + 所有工作日 + 所有目标小时
all_rooms <- unique(df_processed$room)
all_days <- c("Mon", "Tue", "Wed", "Thu", "Fri")
all_hours <- 9:17

full_dimensions <- expand_grid(room = all_rooms, day = all_days, hour = all_hours)

# 3. 展开每个预订覆盖的所有小时
booking_hours <- df_processed %>%
  rowwise() %>%
  # 生成从start_hour的整数部分到end_hour的整数部分的所有小时（如14.5对应到14）
  mutate(hour = list(floor(start_hour):floor(end_hour - 1e-6))) %>%
  unnest(hour) %>%
  select(room, day, hour)

# 4. 统计每个维度的占用次数，再与全维度组合合并填充0
heatmap_df <- full_dimensions %>%
  left_join(booking_hours %>% count(room, day, hour, name = "count"), 
            by = c("room", "day", "hour")) %>%
  replace_na(list(count = 0)) %>%
  # 转成宽表格式
  pivot_wider(names_from = hour, values_from = count)

# 查看结果
print(heatmap_df)

代码解释 #

1. 时间预处理：把字符串格式的时间转成小数形式的小时，方便判断预订覆盖的小时区间；同时统一房间名称的大小写，避免匹配错误。
2. 全维度生成：用expand_grid生成所有房间、工作日、目标小时的组合，确保最终结果包含所有可能的维度，没有遗漏。
3. 预订小时展开：通过rowwise+list+unnest，为每个预订记录生成它覆盖的所有小时（比如14:30结束的预订，会覆盖到14点整，所以取floor(end_hour - 1e-6)避免刚好整点结束时多算一小时）。
4. 统计与转宽：先统计每个房间-日期-小时的预订次数，再和全维度组合左连接，用replace_na填充未被预订的时段为0，最后转成宽表得到目标格式。

效率说明 #

该方案全程使用dplyr和tidyr的向量化操作，避免了嵌套循环，处理2000条记录+50个房间的规模完全无压力，计算效率远高于循环实现。

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