I’m trying to figure out how to add columns to my df for row variances and also add a “flagged column” for when a parameter is met. My data is broken down in columns by a unique ID that has 6 months of values. So six rows of the same unique ID but with different values for each month (sometimes NA) and I need to calculate the variance between each month’s values, where some months have no value and for each unique ID and also flag the months that have an absolute variance (change) greater than 5% and display (add a column) where the month where 5% change first took place. If there’s no absolute variance greater than 5% within the 6 months of values then display “no change”, written in R.

This is what I tried but to no avail:

# Function to calculate variance between consecutive months
calculate_monthly_variance <- function(row) {
  variances <- c()
  for (i in 2:ncol(row)) {
    if (!is.na(row[i-1]) & !is.na(row[i])) {
      variance <- abs((row[i] - row[i-1]) / row[i-1]) 
# Calculate variance
      variances <- c(variances, variance)
    }
  }
  return(variances)
}

variances <- apply(data[, -1], 1, calculate_monthly_variance)

# Function to flag months with variance greater than 5% and find first occurrence
flag_and_find_first_month <- function(variances) {
  flagged_months <- variances > 0.05
  first_flagged_month <- which(flagged_months)[1]
  if (length(first_flagged_month) == 0) {
    return("no change")
  } else {
    return(first_flagged_month)
  }
}

# Apply function to each row (ID)
first_flagged_months <- sapply(variances, flag_and_find_first_month)

# Create result data frame
result <- data.frame(ID = data$ReferenceID, First_Flagged_Month = first_flagged_months)
print(result)

what I wanted to see:

ReferenceID Filedate Value Variance First Flagged Month
2024Q1101 1/1/2024 500000
2024Q1101 2/1/2024 505000 1.0%
2024Q1101 3/1/2024 545000 7.9% 2/1/2024
2024Q1101 4/1/2024 625000 14.7% 3/1/2024
2024Q1101 5/1/2024 630000 0.8%
2024Q1101 6/1/2024 635000 0.8%
2024Q1102 1/1/2024 NA
2024Q1102 2/1/2024 235000
2024Q1102 3/1/2024 240000 2.1%
2024Q1102 4/1/2024 245000 2.1%
2024Q1102 5/1/2024 325000 32.7% 4/1/2024
2024Q1102 6/1/2024 NA
2024Q1103 1/1/2024 415000
2024Q1103 2/1/2024 417000 0.5%
2024Q1103 3/1/2024 435000 4.3%
2024Q1103 4/1/2024 NA
2024Q1103 5/1/2024 NA
2024Q1103 6/1/2024 500000 No Change
2024Q1104 1/1/2024 250000
2024Q1104 2/1/2024 350000 40.0% 1/1/2024
2024Q1104 3/1/2024 NA
2024Q1104 4/1/2024 365000
2024Q1104 5/1/2024 375000 2.7%
2024Q1104 6/1/2024 NA
2024Q1105 1/1/2024 125000
2024Q1105 2/1/2024 130000 4.0%
2024Q1105 3/1/2024 135000 3.8%
2024Q1105 4/1/2024 140000 3.7%
2024Q1105 5/1/2024 145000 3.6%
2024Q1105 6/1/2024 NA No Change