How to Merge Date and Time?

6

1

Given the following time series data where row is date and column is time intervals.

   rawdata = {{"Date", "08:00", "08:30", "09:00", "09:30", "10:00", "10:30", 
      "11:00", "11:30", "12:00", "12:30", 
      "13:00"}, {{2011, 1, 4, 0, 0, 0.}, 47., 59., 71., 77., 115., 111., 
      103., 75., 101., 121., 135.}, {{2011, 1, 5, 0, 0, 0.}, 20., 51., 
      33., 68., 97., 82., 101., 78., 104., 119., 
      137.}, {{2011, 1, 6, 0, 0, 0.}, 3., 39., 61., 59., 63., 108., 66., 
      85., 108., 119., 114.}, {{2011, 1, 7, 0, 0, 0.}, 13., 55., 74., 70.,
       105., 95., 71., 82., 86., 113., 124.}}

I can't figure out how to merge date and time together so that my desired output will be

output = {{Date}, {{2011, 1, 4, 8, 0, 0.}, 47.}, {{2011, 1, 4, 8, 30, 0.}, 
  59.}, {{2011, 1, 4, 9, 0, 0.}, 71.}, ..., {{2011, 1, 6, 13, 0, 0.}, 
  124}}

Thank you

newbie

Posted 2014-02-11T07:49:21.337

Reputation: 457

Answers

4

With[{data = #}, 
   MapThread[{#1, #2} &, {(#1 + DateList[#2]*{0, 0, 0, 1, 1, 1}) & @@@
       Tuples[{data[[2 ;;, 1]], Rest[data[[1]]]}], 
     Flatten[data[[2 ;;, 2 ;;]]]}]] &[rawdata]

All those excess characters... Here's the Twitterized version:

Transpose@{(#1 + DateList[#2]*{0, 0, 0, 1, 1, 1}) & @@@\
   Tuples@{#[[2 ;;, 1]], #[[1, 2 ;;]]}, Flatten@#[[2 ;;, 2 ;;]]} &[rawdata]

ciao

Posted 2014-02-11T07:49:21.337

Reputation: 23 752

Good idea with this -DateList["00:00"], isn't it? :) +1 for handling the Tuples and MapThread usage. – Kuba – 2014-02-11T10:15:28.697

@Kuba: yes, I hated the ugliness of my original...+1 back for inspiration! – ciao – 2014-02-11T10:16:44.490

@rasher nice and compact +1 – ubpdqn – 2014-02-11T10:21:44.077

4

Here is an approach using rawdata in the question:

ans = Module[{time, dates, dt, values},
   time = 
    ToExpression /@ (StringSplit[#, ":"] & /@ Rest@First@rawdata);
   dates = rawdata[[2 ;;, 1]];
   values = rawdata[[2 ;;, 2 ;;]];
   dt = Map[Function[x, Join[x[[1 ;; 3]], #, {0}] & /@ time], dates];
   Flatten[MapThread[{#1, #2} &, {dt, values}, 2], 1]];

Checking

Displaying as a table:

Grid[{DateString[#1], #2} & @@@ ans]

enter image description here

Plotting:

DateListPlot[ans]

Headings (as in desired answer) can just be prepended to ans.

enter image description here

Or if the desire is plot by value by time of day (as per Chris Degnen):

DateListPlot[Transpose[ans], Joined -> True]

enter image description here

Or if each row represents a time series that is desired to be plotted a different color:

DateListPlot[Partition[ans, Length@rawdata[[1]] - 1], Joined -> True]

enter image description here

ubpdqn

Posted 2014-02-11T07:49:21.337

Reputation: 53 491

@rasher thank you for kindness...nice use of DateList...always learn something here – ubpdqn – 2014-02-11T10:51:31.427

3

Flatten[
 Table[
   {rawdata[[ i, 1]] + DateList[rawdata[[ 1, j]]] - DateList["0:00"],
    rawdata[[ i, j]]},
   {i, 2, Length[rawdata] - 1},
   {j, 2, Length[rawdata[[ 1]]] - 1}]
 , 1]
 {{{2011,1,4,8,0,0.},47.},<<28>>,{{2011,1,6,12,30,0.},119.}}

Kuba

Posted 2014-02-11T07:49:21.337

Reputation: 129 207

This code does not return the output as I want because it will not consider the time in each column. Thank you for the good example. – newbie – 2014-02-11T08:26:38.520

@newbie sorry for my mistake, fixed now. – Kuba – 2014-02-11T08:42:27.507

@Kuba use of DateList (and removing date offset) by you and rasher...much nicer than my ham fisted manual approach: +1 – ubpdqn – 2014-02-11T10:48:55.057

@ubpdqn Thanks, but I suspect there is something even more clean. I've failed to find it :/ – Kuba – 2014-02-11T10:58:06.420

2

The following method could be made more compact, but it would be less clear to read :-

minutes = Map[Join[{0, 0, 0}, ToExpression@StringSplit[#, ":"], {0}] &,
   Rest@First@rawdata];

records = Rest@rawdata;

merge[rec_] := Module[{date, data, times},
  date = First@rec;
  data = Rest@rec;
  times = ConstantArray[date, Length@data] + minutes;
  Transpose[{times, data}]]

output = Prepend[Flatten[merge /@ records, 1], {rawdata[[1, 1]]}];

DateListPlot[Transpose[Partition[Rest@output, 11]], Joined -> True]

enter image description here

Chris Degnen

Posted 2014-02-11T07:49:21.337

Reputation: 27 033