在R语言中筛选事件发生前后至少3年观测值的实现方法

Problem Statement #

Originally, the requirement was to filter observations exactly 3 years before and after an event. Now we need to adjust this to retain only companies that have at least 3 years of observational data both before and after their event (where status=0 marks the event year). All observations for these qualifying companies should be kept.

Sample Input Data #

companyID year status
1 2000 1
1 2001 1
1 2002 1
1 2003 1
1 2004 0
1 2005 2
1 2006 2
1 2007 2
2 2012 1
2 2013 0
2 2014 2
2 2015 2
2 2016 2
3 2008 1
3 2009 1
3 2010 1
3 2011 1
3 2012 1
3 2013 0
3 2014 2
3 2015 2
3 2016 2
3 2017 2

Desired Output #

companyID year status
1 2000 1
1 2001 1
1 2002 1
1 2003 1
1 2004 0
1 2005 2
1 2006 2
1 2007 2
3 2008 1
3 2009 1
3 2010 1
3 2011 1
3 2012 1
3 2013 0
3 2014 2
3 2015 2
3 2016 2
3 2017 2

Solution 1: Using dplyr (Tidyverse) #

This approach is intuitive and leverages the tidyverse's grammar for smooth data manipulation.

library(dplyr)

# Convert sample data to a data frame (skip this if your data is already loaded)
df <- tibble(
  companyID = c(1,1,1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3),
  year = c(2000,2001,2002,2003,2004,2005,2006,2007,2012,2013,2014,2015,2016,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017),
  status = c(1,1,1,1,0,2,2,2,1,0,2,2,2,1,1,1,1,1,0,2,2,2,2)
)

# Filter qualifying companies and retain their observations
result <- df %>%
  group_by(companyID) %>%
  mutate(
    # Get the event year (assuming one event per company)
    event_year = year[status == 0],
    # Count years before the event (status=1)
    pre_event_years = sum(status == 1 & year < event_year),
    # Count years after the event (status=2)
    post_event_years = sum(status == 2 & year > event_year)
  ) %>%
  # Keep only companies with at least 3 years pre and post event
  filter(pre_event_years >= 3 & post_event_years >= 3) %>%
  # Remove temporary calculation columns
  select(-event_year, -pre_event_years, -post_event_years) %>%
  ungroup()

# View the result
print(result)

Solution 2: Using Base R #

If you prefer not to use external packages, here's a base R alternative that achieves the same result:

# Convert sample data to a data frame
df <- data.frame(
  companyID = c(1,1,1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3),
  year = c(2000,2001,2002,2003,2004,2005,2006,2007,2012,2013,2014,2015,2016,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017),
  status = c(1,1,1,1,0,2,2,2,1,0,2,2,2,1,1,1,1,1,0,2,2,2,2)
)

# Get event year for each company
event_years <- tapply(df$year, df$companyID, function(x) {
  company_rows <- df$companyID == unique(df$companyID)
  x[df$status[company_rows] == 0]
})

# Calculate pre-event and post-event year counts
pre_counts <- sapply(names(event_years), function(id) {
  sum(df$companyID == id & df$status == 1 & df$year < event_years[[id]])
})

post_counts <- sapply(names(event_years), function(id) {
  sum(df$companyID == id & df$status == 2 & df$year > event_years[[id]])
})

# Identify valid company IDs
valid_companies <- names(event_years)[pre_counts >= 3 & post_counts >= 3]

# Filter the original data to keep only valid companies
result_base <- df[df$companyID %in% valid_companies, ]

# View the result
print(result_base)

Key Notes #

• Both solutions assume each company has exactly one event year (one row with status=0). If your data has multiple events per company, adjust the logic (e.g., use first(year[status==0]) or last(year[status==0]) to pick a specific event).
• The pre_event_years and post_event_years counts ensure we only keep companies with at least 3 years of data in both periods.
• The final result retains all observations for qualifying companies, not just the pre/post event rows.

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