R function for pivot wide data to long data

Question

Please, I have this wide data and I want to pivot it to long data.

Country     MDA     BI  MDA Last Year   BI Last Year    Month
Netherland  230     280     234     789     June
Scotland    100     340     456     234     June
UK          100     100     768     567     June
USA         200     780     765     123     June
Ireland     100     890     675     987     June
Nigeria     500     560     234     876     June

I have used the pivot_longer() function, but I don't seem to get it right. I want the output to be in the following format.

Country     Cur_Year Prod_Cat_new   Prev_Year   Prod_cat_old    Month
Netherland  230 MDA         234 MDA Last Year           June
Scotland    100 MDA         456 MDA Last Year           June
UK          100 MDA         768 MDA Last Year           June
USA         200 MDA         765 MDA Last Year           June
Ireland     100 MDA         675 MDA Last Year           June
Nigeria     500 MDA         234 MDA Last Year           June
Netherland  280 BI          789 BI Last Year            June
Scotland    340 BI          234 BI Last Year            June
UK          100 BI          567 BI Last Year            June
USA         780 BI          123 BI Last Year            June
Ireland     890 BI          987 BI Last Year            June
Nigeria     560 BI          876 BI Last Year            June

Answer 1

Reshape to long with pivot_longer and create those Prod_cat_new, Prod_cat_old from the Prod_cat column

library(dplyr)
library(stringr)
library(tidyr)
pivot_longer(df1, cols = matches("_"), names_to = c("Prod_cat", ".value"), 
   names_sep = "_") %>%
   mutate(Prod_cat_new = str_c(Prod_cat, '_new'),
    Prod_cat_old = str_c(Prod_cat, '_old'), Prod_cat = NULL) %>% 
  select(Country, new, Prod_cat_new, old, Prod_cat_old, Month)

-output

# A tibble: 12 × 6
   Country      new Prod_cat_new   old Prod_cat_old Month
   <chr>      <int> <chr>        <int> <chr>        <chr>
 1 Netherland   230 MDA_new        234 MDA_old      June 
 2 Netherland   280 SDA_new        789 SDA_old      June 
 3 Scotland     100 MDA_new        456 MDA_old      June 
 4 Scotland     340 SDA_new        234 SDA_old      June 
 5 UK           100 MDA_new        768 MDA_old      June 
 6 UK           100 SDA_new        567 SDA_old      June 
 7 USA          200 MDA_new        765 MDA_old      June 
 8 USA          780 SDA_new        123 SDA_old      June 
 9 Ireland      100 MDA_new        675 MDA_old      June 
10 Ireland      890 SDA_new        987 SDA_old      June 
11 Nigeria      500 MDA_new        234 MDA_old      June 
12 Nigeria      560 SDA_new        876 SDA_old      June 

data

df1 <- structure(list(Country = c("Netherland", "Scotland", "UK", "USA", 
"Ireland", "Nigeria"), MDA_new = c(230L, 100L, 100L, 200L, 100L, 
500L), SDA_new = c(280L, 340L, 100L, 780L, 890L, 560L), MDA_old = c(234L, 
456L, 768L, 765L, 675L, 234L), SDA_old = c(789L, 234L, 567L, 
123L, 987L, 876L), Month = c("June", "June", "June", "June", 
"June", "June")), class = "data.frame", row.names = c(NA, -6L
))