Reshape long data to multiple wide columns

January 25, 2022

I have data in a long format that I need to selectively move to wide format.

Here is an example of what I have and what I need

Rating <- c("Overall","Overall_rank","Total","Total_rank")
value <- c(6,1,5,2)

example <- data.frame(Rating,value)

Creates data that looks like this:

Rating         value
Overall          6
Overall_rank     1
Total            5
Total_rank       2

However, I want my data to look like:

What my data should look like

I tried pivot_wider, but cannot seem to get it.

>Solution :

Does this work for your real situation?

I think the confusion is stemming from calling column 1 "Rating," when really the "rating" values (as I understand it) are contained in rows 1 and 3.

example %>%
  separate(Rating, sep = "_", into = c("Category", "type")) %>%
  mutate(type = replace(type, is.na(type), "rating")) %>%
  pivot_wider(names_from = type, values_from = value)

  Category rating  rank
  <chr>     <dbl> <dbl>
1 Overall       6     1
2 Total         5     2