R pivot table from long to wide format-CodePudding

I am creating a table with two headers (as shown in the desired output) but its not in the correct order. How do I order the columns and sort the year from most recent to the oldest

My data:

Country Year A B
France  1998    2   1
France  1999    3   4
France  2000    4   4
Newzealand  1999    4   0
Newzealand  2000    0   0
Ireland 1998    5   1
Ireland 1999    1   1
Ireland 2000    1   1

Desired Output:

My code:

data %>%
  select(Country, Year, A, B) %>%
  pivot_wider( names_from = Year,
              values_from = c("A", "B"), values_fill = 0) %>%
  as_flextable() %>%
  separate_header()

This code yields table in the following format

CodePudding user response：

We may change the name order with 'Year' values as prefix and the value column names as suffix with names_glue. In addition, names_vary uses by default "fastest", change it to "slowest" to reorder the names in value1_name1, value2_name1, ... order and then convert to flextable and split the header at delimiter _

library(dplyr)
library(tidyr)
library(flextable)
data %>%
    select(Country, Year, A, B) %>%
    pivot_wider( names_from = Year,
             values_from = c("A", "B"),
   values_fill = 0, names_glue = "{Year}_{.value}", names_vary = "slowest") %>%
    flextable() %>% 
   separate_header()

-output