pivot_wider, apply values_fill argument for multiple columns (values_from)

[FENGQI]

I'd like to separate columns "A" and "B" to multiple columns based on the "F_FY" value with the input tibble as below

tibble::tribble(
  ~F_FY,        ~F_FW, ~C_Week, ~A, ~B,
   2015, "10/10/2015",       1,  2,  1,
   2015, "10/17/2015",       2,  4,  4,
   2015, "10/24/2015",       3,  6,  9,
   2016,  "9/10/2016",       1,  8, 16,
   2016,  "9/20/2016",       2, 10, 25,
   2016,  "9/30/2016",       3, 12, 36,
   2017,   "8/1/2017",       1, 14, 49,
   2017,   "8/1/2017",       2, 16, 64,
   2017,  "8/21/2017",       3, 18, 81
  ) -> mydata

Using the pivot_wider, I can easily get what I want

mydata %>% mutate(F_FW = mdy(F_FW)) %>% pivot_wider(names_from = F_FY, values_from = A, values_fill = list(A = 0), names_prefix = "FY")

## A tibble: 9 x 6
#  F_FW       C_Week     B FY2015 FY2016 FY2017
#  <date>      <dbl> <dbl>  <dbl>  <dbl>  <dbl>
#1 2015-10-10      1      1      2      0       0
#2 2015-10-17      2      4      4      0       0
#3 2015-10-24      3      9      6      0       0
#4 2016-09-10      1    16      0      8       0
#5 2016-09-20      2    25      0    10       0
#6 2016-09-30      3    36      0    12       0
#7 2017-08-01      1    49      0      0     14
#8 2017-08-01      2    64      0      0     16
#9 2017-08-21      3    81      0      0     18

Now, I want to apply the pivot_wider to both column "A" and "B", but I am not sure what is the best way to specify the values_fill for multiple columns.

I managed to get the result correctly, but want to know if there is a better way to do that

vars = c("A", "B")
fill_vars = as.list(rep(0, 2))
names(fill_vars) = vars

mydata %>% mutate(F_FW = mdy(F_FW)) %>% 
           pivot_wider(names_from = F_FY, values_from = vars, 
                              values_fill = fill_vars,  names_prefix = "FY")

Thanks

[aosmith]

Your approach looks good to me! I've played around with pivot_longer() but not pivot_wider() yet so was glad to see your example.

I think making the fill_vars in another step like you have will be especially useful if you have lots of variables.

Note you can use the vector from rep() directly rather than using as.list() if you want.

fill_vars = rep(0, 2)
names(fill_vars) = vars

You can write the vector of variables and the named list of fills directly within pivot_wider(), but I honestly think this is a personal style thing. I might do it this way if I had only a couple variables as in your small example.

mydata %>% 
    mutate(F_FW = lubridate::mdy(F_FW)) %>% 
    pivot_wider(names_from = F_FY, 
                values_from = c("A", "B"), 
                values_fill = c(A = 0, B = 0),  
                names_prefix = "FY")

[FENGQI]

Thank you, @aosmith

In my real project, I do have a lots of variables, I like to specify it at the beginning of the process.