Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

My proposal:

{##, data[[1, 1]] ~DayCount~ #} & @@@ data
{{{2012, 6, 1}, 16, 0}, {{2012, 6, 8}, 14.24, 7}, ..., {{2012, 10, 9}, 4.53, 130}}
share|improve this answer

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]
share|improve this answer
    
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 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 at 19:34
    
thanks for the advice, I'll try to improve! :) –  Elisa Aug 26 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 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}}
share|improve this answer
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]}
share|improve this answer
 {## & @@ #, 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}}*)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.