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.

Improve this question
$\endgroup$
2
  • $\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
    Commented Apr 15, 2015 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
    Commented Apr 15, 2015 at 6:55

4 Answers 4

Reset to default
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

Improve this answer
$\endgroup$
4
  • $\begingroup$ it returns me that: The data TemporalData...is not a structurally valid TemporalData object. $\endgroup$
    – Johny
    Commented Apr 15, 2015 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
    Commented Apr 15, 2015 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
    Commented Apr 15, 2015 at 12:41
  • $\begingroup$ I removed the TimeObject and left only the DateObject in the imported data and it works! Thanks, @Edmund! $\endgroup$
    – Johny
    Commented Apr 15, 2015 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}
Improve this answer
$\endgroup$
2
  • $\begingroup$ Not true. Sample data are missing 05 & 06 Jan, and 12 & 13 Jan. $\endgroup$
    – dwa
    Commented Apr 15, 2015 at 5:56
  • $\begingroup$ @Mr.Wizard, please, see last edit. $\endgroup$
    – Johny
    Commented Apr 15, 2015 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} *)
Improve this answer
$\endgroup$
6
  • $\begingroup$ The date in first column in Excel file is imported to Mathemtica as DateObject... $\endgroup$
    – Johny
    Commented Apr 14, 2015 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
    Commented Apr 14, 2015 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
    Commented Apr 14, 2015 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
    Commented Apr 15, 2015 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
    Commented Apr 15, 2015 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}

Improve this answer
$\endgroup$
2
  • $\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
    Commented Apr 15, 2015 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
    Commented Apr 15, 2015 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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