在R语言中筛选DataReferencia列以保留对应Date列日期的当月及未来12个月数据并计算Valor列差值

First, let's break down why your original code returned an empty data frame:

• You referenced columns Data.Ano and Data.Mes which don't exist in your data frame—these need to be derived from the Date column using lubridate functions.
• Your filter condition uses an implicit AND (via the comma), which requires month(DataReferencia) to be both equal to the current month and 11 months ahead at the same time—this is impossible, so no rows match.

Here's a step-by-step solution to achieve your goal:

Step 1: Load Required Libraries #

library(dplyr)
library(lubridate)
library(tidyr) # For reshaping data

Step 2: Filter Relevant DataReferencia Entries #

We'll calculate the exact target dates (first day of the current month, and first day of 11 months later) for each Date, then filter rows where DataReferencia matches either of these dates:

filtered_data <- test %>%
  # Calculate the two target reference dates for each Date
  mutate(
    current_month_start = floor_date(Date, "month"), # First day of Date's month
    future_month_start = current_month_start %m+% months(11) # First day of 11 months later
  ) %>%
  # Keep only rows where DataReferencia is one of the two target dates
  filter(DataReferencia %in% c(current_month_start, future_month_start)) %>%
  # Add a label to distinguish current vs future entries
  mutate(period = case_when(
    DataReferencia == current_month_start ~ "current",
    DataReferencia == future_month_start ~ "future"
  ))

Step 3: Calculate the Valor Difference #

Next, we'll reshape the data to have current and future Valor values in separate columns, then compute their difference:

result <- filtered_data %>%
  pivot_wider(
    # Group by all non-Valor columns to keep context
    id_cols = c(Instituicao, Date, DataReuniao, Reuniao, MetaSelic),
    names_from = period,
    values_from = Valor
  ) %>%
  # Compute the difference (adjust to `current - future` if you want the reverse)
  mutate(Valor_diff = future - current) %>%
  # Optional: Remove rows where either current or future Valor is missing
  drop_na(current, future)

What This Does #

• For each Date, we retain only the DataReferencia entries for the first day of the current month and the first day of 11 months later (matching your example of 2003-01-17 → 2003-01-01 and 2003-12-01).
• The pivot_wider step aligns the current and future Valor values for each Date, making it easy to compute the subtraction.
• The drop_na step removes any Dates where either the current or future Valor entry is missing (you can omit this if you want to keep incomplete rows with NA differences).

Example Output from Your Sample Data #

For the Date 2003-01-17, the result will show:

• current = 26 (Valor from 2003-01-01)
• future = 22 (Valor from 2003-12-01)
• Valor_diff = -4 (22 - 26)

Dates like 2003-01-18 (which don't have a future 2003-12-01 entry in your sample) will be excluded if you use drop_na.

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