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

Improve this question
$\endgroup$
6
  • $\begingroup$ Can you link to an example file? $\endgroup$
    – J. M.'s eventual burnout ♦
    Jul 23, 2016 at 14:24
  • $\begingroup$ Use DateList, e.g., DateList["2010/12/24"] returns {2010, 12, 24, 0, 0, 0.} $\endgroup$
    – Bob Hanlon
    Jul 23, 2016 at 14:46
  • $\begingroup$ Can do but how do I link a file to this conversation/post ? $\endgroup$
    – serge gainsbourg
    Jul 23, 2016 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, 2016 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, 2016 at 15:08

3 Answers 3

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

Improve this answer
$\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

Improve this answer
$\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}}
Improve this answer
$\endgroup$

Your Answer

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

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