6
$\begingroup$

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

$\endgroup$

4 Answers 4

4
$\begingroup$

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

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

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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