R语言：如何从最后m个值中获取指定排名的极值均值？

To replicate the Excel functionality of calculating the average of specific ranked smallest/largest values over a trailing window of size m in R, here's a step-by-step solution:

Approach #

The core idea is to:

1. For each position in your dataset (starting from the m-th element), extract the trailing window of the last m values.
2. For each window, sort the values and select the specified ranks for smallest and largest values.
3. Compute the average of those selected values and assign it to the corresponding position (with NA for positions before the m-th element).

Solution Code #

We'll cover both a base R implementation (no extra packages needed) and a more concise version using the zoo package for rolling window operations.

Base R Implementation

This uses a straightforward loop to iterate through each valid window:

# Define your data and parameters
Data <- c(8,2,9,7,8,8,9,8,4,9,9,7,2,5,2,2,1,9,9,7)
m <- 10  # Number of trailing values to consider
min_ranks <- 2:4  # Ranks of smallest values to average
max_ranks <- 2:4  # Ranks of largest values to average

compute_window_stats <- function(data, window_size, min_ranks, max_ranks) {
  n <- length(data)
  min_avg <- rep(NA, n)
  max_avg <- rep(NA, n)
  
  # Iterate through each valid window (starting from window_size-th element)
  for (i in window_size:n) {
    window <- data[(i - window_size + 1):i]
    
    # Calculate average of specified smallest ranks
    sorted_window <- sort(window)
    min_avg[i] <- mean(sorted_window[min_ranks])
    
    # Calculate average of specified largest ranks
    sorted_window_desc <- sort(window, decreasing = TRUE)
    max_avg[i] <- mean(sorted_window_desc[max_ranks])
  }
  
  return(data.frame(val = data, min = min_avg, max = max_avg))
}

# Generate the result
result <- compute_window_stats(Data, m, min_ranks, max_ranks)

# View the first 10 rows to match your example
head(result, 10)

Using zoo Package (Concise Version)

The rollapply function from the zoo package simplifies rolling window operations:

library(zoo)

# Define parameters (same as above)
Data <- c(8,2,9,7,8,8,9,8,4,9,9,7,2,5,2,2,1,9,9,7)
m <- 10
min_ranks <- 2:4
max_ranks <- 2:4

# Calculate rolling averages
min_avg <- rollapply(
  Data,
  width = m,
  FUN = function(x) mean(sort(x)[min_ranks]),
  fill = NA,
  align = "right"  # Align window to the end (trailing values)
)

max_avg <- rollapply(
  Data,
  width = m,
  FUN = function(x) mean(sort(x, decreasing = TRUE)[max_ranks]),
  fill = NA,
  align = "right"
)

# Combine into a data frame
result <- data.frame(val = Data, min = min_avg, max = max_avg)

Example Output #

Running either code will produce a data frame where the 10th row matches your expected result:

val      min      max
1    8       NA       NA
2    2       NA       NA
3    9       NA       NA
4    7       NA       NA
5    8       NA       NA
6    8       NA       NA
7    9       NA       NA
8    8       NA       NA
9    4       NA       NA
10   9 6.333333 8.666667

Customization #

You can easily adjust:

• m: Change the size of the trailing window (e.g., m=5 to use the last 5 values).
• min_ranks/max_ranks: Modify which ranks to average (e.g., min_ranks=1:3 for top 3 smallest values).

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