title: Filling in missing (blanks) in a data table, per category - backwards and forwards
body: <p>I am working with a large data set of <em>billing records for my clinical practice over 11 years</em>. Quite a few of the rows are missing the referring physician. However, using some rules I can quite easily fill them in but do not know how to implement it in data.table under R. I know that there are things such as <code>na.locf</code> in the zoo package and self rolling join in the data.table package. The examples that I have seen are too simplistic and do not help me.</p>
  
  <p>Here is some fictitious data to orient you (as a dput ASCII text representation)</p>
  
  <pre><code>    structure(list(patient.first.name = structure(c(1L, 1L, 1L, 1L, 
  1L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L), .Label = c("John", "Kathy", 
  "Timothy"), class = "factor"), patient.last.name = structure(c(3L, 
  3L, 3L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L), .Label = c("Jones", 
  "Martinez", "Squeal"), class = "factor"), medical.record.nr = c(4563455, 
  4563455, 4563455, 4563455, 4563455, 2663775, 2663775, 2663775, 
  2663775, 2663775, 3330956, 3330956, 3330956, 3330956), date.of.service = c(39087, 
  39112, 39112, 39130, 39228, 39234, 39244, 39244, 39262, 39360, 
  39184, 39194, 39198, 39216), procedure.code = c(44750, 38995, 
  40125, 44720, 44729, 44750, 38995, 40125, 44720, 44729, 44750, 
  44729, 44729, 44729), diagnosis.code.1 = c(456.87, 456.87, 456.87, 
  456.87, 456.87, 521.37, 521.37, 521.37, 521.37, 356.36, 456.87, 
  456.87, 456.87, 456.87), diagnosis.code.2 = c(413, 413, 413, 
  413, 413, 532.23, NA, NA, NA, NA, NA, NA, NA, NA), referring.doctor.first = structure(c(1L, 
  1L, 1L, 1L, 1L, 2L, 2L, 2L, NA, NA, NA, 1L, 1L, NA), .Label = c("Abe", 
  "Mark"), class = "factor"), referring.doctor.last = structure(c(1L, 
  1L, 1L, 1L, 1L, 2L, 2L, 2L, NA, NA, NA, 1L, 1L, NA), .Label = c("Newstead", 
  "Wydell"), class = "factor"), referring.docotor.zip = c(15209, 
  15209, 15209, 15209, 15209, 15222, 15222, 15222, NA, NA, NA, 
  15209, 15209, NA), some.other.stuff = structure(c(1L, 1L, 1L, 
  NA, 3L, NA, NA, 4L, NA, 6L, NA, 2L, 5L, NA), .Label = c("alkjkdkdio", 
  "cheerios", "ddddd", "dddddd", "dogs", "lkjljkkkkk"), class = "factor")), .Names = c("patient.first.name", 
  "patient.last.name", "medical.record.nr", "date.of.service", 
  "procedure.code", "diagnosis.code.1", "diagnosis.code.2", "referring.doctor.first", 
  "referring.doctor.last", "referring.docotor.zip", "some.other.stuff"
  ), row.names = c(NA, 14L), class = "data.frame")
  </code></pre>
  
  <p>The obvious solution is to use some sort of last observation carried forward (LOCF) algorithm on referring.doctor.last and referring.doctor.first. However, it must stop when it gets to a new patient. In other words the LOCF must only be applied to one patient who is identified by the combination of patient.first.name, patient.last.name, medical.record.nr. Also note how some patients are missing the referring doctor on their very first visit so that means that some observations have to be carried <strong>backwards</strong>. To complicate matters some patients change primary care physicians and so there may be one referring doctor earlier on and another one later on. The alogorithm therefore needs to be aware of the date order of the rows with missing values. </p>
  
  <p>In zoo <code>na.locf</code> I do not see an easy way to group the LOCF per patient. The rolling join examples that I have seen, would not work here becasuse I cannot simply take out the rows with the missing referring.doctor information since I would then loose date.of.service and procedure.code etcetera. I would love your help in learning how R can fill in my missing data.</p>