r - How to manage a table/matrix to obtain information using conditions

Question

Ive been thinking about what is the best way to solve this, any advise?

The table/matrix X is:

X <- read.table(text = "    a       b       c       d       e
    0       27        0       28         8
    1       14       24       32        33
    0        4       22       25        27 
    0        3        7       26        34
    0       28       33       31        21
    0       16       17       24        18
    1        3       19        0        12
    0        2       23        5        24
    2       17       22       22        10
    0       35       15       17         2", 
                  header = TRUE, stringsAsFactors = FALSE)

or using matlab:

X =[
        0       27        0       28         8
        1       14       24       32        33
        0        4       22       25        27 
        0        3        7       26        34
        0       28       33       31        21
        0       16       17       24        18
        1        3       19        0        12
        0        2       23        5        24
        2       17       22       22        10
        0       35       15       17         2];

How could I obtain a table/matrix A and B which contains:

rows and columns accumulated each 5 values until the maximum value of table X -> in this case: 0-5;0-10;0-15;0-20.. 0-35 (for both rows and columns).

Table A:

The first thing is to select only the positive values of a, and then count the number of times these conditions occur:

1. The entry (1,1) of the table/matrix A would be the number of times the b values are among 0-5 and d values are among 0-5. The entry (2,1) would be the number of times the b values are among 0-5 and d values are among 0-10. The entry (3,1) would be the number of times the b values are among 0-5 and d values are among 0-15... this until the row is 0-35.

2. The entry (1,2) of the table/matrix A would be the number of times the b values are among 0-10 and d values are among 0-5. The entry (2,2) would be the number of times the b values are among 0-10 and d values are among 0-10. The entry (3,2) would be the number of times the b values are among 0-10 and d values are among 0-15... this until the row is 0-35.

We form a 7x7 table/matrix, the results show this:

   A:
          0-5     0-10     0-15     0-20     0-25     0-30     0-35
   0-5     1        1        1        1         1        1        1
   0-10    1        1        1        1         1        1        1
   0-15    1        1        1        1         1        1        1
   0-20    1        1        1        1         1        1        1
   0-25    1        1        1        2         2        2        2
   0-30    1        1        1        2         2        2        2
   0-35    1        1        2        3         3        3        3

Where the columns are the intervals of b values and the rows the intervals for d values

The meaning of the table/matrix A is the number of times the a values are higher than 0 and match different combinations intervals (each 5 values) among column b and d. For example, for the entry (1,1) of result table A is 1 because in the row 7 of table X, there is only 1 number which a>0, b=3 (among 0-5) and d=0 (among 0-5). Other example is the entry (7,4), which is 3 since there three times: a>0, b is among 0-20 and d is among 0-35

The table/matrix B, would be the same table A but comparing columns c and e. B is:

  B:
          0-5     0-10     0-15     0-20     0-25     0-30     0-35
   0-5     1        1        1        1         1        1        1
   0-10    2        2        2        2         2        2        2
   0-15    2        2        2        2         2        2        2
   0-20    2        2        2        2         2        2        3
   0-25    2        2        2        4         4        4        5
   0-30    4        4        4        6         6        7        8
   0-35    4        4        5        7         7        9       10

Answer 1

I wrote this code and it worked, but I would like to improve it in order to solve it in a more efficient way, and avoid it to be too tedious.

library(magrittr)
library(dplyr)

B1 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b< 5) & (d >=0 & d<= 35) ) )

B1=as.data.frame(t(B1))

B2 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b< 10) & (d >=0 & d<= 35) ) )

B2=as.data.frame(t(B2))

B3 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b< 15) & (d >=0 & d<= 35) ) )

B3=as.data.frame(t(B3))

B4 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b< 20) & (d >=0 & d<= 35) ) )

B4=as.data.frame(t(B4))

B5 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b< 25) & (d >=0 & d<= 35) ) )

B5=as.data.frame(t(B5))

B6 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b< 30) & (d >=0 & d<= 35) ) )

B6=as.data.frame(t(B6))

B7 = A %>%
  summarise( i0_5 = sum( (a > 0) & (b>= 0 & b< 35) & (d >=0 & d<  5) ),
             i0_10 = sum( (a > 0) & (b>= 0 & b< 35) & (d >=0 & d< 10) ),
             i0_15 = sum( (a > 0) & (b>= 0 & b<= 35) & (d >=0 & d< 15) ),
             i0_20 = sum( (a > 0) & (b>= 0 & b<= 35) & (d >=0 & d< 20) ),
             i0_25 = sum( (a > 0) & (b>= 0 & b<= 35) & (d >=0 & d< 25) ),
             i0_30 = sum( (a > 0) & (b>= 0 & b<= 35) & (d >=0 & d< 30) ),
             i0_35 = sum( (a > 0) & (b>= 0 & b<= 35) & (d >=0 & d<= 35) ) )

B7=as.data.frame(t(B7))
Em=cbind(B1,B2,B3,B4,B5,B6,B7)
colnames(Em) =c('0-5','0-10','0-15','0-20','0-25','0-30','0-35')

For the second table:

B1 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b< 5) & (d >=0 & d<= 35) ) )

B1=as.data.frame(t(B1))

B2 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b< 10) & (d >=0 & d<= 35) ) )

B2=as.data.frame(t(B2))

B3 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b< 15) & (d >=0 & d<= 35) ) )

B3=as.data.frame(t(B3))

B4 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b< 20) & (d >=0 & d<= 35) ) )

B4=as.data.frame(t(B4))

B5 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b< 25) & (d >=0 & d<= 35) ) )

B5=as.data.frame(t(B5))

B6 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b< 30) & (d >=0 & d<= 35) ) )

B6=as.data.frame(t(B6))

B7 = A %>%
  summarise( i0_5 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d<  5) ),
             i0_10 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d< 10) ),
             i0_15 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d< 15) ),
             i0_20 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d< 20) ),
             i0_25 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d< 25) ),
             i0_30 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d< 30) ),
             i0_35 = sum( (a >= 0) & (b>= 0 & b<= 35) & (d >=0 & d<= 35) ) )

B7=as.data.frame(t(B7))

Em2=cbind(B1,B2,B3,B4,B5,B6,B7)
colnames(Em2) =c('0-5','0-10','0-15','0-20','0-25','0-30','0-35')