0

I am dealing with a time series of balance sheet of thousands of listed companies from 1990-2018. In some years I only have annual balance sheet while in other years I have 5 balance sheets, including 1 annual and 4 quarterly balance sheets. I am trying to use all information available. The date of balance sheet is always on xxxx-01-01/xxxx-03-31/xxxx-06-30/xxxx-09-30/xxxx-12-31. I select code number, date, long-term liability and short-term liability. I want to first calculate sum of long-term and short-term liability as a new column and do a linear spine interpolation of the new column by code number for each month. The date is in the form of month year.

code       date type           cl        ll       
 1   1990-12-31    A     56280000         0        
 1   1991-12-31    A     77230000         0        
 1   1992-12-31    A    195893200         0        
 1   1993-01-01    A            0         0        
 1   1994-06-30    A            0         0        
 1   1994-12-31    A            0         0        
 1   1996-12-31    A            0         0        
 2   1991-12-31    A    374334527   3500000   
 2   1992-12-31    A    688472115  19820785  
 2   1993-12-31    A   1135584690  70268722  
 2   1994-12-31    A   1442120726  85175588  
 2   1995-06-30    A   1571620470         0

I know how to do it when time interval is constant using splinefun and na.approx. But I have no idea how to deal with non-constant time intervals. Thank you!

YellowRiver
  • 65
  • 1
  • 7

1 Answers1

0

I just got the desired result. The idea is from a similar question https://stackoverflow.com/a/31383995/10714457 . One thing worth to notice is month column is in character form. I need as.numeric(month) to convert them to numeric first. 

 DF$month <- format(as.Date(DF$date), "%m")
 DF$year <- format(as.Date(DF$date), "%Y")
 res <- setDT(DF)[, .SD[match(1:12, as.numeric(month))], by = .(year, code)]
 cols <- c("ll", "cl", "ncl")
 Interpolation <- res[, (cols) := 
 lapply(.SD, na.approx, na.rm = FALSE), .SDcols = cols]
YellowRiver
  • 65
  • 1
  • 7