R语言数据框行滚动差值（Running delta）计算实现问询

Hey there! Let's break down how to create your desired data frame df2 from df1, with the rolling year-over-year differences and an NA-filled 1980 column. Below are two practical approaches—one using base R, and another with the tidyverse toolkit for more readable code.

Approach 1: Base R #

This method is great if you prefer working without external packages:

# Start by creating df2 with just the Company column
df2 <- df1[, "Company", drop = FALSE]

# Identify all year columns (matches columns starting with X198)
year_columns <- grep("^X198", names(df1), value = TRUE)

# Calculate the year-over-year differences (current year minus prior year)
diff_values <- df1[, year_columns[-1]] - df1[, year_columns[-length(year_columns)]]

# Keep the original year column names for the differences
names(diff_values) <- year_columns[-1]

# Add the NA-filled X1980 column and merge the differences
df2$X1980 <- NA
df2 <- cbind(df2, diff_values)

# Reorder columns to match the original df1 structure
df2 <- df2[, names(df1)]

Approach 2: Tidyverse (dplyr + tidyr) #

If you're comfortable with the tidyverse, this approach is more intuitive for data reshaping:

library(dplyr)
library(tidyr)

df2 <- df1 %>%
  # Reshape data to long format for easier group-wise calculations
  pivot_longer(cols = starts_with("X"), names_to = "Year", values_to = "Value") %>%
  # Ensure data is ordered by Company and Year
  arrange(Company, Year) %>%
  # Group by each company to calculate differences per company
  group_by(Company) %>%
  # Compute difference: NA for 1980, else current value minus previous year's value
  mutate(Diff = case_when(Year == "X1980" ~ NA_real_, TRUE ~ Value - lag(Value))) %>%
  # Reshape back to wide format to match original structure
  pivot_wider(names_from = Year, values_from = Diff) %>%
  # Remove grouping
  ungroup()

Optimization & Best Practices #

• Robust Column Matching: Instead of hardcoding X198, use matches("X\\d{4}") to match any column with a 4-digit year after an X—this works if you add more years later (like X1984) without changing code.
• Handle Missing Values: If your original df1 has NA values, the difference calculations will also return NA. Use replace_na() (tidyverse) or is.na() (base R) to handle these based on your needs.
• Reusable Function: Wrap the logic into a function for repeated use:

  create_year_diff_df <- function(data, id_col = "Company", year_pattern = "^X\\d{4}") {
    year_cols <- grep(year_pattern, names(data), value = TRUE)
    diff_vals <- data[, year_cols[-1]] - data[, year_cols[-length(year_cols)]]
    names(diff_vals) <- year_cols[-1]
    result <- data[, id_col, drop = FALSE]
    result[[year_cols[1]]] <- NA
    result <- cbind(result, diff_vals)
    result[, names(data)]
  }
  
  # Use the function
  df2 <- create_year_diff_df(df1)
  

• Performance: For very large datasets, base R methods are generally faster. Tidyverse methods are more readable, which is better for collaboration or complex workflows.

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