# How to Merge Date and Time?

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

-

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]


-
+1 for showing neat end results1 – ciao Feb 11 '14 at 10:49
@rasher thank you for kindness...nice use of DateList...always learn something here – ubpdqn Feb 11 '14 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]

-
Good idea with this -DateList["00:00"], isn't it? :) +1 for handling the Tuples and MapThread usage. – Kuba Feb 11 '14 at 10:15
@Kuba: yes, I hated the ugliness of my original...+1 back for inspiration! – ciao Feb 11 '14 at 10:16
@rasher nice and compact +1 – ubpdqn Feb 11 '14 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.}}

-
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 '14 at 8:26
@newbie sorry for my mistake, fixed now. – Kuba Feb 11 '14 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 '14 at 10:48
@ubpdqn Thanks, but I suspect there is something even more clean. I've failed to find it :/ – Kuba Feb 11 '14 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]


-