# Elegant way to rearrange year/date table into time series/values

I often have data in the form of

YEAR    JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1950    -1.022  -1.146  -1.289  -1.058  -1.419  -1.36   -1.334  -1.05   -0.578  -0.395  -1.151  -1.248
1951    -1.068  -1.196  -1.208  -0.437  -0.273  0.48    0.747   0.858   0.776   0.75    0.729   0.466
1952    0.406   0.131   0.086   0.262   -0.267  -0.634  -0.231  -0.156  0.362   0.309   -0.34   -0.124
1953    0.024   0.379   0.263   0.712   0.84    0.241   0.416   0.253   0.524   0.092   0.049   0.314
-0.093  -0.312
and so on ...


I want to convert this into time/value tuples of the type that can be easily plotted using DateListPlot. I have written some very hackish code that does the job, but it feels messy and unsatisfactory. Does anyone know of an elegant 'Mathematica' way to do this?

-
Where do you have that data? In a text file? –  belisarius Jan 30 '14 at 1:26
Yes. The original data is here - esrl.noaa.gov/psd/enso/mei/table.html I copy/paste it and get rid or the garbage at top. Make CSV in Excel and load into Mathematica. But it's just an example. I have many others, usually financial data. –  Ian Schumacher Jan 30 '14 at 1:36

Here's what I came up with...

data = {{"YEAR" , "JAN", "FEB", "MAR", "APR", "MAY" , "JUN" , "JUL",
"AUG", "SEP" , "OCT", "NOV" , "DEC"},
{1950, -1.022, -1.146, -1.289, -1.058, -1.419, -1.36, -1.334, -1.05,
-0.578, -0.395, -1.151, -1.248},
{1951, -1.068, -1.196, -1.208, -0.437, -0.273, 0.48, 0.747, 0.858,
0.776, 0.75, 0.729, 0.466},
{1952, 0.406, 0.131, 0.086, 0.262, -0.267, -0.634, -0.231, -0.156,
0.362, 0.309, -0.34, -0.124},
{1953, 0.024 , 0.379, 0.263, 0.712, 0.84, 0.241, 0.416, 0.253,
0.524, 0.092, 0.049, 0.314}};


Since the data is arranged per month, we can build the dates as such...

Thread[{#[[1]], Range[12]}] & /@ Rest@data


Now, we can Transpose in the data and plot away...

Transpose[{Thread[{#[[1]], Range[12]}], Rest@#}] & /@ Rest@data

DateListPlot[%, Joined->True]


You can also plot as one series by using Flatten at level 1.

DateListPlot[
Rest@data, 1], Joined -> True]


UPDATE

Just saw where the actual data is located which can be imported:

data = Cases[
Import["http://www.esrl.noaa.gov/psd/enso/mei/table.html",
"Table"], {_?NumberQ, __}];


-
That looks pretty good. I was thinking about 'Thread', but I'm not experienced with it and my first attempts didn't work. –  Ian Schumacher Jan 30 '14 at 1:40
Great! Love the addition of the import from scratch. Thanks. –  Ian Schumacher Jan 30 '14 at 1:45

I'll assume your data is on file (named datas.txt) as you suggested in your comment. Then:

data = ReadList["datas.txt", Word, RecordLists -> True]


Gives

{{"YEAR", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG",
"SEP", "OCT", "NOV", "DEC"}, {"1950", "-1.022", "-1.146", "-1.289",
"-1.058", "-1.419", "-1.36", "-1.334", "-1.05", "-0.578", "-0.395",
"-1.151", "-1.248"}, {"1951", "-1.068", "-1.196", "-1.208",
"-0.437", "-0.273", "0.48", "0.747", "0.858", "0.776", "0.75",
"0.729", "0.466"}, {"1952", "0.406", "0.131", "0.086", "0.262",
"-0.267", "-0.634", "-0.231", "-0.156", "0.362", "0.309", "-0.34",
"-0.124"}, {"1953", "0.024", "0.379", "0.263", "0.712", "0.84",
"0.241", "0.416", "0.253", "0.524", "0.092", "0.049", "0.314"}}


Now, very crude, but I had to approach this differently from kale's elegant method:

   pl = Table[{StringJoin[data[[1, #]], " ", data[[k, 1]]],
ToExpression[data[[k, #]]]} & /@ Range[2, 13], {k, 2, Length@data}]


Finally,

DateListPlot[pl, Joined -> True, PlotStyle -> Thick]


-
+1 for the ReadList function, which I wasn't aware of. –  Ian Schumacher Jan 30 '14 at 18:07
@IanSchumacher. Thanks. Also, note that for very large data, ReadList is much faster than Import –  RunnyKine Jan 30 '14 at 18:09