How to create a route in a dataframe with R

I am trying to map routes in a dataframe in R.
Spent the whole afternoon trying to do it...

Here is how my data looks like, i generated a simple dataset because of confidentiality.

end_point <- c("DC_Frankfurt","F6_DC_Bordeaux","B3_Paris","BEAG_Toronto","DC_Frankfurt")
product_code <- c("000001","000001","000001","000002","000002")
site <- c("Plant1","DC_Frankfurt","DC_Frankfurt","DC_Frankfurt","Plant2")
step <- c("step1","step2","step2","step2","step1")

df <- data.frame(end_point, product_code,site,step)

Here is what I would like it to be :

product_code <- c("000001","000001","000002")
step1 <- c("Plant1","Plant1","Plant2")
step2 <- c("DC_Frankfurt","DC_Frankfurt","DC_Frankfurt")
end_point <- c("F6_DC_Bordeaux","B3_Paris","BEAG_Toronto")

result_expected <- data.frame(product_code,step1,step2,end_point)

I tried this piece of code already but it is a dead-end:

my_df_test <- df %>%
  pivot_wider(names_from = step,
              values_from = site)

Thank you all!

You could split the data into step 1 and step 2, then perform a left join:

library(tidyverse)

df %>%
  filter(step == "step2") %>%
  rename(step2 = site) %>%
  select(-step) %>%
  left_join(df %>% 
              filter(step == "step1") %>%
              rename(step1 = site) %>%
              select(-step, -end_point), by = "product_code") %>%
  select(product_code, step1, step2, end_point)
#>   product_code  step1        step2      end_point
#> 1       000001 Plant1 DC_Frankfurt F6_DC_Bordeaux
#> 2       000001 Plant1 DC_Frankfurt       B3_Paris
#> 3       000002 Plant2 DC_Frankfurt   BEAG_Toronto

^{Created on 2023-02-08 with reprex v2.0.2}

We could do this with pivot_wider and fill

library(dplyr)
library(tidyr)
df %>% 
  pivot_wider(names_from = 'step', values_from = 'site') %>% 
  fill(starts_with('step'), .direction = 'downup') %>% 
  filter(step2 != end_point) %>% 
  relocate(end_point, .after = last_col())

-output

# A tibble: 3 × 4
  product_code step1  step2        end_point     
  <chr>        <chr>  <chr>        <chr>         
1 000001       Plant1 DC_Frankfurt F6_DC_Bordeaux
2 000001       Plant1 DC_Frankfurt B3_Paris      
3 000002       Plant1 DC_Frankfurt BEAG_Toronto