library(tidyverse)
#left side of grey table
ls_df <- filter(dat,
MOB==0) %>%
select(Actual_UPB,Units,Vintage) %>% as_tibble()
#right side of grey table
rs_df1 <- select(dat,
Vintage,MOB,Actual_NCO)
#seems the actual_nco values need accumulating
rs_df2 <- group_by(rs_df1,
Vintage) %>%
mutate(NCO = cumsum(Actual_NCO))
rs_df3 <- pivot_wider(rs_df2,
id_cols=Vintage,names_from=MOB,values_from = NCO)
#combine
df_1 <- left_join(
ls_df,
rs_df3
)
# rescale the numeric titled columns (0,1,2..) to pcnt of actual_UPB
df_1_names <- names(df_1)
ls_df_names <- names(ls_df)
names_to_mutate <- setdiff(df_1_names,ls_df_names)
df_2<- mutate_at(df_1,
names_to_mutate,
~100*(.)/Actual_UPB)
> df_2
# A tibble: 3 x 6
Actual_UPB Units Vintage `0` `1` `2`
<dbl> <int> <int> <dbl> <dbl> <dbl>
1 1000000 21684 201912 0.025 6.91 13.9
2 1000000 10559 202001 0.025 1.70 NA
3 1000000 9952 202002 0.025 NA NA
1 Like