In `dplyr`, when use `pivot_wide` ,I want to replace 'NA' at the same time

Question

In dplyr, when use pivot_wide ,I want to replace 'NA' at the same time . Here is the code as below ,they are not work. Anyone can help? Thanks!

library(tidyverse)
test_data <- data.frame(category=c('A','B','A','B','A','B','A','B'),
                        sub_category=c('a1','b1','a2','b2','a1','b1','a2','b2'),
                        sales=c(1,2,4,5,8,1,4,6))

#method1: Error: Can't convert <double> to <list>.
test_data %>% pivot_wider(names_from = 'category',
                          values_from = 'sales',
                          values_fill = 0) %>% unnest() 


#method2: code can't run
test_data %>% pivot_wider(names_from = 'category',
                          values_from = 'sales') %>% unnest() %>% 
  as.data.frame() %>% 
  mutate(across(where(is.numeric),function(x) stringr::str_replace('NA',0)))

The `values_fill` argument must be a list of the form `values_fill = list(column_name = value)`. — Gregor Thomas, Dec 08 '21 at 02:25
I can't get any of your code to run. Can you please show the output you want for the sample input? — Gregor Thomas, Dec 08 '21 at 02:26
Also, just for general knowledge, missing values `NA` are not the same as strings `"NA"`. — Gregor Thomas, Dec 08 '21 at 02:27
@Gregor Thomas I tried 'values_fill = list(column_name = value)',but it can't work,could you help to show the entiry code ? Thanks! — anderwyang, Dec 08 '21 at 02:33
I would love to show you code, but I don't know what your goal is. Can you please show the output that you want, as per my 2nd comment? — Gregor Thomas, Dec 08 '21 at 02:36

score 3 · Answer 1 · answered Dec 08 '21 at 02:30

3

Is this what you want?

test_data %>% 
    pivot_wider(names_from = 'category',
                values_from = 'sales') %>% 
    unnest(cols = c(A, B)) %>% 
    mutate(across(where(is.numeric), replace_na, 0))

answered Dec 08 '21 at 02:30

Michael Dewar

2,553
1
6
22

score 3 · Accepted Answer · answered Dec 08 '21 at 02:47

test_data %>% 
     pivot_wider(names_from = 'category',  values_from = 'sales', 
                 values_fn = list, values_fill = list(0))  %>%
     unnest(c(A, B))

# A tibble: 8 x 3
  sub_category     A     B
  <chr>        <dbl> <dbl>
1 a1               1     0
2 a1               8     0
3 b1               0     2
4 b1               0     1
5 a2               4     0
6 a2               4     0
7 b2               0     5
8 b2               0     6

In `dplyr`, when use `pivot_wide` ,I want to replace 'NA' at the same time

2 Answers2