1
$\begingroup$

I am hoping to get some guidance as to how to tackle this problem. I have the following sample dataset with the dates in YYYYMMDD format:

data={{{20120802}, 193.9}, {{20120912}, 493.9}, {{20130412}, 33.9},{{20130502}, 193.9},
 {{20130802}, 193.9}, {{20130822}, 193.9}, {{20131002}, 193.9}, {{20131022}, 193.9}};

The "Y" values themselves don't matter for this question since I am looking at frequency. I am wondering how I would be able to calculate the frequency that such data points occur relative to the time period. I want to divide it so when I graph it in a line plot, it is divided into 4 quarters (3 months each) over the span of several years (in the example above it is 2 years) and for each quarter it includes all the points that occurred within that date range.

For visualization sake with the data above, the line plot that I want to make would have an x-axis with the tickers: 2012Q1, 2012Q2, 2012Q3, 2012Q4, 2013Q1, 2013Q2, 2013Q3, 2013Q4 and the data points would only be located on those tickers. So for points {{20130412},33.9},{{20130502},193.9}, this would be a "2" on the y-axis and have the x-coordinate of 2013Q2.

If any has any pointers on how to tackle this I would really appreciate it.

$\endgroup$
2
$\begingroup$

Update

Show zero values and some other improvements

 data = 
   {{{20120802}, 193.9}, {{20120912}, 493.9}, {{20130412}, 33.9},
    {{20130502}, 193.9}, {{20130802}, 193.9}, {{20130822}, 193.9},
    {{20131002}, 193.9}, {{20131022}, 193.9}};

Convert dates

dat = MapAt[DateList@*ToString@*First, data, {All, 1}]

{{{2012, 8, 2, 0, 0, 0.}, 193.9}, {{2012, 9, 12, 0, 0, 0.}, 493.9}...}

All months with default value zero - must start with year or quarter

amo = Transpose[{#, Array[0 &, {Length@#}]}] &[DateRange[{2012}, {2014}, "Month"]]

{{{2012, 1, 1}, 0}, {{2012, 2, 1}, 0} ... {{2014, 1, 1}, 0}}

DateListPlot[
 TimeSeriesAggregate[Join[dat, amo], {"Quarter", Left}, Count[#, _?Positive] &],
 DateTicksFormat -> {"Year", "/", "QuarterNameShort"},
 Filling -> Bottom,
 GridLines -> Automatic,
 InterpolationOrder -> 0,
 PlotRange -> {{{2012, 4, 1}, {2013, 12, 1}}, Automatic},
 Mesh -> Full]

enter image description here

Original answer

To get an accurate aggregation we must start at {20120101}:

 data = {{{20120101}, Missing[]}, {{20120802}, 193.9}, {{20120912}, 
   493.9}, {{20130412}, 33.9}, {{20130502}, 193.9}, {{20130802}, 
   193.9}, {{20130822}, 193.9}, {{20131002}, 193.9}, {{20131022}, 193.9}};

Then we must transform the dates in a Mathematica-like form:

dates = 
  ToExpression[{StringTake[#, 4], StringTake[#, {5, 6}], 
      StringTake[#, -2]}] & /@ (ToString /@ Flatten@data[[All, 1]]);

Now we use TimeseriesAggregate to count the frequency per quarter

DateListPlot[
 TimeSeriesAggregate[Transpose[{dates, data[[All, 2]]}], "Quarter", Length],
 DateTicksFormat -> {"Year", "/", "QuarterNameShort"},
 GridLines -> Automatic,
 PlotRange -> {{{2012, 8, 1}, {2013, 12, 31}}, Automatic},
 Mesh -> Full]

enter image description here

And here are the monthly figures

DateListPlot[
 TimeSeriesAggregate[Transpose[{dates, data[[All, 2]]}], "Month", Length],
 DateTicksFormat -> {"Year", "/", "Month"},
 GridLines -> Automatic,
 PlotRange -> {{{2012, 8, 1}, {2013, 10, 31}}, Automatic},
 Mesh -> Full]

enter image description here

$\endgroup$
  • $\begingroup$ Thanks eldo! Really helpful. Thanks for taking the time to write this out. $\endgroup$ – jeff Aug 2 '17 at 14:22
  • $\begingroup$ Hi eldo, just one last quick question. Do you have a recommendation of how to format the graph so when there isn't a data input for a period e,g, 2013Q2, it goes straight to 0 instead of connecting with the next non-zero datapoint? Thanks. $\endgroup$ – jeff Aug 2 '17 at 16:40
  • $\begingroup$ Please wait, will respond in some hours - and thanks for acceptance $\endgroup$ – eldo Aug 2 '17 at 17:14
  • $\begingroup$ Sounds good, thanks! $\endgroup$ – jeff Aug 2 '17 at 17:49
  • $\begingroup$ See updated answer $\endgroup$ – eldo Aug 2 '17 at 20:56

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.