4
tst <- data.frame(ID = c(123, 123, 123, 124, 124, 124), 
              Identifier = c("S2", "S2", "S5", "S3", "S2", "S2"),
              Speed = c(15,24,11,34,13,15),
              Box_loc = c(801,802,803,801,802,803),
              Ord = c(86,87,90,81,82,84))

I have been attempting to flip the above data from long to wide format, however whenever i try to research it myself, the data does not look similar enough for me to be able to translate it properly. And it seems like it should be a simple solution, but maybe I am missing some critical details.

ID Identifier(801) Speed(801) Ord(801) Identifier(802) Speed(802) Ord(802) Identifier(803) Speed(803) Ord(803)
123 S2 15 86 S2 24 87 S5 11 90
124 S3 34 81 S2 13 82 S2 15 84

I want to get my data to look like this table. The actual data has more than just 2 ID's (if that matters), and the numbers in the headers are the Box_loc. So in other words, for each Box_loc, there are IDs from 123-250 which each need their own row with these columns. My actual file has 10 columns or so, but the syntax should be about the same.

Duba Minus
  • 41
  • 2

1 Answers1

8

You could use the following solution:

library(tidyr)

tst %>%
  pivot_wider(names_from = Box_loc, 
              values_from = !c(ID, Box_loc), 
              names_glue = "{.value}({Box_loc})")

# A tibble: 2 x 10
     ID `Identifier(801)` `Identifier(802)` `Identifier(803)` `Speed(801)` `Speed(802)`
  <dbl> <chr>             <chr>             <chr>                    <dbl>        <dbl>
1   123 S2                S2                S5                          15           24
2   124 S3                S2                S2                          34           13
# ... with 4 more variables: Speed(803) <dbl>, Ord(801) <dbl>, Ord(802) <dbl>, Ord(803) <dbl>
Anoushiravan R
  • 21,622
  • 3
  • 18
  • 41
  • 1
    That's really a great answer. Pivoting more than one column is often too complex for my brain. +1 – Martin Gal Aug 26 '21 at 19:36
  • 1
    Thank you dear Martin. It is actually interesting when you set the `names_from` to `Box_loc` and `values_from` to all other than `ID` and `Box_loc`, the names of new columns will be a combination of the values of `Box_loc` and the old column names where that value located. You are being humble and nice again. This is super simple solution. – Anoushiravan R Aug 26 '21 at 21:14
  • 1
    Woke up and realized I havent voted much of your work. Will try to upvote as much as I can – Onyambu May 19 '22 at 15:57
  • Thank you very much dear @onyambu that's my pleasure. These days I am mostly working with Python at work and try to answer some very basic questions about it. But you will remain one of my greatest inspiration in programming who would make a mockery of our code with just one line. I owe you big time. – Anoushiravan R May 19 '22 at 17:24
  • 1
    Hahaha *mockery with one line* thats funny. And maybe by just using the base functions. Anyway I do respond to python questions to. Probably havent met your question – Onyambu May 19 '22 at 17:27
  • @onyambu I remember took a great liking to base R recently it's very original and brilliant. But then I totally fell for Python however, for me both do the same job in data science, still what I learned in R helped me to excel faster in Python. I haven't answer many questions in Python but will do soon :D – Anoushiravan R May 19 '22 at 17:32