Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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

share|improve this question

4 Answers 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

share|improve this answer
    
+1 for showing neat end results1 –  rasher Feb 11 at 10:49
    
@rasher thank you for kindness...nice use of DateList...always learn something here –  ubpdqn Feb 11 at 10:51
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]
share|improve this answer
    
Good idea with this -DateList["00:00"], isn't it? :) +1 for handling the Tuples and MapThread usage. –  Kuba Feb 11 at 10:15
    
@Kuba: yes, I hated the ugliness of my original...+1 back for inspiration! –  rasher Feb 11 at 10:16
    
@rasher nice and compact +1 –  ubpdqn Feb 11 at 10:21
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.}}
share|improve this answer
    
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 Feb 11 at 8:26
    
@newbie sorry for my mistake, fixed now. –  Kuba Feb 11 at 8:42
    
@Kuba use of DateList (and removing date offset) by you and rasher...much nicer than my ham fisted manual approach: +1 –  ubpdqn Feb 11 at 10:48
    
@ubpdqn Thanks, but I suspect there is something even more clean. I've failed to find it :/ –  Kuba Feb 11 at 10:58

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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