0
$\begingroup$

I am new to Mathematica and have v10. I would like to you use the new TimeSeriesAggregate[tsdata, "Quarter", Total] feature to return aggregated descriptive stats. I import a number of columns of returns data using import[,"CSV"] and the dates (first column) are being returned as "2010/12/12". I don't mind keeping this format but need the dates in the default format {yy,mm,dd..} for the TimeSeriesAggregate[[ function to work . COuld somebody tell me how I operate on the datalist to create or modify a column to get from an Excel Date format to a Mathematica date format. Thanks

$\endgroup$
  • $\begingroup$ Can you link to an example file? $\endgroup$ – J. M. will be back soon Jul 23 '16 at 14:24
  • $\begingroup$ Use DateList, e.g., DateList["2010/12/24"] returns {2010, 12, 24, 0, 0, 0.} $\endgroup$ – Bob Hanlon Jul 23 '16 at 14:46
  • $\begingroup$ Can do but how do I link a file to this conversation/post ? $\endgroup$ – serge gainsbourg Jul 23 '16 at 14:46
  • $\begingroup$ Thanks Bob. I can get that to work for the one-off date but it fails against my imported data for a) I have a header line which I would like to retain so I need to suppress that somehow and b) my date format remains unchanged after using DateLIst[] but might that be because if's in a dd/mm/yyyy format (i.e UK rather than US format) ? $\endgroup$ – serge gainsbourg Jul 23 '16 at 14:56
  • $\begingroup$ actually here's some simple data Date,Price 16/05/2007,3655 16/06/2007,3435 16/07/2007,3528 $\endgroup$ – serge gainsbourg Jul 23 '16 at 15:08
2
$\begingroup$
(data = {{Date, Price}, {"16/05/2007", 3655}, {"16/06/2007", 
     3435}, {"16/07/2007", 3528}}) // Grid

enter image description here

(data2 = MapAt[DateList[{#, {"Day", "Month", "Year"}}] &, 
    data, {2 ;;, 1}]) // Grid

enter image description here

$\endgroup$
2
$\begingroup$
rawData = {{"13/12/2010", 10}, {"15/12/2010", 20}, {"17/12/2010", 30},
   {"19/12/2010", 40}, {"21/12/2010", 50}, {"23/12/2010", 60},
   {"13/12/2011", 80}, {"15/12/2011", 70}, {"17/12/2011", 60},
   {"19/12/2011", 40}, {"21/12/2011", 50}, {"23/12/2011", 60},
   {"13/12/2012", 10}, {"15/12/2012", 20}, {"17/12/2012", 30},
   {"19/12/2012", 20}, {"21/12/2012", 10}, {"23/12/2012", 0}};

date = StringCases[rawData[[All, 1]], 
   x : DatePattern[{"Day", "Month", "Year"}] :> DateList[x]];
data = Partition[Riffle[Flatten[date, 1], rawData[[All, 2]]], 2]

DateListPlot[TimeSeriesAggregate[data, "Quarter"]]

enter image description here

$\endgroup$
1
$\begingroup$

if after you remove headers you have:

rawData = {{"13/12/2010", 10}, {"15/12/2010", 20}, {"17/12/2010", 
30}, {"19/12/2010", 40}, {"21/12/2010", 50}, {"23/12/2010", 
60}, {"13/12/2011", 80}, {"15/12/2011", 70}, {"17/12/2011", 
60}, {"19/12/2011", 40}, {"21/12/2011", 50}, {"23/12/2011", 
60}, {"13/12/2012", 10}, {"15/12/2012", 20}, {"17/12/2012", 
30}, {"19/12/2012", 20}, {"21/12/2012", 10}, {"23/12/2012", 0}};

Then applying TimeSeries directly will do the right thing:

In[45]:= ts = TimeSeries[rawData];

In[46]:= ts["Dates"]

Out[46]= {DateObject[{2010, 12, 13}], DateObject[{2010, 12, 15}], 
 DateObject[{2010, 12, 17}], DateObject[{2010, 12, 19}], 
 DateObject[{2010, 12, 21}], DateObject[{2010, 12, 23}], 
 DateObject[{2011, 12, 13}], DateObject[{2011, 12, 15}], 
 DateObject[{2011, 12, 17}], DateObject[{2011, 12, 19}], 
 DateObject[{2011, 12, 21}], DateObject[{2011, 12, 23}], 
 DateObject[{2012, 12, 13}], DateObject[{2012, 12, 15}], 
 DateObject[{2012, 12, 17}], DateObject[{2012, 12, 19}], 
 DateObject[{2012, 12, 21}], DateObject[{2012, 12, 23}]}

TimeSeriesAggregate will work with the resulting TimeSeries object:

In[51]:= TimeSeriesAggregate[ts, Quantity[3, "Days"], Total] // Normal

Out[51]= {{DateObject[{2010, 12, 14}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  30}, {DateObject[{2010, 12, 17}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  30}, {DateObject[{2010, 12, 20}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  90}, {DateObject[{2010, 12, 23}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  60}, {DateObject[{2011, 12, 12}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  80}, {DateObject[{2011, 12, 15}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  70}, {DateObject[{2011, 12, 18}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  100}, {DateObject[{2011, 12, 21}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  50}, {DateObject[{2011, 12, 24}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  60}, {DateObject[{2012, 12, 12}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  10}, {DateObject[{2012, 12, 15}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  20}, {DateObject[{2012, 12, 18}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  50}, {DateObject[{2012, 12, 21}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 
  10}, {DateObject[{2012, 12, 24}, 
   TimeObject[{12, 0, 0.}, TimeZone -> -5.], TimeZone -> -5.], 0}}
$\endgroup$

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.