How to use `pivot_longer` for a matrix

I have a matrix. Now I want to use pivot_longer in my matrix. My matrix is given below

       ID_1 ID_2 ID_3 ID_4 ID_8 ID_5 ID_7 ID_100
ID_1      0    1    1    1  Inf    2    2    Inf
ID_2      1    0    2    1  Inf    1    2    Inf
ID_3      1    2    0    2  Inf    3    1    Inf
ID_4      1    1    2    0  Inf    2    1    Inf
ID_8    Inf  Inf  Inf  Inf    0  Inf  Inf      1
ID_5      2    1    3    2  Inf    0    3    Inf
ID_7      2    2    1    1  Inf    3    0    Inf
ID_100  Inf  Inf  Inf  Inf    1  Inf  Inf      0

Using pivot_longer I want only those values that have 3. The output look likes

nodeA nodeB value
ID_3 ID_5 3
ID_5 ID_3 3
ID_5 ID_7 3
ID_7 ID_5 3

The code I am trying

mat <- mat %>% 
  pivot_longer(names_to = "nodeB", values_to = "valu")

But I am getting an error, like

Error in UseMethod("pivot_longer") : 
  no applicable method for 'pivot_longer' applied to an object of class "c('matrix', 'double', 'numeric')"

Then, I tried to convert the matrix into dataframe and trying to use column_to_rownames but getting an error

mat_df <- as_data_frame(mat)
mat_df <-column_to_rownames(mat_df, var = ID_1)

The error I am getting

Error in name %in% names2(x) : object 'ID_1' not found

How can I use pivot_longer in my matrix?

Note: My actual expected output is like the below one. Because, ID_3 ID_5 3 and ID_5 ID_3 3 are same (in terms of concept).

nodeA nodeB value
ID_3 ID_5 3
ID_5 ID_7 3

Reproducible Data

structure(c(0, 1, 1, 1, Inf, 2, 2, Inf, 1, 0, 2, 1, Inf, 1, 2, 
Inf, 1, 2, 0, 2, Inf, 3, 1, Inf, 1, 1, 2, 0, Inf, 2, 1, Inf, 
Inf, Inf, Inf, Inf, 0, Inf, Inf, 1, 2, 1, 3, 2, Inf, 0, 3, Inf, 
2, 2, 1, 1, Inf, 3, 0, Inf, Inf, Inf, Inf, Inf, 1, Inf, Inf, 
0), .Dim = c(8L, 8L), .Dimnames = list(c("ID_1", "ID_2", "ID_3", 
"ID_4", "ID_8", "ID_5", "ID_7", "ID_100"), c("ID_1", "ID_2", 
"ID_3", "ID_4", "ID_8", "ID_5", "ID_7", "ID_100")))

Found the solution

mad_pivot_lon <- as.data.frame.table(mat, responseName = "value") %>%
  filter(value ==3)

But I am getting this output

nodeA nodeB value
ID_3 ID_5 3
ID_5 ID_3 3
ID_5 ID_7 3
ID_7 ID_5 3

Any idea how can I filter and get the output like this

nodeA nodeB value
ID_3 ID_5 3
ID_5 ID_7 3

What is the logic behind this filter? Do you say, ID_3, ID_5 are same as ID_5, ID_3, i.e. ID_x, ID_y are same as ID_y, ID_x and get the output. Thanks

@mhakanda yes, that's it

The filter logic is not straight. I make a set (Var1, Var2) at the temp column and then I use the distinct to get the distinct rows. Note, in set perspective, (3, 5) is same as (5,3). Lastly, temp is eliminated. Here, I consider order of Var1 and Var2 are not important (so you may find different values in Var1 and Var2, but essentially it captures what you want).

mad_pivot_lon
#>   Var1 Var2 value
#> 1 ID_5 ID_3     3
#> 2 ID_3 ID_5     3
#> 3 ID_7 ID_5     3
#> 4 ID_5 ID_7     3
mad_pivot_lon %>% 
  rowwise()%>%
  mutate(temp= list(sort(base::union(Var1,Var2)))) %>%
  ungroup() %>%
  distinct(temp,.keep_all = TRUE) %>%
  select(-temp)
#> # A tibble: 2 x 3
#>   Var1  Var2  value
#>   <fct> <fct> <dbl>
#> 1 ID_5  ID_3      3
#> 2 ID_7  ID_5      3

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.