Convert from many sub-tables to a single tidy dataframe

Question

I have data in a bad format that I want to make tidy using an R script. I lack the skills to convert it into the format I want, so I look for an answer that (a) outlines a method, or (b) hands me the entire script.

The definition of tidy here is that the output has the following columns:

• swap_id a unique identifier.
• leg either pay or rec.
• cashflow the amount that is recieved
• date the date the cashflow arrives
• type whether the cashflow is of INTEREST of FINAL_EX.

The variable leg takes on two different values: pay and rec. Currently leg=pay is in one excel sheet and leg=rec is in a second excel sheet. The entire excel workbook hence contains two sheets.

I have uploaded the entire excel file to https://we.tl/V9C5iVMH4w

Here is a sample of the content of sheet "rec":

    id1                                 id2         
    date        cf      type            date        cf        type  
    2017-04-04  42961   INTEREST        2015-04-07  33953   INTEREST    
    2017-07-04  43438   INTEREST        2016-04-04  203161  INTEREST    
    2017-10-04  43915   INTEREST        2017-04-04  203161  INTEREST    
    2018-01-04  43915   INTEREST        2018-04-04  203161  INTEREST    
    2018-04-04  42961   INTEREST        2019-04-04  203161  INTEREST    
    2018-07-04  43438   INTEREST        2020-04-06  203161  INTEREST    
    2018-10-04  43915   INTEREST        2021-04-06  203161  INTEREST    
    2019-01-04  43915   INTEREST        2022-04-04  203161  INTEREST    
    2019-04-04  42961   INTEREST        2023-04-04  203161  INTEREST    
    2019-07-04  43438   INTEREST        2023-04-04  5016330 FINAL_EX    
    2019-10-04  43915   INTEREST                    
    2020-01-07  43915   INTEREST                    
    2020-04-06  43438   INTEREST                    
    2020-07-06  43438   INTEREST                    
    2020-10-05  43915   INTEREST                    
    2021-01-04  43915   INTEREST                    
    2021-04-06  42961   INTEREST                    
    2021-07-05  43438   INTEREST                    
    2021-10-04  43915   INTEREST                    
    2021-10-04  2988563 FINAL_EX

Here is a sample of the content of sheet "pay":

    id1                                 id2     
    date        cf      type            date        cf      type
    2017-04-04  5250    INTEREST        2015-04-07  30938   INTEREST
    2017-07-04  5308    INTEREST        2016-04-04  30938   INTEREST
    2017-10-04  5367    INTEREST        2017-04-04  30938   INTEREST
    2018-01-04  5367    INTEREST        2018-04-04  30938   INTEREST
    2018-04-04  5250    INTEREST        2019-04-04  30938   INTEREST
    2018-07-04  5308    INTEREST        2020-04-06  30938   INTEREST
    2018-10-04  5367    INTEREST        2021-04-06  30938   INTEREST
    2019-01-04  5367    INTEREST        2022-04-04  30938   INTEREST
    2019-04-04  5250    INTEREST        2023-04-04  30938   INTEREST
    2019-07-04  5308    INTEREST        2023-04-04  540000  FINAL_EX
    2019-10-04  5367    INTEREST                
    2020-01-06  5367    INTEREST                
    2020-04-06  5308    INTEREST                
    2020-07-06  5308    INTEREST                
    2020-10-05  5367    INTEREST                
    2021-01-04  5367    INTEREST                
    2021-04-06  5250    INTEREST                
    2021-07-05  5308    INTEREST                
    2021-10-04  5367    INTEREST                
    2021-10-04  315000  FINAL_EX                

In other words, each id gets itws own little table. An every table is separated using an empty column. This is good for eye balling the data, but horrible for working with it.

Here is how I want the output to be structured after the transformation.

    swap_id leg date        cf      type
    id1     pay 2017-04-04  5250    INTEREST
    id1     pay 2017-07-04  5308    INTEREST
    id1     pay 2017-10-04  5367    INTEREST
    id1     pay 2018-01-04  5367    INTEREST
    id1     pay 2018-04-04  5250    INTEREST
    id1     pay 2018-07-04  5308    INTEREST
    id1     pay 2018-10-04  5367    INTEREST
    id1     pay 2019-01-04  5367    INTEREST
    id1     pay 2019-04-04  5250    INTEREST
    id1     pay 2019-07-04  5308    INTEREST
    id1     pay 2019-10-04  5367    INTEREST
    id1     pay 2020-01-06  5367    INTEREST
    id1     pay 2020-04-06  5308    INTEREST
    id1     pay 2020-07-06  5308    INTEREST
    id1     pay 2020-10-05  5367    INTEREST
    id1     pay 2021-01-04  5367    INTEREST
    id1     pay 2021-04-06  5250    INTEREST
    id1     pay 2021-07-05  5308    INTEREST
    id1     pay 2021-10-04  5367    INTEREST
    id1     pay 2021-10-04  315000  FINAL_EX
    id1     rec 2017-04-04  42961   INTEREST
    id1     rec 2017-07-04  43438   INTEREST
    id1     rec 2017-10-04  43915   INTEREST
    id1     rec 2018-01-04  43915   INTEREST
    id1     rec 2018-04-04  42961   INTEREST
    id1     rec 2018-07-04  43438   INTEREST
    id1     rec 2018-10-04  43915   INTEREST
    id1     rec 2019-01-04  43915   INTEREST
    id1     rec 2019-04-04  42961   INTEREST
    id1     rec 2019-07-04  43438   INTEREST
    id1     rec 2019-10-04  43915   INTEREST
    id1     rec 2020-01-07  43915   INTEREST
    id1     rec 2020-04-06  43438   INTEREST
    id1     rec 2020-07-06  43438   INTEREST
    id1     rec 2020-10-05  43915   INTEREST
    id1     rec 2021-01-04  43915   INTEREST
    id1     rec 2021-04-06  42961   INTEREST
    id1     rec 2021-07-05  43438   INTEREST
    id1     rec 2021-10-04  43915   INTEREST
    id1     rec 2021-10-04  2988563 FINAL_EX
    id2 …   …   …   … and so on... 

Answer 1

## read file 
# create path 
file4 <- "GetSwapFlowdb2.xlsm"
filepath3 <- paste(folder1, file4, sep="")
# read. sheet 2 is recieve leg, and 3 is pay leg
db_rec <- read_excel(filepath3, col_names = FALSE, sheet=2) %>% as.data.frame()
db_pay <- read_excel(filepath3, col_names = FALSE, sheet=3) %>% as.data.frame() 

## save swap IDs 
(transid_list <- db_rec[1,] %>% t() %>% na.omit() %>% as.vector()) #%>% as.numeric()
transid_list_pay <- db_pay[1,] %>% t() %>% na.omit() %>% as.vector()
stopifnot(transid_list_pay == transid_list)
rm(transid_list_pay)
(nr_id <- length(transid_list))

## make function that cleans a single swap
db_clean_oneswap <- function(aswap){
  # input...
  # aswap: a single swap in the form fo data.frame
  # output...
  # aswpap: the cleaned version

  # format cf 
  aswap$cf <- as.numeric(aswap$cf)
  # format dates from excel format to yyyy-mm-dd
  aswap$date <- as.POSIXct(as.Date(as.numeric(aswap$date), origin="1899-12-30"))
  aswap$date <- as.Date(aswap$date)
  # create t 
  aswap$t <- time_length(difftime(aswap$date, date_valuation), "years")
  # save the swap to our list 
  return(aswap)
}


## make function so we can save swap cashflows to a list 
db_create_swaplist <- function(data, transids){
  # input... 
  # data: the dataframe which comes from excel 
  # transids: a list character vector of swap trans IDs
  # output... 
  # a list with each element containing a dataframe of the swap 

  # create empty list
  swap_list <- NULL

  # for loop to create a list containing each swap. 
  for (id in transids){
    # create df we will "eat" from 
    data <- db_rec
    # select three cols, save to a swap
    one_swap <- data[-1, c(1,2,3)]
    # remove those cols we saved 
    data <- data[-c(1,2,3)]
    # rename cols 
    names(one_swap) <- c("date", "cf", "type")
    # remove a row, remove na 
    one_swap <- one_swap[-1, ]
    one_swap <- na.omit(one_swap)

    # clean it
    one_swap <- db_clean_oneswap(one_swap)
    # save the swap to our list 
    swap_list[[id]] <- one_swap
  }
  return(swap_list)
}

## create a list for rec and pay
swaps_rec <- db_create_swaplist(data = db_rec, transids = transid_list)
swaps_pay <- db_create_swaplist(data = db_pay, transids = transid_list)

## ways of accessing the lists
swaps_rec[[1]]
transid_list[1]
swaps_rec[[ transid_list[2] ]]
swaps_rec[[as.character(transid)]]