1
$\begingroup$

I have dataset such as:

1.1.2013    12
2.1.2013    0
3.1.2013    0
4.1.2013    0
7.1.2013    2
8.1.2013    0
9.1.2013    0
10.1.2013   0
11.1.2013   0
14.1.2013   0
15.1.2013   49
16.1.2013   0
17.1.2013   0
18.1.2013   0
21.1.2013   0
22.1.2013   0
23.1.2013   1

I need to get a list of numbers which will indicate number of days between two non-zero observations.

(in reality, the dataset is much longer; it is in Excel in two columns)

enter image description here

As can be seen, the data are imported also with "00:00:00" which is not in Excel file. So it is NOT imported only as a date, but also with hours, minutes and seconds (always "00:00:00"). I do not know why. I want to import only what I have in Excel: dates (from the first column) and values (from the second column) and NOT the time (which is not stated in my Excel file).

Moreover, some dates are not reported in the Excel file (say Tuesdays), but I want such a code that will count also these "not reported" days.

$\endgroup$
  • $\begingroup$ Humm, It seems you are not interested in the number of days as you are not counting what appear to be weekends. You are interested in weekdays only or the number of zero observations until the next none zero (inclusive). $\endgroup$ – Edmund Apr 15 '15 at 0:15
  • $\begingroup$ @Edmund, sorry for not specifying that immediately. I am interested in number of days, but some dates are not reported (but these should be counted too). $\endgroup$ – Johny Apr 15 '15 at 6:55
0
$\begingroup$

I'm not sure if you are looking for weekdays or days so the below will work for either if you change the "Weekday" part to All (notice no quotation marks). With the import already giving you DateObjects in position 1 of the pairs then the following will work.

d0 = {{DateObject[{2013, 1, 1}], 12}, 
      {DateObject[{2013, 1, 2}], 0}, 
      {DateObject[{2013, 1, 3}], 0}, 
      {DateObject[{2013, 1, 4}], 0}, 
      {DateObject[{2013, 1, 7}], 2}, 
      {DateObject[{2013, 1, 8}], 0}, 
      {DateObject[{2013, 1, 9}], 0}, 
      {DateObject[{2013, 1, 10}], 0}, 
      {DateObject[{2013, 1, 11}], 0}, 
      {DateObject[{2013, 1, 14}], 0}, 
      {DateObject[{2013, 1, 15}], 49}, 
      {DateObject[{2013, 1, 16}], 0}, 
      {DateObject[{2013, 1, 17}], 0}, 
      {DateObject[{2013, 1, 18}], 0}, 
      {DateObject[{2013, 1, 21}], 0}, 
      {DateObject[{2013, 1, 22}], 0}, 
      {DateObject[{2013, 1, 23}], 1}};

(* Version 10.1 *)
MovingMap[DayCount[#Values[[1]], #Values[[2]], All] &, 
 Select[d0, #[[2]] != 0 &][[All, 1]], 
 {1, Left}]
(* {6, 8, 8} *)

(* Version 10.0.2 *)
MovingMap[DayCount[#[[1]], #[[2]], All] &, 
 Select[d0, #[[2]] != 0 &][[All, 1]], 
 {2, Left}]
(* {6, 8, 8} *)
  1. The Select gets all of the pairs with non-zero value in position 2 and takes the dates in position 1.
  2. MovingMapthen applies DayCount to the pairs using a moving window length of <<1 for version 10.1 or 2 for version 10.0.2>>. The "Weekday" can be changed to All if that is what is really needed.

Hope this helps.

Updated for breaking change in MovingMap window spec between 10.1 and 10.0.2

$\endgroup$
  • $\begingroup$ it returns me that: The data TemporalData...is not a structurally valid TemporalData object. $\endgroup$ – Johny Apr 15 '15 at 6:49
  • $\begingroup$ @Johny There is a breaking change in the window spec of MovingMap between verson 10.1 and 10.0.2. I've updated the answer for this. $\endgroup$ – Edmund Apr 15 '15 at 10:05
  • $\begingroup$ still the same... But, please, look at the imported data as posted in the question. It is imported along with "hours", "minutes" and "seconds" (even if they are not stated in the Excel file) - always "00:00:00". Could that cause the problem? @Edmund $\endgroup$ – Johny Apr 15 '15 at 12:41
  • $\begingroup$ I removed the TimeObject and left only the DateObject in the imported data and it works! Thanks, @Edmund! $\endgroup$ – Johny Apr 15 '15 at 21:35
2
$\begingroup$

In the example you gave it would seem that the dates are not needed:

v = {12, 0, 0, 0, 2, 0, 0, 0, 0, 0, 49, 0, 0, 0, 0, 0, 1};

Join @@ Differences @ Position[v, _?Positive]
{4, 6, 6}

A more efficient but undocumented formulation:

Differences @ SparseArray[v]["AdjacencyLists"]
{4, 6, 6}
$\endgroup$
  • $\begingroup$ Not true. Sample data are missing 05 & 06 Jan, and 12 & 13 Jan. $\endgroup$ – dwa Apr 15 '15 at 5:56
  • $\begingroup$ @Mr.Wizard, please, see last edit. $\endgroup$ – Johny Apr 15 '15 at 6:56
2
$\begingroup$
d0 = {{"1.1.2013", 12}, {"2.1.2013", 0}, {"3.1.2013", 0},
      {"4.1.2013", 0}, {"7.1.2013", 2}, {"8.1.2013", 0}, 
      {"9.1.2013", 0}, {"10.1.2013", 0}, {"11.1.2013", 0}, 
      {"14.1.2013", 0}, {"15.1.2013", 49}, {"16.1.2013", 0}, 
      {"17.1.2013", 0}, {"18.1.2013", 0}, {"21.1.2013", 0}, 
      {"22.1.2013", 0}, {"23.1.2013", 1}};

Delete the rows where the second column is zero, take the first column, and transform the date strings into AbsoluteTimes:

d1 = AbsoluteTime[{#, {"Day", "Month", "Year"}}]& /@DeleteCases[d0, {_, 0}][[All, 1]];

Take consecutive date pairs and use DayCount to count the difference for each pair:

DayCount @@@ Partition[d1, 2, 1]
(* {6, 8, 8} *)

or, to count the business days,

DayCount[##, "BusinessDay"] & @@@ Partition[ d1, 2, 1]
(* {4, 6, 5} *)

Alternatively,

Developer`PartitionMap[DayCount[##, "BusinessDay"] & @@ # &, d1, 2, 1]
(* {4, 6, 5} *)

Update: Creating random data with a structure similar to the one pictured in OP's update:

SeedRandom[1]
randomdates=Sort@RandomChoice[DateRange["20.1.2013","20.1.2014","Day"],30];

dd0={DateObject@#, RandomChoice[{3,1}->{0,1}]}&/@randomdates

enter image description here

dd1 = AbsoluteTime/@DeleteCases[dd0, {_, 0}][[All, 1]]
(* {3573417600, 3575836800, 3577478400, 3578342400, 
    3582662400, 3583699200, 3597004800} *)

DayCount @@@ Partition[dd1, 2, 1]
(* {28, 19, 10, 50, 12, 154} *)

DayCount[##,"BusinessDay"]& @@@ Partition[dd1, 2, 1]
(* {20, 13, 8, 33, 9, 105} *)
$\endgroup$
  • $\begingroup$ The date in first column in Excel file is imported to Mathemtica as DateObject... $\endgroup$ – Johny Apr 14 '15 at 22:55
  • 1
    $\begingroup$ @Johny, I don't have mma version 10. But the functions AbsoluteTime and DayCount should work on DateObjects. $\endgroup$ – kglr Apr 14 '15 at 23:12
  • $\begingroup$ when I want to input "d1", it returns me that argument cannot be interpreted as a date or time input. $\endgroup$ – Johny Apr 14 '15 at 23:14
  • $\begingroup$ The difference is that your data points are only dates. My data points are in Excel also only dates, but when I import that, there is "00:00:00" added for each day. And I also dont get for what does these numbers: (* {28, 19, 10, 50, 12, 154} *) mean. @kguler $\endgroup$ – Johny Apr 15 '15 at 9:12
  • $\begingroup$ It was evaluating for a such a long time and my notebook freezed. Tried the same again and the same happened, @kguler. The method proposed by you works if I generate the data manually such that there is only date and not time (which is what I want), but I dont know how to import the data in order to have in Mathematica ONLY dates and not that Mathematica add to the date values "00:00:00". $\endgroup$ – Johny Apr 15 '15 at 15:58
0
$\begingroup$

Using

dat={{"1.1.2013", 12}, {"2.1.2013", 0}, {"3.1.2013", 0},
      {"4.1.2013", 0}, {"7.1.2013", 2}, {"8.1.2013", 0}, 
      {"9.1.2013", 0}, {"10.1.2013", 0}, {"11.1.2013", 0}, 
      {"14.1.2013", 0}, {"15.1.2013", 49}, {"16.1.2013", 0}, 
      {"17.1.2013", 0}, {"18.1.2013", 0}, {"21.1.2013", 0}, 
      {"22.1.2013", 0}, {"23.1.2013", 1}};

->

 Differences[
 Last@Reap[
           Sow @@@ ({1/86400 AbsoluteTime@
           DateList[{#1, {"Day", "Month", "Year"}}], #2} & @@@ dat), _?Positive,
           Sequence @@ #2 &]]

=> {6, 8, 8}

$\endgroup$
  • $\begingroup$ As in other cases, it returns me that it cannot be interpreted as a date or time input. Please, look at the imported data as posted in the question. It is imported along with "hours", "minutes" and "seconds" (even if they are not stated in the Excel file) - always "00:00:00". Could that cause the problem? $\endgroup$ – Johny Apr 15 '15 at 12:56
  • $\begingroup$ @Johnny this suggests that either your minimum working example is not representative of the data you want to work on or that some of your 'dates' are in uninterpretable or ambiguous format. Without knowing the actual way you have formatted dates this cannot be resolved. $\endgroup$ – ubpdqn Apr 15 '15 at 21:16

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.