R/Combined_Data.R

In canarionyc/loanroll: Routines for Parsing and Doing Analysis on Loan Data Files from Fannie Mae

aggregate.Combined_Data <- function(x, ...) {

  out <- x[, j=list(
    "LCount"=.N,
    "Orig.Amt.M"=sum(ORIG_AMT)/1e6,

    "Active.LCount"=sum(is.na(ZB_DTE)),
    "Active.UPB.M"=sum(ifelse(is.na(ZB_DTE), last_upb,0)/1e6),

    "First_Modification.LCount"=sum(ifelse(is.na(FMOD_DTE),0,1)),
    "First_Modification.UPB.M"=sum(FMOD_UPB, na.rm = TRUE)/1e6,
    "First_Modification.UPB.Pct"=sum(FMOD_UPB, na.rm = TRUE) / sum(ORIG_AMT),

    # "SMOD.LCount"=sum(ifelse(is.na(SMOD_DTE),0,1)),
    # "SMOD.UPB.M"=sum(SMOD_UPB, na.rm = TRUE)/1e6,
    # "SMOD.UPB.Pct"=sum(SMOD_UPB, na.rm = TRUE) / sum(ORIG_AMT),


    "D60.LCount"=sum(ifelse(is.na(D60_DTE),0,1)),
    "D60.UPB.M"=sum(D60_UPB, na.rm = TRUE)/1e6,
    "D60.UPB.Pct"=sum(D60_UPB, na.rm = TRUE) / sum(ORIG_AMT),

    "D90.LCount"=sum(ifelse(is.na(D90_DTE),0,1)),
    "D90.UPB.M"=sum(D90_UPB, na.rm = TRUE)/1e6,
    "D90.UPB.Pct"=sum(D90_UPB, na.rm = TRUE) / sum(ORIG_AMT),

    "D150.LCount"=sum(ifelse(is.na(D150_DTE),0,1)),
    "D150.UPB.M"=sum(D150_UPB, na.rm = TRUE)/1e6,
    "D150.UPB.Pct"=sum(D150_UPB, na.rm = TRUE) / sum(ORIG_AMT),


    "D180.LCount"=sum(ifelse(is.na(D180_DTE),0,1)),
    "D180.UPB.M"=sum(D180_UPB, na.rm = TRUE)/1e6,
     "D180.UPB.Pct"=sum(D180_UPB, na.rm = TRUE) / sum(ORIG_AMT),

    "D180_or_Credit_Event.LCount"=sum(ifelse(is.na(D180CE_DTE),0,1)),
    "D180_or_Credit_Event.UPB.M"=sum(D180CE_UPB, na.rm = TRUE)/1e6,
    "D180_or_Credit_Event.UPB.Pct"=sum(D180CE_UPB, na.rm = TRUE) / sum(ORIG_AMT),

    "Disposed.LCount"=sum(ifelse(Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),1,0)),

    "Third_Party_Sale.LCount"=sum(ifelse(Zero.Bal.Code =="02" & !is.na(DISP_DT), 1,0)),
    "Short_Sale.LCount"=sum(ifelse(Zero.Bal.Code =="03" & !is.na(DISP_DT), 1,0)),
    "Repurchased.LCount"=sum(ifelse(Zero.Bal.Code =="06", 1,0)),
    "REO.LCount"=sum(ifelse(Zero.Bal.Code =="09" & !is.na(DISP_DT), 1,0)),
    "Non_Performing_Note_Sale.LCount"=sum(ifelse(Zero.Bal.Code =="15" & !is.na(DISP_DT), 1,0)),
    "Reperforming_Loan_Sale.LCount"=sum(ifelse(Zero.Bal.Code =="16" , 1,0)),

    "Default_Amt.M"=sum(ifelse(Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),last_upb,0))/1e6,

    #    "Delinq_Int.Pct"=sum(INT_COST)/sum(last_upb),
    "Foreclosure_Cost.Pct"=sum(FCC_COST, na.rm = TRUE)/sum(last_upb),
    "Property_Preservation_Cost.Pct"=sum(PP_COST, na.rm = TRUE)/sum(last_upb),
    "Asset_Recovery_Cost.Pct"=sum(AR_COST, na.rm = TRUE)/sum(last_upb),
    "Tax_Cost.Pct"=sum(TAX_COST, na.rm = TRUE)/sum(last_upb),
    "Miscellaneous_Cost.Pct"=sum(IE_COST, na.rm = TRUE)/sum(last_upb),
    "Total_Liquidation_Expenses.Pct"=sum(Tot_Liq_Ex, na.rm = TRUE)/sum(last_upb),
    "Total_Cost.Pct"=sum(Total_Cost, na.rm = TRUE)/sum(last_upb),
    "Net_Sale_Proc.Pct"=sum(NS_PROCS, na.rm = TRUE)/sum(last_upb),
    "Credit_Enhancement_Proc.Pct"=sum(CE_PROCS, na.rm = TRUE)/sum(last_upb),
    "Repurchase_Made_Whole_Proc.Pct"=sum(RMW_PROCS, na.rm = TRUE)/sum(last_upb),
    "Other_Proc.Pct"=sum(O_PROCS, na.rm = TRUE)/sum(last_upb),
    "Total_Proceeds.Pct"=sum(-Tot_Procs, na.rm = TRUE)/sum(last_upb),
    "Net_Loss.M"=sum(NET_LOSS, na.rm = TRUE)/1e6,
    "Severity"=sum(NET_LOSS, na.rm = TRUE)/sum(last_upb)

  ),by=c("OrigYr")]

  # out[, c("MOD.UPB.Pct","D60.UPB.Pct","D90.UPB.Pct", "D180.UPB.Pct","D180_or_Credit_Event.UPB.Pct"):=list(
  #   MOD.UPB.M/Orig.Amt.M, D60.UPB.M/Orig.Amt.M,D90.UPB.M/Orig.Amt.M,
  #   D180.UPB.M/Orig.Amt.M,D180_or_Credit_Event.UPB.M/Orig.Amt.M
  # )]
  setorderv(out, c("OrigYr"), order = -1L)
  #   setcolorder(out,
  #     c("OrigYr",         "LCount",         "Orig.Amt.M",
  #       "MOD.LCount",     "MOD.UPB.M", "MOD.UPB.Pct",
  #       "D60.LCount",     "D60.UPB.M", "D60.UPB.Pct",
  #       "D90.LCount",     "D90.UPB.M"   , "D90.UPB.Pct"  ,
  # #      "D180.LCount" ,   "D180.UPB.M", "D180.UPB.Pct",
  #       "D180_or_Credit_Event.LCount",  "D180_or_Credit_Event.UPB.M"  ,"D180_or_Credit_Event.UPB.Pct")
  #   )
#   if(verbose) print(out)
  return(out)
}


# # Warning: DISP_DT is often NA
# Combined_Data.summary <- function(Combined_Data, xvar="OrigYr", by.var="FicoBkt") {
#   out <- rollup(Combined_Data,
#     j=.(
#       "Orig.Amt.M"=sum(ORIG_AMT)/1e6,
#       "Defaulted.UPB.M"=sum(ifelse(Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT), last_upb, 0))/1e6,
#       "Net.Loss.M"=sum(ifelse(Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT), NET_LOSS, 0))/1e6
#     ),
#     by=c(xvar,by.var), id=TRUE)[, c("Default.Rate","Net.Loss.Rate", "Severity"):=list(
#       100*Defaulted.UPB.M/Orig.Amt.M,
#       100*Net.Loss.M/Orig.Amt.M,
#       100*Net.Loss.M/Defaulted.UPB.M
#     )]
#   if(isOn(verbose)) {
#     print(summary(out))
#   }
#   return(out)
# }
#
#
# Disposed_Summary <- function(Combined_Data) {
#
#   out <- Combined_Data[,.(
#     "NumLoans"=.N,
#
#     "DelinqIntPct"=100*sum(INT_COST)/sum(last_upb),
#     "FcCostPct"=100*sum(FCC_COST, na.rm = TRUE)/sum(last_upb),
#     "PPCostPct"=100*sum(PP_COST, na.rm = TRUE)/sum(last_upb),
#     "ARCostPct"=100*sum(AR_COST, na.rm = TRUE)/sum(last_upb),
#     "TaxCostPct"=100*sum(TAX_COST, na.rm = TRUE)/sum(last_upb),
#     "IECostPct"=100*sum(IE_COST, na.rm = TRUE)/sum(last_upb),
#     "TotLiqExPct"=100*sum(Tot_Liq_Ex)/sum(last_upb),
#     "TotalCostPct"=100*sum(Total_Cost)/sum(last_upb),
#     "NSProcPct"=100*sum(NS_PROCS, na.rm = TRUE)/sum(last_upb),
#     "CEProcPct"=100*sum(CE_PROCS, na.rm = TRUE)/sum(last_upb),
#     "RMWProcPct"=100*sum(RMW_PROCS, na.rm = TRUE)/sum(last_upb),
#     "OProcPct"=100*sum(O_PROCS, na.rm = TRUE)/sum(last_upb),
#     "TotalProceedsPct"=100*sum(-Tot_Procs)/sum(last_upb),
#     "NetLossM"=sum(NET_LOSS)/1e6,
#     "Severity"=100*sum(NET_LOSS)/sum(last_upb)
#   ), keyby=c("OrigYr")]
#   return(out)
# }

# fwrite(Disposed_Summary, file = file.path(Sys.getenv("OUTDIR"),"FNMA","Disposed_Summary_JudicialStates.csv"))

if(getRversion() >= "3.6.0") {
  .S3method("aggregate", "Combined_Data")
}