在R中基于多时间段筛选十万行以上大数据框的技术求助

Hey there! Let's work through this date range filtering problem for your large dataframe—dealing with 100k+ rows means we need something efficient, so let's jump right in. The key issue with most existing code snippets is usually unformatted date-time columns, so we'll start by fixing that first.

Step 1: Prepare Your Data (and Fix Date Formats!) #

First, we need to convert all your character-based time columns into proper POSIXct date-time objects—this is non-negotiable for accurate comparisons. We'll use lubridate for easy parsing, and include your sample data to test with:

# Load required packages
library(tidyverse)
library(lubridate)

# Create your date ranges table (converted to POSIXct)
date_ranges <- tibble(
  start = mdy_hm(c("7/26/2017 14:05", "8/24/2017 13:40", "6/29/2017 20:45")),
  end = mdy_hm(c("7/26/2017 16:05", "8/24/2017 15:40", "6/30/2017 0:41"))
)

# Create your main dataframe (with time converted to POSIXct)
df <- tibble(
  time = mdy_hm(c("6/29/2017 20:40", "6/29/2017 20:40", "6/29/2017 20:50", 
                  "6/29/2017 20:50", "6/29/2017 20:50", "7/26/2017 16:00", 
                  "7/26/2017 16:00", "7/26/2017 16:10", "7/26/2017 16:10", 
                  "8/24/2017 15:40", "8/24/2017 15:50")),
  temp.c = c(33.63, 33.11, 20.42, 20.32, 19.75, 22.9, 23.29, 23.38, 23.67, 24.06, 23.58),
  lux = c(0, 0, 602.8, 721.2, 3788.9, 183, 1237.9, 1173.3, 839.6, 387.5, 0),
  serial.num = c(20168779, 20168780, 20148333, 20148334, 20148335, 20168779, 
                 20168780, 20148333, 20148334, 20168780, 20148332)
)

Step 2: Filter Rows (Two Efficient Methods) #

We'll cover two approaches—one for tidyverse fans, and a faster one for massive datasets using data.table.

Method 1: Tidyverse (dplyr + lubridate) #

This uses vectorized operations (no slow loops!) to check if each row's time falls outside all ranges:

# Extract start/end vectors for faster comparison
starts <- date_ranges$start
ends <- date_ranges$end

# Filter rows where time is NOT in any of the ranges
df_filtered <- df %>%
  filter(rowSums(time >= starts & time <= ends) == 0)

The rowSums trick counts how many ranges each time falls into—if the count is 0, we keep the row. This is way faster than looping through each row individually.

Method 2: data.table (Best for 100k+ Rows) #

If you're dealing with truly large datasets, data.table's non-equi joins are unbeatable for speed. Here's how to use them:

library(data.table)

# Convert to data.table objects
setDT(df)
setDT(date_ranges)

# First, ensure time columns are POSIXct (skip if already done)
df[, time := mdy_hm(time)]
date_ranges[, `:=`(start = mdy_hm(start), end = mdy_hm(end))]

# Find all rows that ARE inside the ranges, then exclude them
inside_ranges <- df[date_ranges, on = .(time >= start, time <= end), nomatch = 0]
df_filtered <- df[!inside_ranges, on = .(time, temp.c, lux, serial.num)]

Verify the Result #

If you print df_filtered, you'll see exactly the rows we want to keep:

time temp.c    lux serial.num
1: 2017-06-29 20:40:00  33.63    0.0   20168779
2: 2017-06-29 20:40:00  33.11    0.0   20168780
3: 2017-07-26 16:10:00  23.38 1173.3   20148333
4: 2017-07-26 16:10:00  23.67  839.6   20148334
5: 2017-08-24 15:50:00  23.58    0.0   20148332

Why Your Original Code Failed #

Chances are, your time columns were still stored as character strings instead of POSIXct objects. Most solutions assume your dates are already formatted correctly, so always double-check with class(df$time) to confirm it's a date-time type.

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