使用dplyr/purrr动态填充数据框两列的技术方案问询

Hey there! Let's work through this problem with dplyr—it's super efficient for this kind of row-wise and grouped data manipulation. First, let's recap your input data for clarity:

test <- data.frame(
  date = c("2018-01-01", "2018-01-01", "2018-01-15", "2018-01-15"),
  name = c("a","b","a","b"),
  contrib = c(4,2,4,2),
  amt_needed = c(100,100, NA,NA),
  remaining = c(94,94, NA,NA)
)

Step 1: Fill NA Values in amt_needed #

To replace NAs in amt_needed with the remaining value from two rows above, we can use dplyr::lag() which lets us access values from previous rows. The coalesce() function is perfect here because it picks the first non-NA value between the original amt_needed and the lagged remaining value—no messy conditional checks needed.

Step 2: Calculate Missing remaining Values #

For the remaining column, we need to subtract the total of contrib values (for each date group) from the newly filled amt_needed. Grouping by date ensures we compute the total contribution per time period, which aligns with your sample data's structure (each date has two rows for "a" and "b").

Full Chained Operation #

Putting it all together in a single, readable dplyr pipeline:

library(dplyr)

test_processed <- test %>%
  # Fill amt_needed NA with remaining value from 2 rows prior
  mutate(amt_needed = coalesce(amt_needed, lag(remaining, n = 2))) %>%
  # Group by date to calculate total contribution per period
  group_by(date) %>%
  # Compute remaining for NA rows using filled amt_needed
  mutate(remaining = coalesce(remaining, amt_needed - sum(contrib))) %>%
  ungroup()

# View the final result
test_processed

What This Does #

• lag(remaining, n = 2) precisely targets the remaining value two rows above the current one, which fits your requirement perfectly even with larger datasets.
• Grouping by date ensures we're only summing contributions within the same time period, which makes logical sense for your data structure.
• coalesce() preserves existing non-NA values while replacing NAs with our calculated values—cleaner and more efficient than nested ifelse() statements.

Output Preview #

Running this code will give you the expected result:

# A tibble: 4 × 5
  date       name  contrib amt_needed remaining
  <chr>      <chr>   <dbl>      <dbl>     <dbl>
1 2018-01-01 a           4        100        94
2 2018-01-01 b           2        100        94
3 2018-01-15 a           4         94        88
4 2018-01-15 b           2         94        88

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