如何在R语言中筛选满足for_hire_light特定规则的车辆数据

Hey there! Let's work through this data cleaning task together. Using the tidyverse package (which plays nicely with your tibble data), we can implement your requirements step by step:

Step 1: Load Required Package #

First, make sure you have tidyverse installed and loaded:

install.packages("tidyverse") # Run once if not installed
library(tidyverse)

Step 2: Define the Data (for reference) #

Your sample data structure is already provided, but let's recap it for clarity:

my.df <- structure(list(vehicle_id = c("zzJcCfa6nuUF9A02Sud5fASxowM", "zzJcCfa6nuUF9A02Sud5fASxowM", "zzJcCfa6nuUF9A02Sud5fASxowM", "zzJcCfa6nuUF9A02Sud5fASxowM", "zzJcCfa6nuUF9A02Sud5fASxowM", "zzJcCfa6nuUF9A02Sud5fASxowM", "zzJcCfa6nuUF9A02Sud5fASxowM", "/+bx80f3gOoPMoFBsS+3xX6jpi8", "/+bx80f3gOoPMoFBsS+3xX6jpi8", "/+bx80f3gOoPMoFBsS+3xX6jpi8"), location = c("100.50457_13.90834", "100.51297_13.91534", "100.51323_13.91548", "100.50572_13.90243", "100.50717_13.8986", "100.50979_13.89154", "100.51099_13.88835", "100.6657_13.90103", "100.66742_13.90093", "100.66916_13.90055"), time = c("05:19:37", "05:21:37", "05:22:37", "05:24:37", "05:25:37", "05:26:37", "05:28:37", "22:41:30", "22:42:30", "22:44:30"), for_hire_light = c(0, 0, 0, 0, 0, 0, 0, 1, 1, 1)), row.names = c(NA,-10L), class = c("tbl_df", "tbl", "data.frame"))

Step 3: Implement the Filtering Logic #

Here's the core code to meet your requirements:

filtered_df <- my.df %>%
  # Group by vehicle ID to process each vehicle separately
  group_by(vehicle_id) %>%
  # First, keep only vehicles that have both 0 and 1 in for_hire_light
  filter(n_distinct(for_hire_light) == 2) %>%
  # Now, find the first and last occurrence of 1 for each vehicle
  mutate(
    first_one = min(which(for_hire_light == 1)),
    last_one = max(which(for_hire_light == 1))
  ) %>%
  # Keep rows from the first 1 to the last 1 (inclusive)
  filter(row_number() >= first_one & row_number() <= last_one) %>%
  # Remove the helper columns we created
  select(-first_one, -last_one) %>%
  # Ungroup to return to a regular tibble
  ungroup()

How This Works: #

• group_by(vehicle_id): Ensures all operations are applied per vehicle.
• filter(n_distinct(for_hire_light) == 2): Removes vehicles that only have 0 or only have 1 in their records.
• mutate(...): Creates helper columns to mark the first and last positions where for_hire_light is 1.
• filter(row_number() >= first_one & ...): Retains only the rows between (and including) the first and last 1, which guarantees the sequence starts and ends with 1, and includes 0s in between (since we already filtered for vehicles with both values).

Note on Your Sample Data: #

In your provided sample data, neither vehicle has both 0 and 1 values in for_hire_light (one has all 0s, the other all 1s), so running this code will return an empty tibble. But it will work exactly as expected on data that matches your desired output criteria (like the example you shared with ZKfjZ13x53D6mssc2Acqf6U5i3g).

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