How to pivot certain columns below other columns?

[r_q]

I've basically got a formatting question. This is a random data set.

I've got a data frame with my juice shops and other shops in the city. Here's a tiny example of just one of the shops:

| our_id   | Location       | Sales | competitor | Their_Sales |
| -------- | -------------- | ----- | ---------- | ----------- |
| 1        | Smith St       | 50    | 100        | 162         |
| 1        | Smith St       | 50    | 101        | 122         |

I want to pivot the data:

| our_id   | Location       | competitor| Sales |Classification | 
| -------- | -------------- | --------- | --------------------- | 
| 1        | Smith St       |           | 50    | Us            | 
| 1        | Smith St       |     100   | 162   | Comp          | 
| 1        | Smith St       |     101   | 122   | Comp          |

Should I make a new column to classify them and then pivot wider? I'm not sure how to format this.

[JackDavison]

I think this achieves what you want to do. Note that you can't have an "empty" cell, it needs to have something in it, even if it is NA.

library(tidyverse)

# set up data
dat = tibble(our_id = c(1,1),
             location = c("smith_st", "smith_st"),
             sales = c(50,50),
             competitor = c(100,101),
             their_sales = c(162, 122))

# get most of the way there
dat |> 
  pivot_longer(c(sales, their_sales), names_to = "class", values_to = "sales") |> 
  mutate(class = case_when(
    class == "sales" ~ "Us",
    class == "their_sales" ~ "Comp",
    TRUE ~ NA_character_
  ))
#> # A tibble: 4 x 5
#>   our_id location competitor class sales
#>    <dbl> <chr>         <dbl> <chr> <dbl>
#> 1      1 smith_st        100 Us       50
#> 2      1 smith_st        100 Comp    162
#> 3      1 smith_st        101 Us       50
#> 4      1 smith_st        101 Comp    122

# drop "competitor" column for "Us"
dat |> 
  pivot_longer(c(sales, their_sales), names_to = "class", values_to = "sales") |> 
  mutate(class = case_when(
    class == "sales" ~ "Us",
    class == "their_sales" ~ "Comp",
    TRUE ~ NA_character_
  )) |> 
  mutate(competitor = if_else(class == "Us", NA_real_, competitor)) |> 
  distinct()
#> # A tibble: 3 x 5
#>   our_id location competitor class sales
#>    <dbl> <chr>         <dbl> <chr> <dbl>
#> 1      1 smith_st         NA Us       50
#> 2      1 smith_st        100 Comp    162
#> 3      1 smith_st        101 Comp    122

^{Created on 2021-12-16 by the reprex package (v2.0.1)}

[r_q]

@JackDavison

|>

Hi Jack,

Thank you for your reply. I'll look into it. I do have one quick question though - what is this |>? Is it like a pipe?

[JackDavison]

It's the base R pipe!

It works like the magrittr pipe, but:

• mtcars %>% head works, mtcars |> head doesn't.

• You can do dat %>% lm(y ~ x, .), but you can't do dat |> lm(y ~ x, .).
  You'd have to do dat |> (\(x) lm(y ~ x, x))() instead.

It's personal preference what you use. I just use it by default to avoid having a magrittr dependency on everything.

The New Base Pipe | MyKo101

[r_q]

Thank you for your help!