4
$\begingroup$

My question differs from earlier questions about getting the eom dates from a list; I want to get the position of those dates so I can get the corresponding values from other lists. My problem seems to boil down to one about how to use Position where pattern is a list. It's an ordinary list of about 5000 dates.

dates[[1 ;; 5]]
{{1995, 1, 31}, {1995, 2, 1}, {1995, 2, 2}, {1995, 2, 3}, {1995, 2, 6}}

As I said, I know how to get the eom dates (there are 229), but I can't seem to make Position work with a list.

Map[Last, GatherBy[dates, {#[[1]], #[[2]]} &]][[1 ;; 5]]
{{1995, 1, 31}, {1995, 2, 28}, {1995, 3, 31}, {1995, 4, 28}, {1995, 5,31}}

So, here's the kluge. Partition the dates into pairs. Map a function over the list of pairs that returns 1 if the day of the second member is less than the day of the first member, and zero otherwise. Finally, get the position of the members of this list that equal 1.

p = Flatten[Position[If[#[[2,3]]-#[[1, 3]]<0,1,0]&/@ Partition[dates,2,1],1]]
{1, 21, 44, 64, 87, 109, 130, 153, 174, 196, 218, 239,...}

And here is the list of values:

csh[[p]]
{114.141, 115.698, 116.331, 117.328, 119.333, 119.983,...}

It works, but it seems kind of clumsy.

| improve this question | |
$\endgroup$
  • $\begingroup$ So you have a list of dates and corresponding list of vales. Now you want to take values that are corresponding to last days of available months? $\endgroup$ – Kuba♦ Feb 17 '14 at 22:18
  • $\begingroup$ If you want to use Position with your first way of finding out the eom dates, you can probably use Position[dates,Or@@(that expression)]. $\endgroup$ – C. E. Feb 17 '14 at 22:35
  • $\begingroup$ Are these true end of month dates or are you looking for the last available day for each month in your data? For example would {{2001,1,30},{2001,1,31},{2001,2,12}} give {{2},{3}} or {{2}} ? $\endgroup$ – Andy Ross Feb 18 '14 at 14:13
6
$\begingroup$

In general, enumerating explicit patterns for Position works well for list structures without much complexity (and will be faster), but what you really want to do for more complex problems, is to construct a Boolean test that reads in an element of your list and gives you True or False depending on whether it satisfies your condition.

For example, consider the date list:

dates = {{1995, 1, 31}, {1995, 4, 1}, {1995, 7, 2}, {1995, 7, 3}, {1995, 10, 31}};

Suppose we wanted to enumerate patterns to pick out positions of the last day of the month from this list (assume for now that we know it only contains dates from Jan, Apr, Jul and Oct) we could do:

Position[dates, {_, 1, 31} | {_, 4, 30} | {_, 7, 31} | {_, 10, 31}]
(* {{1}, {5}} *)

Here we used our knowledge of the last day for these months and created an appropriate pattern. However this is not a strategy that scales well. You start by writing the patterns out for each month till you hit leap years. Then you add in a check for leap years till you hit century leap years. Depending on how far back in time you want to go, you'll encounter more complexities like missing months, skipped dates, different calendar systems, etc.

Instead, you can construct a lastDayQ function that is "aware" of all the complexities (or most of them). Using Mathematica's date-time functions:

lastDayQ[date_] := DatePlus[date, {1, "Day"}][[3]] == 1

Now you use Position and use a pattern that checks if the list satisfies the "last day of month" criterion:

Position[dates, _?lastDayQ, {1}, Heads -> False]
(* {{1}, {5}} *)
| improve this answer | |
$\endgroup$
  • 2
    $\begingroup$ The lastDayQ function is clever. I tend to avoid DatePlus because, in my experience, it's slow, but it was fast enough with my list of 5000 dates. $\endgroup$ – George Wolfe Feb 17 '14 at 23:31
  • $\begingroup$ Position[dates, _?lastDayQ,{1},Heads->False] works without setting Heads->False, but gives error messages. I'm reading the documentation, but still not completely clear about what Heads does. $\endgroup$ – George Wolfe Feb 17 '14 at 23:52
  • 1
    $\begingroup$ @George Position also looks at the heads, and when it encounters the head List, it will try to ascertain if lastDayQ[List] is True or False. This test is meaningless which is why you get the error messages. To avoid that, we tell Position to not look at the heads. To see what the option does, compare the outputs: {Position[f[f, g], f, Heads -> False], Position[f[f, g], f, Heads -> True]}. Alternately, we could've defined lastDayQ to give False for any input other than a list (you can tighten the pattern further) as: lastDayQ[date_List] := (* stuff *); lastDayQ[_] := False $\endgroup$ – rm -rf♦ Feb 18 '14 at 0:00
  • $\begingroup$ @GeorgeWolfe Could it happen that there are months with records ending at not the last day but e.g. 15? $\endgroup$ – Kuba♦ Feb 19 '14 at 7:29
  • $\begingroup$ @Kuba I'm working with daily financial index prices in the US. If the last day of the month is a Saturday or Sunday (or Holiday), the last value will be for the last trading day of the month. $\endgroup$ – George Wolfe Feb 19 '14 at 13:17
3
$\begingroup$

You can also use the new day functionality, though this may be a bit slow.

dates = {{1995, 1, 31}, {1995, 2, 1}, {1995, 2, 2}, {1995, 2, 3}, {1995, 2, 6}};

Pick[Range[Length[#]], DayMatchQ[#, "EndOfMonth"] & /@ #] &[dates]

(* {1} *)

If nothing else it is a very clean solution and ensures that any odd things like leap years are properly accounted for.

Edit:

Borrowing from @rm-rf we can speed this up about 2x by using Position.

Position[dates, x_ /; DayMatchQ[x, "EndOfMonth"], {1}, Heads -> False]
| improve this answer | |
$\endgroup$
2
$\begingroup$

Starting with a list of dates, a, the position of month-ends is given by e :-

a = {{2003, 1, 31}, {2003, 1, 30}, {2003, 2, 28}};
b = ConstantArray[{0, 0, 1}, Length@a];
c = Take[DateList@#, 3] & /@ (a + b);
d = MapThread[Equal, {a[[All, 2]], c[[All, 2]]}];
e = Position[d, False]

{{1}, {3}}

Extract[a, e]

{{2003, 1, 31}, {2003, 2, 28}}

| improve this answer | |
$\endgroup$
  • $\begingroup$ This procedure adds one day to each date, uses DateList to "fix" the dates if an extra day pushes a date into the following month, compares the month in the before and after lists, and uses Postion[d,False] to identify the month ends. Clever. $\endgroup$ – George Wolfe Feb 18 '14 at 15:43
  • $\begingroup$ I didn't know about ConstantArray, and I didn't know that DateList "corrected" dates. I tested your method and noticed that it requires that the latest date in each month be the actual last day of that month. That isn't always the case with my data, but I learned two things I didn't know from your answer. Thanks. $\endgroup$ – George Wolfe Feb 18 '14 at 15:47
1
$\begingroup$

Is this what you are after?

dates = {{1995, 1, 31}, {1995, 2, 1}, {1995, 2, 2}, {1995, 2, 3}, {1995, 2, 6},
         {1995, 3, 6}}
values = Range[6]

list = Join[dates, {values}\[Transpose], 2]

And, if dates were sorted, it could be:

GatherBy[list, #[[{1, 2}]] &][[;; , -1, -1]]

{1, 5, 6}
| improve this answer | |
$\endgroup$
  • $\begingroup$ Your answer inspired: tr = Transpose[{dates, csh}]; eom = Map[Last, GatherBy[tr, #[[1, {1, 2}]] &]]; which gave me a dated list of end of month values, which works a bit better for me. $\endgroup$ – George Wolfe Feb 18 '14 at 0:32

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.