matrix problem

[Eric yang <yang_eric9@yahoo.com>]

Dear members,

 

I have the matrix

> mat1
               A      B       C      D      E     F
07/01/04 141.057 143.37 143.605 142.08 142.50 142.1
08/01/04 141.895 143.37 143.268 142.51 142.50 142.1
09/01/04 142.565 143.37 143.901 143.97 142.50 142.1
10/01/04 140.660 152.38 143.231 143.23 148.98 146.7
11/01/04 150.825 152.38 147.788 146.53 148.98 153.2
12/01/04 158.957 152.38 151.811 150.39 148.98 153.2
01/01/05 161.793 152.38 161.534 158.84 159.98 153.2
02/01/05 157.299 152.38 158.350 155.10 159.98 154.6
03/01/05 150.782 152.38 153.205 147.96 159.98 154.6
04/01/05 145.834 142.44 143.746 143.95 142.25 145.3
05/01/05 142.636 142.44 142.388 141.86 142.25 146.6
06/01/05 140.078 142.44 141.903 140.18 142.25 146.6
07/01/05 140.145 142.44 142.055 140.12 142.25 146.6
08/01/05 140.964 142.44 142.260 140.53 142.25 146.6
09/01/05 141.619 142.44 142.605 141.92 142.25 146.6
10/01/05 141.990 151.50 141.092 140.92 148.73 154.1
11/01/05 149.894 151.50 144.805 144.07 148.73 154.1
12/01/05 154.933 151.50 147.035 147.71 148.73 154.1
01/01/06 160.060 151.50 159.407 156.10 159.73 155.1
02/01/06 155.692 151.50 156.132 152.52 159.73 155.1

The data can be either categorized as monthly, 2-monthly (jan-feb, march-april, etc) 3-monthly(jan-march, april-june, etc), 6-monthly (april-sept, nov-march) or annual.

 

I'm interested in the type of data in mat1 (i.e. monthly, 2-monthly etc) so I have

 

>mat2
         A B C D E F
07/01/04 1 3 1 1 3 3
08/01/04 1 3 1 1 3 3
09/01/04 1 3 1 1 3 3
10/01/04 1 6 1 1 3 1
11/01/04 1 6 1 1 3 3
12/01/04 1 6 1 1 3 3
01/01/05 1 6 1 1 3 3
02/01/05 1 6 1 1 3 2
03/01/05 1 6 1 1 3 2
04/01/05 1 6 1 1 6 1
05/01/05 1 6 1 1 6 5
06/01/05 1 6 1 1 6 5
07/01/05 1 6 1 1 6 5
08/01/05 1 6 1 1 6 5
09/01/05 1 6 1 1 6 5
10/01/05 1 5 1 1 3 3
11/01/05 1 5 1 1 3 3
12/01/05 1 5 1 1 3 3
01/01/06 1 5 1 1 2 2
02/01/06 1 5 1 1 2 2

 

I would like at least 4 measurements of the same category of data with monthly considered the best, then
2-monthly, 3-monthly etc, the rest of the data is removed.

I've written a small function to do this

 

mat3 <- apply(mat2, 1, function(x) {
 y <- x[!is.na(x)]
 if(length(y)>0) {
  if(sum(y==1)>=4){
   y[y>1] <- NA
  }
  else if(sum(y<=3)>=4){
   y[y>3] <- NA
  }
  else if(sum(y<=6)>=4){
   y[y>6] <- NA
  }
  else if(sum(y<=12)>=4){
   y[y>12] <- NA
  }
  else y <- NA
  x[!is.na(x)] <- y
 }
 names(x) <- LETTERS[1:6]
 return(x)
})

 

 

Which gives
> t(mat3)
         A  B C D  E F
07/01/04 1  3 1 1  3 3
08/01/04 1  3 1 1  3 3
09/01/04 1  3 1 1  3 3
10/01/04 1 NA 1 1 NA 1
11/01/04 1 NA 1 1  3 3
12/01/04 1 NA 1 1  3 3
01/01/05 1 NA 1 1  3 3
02/01/05 1 NA 1 1  3 2
03/01/05 1 NA 1 1  3 2
04/01/05 1 NA 1 1 NA 1
05/01/05 1  6 1 1  6 5
06/01/05 1  6 1 1  6 5
07/01/05 1  6 1 1  6 5
08/01/05 1  6 1 1  6 5
09/01/05 1  6 1 1  6 5
10/01/05 1 NA 1 1  3 3
11/01/05 1 NA 1 1  3 3
12/01/05 1 NA 1 1  3 3
01/01/06 1 NA 1 1  2 2
02/01/06 1 NA 1 1  2 2

 

I want to make all the data in each row consistant, so for row 1 I would like to calculate 3-monthly data based on the quarter 07, 08, 09 giving

                A      B        C        D      E        F
07/01/04 141.8390 143.37 143.5913 142.8533 142.50 142.1000

 

 

 

The final matrix should give

 

> mat.final
                A      B        C        D      E        F
07/01/04 141.8390 143.37 143.5913 142.8533 142.50 142.1000
08/01/04 141.8390 143.37 143.5913 142.8533 142.50 142.1000
09/01/04 141.8390 143.37 143.5913 142.8533 142.50 142.1000
10/01/04 140.6600     NA 143.2310 143.2300     NA 146.7000
11/01/04 157.1917     NA 153.7110 151.9200 148.98 151.0333
12/01/04 157.1917     NA 153.7110 151.9200 148.98 151.0333
01/01/05 157.1917     NA 153.7110 151.9200 159.98 153.2000
02/01/05 156.6247     NA 157.6963 153.9667 159.98 154.1333
03/01/05 156.6247     NA 157.6963 153.9667 159.98 154.1333
04/01/05 145.8340     NA 143.7460 143.9500     NA 145.3000
05/01/05 141.8793 142.44 142.4928 141.4267 142.25 146.3833
06/01/05 141.8793 142.44 142.4928 141.4267 142.25 146.3833
07/01/05 141.8793 142.44 142.4928 141.4267 142.25 146.3833
08/01/05 141.8793 142.44 142.4928 141.4267 142.25 146.3833
09/01/05 141.8793 142.44 142.4928 141.4267 142.25 146.3833
10/01/05 148.9390     NA 144.3107 144.2333 148.73 154.1000
11/01/05 148.9390     NA 144.3107 144.2333 148.73 154.1000
12/01/05 148.9390     NA 144.3107 144.2333 148.73 154.1000
01/01/06 157.8760     NA 157.7695 154.3100 159.73 155.1000
02/01/06 157.8760     NA 157.7695 154.3100 159.73 155.1000

 

Can anyone suggest a way of doing this?
Thanks in advance for any help.

 

Regards
Eric

---

 Yahoo! Mail
 
Use Photomail to share photos without annoying attachments.