Home > database >  Transform a matrix (or table) into a table-list?
Transform a matrix (or table) into a table-list?

Time:06-08

I want to transform my dataset into a table-list (I don't know what it's called) but here's an example (obviously the initial dataset is much larger). initial data :

station SP1 SP2 SP3
2 0 1 1
10 0 3 0
34 0 0 0
53 0 3 5
56 6 0 3
57 1 0 0
62 1 8 10

and what I would like :

final table

thank you

CodePudding user response:

Inferring that you don't want 0 rows, this is just a pivot/filter 2-step.

base R and reshape2

longdat <- reshape2::melt(dat, "station", variable.name = "sp", value.name = "number")
longdat
#    station  sp number
# 1        2 SP1      0
# 2       10 SP1      0
# 3       34 SP1      0
# 4       53 SP1      0
# 5       56 SP1      6
# 6       57 SP1      1
# 7       62 SP1      1
# 8        2 SP2      1
# 9       10 SP2      3
# 10      34 SP2      0
# 11      53 SP2      3
# 12      56 SP2      0
# 13      57 SP2      0
# 14      62 SP2      8
# 15       2 SP3      1
# 16      10 SP3      0
# 17      34 SP3      0
# 18      53 SP3      5
# 19      56 SP3      3
# 20      57 SP3      0
# 21      62 SP3     10
subset(longdat, number > 0)
#    station  sp number
# 5       56 SP1      6
# 6       57 SP1      1
# 7       62 SP1      1
# 8        2 SP2      1
# 9       10 SP2      3
# 11      53 SP2      3
# 14      62 SP2      8
# 15       2 SP3      1
# 18      53 SP3      5
# 19      56 SP3      3
# 21      62 SP3     10

dplyr

library(dplyr)
dat %>%
  pivot_longer(-station, names_to = "sp", values_to = "number") %>%
  dplyr::filter(number > 0)
# # A tibble: 11 x 3
#    station sp    number
#      <int> <chr>  <int>
#  1       2 SP2        1
#  2       2 SP3        1
#  3      10 SP2        3
#  4      53 SP2        3
#  5      53 SP3        5
#  6      56 SP1        6
#  7      56 SP3        3
#  8      57 SP1        1
#  9      62 SP1        1
# 10      62 SP2        8
# 11      62 SP3       10

data.table

(Effectively the same as reshape2.)

library(data.table)
data.table::melt(as.data.table(dat), "station", variable.name = "sp", value.name = "number"
   )[ number > 0, ]
#     station     sp number
#       <int> <fctr>  <int>
#  1:      56    SP1      6
#  2:      57    SP1      1
#  3:      62    SP1      1
#  4:       2    SP2      1
#  5:      10    SP2      3
#  6:      53    SP2      3
#  7:      62    SP2      8
#  8:       2    SP3      1
#  9:      53    SP3      5
# 10:      56    SP3      3
# 11:      62    SP3     10

Data

dat <- structure(list(station = c(2L, 10L, 34L, 53L, 56L, 57L, 62L), SP1 = c(0L, 0L, 0L, 0L, 6L, 1L, 1L), SP2 = c(1L, 3L, 0L, 3L, 0L, 0L, 8L), SP3 = c(1L, 0L, 0L, 5L, 3L, 0L, 10L)), class = "data.frame", row.names = c(NA, -7L))
  • Related