I have a dataset which looks like below and i want to flatten it.

inp <- matrix(c(0,1,0,0,2,3,5,0,4,3,6,0),nrow = 4, byrow = T)
rownames(inp)<- c("A","B","C","D")
colnames(inp) <-c("x","y","z")

Expected Output:

A y 1
B y 2
B z 3
C x 5
C z 4
D x 3
D y 6

CodePudding user response：

Regarding inp as a table, convert it to a data frame and remove zero rows. Omit the last line if the order of rows is unimportant. No packages are used.

d <- subset(as.data.frame.table(inp), Freq > 0)
d[order(d$Var1), ]

giving:

   Var1 Var2 Freq
5     A    y    1
6     B    y    2
10    B    z    3
3     C    x    5
11    C    z    4
4     D    x    3
8     D    y    6

CodePudding user response：

You need to:

1. convert the matrix into a data.frame
2. convert row names into an actual column
3. reshape longer
4. remove the zeros

inp |> 
  as.data.frame() |> 
  tibble::rownames_to_column("letter") |> 
  tidyr::pivot_longer(x:z) |> 
  subset(value != 0)

Output:

#> # A tibble: 7 x 3
#>   letter name  value
#>   <chr>  <chr> <dbl>
#> 1 A      y         1
#> 2 B      y         2
#> 3 B      z         3
#> 4 C      x         5
#> 5 C      z         4
#> 6 D      x         3
#> 7 D      y         6

^{Created on 2022-05-17 by the reprex package (v2.0.1)}

CodePudding user response：

library(tidyverse)

inp <- matrix(c(0,1,0,0,2,3,5,0,4,3,6,0),nrow = 4, byrow = T)
rownames(inp)<- c("A","B","C","D")
colnames(inp) <-c("x","y","z")

inp |> as_tibble(rownames = "alpha") |> 
  pivot_longer(-alpha) |> 
  filter(value != 0)
#> # A tibble: 7 × 3
#>   alpha name  value
#>   <chr> <chr> <dbl>
#> 1 A     y         1
#> 2 B     y         2
#> 3 B     z         3
#> 4 C     x         5
#> 5 C     z         4
#> 6 D     x         3
#> 7 D     y         6

^{Created on 2022-05-17 by the reprex package (v2.0.1)}