Vintage Analysis - Excel Replication

I'd like to post an Excel file to ask the community how I can replicate the results. Any suggestions on how I can upload an excel file? In the meantime, the picture illustrates what I am trying to replicate in R (i.e., the grey boxed area is what I am trying to recreate in R. (It's a vintage analysis with dummy data!)


dat <- structure(list(Vintage = c(201912L, 201912L, 201912L, 202001L, 
202001L, 202002L), Month_Key = c(201912L, 202001L, 202002L, 202001L, 
202002L, 202002L), MOB = c(0L, 1L, 2L, 0L, 1L, 0L), Units = c(21684L, 
21277L, 20216L, 10559L, 10340L, 9952L), Model_UPB = c(1e+06, 
941544.3761, 870733.2831, 1e+06, 939308.4539, 1e+06), Model_NCO = c(1000, 
12785.51286, 48072.55081, 1000, 9721.796368, 1000), Actual_UPB = c(1e+06, 
933959.488, 892096.5154, 1e+06, 929145.0864, 1e+06), Actual_NCO = c(250, 
68824.8, 70067.32, 250, 16714.96, 250)), class = "data.frame", row.names = c(NA, 
-6L))
dat
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

Ran into some trouble with the pivot_wider function and had to upgrade R and tidyverse.....but I think it is working now! Thanks very much!

yes the pivot_*, are nice new additions to the 'verse.
I much prefer them to the old gather and spread

1 Like

Yes! Once I was able to use them, they are substantially more intuitive!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.