transform data frame using tidyr

Question

I have this:

library(tidyr)

haves <- data.frame(
        model1_prediction = c(1, 2)
        , model2_prediction = c(3, 4)
        , model3_prediction = c(5, 6)
        , actuals = c(99.1, 99.2)
      )

model1_prediction model2_prediction model3_prediction actuals                 
1                 3                 5    99.1
2                 4                 6    99.2

I would like to obtain this:

wants <- data.frame(
        long = c(1, 2, 3, 4, 5, 6)
        , actuals = c(99.1, 99.2, 99.1, 99.2, 99.1, 99.2)
     )

wants

long actuals
1    99.1
2    99.2
3    99.1
4    99.2
5    99.1
6    99.2

My long wounded working attempt is the following but wonder if there is a better way? Thanks.

t1 <- haves %>%
    select(
        model1_prediction
        , model2_prediction
        , model3_prediction
    ) %>%
    mutate(
        id = row_number()
    ) %>%
    gather(
       key, long, -id
    )
t1
t2 <- haves %>%
    select(
        actuals
    ) %>%
    mutate(
        id = row_number()
    ) %>%
    gather(
       key, actuals, - id
    )
t2
my_longwounded_wants <- t1 %>%
    inner_join(t2, by = c("id" = "id")) %>%
    select(
        long
        , actuals
    )
my_longwounded_wants

score 1 · Accepted Answer · answered Aug 17 '20 at 12:31

1

Try this:

library(tidyr)

haves %>% pivot_longer(cols = -actuals) %>% arrange(value) %>% select(value,actuals)

Output:

  value actuals
1     1    99.1
2     2    99.2
3     3    99.1
4     4    99.2
5     5    99.1
6     6    99.2

answered Aug 17 '20 at 12:31

Duck

39,058
13
42
84

Am I right in assuming that the arrange is redundant? – cs0815 Aug 17 '20 at 12:40
1

@cs0815 It depends of how you want your final output. For sure you can avoid it :) – Duck Aug 17 '20 at 12:45

Darren Tsai · Answer 2 · 2020-08-17T13:05:40.550

You can set names_to = NULL in pivot_longer() to drop the column which records original column names. That can save you the use of select() to make the code more concise.

library(tidyr)
library(dplyr)

haves %>%
  pivot_longer(-actuals, names_to = NULL) %>%
  arrange(value)

# # A tibble: 6 x 2
#   actuals value
#     <dbl> <dbl>
# 1    99.1     1
# 2    99.2     2
# 3    99.1     3
# 4    99.2     4
# 5    99.1     5
# 6    99.2     6

transform data frame using tidyr

2 Answers2