How to Merge Date and Time?

Question

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

Answer 1

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]

Plotting:

DateListPlot[ans]

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

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

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

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]

Answer 2

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]

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

Answer 4

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]