tidyr/dplyr技术问询：为每个id补全指定账单月份并填充缺失值

Let's walk through a practical, straightforward solution to this problem—making sure every ID in your dataframe has entries for 3 predefined bill months, adding rows with "Not found" where those months are missing.

First, let's set up a sample incomplete dataframe to work with:

library(tidyverse)

# Sample input: some IDs are missing required bill months
df_incomplete <- tibble(
  id = c(1, 1, 2, 3, 3, 3),
  bill_month = c("2023-01", "2023-03", "2023-02", "2023-01", "2023-02", "2023-03"),
  payment_status = c("Paid", "Paid", "Pending", "Paid", "Paid", "Paid")
)

In this example, ID 1 is missing "2023-02", and ID 2 is missing both "2023-01" and "2023-03". Our goal is to fill these gaps.

Step-by-Step Solution #

1. Define Your Target Bill Months #

First, specify the 3 months every ID must have. For this example, we'll use:

target_months <- c("2023-01", "2023-02", "2023-03")

2. Create a Complete Grid of ID-Month Pairs #

We need to generate every possible combination of existing IDs and target months. This ensures we have a row for every required pair, even if it wasn't present in the original data:

complete_grid <- df_incomplete %>%
  expand_grid(id = unique(.$id), bill_month = target_months)

3. Merge with Original Data and Fill Missing Values #

Use a left join to attach the existing data to our complete grid, then replace any missing values (NA) with "Not found":

df_complete <- complete_grid %>%
  left_join(df_incomplete, by = c("id", "bill_month")) %>%
  mutate(payment_status = ifelse(is.na(payment_status), "Not found", payment_status))

Final Result #

If you print df_complete, you'll see exactly what we need:

# A tibble: 9 × 3
     id bill_month payment_status
  <dbl> <chr>      <chr>         
1     1 2023-01    Paid          
2     1 2023-02    Not found     
3     1 2023-03    Paid          
4     2 2023-01    Not found     
5     2 2023-02    Pending       
6     2 2023-03    Not found     
7     3 2023-01    Paid          
8     3 2023-02    Paid          
9     3 2023-03    Paid          

Every ID now has all 3 target months, with missing entries filled as requested.

Handling Edge Cases #

• Duplicate Entries: If your original data has multiple rows for the same ID and month, deduplicate first using distinct(id, bill_month, .keep_all = TRUE) before creating the complete grid to avoid conflicts.
• Dynamic Target Months: If your target months aren't fixed (e.g., the last 3 months from the dataset), you can generate them programmatically:

  # Get the most recent 3 unique months from the data
  target_months <- df_incomplete %>%
    pull(bill_month) %>%
    unique() %>%
    sort() %>%
    tail(3)
  

Post-Processing Checks #

To ensure everything worked as expected, run these quick validation checks:

• Verify every ID has exactly 3 rows:

  df_complete %>% count(id) %>% filter(n != 3)
  

• Confirm no missing values remain in the status column:

  df_complete %>% filter(is.na(payment_status))
  

• Check all target months are present for each ID:

  df_complete %>% count(id, bill_month) %>% filter(!bill_month %in% target_months)
  

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