R语言：如何优雅扩展现金预测表以整合交易金额数据？

Absolutely! You can achieve this cleanly and elegantly using the tidyverse ecosystem (building on the dplyr you're already using, plus tidyr for handling missing values). Here's a streamlined approach that aligns with your existing workflow:

Step 1: Standardize Date Formats & Column Names #

First, we'll align the date columns and naming conventions across both datasets to avoid confusion:

library(tidyverse)

# Clean up cash forecast data: convert date string to proper Date type, rename column
cash_forecast_clean <- cash_forecast_long %>%
  mutate(Day = as.Date(Day, "%m/%d/%Y")) %>%
  rename(Date = Day)

# Rename transaction data's date column to match
transaction_data_clean <- Sum_Trade_Amt_by_Funding_Date_Ccy %>%
  rename(Date = Funding_date)

Step 2: Merge, Fill Missing Values, & Calculate Updated Balances #

This step combines both datasets, fills in missing balance values using the most recent available balance, and computes the updated balance for every date (including new transaction dates):

extended_cash_forecast <- bind_rows(
  # Bring in existing cash forecast records
  cash_forecast_clean %>% select(Date, CCY_local, Balance),
  # Bring in transaction records (we'll match these to the cash forecast)
  transaction_data_clean %>% select(Date, CCY_local, Total)
) %>%
  # Sort by currency and date to ensure correct order for calculations
  arrange(CCY_local, Date) %>%
  # Group by currency to handle each one independently
  group_by(CCY_local) %>%
  # Fill missing Balance values with the most recent non-missing balance
  fill(Balance, .direction = "down") %>%
  # For dates with no transactions, set Total to 0 (so they don't affect balance)
  replace_na(list(Total = 0)) %>%
  # Calculate the running balance: starting balance + cumulative sum of transactions
  mutate(Updated_Balance = first(Balance) + cumsum(Total)) %>%
  # Keep only unique date-currency records (in case a date exists in both datasets)
  distinct(Date, CCY_local, .keep_all = TRUE) %>%
  # Remove grouping to return a regular tibble
  ungroup()

How This Works #

Let's break down the key parts:

• bind_rows(): Combines your existing cash forecast dates with all transaction dates, ensuring we don't miss any dates we need to include.
• fill(Balance, .direction = "down"): Automatically propagates the most recent balance forward to any new dates (like your transaction dates that aren't in the original cash forecast).
• replace_na(list(Total = 0)): Ensures dates without transactions don't alter the balance (since adding 0 keeps the balance the same as the previous date).
• first(Balance) + cumsum(Total): Computes the running balance by starting with the earliest available balance for each currency and adding up all transactions up to each date. This handles both new transaction dates (adding the total) and existing dates without transactions (carrying forward the balance).

Example Output #

For your sample data, this would generate a table where:

• For HKD on 2018-01-15, the updated balance would be the most recent HKD balance from cash_forecast_long plus 767200.
• For dates without transactions (like 2018-01-16 for AUD), the balance would stay the same as the previous AUD balance.

内容的提问来源于stack exchange，提问作者Yi Wen Edwin Ang