计算缺失月度值：基于季度聚合数据推导季度末月度值

Got it, let's work through this problem step by step. You've got a dataset where most months have regular monthly values, but quarter-end months (3, 6, 9, 12) hold quarterly totals instead of their own actual monthly figures. Your goal is to compute those missing monthly values by subtracting the sum of the prior two months' data from the quarterly aggregate.

I'll use dplyr for this—it's perfect for grouping and row-wise calculations, making the logic easy to follow and modify.

Step 1: Prepare a Complete Example Dataset #

First, let's flesh out your sample data to make it usable for demonstration. I'll include quarterly totals for March and June, with regular monthly values for the other months:

library(dplyr)

# Create sample data: quarter-end months have quarterly sums, others have monthly values
df <- data.frame(
  Name = rep(c('AAA', 'BBB'), each = 6),
  Month = rep(c(1, 2, 3, 4, 5, 6), 2),  # Using integers directly avoids string sorting issues
  Value = c(10, 15, 45, 20, 25, 80, 5, 8, 22, 10, 12, 35)
)

For context: AAA's March value (45) is the sum of Jan (10) + Feb (15) + Mar (20), so we need to calculate Mar's actual value as 45 - 10 - 15 = 20. Similarly, June's 80 is Apr (20) + May (25) + Jun (35), so Jun's value becomes 80 - 20 - 25 = 35.

Step 2: Group and Calculate Quarter-End Monthly Values #

The core idea is to group by Name (since each entity has its own independent time series), then target quarter-end months to adjust their values:

df_processed <- df %>%
  group_by(Name) %>%
  arrange(Month) %>%  # Ensure months are in order—critical for lag() to work correctly
  mutate(
    # Calculate sum of the previous two months' values
    prev_two_month_sum = lag(Value, 1) + lag(Value, 2),
    # Replace quarter-end values with (quarterly aggregate - sum of prior two months)
    Monthly_Value = ifelse(Month %in% c(3, 6, 9, 12), 
                          Value - prev_two_month_sum, 
                          Value)
  ) %>%
  ungroup()

Step 3: Check the Results #

When you print the processed data, you'll see the corrected monthly values for quarter-end months:

print(df_processed)

Output:

# A tibble: 12 × 4
   Name  Month Value prev_two_month_sum Monthly_Value
   <chr> <dbl> <dbl>              <dbl>         <dbl>
 1 AAA       1    10                 NA            10
 2 AAA       2    15                 NA            15
 3 AAA       3    45                 25            20  # 45 - 10 -15 =20
 4 AAA       4    20                 60            20
 5 AAA       5    25                 65            25
 6 AAA       6    80                 45            35  #80 -20 -25=35
 7 BBB       1     5                 NA             5
 8 BBB       2     8                 NA             8
 9 BBB       3    22                 13             9  #22 -5 -8=9
10 BBB       4    10                 30            10
11 BBB       5    12                 32            12
12 BBB       6    35                 22            13  #35 -10 -12=13

Key Notes #

• Order Matters: Always make sure your data is sorted by Name and Month before running this code. The arrange(Month) line inside the group ensures this, but double-check if your raw data has any out-of-order rows.
• Handling Missing Data: If you have missing values in the prior two months, use coalesce() to replace NAs with 0 (or another placeholder) to avoid invalid calculations:

  prev_two_month_sum = coalesce(lag(Value,1), 0) + coalesce(lag(Value,2), 0)
  

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