How to pivot/merge data in R

Question

This is an example of my dataset

Reproducible dataframe

datt <- structure(
  list(
    Name = c("John", "John", "John", "John"),
    Type = c("a",
             "a", "b", "b"),
    Distance = c(50, 100, 50, 100),
    Value = c(2,
              4, 3, 6),
    Peak = c(30, 30, 45, 45)
  ),
  class = c("spec_tbl_df",
            "tbl_df", "tbl", "data.frame"),
  row.names = c(NA, -4L),
  spec = structure(list(
    cols = list(
      Name = structure(list(), class = c("collector_character",
                                         "collector")),
      Type = structure(list(), class = c("collector_character",
                                         "collector")),
      Distance = structure(list(), class = c("collector_double",
                                             "collector")),
      Value = structure(list(), class = c("collector_double",
                                          "collector")),
      Peak = structure(list(), class = c("collector_double",
                                         "collector"))
    ),
    default = structure(list(), class = c("collector_guess",
                                          "collector")),
    skip = 1
  ), class = "col_spec")
)

This is the structure i would like to get to in order to visualise

Using R, can anyone point in the right direction? I think I need to merge Peak and Value, with a pivot in there somehow.

Answer 1

Here is data.table option

setDT(datt)[,
  rbind(setNames(data.frame("Peak", unique(Peak)), names(.SD)), .SD),
  .(Name, Type),
  .SDcols = c("Distance", "Value")
]

which gives

   Name Type Distance Value
1: John    a     Peak    30
2: John    a       50     2
3: John    a      100     4
4: John    b     Peak    45
5: John    b       50     3
6: John    b      100     6

Answer 2

You can try this :

library(dplyr)
library(tidyr)

datt %>%
  select(Name, Type, Peak) %>%
  pivot_longer(cols = Peak, names_to = 'Distance', values_to = 'Value') %>%
  distinct() %>%
  bind_rows(datt %>% 
              mutate(Distance = as.character(Distance)) %>%
              select(-Peak)) %>%
  arrange(Name, Type)

#  Name  Type  Distance Value
#  <chr> <chr> <chr>    <dbl>
#1 John  a     Peak        30
#2 John  a     50           2
#3 John  a     100          4
#4 John  b     Peak        45
#5 John  b     50           3
#6 John  b     100          6

However, note that Distance column is now of type character since you can have column of only one type in a dataframe.