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
3 Answers
(data = {{Date, Price}, {"16/05/2007", 3655}, {"16/06/2007",
3435}, {"16/07/2007", 3528}}) // Grid
(data2 = MapAt[DateList[{#, {"Day", "Month", "Year"}}] &,
data, {2 ;;, 1}]) // Grid
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"]]
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}}
DateList["2010/12/24"]
returns{2010, 12, 24, 0, 0, 0.}
$\endgroup$