1
$\begingroup$

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?

Improve this question
$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

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]]

enter image description here

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}}

enter image description here

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"]

enter image description here

The missing values were replaced by the next available previous values

Finally:

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

enter image description here

Improve this answer
$\endgroup$
1
  • $\begingroup$ Hi In your example the dateobject is replaced by a dateObject without the timestamp (hours, minites, seconds). When I copy your code in my own notebook, the result of d3[[All, 1 ;; 4]] /. {DateObject[a_, ], v} :> {DateObject[a], {v}} is still a Dateobject with the timestamp. In that case, the code will replace the zero values from hours/minutes/seconds in missing[]. Do you have a suggestion how to deal with this issue? $\endgroup$
    – Michiel van Mens
    Jul 17, 2017 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.