基于R语言为国家-危机对生成序列变量的技术咨询

Got it, let's break this down. You want a new variable where every time crisis hits 1 for a country, the value resets to 0. For dates before that crisis, the variable counts backward (negative numbers), and for dates after, it counts forward (positive numbers). If a country has multiple crisis points, each one becomes a new starting point for the sequence.

Here are two practical implementations using common R packages:

Method 1: Using dplyr + zoo #

This approach is intuitive and great for readable code, especially if you're already using the tidyverse:

library(dplyr)
library(zoo)

# Your reproducible dataset
date <- c("2011Q2","2011Q3","2011Q4","2012Q1","2012Q2","2011Q2","2011Q3","2011Q4","2012Q1","2012Q2")
date <- as.yearqtr(date)
country <- c("AT","AT","AT","AT","AT","BE","BE","BE","BE","BE")
crisis <- c(0,0,1,0,1,0,0,0,1,0)
df <- data.frame(date, country, crisis)

# Generate the new variable
df <- df %>%
  group_by(country) %>%
  # Mark dates where crisis occurs, leave others as NA
  mutate(crisis_date = ifelse(crisis == 1, date, NA)) %>%
  # Fill NA values with the most recent crisis date (forward first, then backward)
  fill(crisis_date, .direction = "up") %>%
  fill(crisis_date, .direction = "down") %>%
  # Calculate quarter difference: convert yearqtr to numeric, multiply by 4 to get quarters
  mutate(crisis_seq = as.numeric(date - crisis_date) * 4) %>%
  select(-crisis_date) %>% # Clean up the intermediate column
  ungroup()

# Check the result
print(df)

Output: #

date country crisis crisis_seq
1 2011 Q2       AT      0         -2
2 2011 Q3       AT      0         -1
3 2011 Q4       AT      1          0
4 2012 Q1       AT      0          1
5 2012 Q2       AT      1          0
6 2011 Q2       BE      0         -3
7 2011 Q3       BE      0         -2
8 2011 Q4       BE      0         -1
9 2012 Q1       BE      1          0
10 2012 Q2       BE      0          1

Method 2: Using data.table (For Large Datasets) #

If you're working with a big dataset, data.table will be much faster. Here's how to do the same logic:

library(data.table)
library(zoo)

# Convert your data frame to data.table
dt <- as.data.table(df)

# Generate the crisis sequence
dt[, crisis_date := fifelse(crisis == 1, date, NA_real_), by = country]
dt[, crisis_date := nafill(crisis_date, type = "locf"), by = country] # Forward fill recent crisis date
dt[, crisis_date := nafill(crisis_date, type = "nocb"), by = country] # Backward fill for dates before first crisis
dt[, crisis_seq := as.numeric(date - crisis_date) * 4]
dt[, crisis_date := NULL] # Remove intermediate column

# View the result
print(dt)

Key Logic Breakdown #

1. Group by Country: Ensures we calculate the sequence independently for each country.
2. Map Dates to Crisis Points: We fill in each date with the closest crisis date (either the most recent past crisis or the next future crisis if we're before the first crisis).
3. Calculate Quarter Difference: Since yearqtr values are stored as year + quarter/4, subtracting two yearqtr values gives a year difference—multiply by 4 to get the number of quarters between dates.

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