如何在R语言中筛选符合条件的DataFrame数据：含前置Depth的Activity数据

Problem Statement #

You have a dataframe df1 tracking fish activity and depth over time, and you need to:

• Filter Activity records where the same fish (by ID) has a Depth record within the 3 minutes before the Activity timestamp
• Combine these valid Activity records with their matching prior Depth records into the final dataframe df2

Sample Data #

df1<- data.frame(ID=c(1,1,2,3,1,2,3,1,2,2,3,1,3,2,3), 
                 DateTime=c("2017-05-08 10:15:23","2017-05-08 10:19:31","2017-05-08 10:11:12","2017-05-08 10:02:23","2017-05-08 10:21:32","2017-05-08 10:15:52","2017-05-08 10:13:23","2017-05-08 10:22:19","2017-05-08 10:19:42","2017-05-08 10:21:27","2017-05-08 10:16:07","2017-05-08 10:24:53","2017-05-08 10:28:39","2017-05-08 10:23:48","2017-05-08 10:33:01"), 
                 DataType=c("Activity","Depth","Depth","Activity","Activity","Activity","Depth","Depth","Activity","Depth","Activity","Depth","Depth","Activity","Activity"), 
                 Value=c(0.89,24,19,1.8,1.1,0.7,17,28,2.1,20,1.35,12,19,0.4,0.97))

Solution Code #

First, load the required packages for data manipulation and time handling:

library(dplyr)
library(lubridate)

Step 1: Convert the DateTime column to a POSIXct format (required for accurate time difference calculations):

df1 <- df1 %>%
  mutate(DateTime = ymd_hms(DateTime))

Step 2: Isolate Depth data and identify valid Activity records:

# Extract all Depth records to use for matching
depth_records <- df1 %>% filter(DataType == "Depth")

# Find Activity records that have a matching Depth in the prior 3 minutes
valid_activities <- df1 %>%
  filter(DataType == "Activity") %>%
  group_by(ID) %>%
  mutate(
    # For each Activity, find the most recent Depth record for the same fish within 3 minutes prior
    matching_depth_time = map(DateTime, ~ depth_records %>%
                                filter(ID == cur_group()$ID, DateTime <= .x, .x - DateTime <= minutes(3)) %>%
                                arrange(desc(DateTime)) %>%
                                slice(1) %>%
                                pull(DateTime))
  ) %>%
  # Keep only Activities with a valid matching Depth
  filter(!is.na(matching_depth_time)) %>%
  ungroup() %>%
  select(-matching_depth_time) # Remove the helper column

Step 3: Combine valid Activities with their matching Depth records:

# Get the exact Depth records that match the valid Activities
matching_depths <- depth_records %>%
  inner_join(
    valid_activities %>%
      group_by(ID) %>%
      mutate(
        match_time = map(DateTime, ~ depth_records %>%
                          filter(ID == cur_group()$ID, DateTime <= .x, .x - DateTime <= minutes(3)) %>%
                          arrange(desc(DateTime)) %>%
                          slice(1) %>%
                          pull(DateTime))
      ) %>%
      ungroup() %>%
      select(ID, match_time) %>%
      unnest(match_time),
    by = c("ID", "DateTime" = "match_time")
  )

# Combine and sort to match your desired output
df2 <- bind_rows(matching_depths, valid_activities) %>%
  arrange(ID, DateTime)

Final Output #

> df2
# A tibble: 6 × 4
     ID DateTime           DataType Value
  <dbl> <dttm>              <chr>   <dbl>
1     1 2017-05-08 10:19:31 Depth     24  
2     1 2017-05-08 10:21:32 Activity   1.1
3     2 2017-05-08 10:21:27 Depth     20  
4     2 2017-05-08 10:23:48 Activity   0.4
5     3 2017-05-08 10:13:23 Depth     17  
6     3 2017-05-08 10:16:07 Activity   1.35

How It Works #

• We first standardize the time format to enable precise time window checks
• For each Activity record, we scan the same fish's Depth history to find the most recent entry within the 3-minute window before the Activity
• We then combine only the valid Activity records with their corresponding Depth entries, sorting to match your expected output order

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