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 :
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))