I have the follow type of table:
SEZ | Class | Val |
---|---|---|
1_1_1 | 1 | 2 |
1_1_1 | 5 | 2 |
1_1_2 | 5 | 2 |
1_1_3 | 1 | 1 |
1_1_3 | 5 | 2 |
1_1_4 | 1 | 1 |
1_1_5 | 2 | 1 |
1_2_1 | 1 | 2 |
1_2_1 | 5 | 2 |
In order to spread column "Class" in multiple new columns getting value from "Val" column I used pivot_wider and everything gone well. I entered this code:
pivot_wider(names_from = Class, values_from = Val, names_sort=T, values_fill = list(n = 0))
obataining a result like this:
Sez | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
---|---|---|---|---|---|---|---|
1_1_1 | 2 | 0 | 0 | 0 | 2 | 0 | 0 |
1_1_2 | 0 | 0 | 0 | 0 | 2 | 0 | 0 |
1_1_3 | 1 | 0 | 0 | 0 | 2 | 0 | 0 |
Unfortunately I have to use an external computer where only the basic R packages are present, and the times for requesting additional packages are not short.
I tried to use this solution:
newdata <- xtabs(dat$Val ~ dat$Sez + dat$Class)
But it gives me a frequency distribution with each row:
SEZ | Class | Freq |
---|---|---|
1_1_1 | 1 | 2 |
1_1_2 | 1 | 0 |
1_1_3 | 1 | 1 |
1_1_4 | 1 | 1 |
1_1_5 | 1 | 0 |
1_2_1 | 1 | 1 |
I'm looking for a solution with the basic function of R that gives me an object equal to the one got using with pivot_wider.
We create the 'Class' as factor
and use xtabs
df1$Class <- factor(df1$Class, levels = 1:7)
xtabs(Val ~ SEZ + Class, df1)
-output
Class
SEZ 1 2 3 4 5 6 7
1_1_1 2 0 0 0 2 0 0
1_1_2 0 0 0 0 2 0 0
1_1_3 1 0 0 0 2 0 0
1_1_4 1 0 0 0 0 0 0
1_1_5 0 1 0 0 0 0 0
1_2_1 2 0 0 0 2 0 0
If we need a data.frame
output
out <- as.data.frame.matrix( xtabs(Val ~ SEZ + Class, df1))
out$SEZ <- row.names(out)
row.names(out) <- NULL
df1 <- structure(list(SEZ = c("1_1_1", "1_1_1", "1_1_2", "1_1_3", "1_1_3",
"1_1_4", "1_1_5", "1_2_1", "1_2_1"), Class = c(1L, 5L, 5L, 1L,
5L, 1L, 2L, 1L, 5L), Val = c(2L, 2L, 2L, 1L, 2L, 1L, 1L, 2L,
2L)), row.names = c(NA, -9L), class = "data.frame")