将R语言日期范围数据框转换为按小时统计的时长总和

Got it, let's break down how to solve this problem—you need to sum up the total event duration per hour from a data frame with start and end timestamps. The key challenge here is handling events that cross hour boundaries (like an event starting at 10:52 and ending at 11:01, which contributes to both the 10 AM and 11 AM hours).

Step 1: Prepare Your Data #

First, make sure your timestamp columns are formatted as POSIXct (R's datetime type). Here's how to set up your sample data:

# Sample data frame
df <- data.frame(
  timestamp = as.POSIXct(c(
    "2018-03-27 10:00:27", "2018-03-27 10:27:28", "2018-03-27 10:52:59",
    "2018-03-27 11:17:59", "2018-03-27 12:03:29", "2018-03-27 12:51:00",
    "2018-03-27 13:18:31", "2018-03-27 13:42:56", "2018-03-27 14:08:26"
  )),
  endtimestamp = as.POSIXct(c(
    "2018-03-27 10:07:27", "2018-03-27 10:37:58", "2018-03-27 11:01:29",
    "2018-03-27 11:27:00", "2018-03-27 12:15:59", "2018-03-27 13:01:30",
    "2018-03-27 13:26:01", "2018-03-27 13:50:56", "2018-03-27 14:21:27"
  ))
)

Step 2: Use lubridate and purrr for Hourly Splitting #

We'll use the lubridate package for easy datetime manipulation and purrr to apply a custom function across all rows.

First, install and load the packages:

install.packages(c("lubridate", "purrr", "dplyr"))
library(lubridate)
library(purrr)
library(dplyr)

Next, create a function that splits an event into hourly intervals and calculates the duration for each interval:

# Function to split an event into hourly chunks and compute duration per hour
split_event <- function(start_time, end_time) {
  # Get the start of the hour for both timestamps
  start_hour <- floor_date(start_time, "hour")
  end_hour <- floor_date(end_time, "hour")
  
  # Case 1: Event stays within one hour
  if (start_hour == end_hour) {
    data.frame(
      hour = start_hour,
      duration_mins = as.numeric(difftime(end_time, start_time, units = "mins"))
    )
  } 
  # Case 2: Event crosses multiple hours
  else {
    # Duration for the first partial hour
    first_hour_duration <- as.numeric(difftime(start_hour + hours(1), start_time, units = "mins"))
    # Duration for the last partial hour
    last_hour_duration <- as.numeric(difftime(end_time, end_hour, units = "mins"))
    # Full hours in between (each contributes 60 minutes)
    middle_hours <- seq(start_hour + hours(1), end_hour - hours(1), by = "hour")
    middle_durations <- rep(60, length(middle_hours))
    
    # Combine all chunks into one data frame
    rbind(
      data.frame(hour = start_hour, duration_mins = first_hour_duration),
      data.frame(hour = middle_hours, duration_mins = middle_durations),
      data.frame(hour = end_hour, duration_mins = last_hour_duration)
    )
  }
}

Step 3: Apply the Function and Sum Durations #

Now, apply the function to every row in your data frame, combine the results, and sum the duration per hour:

# Split all events into hourly intervals
hourly_chunks <- map2_df(df$timestamp, df$endtimestamp, split_event)

# Calculate total duration per hour
hourly_total <- hourly_chunks %>%
  group_by(hour) %>%
  summarise(total_duration_mins = round(sum(duration_mins), 2)) %>%
  ungroup()

# View the final result
print(hourly_total)

Alternative: Efficient data.table Approach (For Large Datasets) #

If you're working with a huge dataset, data.table will be faster. Here's how to do it:

install.packages("data.table")
library(data.table)

setDT(df)
df[, `:=`(start = timestamp, end = endtimestamp)]

# Generate all hours covered by each event
df[, hour_seq := lapply(1:.N, function(i) {
  seq(floor_date(start[i], "hour"), floor_date(end[i], "hour"), by = "hour")
})]

# Unnest the hour sequences and calculate duration per hour
df_long <- df[, .(hour = unlist(hour_seq), start = start, end = end), by = 1:nrow(df)]
df_long[, duration_mins := pmax(0, as.numeric(pmin(end, hour + hours(1)) - pmax(start, hour), units = "mins"))]

# Sum durations by hour
hourly_total_dt <- df_long[, .(total_duration_mins = round(sum(duration_mins), 2)), by = hour]
print(hourly_total_dt)

What the Result Looks Like #

You'll get a data frame with two columns:

• hour: The start time of each hour (e.g., 2018-03-27 10:00:00)
• total_duration_mins: The total number of minutes all events ran during that hour

For your sample data, the 10 AM hour will include the first two full events plus the partial third event, and the 11 AM hour will include the partial third event plus the fourth full event.

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