Finding the location of the last day of the month in a list of dates

Question

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.

Answer 1

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}} *)

Answer 2

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]

Answer 3

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}}

Answer 4

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}