Math Help - MathCAD: how to read matrix into a time series

Hello

I'm trying to read a file which contains data for every day of the year for several years

The file is in CSV format (hurray!) and can be easily read by MathCAD but the resultant table is in the format:

Year, Day of Month, Jan, Feb, Mar, Apr, May, Jun, July, Aug, Sep, Oct, Nov, Dec

1955,1,25,74,7,55,101,137,143,198,159,112,28,29
1955,2,26,56,12,58,100,126,126,200,194,125,46,56
1955,3,21,53,19,84,93,137,136,180,166,109,82,73
1955,4,8,50,25,100,110,135,142,171,165,98,128,61
1955,5,9,38,30,118,111,137,146,162,162,100,116,62
1955,6,16,32,14,93,101,165,157,166,147,112,116,86
1955,7,20,60,6,88,103,145,184,143,161,108,122,108
1955,8,18,67,14,108,111,126,204,134,164,136,118,40
1955,9,33,59,17,96,124,107,181,124,157,148,113,59
1955,10,52,24,19,104,104,87,187,158,135,149,111,66
1955,11,10,4,28,120,73,103,181,172,149,111,113,29
1955,12,-10,-3,18,113,95,117,201,194,123,111,88,15
1955,13,-25,-4,20,115,105,130,209,170,133,129,73,43
1955,14,-14,6,46,80,71,139,206,171,110,134,75,64
1955,15,-4,11,50,64,72,159,196,178,117,130,65,82
1955,16,23,13,64,62,72,156,197,191,117,37,22,83
1955,17,-23,-16,34,75,62,138,203,188,126,48,27,54
1955,18,-11,-18,21,76,56,120,182,184,142,45,25,13
1955,19,-12,-13,21,86,61,114,172,206,125,72,44,-10
1955,20,-3,-33,7,96,75,140,163,184,136,104,61,12
1955,21,29,-27,18,71,63,152,175,204,140,82,59,16
1955,22,53,-19,22,84,87,154,175,221,158,71,72,20
1955,23,43,-17,57,93,109,162,190,221,156,89,74,46
1955,24,39,-6,89,87,123,160,182,214,128,94,71,61
1955,25,41,0,95,79,118,149,171,199,152,108,29,75
1955,26,57,-11,84,106,109,145,166,183,123,109,37,79
1955,27,57,-23,51,113,113,158,192,179,116,47,51,92
1955,28,67,-23,33,126,109,147,192,171,125,43,71,101
1955,29,88,-999,24,123,119,146,168,179,126,32,45,65
1955,30,97,-999,22,119,125,148,182,175,146,39,20,38
1955,31,79,-999,28,-999,130,-999,180,188,-999,18,-999,56
1956,1,72,-68,80,65,110,146,162,134,96,122,58,45
1956,2,52,-65,102,71,108,141,151,145,127,120,74,84
1956,3,35,-54,86,75,113,121,150,143,132,108,91,103
1956,4,51,-15,70,63,127,119,162,127,136,91,84,93
1956,5,13,26,56,76,122,119,166,119,149,84,92,104
1956,6,-1,63,64,48,130,113,155,128,150,75,68,105
1956,7,9,42,52,67,130,103,174,133,143,88,64,81


What I'd like to be able to do is read the data once and reformat it so that I have a single column matrix which contains the data in consecutive order from Jan 1 to Dec 31 for each year, missing out the -999 data.

I'm a little bone-headed about how to program this in MathCad. Can someone help?

Originally Posted by JohnA

Hello

I'm trying to read a file which contains data for every day of the year for several years

The file is in CSV format (hurray!) and can be easily read by MathCAD but the resultant table is in the format:

Year, Day of Month, Jan, Feb, Mar, Apr, May, Jun, July, Aug, Sep, Oct, Nov, Dec

1955,1,25,74,7,55,101,137,143,198,159,112,28,29
1955,2,26,56,12,58,100,126,126,200,194,125,46,56
1955,3,21,53,19,84,93,137,136,180,166,109,82,73
1955,4,8,50,25,100,110,135,142,171,165,98,128,61
1955,5,9,38,30,118,111,137,146,162,162,100,116,62
1955,6,16,32,14,93,101,165,157,166,147,112,116,86
1955,7,20,60,6,88,103,145,184,143,161,108,122,108
1955,8,18,67,14,108,111,126,204,134,164,136,118,40
1955,9,33,59,17,96,124,107,181,124,157,148,113,59
1955,10,52,24,19,104,104,87,187,158,135,149,111,66
1955,11,10,4,28,120,73,103,181,172,149,111,113,29
1955,12,-10,-3,18,113,95,117,201,194,123,111,88,15
1955,13,-25,-4,20,115,105,130,209,170,133,129,73,43
1955,14,-14,6,46,80,71,139,206,171,110,134,75,64
1955,15,-4,11,50,64,72,159,196,178,117,130,65,82
1955,16,23,13,64,62,72,156,197,191,117,37,22,83
1955,17,-23,-16,34,75,62,138,203,188,126,48,27,54
1955,18,-11,-18,21,76,56,120,182,184,142,45,25,13
1955,19,-12,-13,21,86,61,114,172,206,125,72,44,-10
1955,20,-3,-33,7,96,75,140,163,184,136,104,61,12
1955,21,29,-27,18,71,63,152,175,204,140,82,59,16
1955,22,53,-19,22,84,87,154,175,221,158,71,72,20
1955,23,43,-17,57,93,109,162,190,221,156,89,74,46
1955,24,39,-6,89,87,123,160,182,214,128,94,71,61
1955,25,41,0,95,79,118,149,171,199,152,108,29,75
1955,26,57,-11,84,106,109,145,166,183,123,109,37,79
1955,27,57,-23,51,113,113,158,192,179,116,47,51,92
1955,28,67,-23,33,126,109,147,192,171,125,43,71,101
1955,29,88,-999,24,123,119,146,168,179,126,32,45,65
1955,30,97,-999,22,119,125,148,182,175,146,39,20,38
1955,31,79,-999,28,-999,130,-999,180,188,-999,18,-999,56
1956,1,72,-68,80,65,110,146,162,134,96,122,58,45
1956,2,52,-65,102,71,108,141,151,145,127,120,74,84
1956,3,35,-54,86,75,113,121,150,143,132,108,91,103
1956,4,51,-15,70,63,127,119,162,127,136,91,84,93
1956,5,13,26,56,76,122,119,166,119,149,84,92,104
1956,6,-1,63,64,48,130,113,155,128,150,75,68,105
1956,7,9,42,52,67,130,103,174,133,143,88,64,81


What I'd like to be able to do is read the data once and reformat it so that I have a single column matrix which contains the data in consecutive order from Jan 1 to Dec 31 for each year, missing out the -999 data.

I'm a little bone-headed about how to program this in MathCad. Can someone help?

I can't tell you how to do this as I don't use MathCad, but I can tell you that you cannot just omit the -999's, they are missing data indicators and to drop them will leave your data uninterpretable unless you replace them with some other missing data indicator.

CB

In this case it can, because the -999 represent days that do not exist like February 30th and 31st for example.

It could be a two-step operation: first decompose the data into a column, then parse through the column to delete the -999s