在R语言中处理时间序列数据：解决时间列格式问题并计算数值大于阈值的月度时长

Hey there! Let's work through your two problems step by step, starting with the time column issue and then moving on to calculating the monthly duration.

1. Fixing the Time Column Problem #

The reason as.POSIXct() adds a date to your Time column is that POSIXct is a datetime type that requires both date and time information—it can't store just time on its own. If you only need to work with the time component without dates, here are two better options:

Option 1: Use the hms package for time-only values #

This package creates a dedicated time type that doesn't include dates:

# Install and load the package if you haven't already
install.packages("hms")
library(hms)

# Extract just the time from your datetime column
df$Time <- as_hms(df$a)

Now df$Time will show values like 08:00:00 without any attached date, and it's still a numeric-based type that works well for calculations.

Option 2: Keep time as a character string #

If you don't need to perform calculations on the time itself, just keep it as a formatted string:

df$Time <- format(df$a, "%H:%M:%S")

This will give you a character column with just the time, no date attached.

2. Calculating Monthly Duration of Values > 5 #

First, we need to clean up your data and set it up for calculations. Let's walk through each step:

Step 1: Clean the numeric columns (b and c) #

Your b and c columns have "N/A" values which are character strings—we need to convert these to proper NA values and turn the columns into numeric types:

# Replace "N/A" with NA and convert to numeric
df$b <- as.numeric(ifelse(df$b == "N/A", NA, df$b))
df$c <- as.numeric(ifelse(df$c == "N/A", NA, df$c))

Step 2: Convert datetime to POSIXct (better for time calculations) #

POSIXlt works, but POSIXct is more efficient for grouping and time difference calculations:

df$a <- as.POSIXct(df$a, tz = "EST")

Step 3: Calculate time intervals between rows #

Looking at your sample data, most entries are 15 minutes apart, but some are duplicates. We'll calculate the time difference between each row and the previous one to get the duration for each entry:

# Calculate time difference in minutes
df$interval_min <- c(NA, difftime(df$a[-1], df$a[-nrow(df)], units = "mins"))

# For duplicate times, set interval to 0 (since no time passes between them)
df$interval_min[is.na(df$interval_min)] <- 0

Step 4: Group by month and sum eligible duration #

We'll use dplyr for easy grouping and summarizing (it's more intuitive than base R for this task):

# Install and load dplyr if needed
install.packages("dplyr")
library(dplyr)
library(lubridate) # For easy month/year extraction

# Calculate monthly duration for columns b and c where value >5
monthly_duration <- df %>%
  # Extract year and month to group by
  mutate(month_year = floor_date(a, "month")) %>%
  # For each row, mark if b/c is >5, multiply by interval to get eligible duration
  mutate(
    duration_b = ifelse(b > 5, interval_min, 0),
    duration_c = ifelse(c > 5, interval_min, 0)
  ) %>%
  # Group by month and sum the eligible durations
  group_by(month_year) %>%
  summarize(
    total_duration_b_mins = sum(duration_b, na.rm = TRUE),
    total_duration_c_mins = sum(duration_c, na.rm = TRUE),
    # Optional: convert to hours for readability
    total_duration_b_hours = total_duration_b_mins / 60,
    total_duration_c_hours = total_duration_c_mins / 60
  )

# View the result
print(monthly_duration)

What this does: #

• floor_date(a, "month") rounds each datetime to the first day of its month, giving us a clean grouping variable.
• ifelse(b >5, interval_min, 0) assigns the row's time interval to the duration only if the value is above 5; otherwise, it's 0.
• sum(...) adds up all eligible intervals per month, giving you total minutes (and optionally hours) where values exceeded 5.

Notes on edge cases: #

• If your data has missing rows (gaps in time), the difftime calculation will automatically account for the actual time gap between entries.
• The na.rm = TRUE in sum() ignores rows where b or c was NA (since those values aren't above 5).

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