2

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.

camille
  • 16,432
  • 18
  • 38
  • 60
Steven
  • 73
  • 5
  • 1
    What did you try exactly, and what did you get? You'll probably want to make use of either the `names_sep` or `names_pattern` arguments – camille Jan 25 '22 at 00:21
  • Does this answer your question? [How to reshape data from long to wide format](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) – divibisan Jan 25 '22 at 00:38
  • @divibisan I don't believe so because I don't want a new column for each category. Instead, I want a row for each category which includes a rating (which is numeric) and a rank variable. – Steven Jan 25 '22 at 00:44

1 Answers1

2

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
John J.
  • 1,450
  • 1
  • 13
  • 28
  • Yes, your interpretation of the Rating issue is correct. I can rename that in future iterations. When I run the code you have, I do not get the out put you show. Instead I get four columns (Overall_rank,Total_rank, Category,Rating). Category and Rating have the correct output, but the two rank columns are incorrect. Not sure why we would get different output? When I ran on my real data, the same thing happened - a new column for each rank variable. – Steven Jan 25 '22 at 00:40
  • Oops. I copy/pasted the wrong code. Here's what I meant to include. – John J. Jan 25 '22 at 00:44
  • That worked! I am getting this error message when I run the code "Warning message: Expected 2 pieces. Missing pieces filled with `NA` in 2 rows [1, 3]." However, it is creating the correct data frame. Thank you so much! – Steven Jan 25 '22 at 00:49
  • 1
    Yup! You get that message because `separate` is telling you that nothing comes after the separator `"_"` in two of the rows. That's why the `mutate` row is needed. Alternatively, you could structure your data so that the first column reads c("Overall_rate", "Overall_rank", etc) – John J. Jan 25 '22 at 00:52
  • 1
    Got it, thanks again! – Steven Jan 25 '22 at 01:13