replace missing values in table with timeseries

Question

I've got my data from a SQL-query like:

 Select[date0 /. 
      SQLDateTime -> DateObject, #[[1]] >  
       DateObject[{2016, 1, 1, 0, 0, 0.`}] &];

A sample of this dataset look likes:

data1 = {{DateObject[{2016, 6, 2, 0, 0, 0.`}, "Instant", "Gregorian", 
    2.`], 447.59, 31.66, 
   18.23}, {DateObject[{2016, 6, 3, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 445.31, 31.46, 
   18.13}, {DateObject[{2016, 6, 6, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 446.44, 31.61, 
   18.11}, {DateObject[{2016, 6, 7, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 451.58, 31.96, 
   18.01}, {DateObject[{2016, 6, 8, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 450.15, 31.75, 
   18.17}, {DateObject[{2016, 6, 9, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 446.1, 31.43, 
   17.9}, {DateObject[{2016, 6, 10, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 435.77, 30.62, 
   17.51}, {DateObject[{2016, 6, 13, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 428.2, 29.91, 
   17.39}, {DateObject[{2016, 6, 14, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 419.08, 29.02, 
   17.17}, {DateObject[{2016, 6, 15, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 419.55, 28.88, 
   16.9}, {DateObject[{2016, 6, 16, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 418.51, 28.44, 
   16.97}, {DateObject[{2016, 6, 17, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 421.77, 28.75, 
   17.17}, {DateObject[{2016, 6, 20, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 436.5, 29.55, 
   17.71}, {DateObject[{2016, 6, 21, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 440.39, 29.52, 
   17.83}, {DateObject[{2016, 6, 22, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 441.25, 29.73, 
   17.7}, {DateObject[{2016, 6, 23, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 449.86, 30.13, 
   18.51}, {DateObject[{2016, 6, 24, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 417.51, 26.75, 
   14.86}, {DateObject[{2016, 6, 27, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 0., 0., 
   0.}, {DateObject[{2016, 6, 28, 0, 0, 0.`}, "Instant", "Gregorian", 
    2.`], 435.88, 27.05, 
   14.9}, {DateObject[{2016, 6, 29, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 435.88, 27.05, 
   14.9}, {DateObject[{2016, 6, 30, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 422.18, 25.95, 
   14.1}, {DateObject[{2016, 7, 1, 0, 0, 0.`}, "Instant", "Gregorian",
     2.`], 0, 25.95, 
   14.1}, {DateObject[{2016, 7, 4, 0, 0, 0.`}, "Instant", "Gregorian",
     2.`], 0., 0., 
   0.}, {DateObject[{2016, 7, 5, 0, 0, 0.`}, "Instant", "Gregorian", 
    2.`], 0., 0., 
   0.}, {DateObject[{2016, 7, 6, 0, 0, 0.`}, "Instant", "Gregorian", 
    2.`], 433.77, 26.66, 
   15.12}, {DateObject[{2016, 7, 7, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 433.77, 26.66, 
   15.12}, {DateObject[{2016, 7, 8, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 433.77, 26.66, 
   15.12}, {DateObject[{2016, 7, 11, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 448.19`, 28.16`, 
   16.07`}, {DateObject[{2016, 7, 12, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 0., 0., 
   0.}, {DateObject[{2016, 7, 13, 0, 0, 0.`}, "Instant", "Gregorian", 
    2.`], 0., 0., 
   0.}, {DateObject[{2016, 7, 14, 0, 0, 0.`}, "Instant", "Gregorian", 
    2.`], 447.51, 0, 
   16.18}, {DateObject[{2016, 7, 15, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 447.51, 28.23, 
   16.18}, {DateObject[{2016, 7, 18, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 448.32`, 28.21`, 
   16.23`}, {DateObject[{2016, 7, 19, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 447.85, 28.42, 
   16.2}, {DateObject[{2016, 7, 20, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 452.18, 28.86, 
   16.42}, {DateObject[{2016, 7, 21, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 451.97, 28.6, 
   16.41}, {DateObject[{2016, 7, 22, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 453.35, 28.48, 
   16.56}, {DateObject[{2016, 7, 25, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 452.84, 28.78, 
   16.4}, {DateObject[{2016, 7, 26, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 454.06, 28.71, 
   16.23}, {DateObject[{2016, 7, 27, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 452.98, 29.03, 
   16.37}, {DateObject[{2016, 7, 28, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 447.82, 29.66, 
   16.24}, {DateObject[{2016, 7, 29, 0, 0, 0.`}, "Instant", 
    "Gregorian", 2.`], 449.83, 29.7, 16.62}};

The example dataset has 4 columns. The real table contains 50 columns. In columns 2,3 and 4, I've to deal with missing values, in this case represented by "0." On Stackexchange I found a nice way to replace this 0-values with the value from the day before.

FoldList[If[#2 == 0, ##] &, data1[[All, 3]]]

This expression works only for one column and without the dateobject column.

is there a way to this for the whole table at once?

Answer 1

First I take a subset of your long example and append 2 columns ("The real table contains 50 columns")

d3 = Append[y]@*Append[x] /@ data1[[16 ;; 25]]

Columns 5 and 6 are not needed for the time being

rest = d3[[All, {-2, -1}]];

The first 4 columns are taken and reformated

d4 = d3[[All, 1 ;; 4]] /. {DateObject[a_, __], v__} :> {DateObject[a], {v}}

Zero values are replaced by Missing[]

d5 = d4 /. {___, 0. | 0, ___} :> {Missing[], Missing[], Missing[]};

(note that I also replace the July 1st missing value)

Now comes the interesting part:

d6 = TemporalData[d5, MissingDataMethod -> {"Interpolation", InterpolationOrder -> 0}]["Path"]

The missing values were replaced by the next available previous values

Finally:

Flatten /@ Transpose[{d6 /. {a_, b_} :> {DateObject@a, b}, rest}]