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

Question

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?

kale · Accepted Answer

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[
 Flatten[Transpose[{Thread[{#[[1]], Range[12]}], Rest@#}] & /@ 
 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, __}];

RunnyKine · Answer

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]

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

2 Answers

Add your own answers!

Ask a Question