1
$\begingroup$

I have two lists of time series data. One of the lists is daily and is formatted like this;

daily={{{2011, 7, 1, 6, 0, 0.}, 284606., 16735.3}, {{2011, 7, 2, 6, 0, 0.}, 
285283., 16669.9}, {{2011, 7, 3, 6, 0, 0.}, 287529., 16445.5}}

And another list which is hourly;

hourly={{{2011, 7, 1, 6, 0, 0.}, {3, 861, "", 2 ""}}, {{2011, 7, 1, 7, 0, 
0.}, {3, 270, "", 2 ""}}, {{2011, 7, 1, 8, 0, 0.}, {3, "", "",2 ""}}}

I would like to merge the two list so the two values in the daily list repeat in the corresponding hourly list.

For the data given the result would look like this;

merged={{{2011, 7, 1, 6, 0, 0.},3, 861, "", 2 "", 284606., 16735.3},   
{{2011, 7, 1, 7, 0,0.}, 3, 270, "", 2 "", 284606., 16735.3},
{{2011, 7, 1, 8, 0, 0.}, 3, "", "",2 "", 284606., 16735.3}}}

I would like to match the two sets by the year-month-day data. The time part of the daily data can be ignored.

I ended up coding a for loop that iterated through both lists to do the merge (after several attempts at doing this in a more elegant way). What I'm looking for is the proper way to do this in Mathematica, not a For loop that I could have done in C.

Any help would be greatly appreciated.

Finding the intersection of two date lists

|improve this question
$\endgroup$
  • $\begingroup$ The structure for hourly seems a little different... is the second element a list unlike for the daily data? In any case, lookup Join and GatherBy (I seem to remember a similar question) $\endgroup$ – rm -rf♦ Jul 5 '13 at 7:58
  • $\begingroup$ is speed an issue? can we assume that there always exists daily data for a given hourly datapoint? If speed is no issue, one could maybe do something like: Function[x, (Append[{#[[1]], Sequence @@ #[[2]]} &@x, Hold@Sequence @@ Select[daily, #[[1, 1 ;; 3]] == x[[1, 1 ;; 3]] &][[1, 2 ;; 3]]])] /@ hourly // ReleaseHold - but there are surely smarter/faster ways $\endgroup$ – Pinguin Dirk Jul 5 '13 at 8:13
  • $\begingroup$ @rm -rf. Joining the sets and using GatherBy to arrange the data by day wasn't really the problem. It was that I had one element for the daily data and 24 for the hourly. I used a for loop to duplicate the daily data 24 times but I thought there must be a better way. $\endgroup$ – Cam Jul 7 '13 at 23:24
2
$\begingroup$

I suppose you want to use whuber's excellent suggestion that you linked to in your question. In which case, this appears to work:

dailyList = {DateList[First[#]], Sequence @@ Rest[#]} & /@ daily;
hourlyList = {DateList[First[#]], Sequence @@ Last[#]} & /@ hourly;
rulesDaily = 
  Dispatch[Flatten[({Take[First[#], 3] -> Rest[#]} & /@ dailyList), 1]];
merged = Map[
  Join[
    #,
    Take[First[#] , 3] /. rulesDaily]
   &, hourlyList]

giving

{{{2011, 7, 1, 6, 0, 0.}, 3, 861, "", 2 "", 284606., 16735.3}, 
 {{2011, 7, 1, 7, 0, 0.}, 3, 270, "", 2 "", 284606., 16735.3}, 
 {{2011, 7, 1, 8, 0, 0.}, 3, "", "", 2 "", 284606., 16735.3}}

which is a bit like yours (I'm a brace short). But don't ask me for an explanation, it looks quite clever.

|improve this answer
$\endgroup$
  • $\begingroup$ @ cormullion Thanks. It does seem to work so I'll give you the tick. Before I do, if anybody could add some explanation that would be great (I didn't press whuber for information at the time and now I regret it). It's a fairly generic problem and it seems like a really clever solution so I think the extra discussion would help many newcomers to Mathematica with similar problems. $\endgroup$ – Cam Jul 8 '13 at 0:25
  • $\begingroup$ @cam Thanks! - but just looking at this code again, it doesn't seem to be doing what I thought it was doing: the Dispatch function seems to be redundant in this case, and it simply generates an ordinary list of rules that does the job just as well (although presumably slower for large datasets). Basically each rule matches a day with its values ({2011, 7, 1} -> {284606., 16735.3}), and for every hour that day's values are found and joined to the hour's values. $\endgroup$ – cormullion Jul 8 '13 at 8:02

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.