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)