# How to do DateDifference on table items containing dates

I have a Table with data in form {{date}, value}:

data= {{{2012, 6, 1}, 16}, {{2012, 6, 8}, 14.24}, {{2012, 6, 15}, 13.7},
{{2012, 6, 22}, 12.31}, {{2012, 6, 29}, 11.5}, {{2012, 7, 6}, 10.08},
{{2012, 7, 13}, 9.18}, {{2012, 7, 20}, 8.65}, {{2012, 7, 27}, 7.8},
{{2012, 8, 3}, 7.51}, {{2012, 8, 10}, 7.31}, {{2012, 8, 17}, 6.92},
{{2012, 8, 24}, 6.57}, {{2012, 8, 31}, 6.1}, {{2012, 9, 7}, 5.67},
{{2012, 9, 14}, 5.54}, {{2012, 9, 21}, 5.17}, {{2012, 9, 28}, 4.83},
{{2012, 10, 5}, 4.62}, {{2012, 10, 9}, 4.53}}


How can I know the difference (in days) between each date and the starting date {2012, 6, 1} and associate this new value to each row of the Table?

-

My proposal:

{##, data[[1, 1]] ~DayCount~ #} & @@@ data

{{{2012, 6, 1}, 16, 0}, {{2012, 6, 8}, 14.24, 7}, ..., {{2012, 10, 9}, 4.53, 130}}

-

I think those who find this kind problem challenging enough to ask for s solution on this site are likely to be open to a tutorial answer giving a step-by-step exposition.

The overall plan is to develop a function that will take one item from the table, compute the date difference for that item, and insert it into the item. With such a function, any data table of the kind given in the question can be dealt with by mapping it over the table.

After each step, a test will be performed to validate what was developed thus far. The test results will indicate how to proceed.

Extract the item date from the table item and compute its difference from the starting date.

insertDateDifference[date_, item_] :=
Module[{itemDate, diff},
itemDate = item[[1]];
diff = DateDifference[date, itemDate]]


test

insertDateDifference[{2012, 6, 1}, {{2012, 6, 8}, 14.24}]

Quantity[7, "Days"]


Wanted a number, not a Quanttiy, so need to fix the last line of code.

insertDateDifference[date_, item_] :=
Module[{itemDate, diff},
itemDate = item[[1]];
diff = DateDifference[date, itemDate] // QuantityMagnitude]


test

insertDateDifference[{2012, 6, 1}, {{2012, 6, 8}, 14.24}]

7


Good. Now on to the insertion. The OP didn't specify where the date difference should appear in the output, so I will use Insert, which will make it easy to change the insertion position. For now I put the date difference right after the item date.

insertDateDifference[date_, item_] :=
Module[{itemDate, diff},
itemDate = item[[1]];
diff = DateDifference[date, itemDate] // QuantityMagnitude;
Insert[item, diff, 2]]


test

insertDateDifference[{2012, 6, 1}, {{2012, 6, 8}, 14.24}]

{{2012, 6, 8}, 7, 14.24}


Seems to work. Time to run it over the OP's sample data.

insertDateDifference[data[[1, 1]], #] & /@ data

{{{2012, 6, 1}, 0, 16}, {{2012, 6, 8}, 7, 14.24}, {{2012, 6, 15}, 14, 13.7},
...
{{2012, 9, 28}, 119, 4.83}, {{2012, 10, 5}, 126, 4.62}, {{2012, 10, 9}, 130, 4.53}}


At this point, being confident that I had solved the problem, I would probably rewrite the function in concise form.

insertDateDifference[date_, item_] :=
Insert[item, DateDifference[date, item[[1]]] // QuantityMagnitude, 2]

-
I apologize for my beginner level, hope not to be inappropriate. Thanks for the kind and useful explanation, it seems to works also without specifying "QuantityMagnitude". – Elisa Aug 26 '14 at 16:02
+1 for a kind hand-holding reply. @Elisa we all started as beginners. :-) People usually don't mind answering clearly written questions which have short, easy answers unless the volume becomes excessive, and sometimes you'll even get tutorial answers like this one. However you will do well to learn as much as you can on your own. This site itself is a great resource; read lots of Q&A's. The documentation is quite extensive; use the F1 key to bring up help for a selected function or operator. For a different perspective see Leonid's free e-book: mathprogramming-intro.org – Mr.Wizard Aug 26 '14 at 19:34
thanks for the advice, I'll try to improve! :) – Elisa Aug 26 '14 at 21:57
@Elisa. I am using V10. Earlier versions of Mathematica didn't require QuantityMagnitude to get a numeric result. – m_goldberg Aug 27 '14 at 1:56
DateDifference[First@First@data, #] & /@ (First /@ data)
Append[#, #2] & @@@ Thread@{data, %}

{0, 7, 14, 21, 28, 35, 42, 49, 56, 63, 70, 77, 84, 91, 98, 105, 112, 119, 126, 130}

{{{2012, 6, 1}, 16, 0}, {{2012, 6, 8}, 14.24, 7}, {{2012, 6, 15}, 13.7, 14},
{{2012, 6, 22}, 12.31, 21}, {{2012, 6, 29}, 11.5, 28}, {{2012, 7, 6}, 10.08, 35},
{{2012, 7, 13}, 9.18, 42}, {{2012, 7, 20}, 8.65, 49}, {{2012, 7, 27}, 7.8, 56},
{{2012, 8, 3}, 7.51, 63}, {{2012, 8, 10}, 7.31, 70}, {{2012, 8, 17}, 6.92, 77},
{{2012, 8, 24}, 6.57, 84}, {{2012, 8, 31}, 6.1, 91}, {{2012, 9, 7}, 5.67, 98},
{{2012, 9, 14}, 5.54, 105}, {{2012, 9, 21}, 5.17, 12}, {{2012, 9, 28}, 4.83, 119},
{{2012, 10, 5}, 4.62, 126}, {{2012, 10, 9}, 4.53, 130}}

-
data /. {date_: {_, _, _}, v_} :> {date, v, DateDifference[{2012, 6, 1}, date]}

{{{2012, 6, 1}, 16, Quantity[0, "Days"]}, {{2012, 6, 8}, 14.24, Quantity[7, "Days"]},...


If you are using V10 you need to use QuantityMagnitude to get the number of days as a number. This goes for all answers using DateDifference.

data /. {date_: {_, _, _}, v_} :> {date, v, QuantityMagnitude@DateDifference[{2012, 6, 1}, date]}


{{{2012, 6, 1}, 16, 0}, {{2012, 6, 8}, 14.24, 7}, {{2012, 6, 15}, 13.7, 14},...

Mr.Wizard used DayCount in his answer, which is not based on rules, and that is I think better than DateDifference. New version:

data /. {date_: {_, _, _}, v_} :> {date, v, DayCount[data[[1, 1]], date]}

-
 {## & @@ #, DateDifference[data[[1, 1]], First@#]} & /@ data
(*  {{{2012, 6, 1}, 16, 0}, {{2012, 6, 8}, 14.24, 7}, {{2012, 6, 15}, 13.7, 14},
{{2012, 6, 22}, 12.31, 21}, {{2012, 6, 29}, 11.5, 28}, {{2012, 7, 6}, 10.08, 35},
{{2012, 7, 13}, 9.18,  42}, {{2012, 7, 20}, 8.65, 49}, {{2012, 7, 27}, 7.8,  56},
{{2012, 8, 3}, 7.51, 63}, {{2012, 8, 10}, 7.31, 70}, {{2012, 8, 17}, 6.92, 77},
{{2012, 8, 24}, 6.57, 84}, {{2012, 8, 31}, 6.1, 91}, {{2012, 9, 7}, 5.67,  98},
{{2012, 9, 14}, 5.54, 105}, {{2012, 9, 21}, 5.17, 112}, {{2012, 9, 28}, 4.83, 119},
{{2012, 10, 5}, 4.62, 126}, {{2012, 10, 9}, 4.53, 130}}*)

-