So I have two dataframes, in a tidy format:
df1 <- data.frame(date=as.Date(paste0('2018-12-',c(11,15,18,22,25,29))), balance=c(-500,-250,0,250,-300,500), account='salary') df2 <- data.frame(date=as.Date(paste0('2018-12-',c(16,22,27))), balance=c(1000, 700, 250), account='budget')
Now, this is balance of my budget account and my daily account. the sum of the “balance” column from both thee dataframes would give the amount of money I have on any given day.
However, as there is only a row in the dataframe if a transfer that changes the balance is made, it complicates the computation. The merge has to be done, so for every time there is a row in the one dataframe, there has to be row in the other dataframe, that correponds to the balance in that account on that day. So the result in the toy example would be this:
df.result <- data.frame(date=as.Date(paste0('2018-12-',c(11,15,16,18,22,25,27,29))), balance.salary=c(-500,-250,-250,0,250,-300,-300,500), balance.budget=c(1000,1000,1000,1000,700,700,250,250))
Notice how even though I don’t have information for the budget-account from the first date that the salay-account has a row, I’m using the information from the first time there is a row from the budget account.
here I have changed the column names for the balance-variable, so that one row can have the balance for both, but this is not the essential part of the solution, only that the result can be computed like this:
df.result$ balance.total <- df.result$ balance.salary + df.result$ balance.budget
I have tried using crossing() as per this answer, Copying row from one df into everyone row in another, but isn’t useful in this case, as far as I can tell.