expand data from column to multiple rows

Question

This is a practice an example of the data I am trying to expand over multiple columns in R

Location<-c(rep("ATL",3),rep("CLE",3),rep("BOS",3))
type<-rep(c("sedan","truck","coupe"),3)
pro1<-rep(c("0800 SC","1500 SC","0900 SC"),3)
pro2<- rep(c("0800 SC","1500 SC","0900 SC"),3)
pro3<-rep(c("1000 SC","1700 SC","1100 SC"),3)
pro4<-rep(c("1300 SC","1100 SC","2300 SC"),3)
x<-data.frame(Location,type,pro1,pro2,pro3,pro4)

I am trying to transfer the data into the screenshot of the excel file

Desired output (screenshot from Excel):

Current output:

Answer 1

You just need to use tidyr::pivot_longer().

library(tidyr)

Location<-c(rep("ATL",3),rep("CLE",3),rep("BOS",3))
type<-rep(c("sedan","truck","coupe"),3)
pro1<-rep(c("0800 SC","1500 SC","0900 SC"),3)
pro2<- rep(c("0800 SC","1500 SC","0900 SC"),3)
pro3<-rep(c("1000 SC","1700 SC","1100 SC"),3)
pro4<-rep(c("1300 SC","1100 SC","2300 SC"),3)
x<-data.frame(Location,type,pro1,pro2,pro3,pro4)

x |>  pivot_longer(starts_with("pro"), names_to = "pro", values_to = "time")
#> # A tibble: 36 × 4
#>    Location type  pro   time   
#>    <chr>    <chr> <chr> <chr>  
#>  1 ATL      sedan pro1  0800 SC
#>  2 ATL      sedan pro2  0800 SC
#>  3 ATL      sedan pro3  1000 SC
#>  4 ATL      sedan pro4  1300 SC
#>  5 ATL      truck pro1  1500 SC
#>  6 ATL      truck pro2  1500 SC
#>  7 ATL      truck pro3  1700 SC
#>  8 ATL      truck pro4  1100 SC
#>  9 ATL      coupe pro1  0900 SC
#> 10 ATL      coupe pro2  0900 SC
#> # ℹ 26 more rows

^{Created on 2023-07-05 with reprex v2.0.2}