-1

I have the following data:

   Market POSITION VALUATION
1       A       UP       151
2       A     DOWN       151
3       A    RIGHT      5,26
4       A     LEFT     96,14
5       B    RIGHT    107,07
6       B     LEFT     96,14
7       B       UP    109,25
8       C       UP     96,14
9       C     DOWN    109,25
10      C    RIGHT       650
11      C     LEFT     13912
12      C      TOP     13912
13      C   MIDDLE     13912

I wat to create a variable (in the column) from groups in another variable. I remember somewhere I saw that it was done via dplyr and separate(), but do not remember how. Could you please help how obtain the final output, which looks like this: enter image description here

Thank you!

Ian Campbell
  • 23,484
  • 14
  • 36
  • 57
John Castello
  • 33
  • 4
  • 3
    Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. – Sotos Jul 03 '20 at 07:43
  • I have tried to answer your question, but, please, try to provide your data as a text, not images. It's searchable by search engines and provides a better experience to other people that will face similar problem. Thanks a lot! :-) –  Jul 03 '20 at 08:08
  • 1
    @ Petr Kajzar Hello, sorry. here you are ``` structure(list(Market = c("A", "A", "A", "A", "B", "B", "B", "C", "C", "C", "C", "C", "C"), POSITION = c("UP", "DOWN", "RIGHT", "LEFT", "RIGHT", "LEFT", "UP", "UP", "DOWN", "RIGHT", "LEFT", "TOP", "MIDDLE"), VALUATION = c(1.51, 1.51, 5.26, 96.14, 107.07, 96.14, 109.25, 96.14, 109.25, 650, 13912, 13912, 13912)), row.names = c(NA, -13L), class = c("tbl_df", "tbl", "data.frame")) ``` – John Castello Jul 03 '20 at 08:09
  • Great, thanks a lot for a very quick response and data! I have updated my answer so it uses your data, see below. –  Jul 03 '20 at 08:11

1 Answers1

0

I think you are looking for pivot_wider() from tidyr:

library(dplyr)
library(tidyr)

print(data)
#>    Market POSITION VALUATION
#> 1       A       UP       151
#> 2       A     DOWN       151
#> 3       A    RIGHT      5,26
#> 4       A     LEFT     96,14
#> 5       B    RIGHT    107,07
#> 6       B     LEFT     96,14
#> 7       B       UP    109,25
#> 8       C       UP     96,14
#> 9       C     DOWN    109,25
#> 10      C    RIGHT       650
#> 11      C     LEFT     13912
#> 12      C      TOP     13912
#> 13      C   MIDDLE     13912

data %>%
  pivot_wider(names_from = POSITION,
              names_glue = "{.value}_{POSITION}",
              values_from = VALUATION)
#> # A tibble: 3 x 7
#>   Market VALUATION_UP VALUATION_DOWN VALUATION_RIGHT VALUATION_LEFT
#>   <chr>  <chr>        <chr>          <chr>           <chr>         
#> 1 A      151          151            5,26            96,14         
#> 2 B      109,25       <NA>           107,07          96,14         
#> 3 C      96,14        109,25         650             13912         
#> # ... with 2 more variables: VALUATION_TOP <chr>, VALUATION_MIDDLE <chr>

It glues a name of the new columns with VALUATION name (from which it uses values inside the table) and POSITION value. See documentation examples, they are pretty good.

Data:

data <- structure(list(Market = c("A", "A", "A", "A", "B", "B", "B", 
"C", "C", "C", "C", "C", "C"), POSITION = c("UP", "DOWN", "RIGHT", 
"LEFT", "RIGHT", "LEFT", "UP", "UP", "DOWN", "RIGHT", "LEFT", 
"TOP", "MIDDLE"), VALUATION = c("151", "151", "5,26", "96,14", 
"107,07", "96,14", "109,25", "96,14", "109,25", "650", "13912", 
"13912", "13912")), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13"
))

Created on 2020-07-03 by the reprex package (v0.3.0)