When using 'reduce' combine a list to 'final_0' (refer below image), how to mutate new variable 'amount_from'? my code show error . Thanks!

library(tidyverse)
table_base <- data.frame(cat=c("a","b","c","d"))

table_a <- data.frame(cat=c("a","b"),
                      value=c(1,2))

table_b <- data.frame(cat=c("a","c","d"),
                      value=c(7,9,10))

final_0<- reduce(list(table_base,table_a,table_b),left_join,by='cat') %>% 
  mutate(amount=coalesce(!!!select(.,starts_with('value'))))

#below code show error [no applicable method for 'fill' applied to an object of class "character"]

final_0 %>% pivot_longer(-c('cat','amount')) %>% 
  mutate(value_from=if_else(amount==value,name,NULL)) %>% 
  group_by(cat) %>% 
  mutate(value_from=tidyr::fill(value_from,.direction = "downup"))

CodePudding user response：

Running ?fill will show the issue:

Usage fill(data, ..., .direction = c("down", "up", "downup", "updown"))

The function takes a data frame and a set of columns to fill (from ...). In your implementation, you used it inside a mutate() call. Here's how to fix the issue:

final_0 %>% 
  pivot_longer(-c('cat','amount')) %>% 
  mutate(value_from=if_else(amount==value,name,NULL)) %>% 
  group_by(cat) %>% 
  fill(value_from, .direction = "downup")

# A tibble: 8 x 5
# Groups:   cat [4]
  cat   amount name    value value_from
  <chr>  <dbl> <chr>   <dbl> <chr>     
1 a          1 value.x     1 value.x   
2 a          1 value.y     7 value.x   
3 b          2 value.x     2 value.x   
4 b          2 value.y    NA value.x   
5 c          9 value.x    NA value.y   
6 c          9 value.y     9 value.y   
7 d         10 value.x    NA value.y   
8 d         10 value.y    10 value.y 

Although this works, there is a solution that that avoids unnecessarily creating NULL values using if_else() and then needing to fill them:

final_0 %>%
  pivot_longer(-c("cat", "amount")) %>%
  group_by(cat) %>%
  mutate(value_from = na.omit(name[amount == value]))

# A tibble: 8 x 5
# Groups:   cat [4]
  cat   amount name    value value_from
  <chr>  <dbl> <chr>   <dbl> <chr>     
1 a          1 value.x     1 value.x   
2 a          1 value.y     7 value.x   
3 b          2 value.x     2 value.x   
4 b          2 value.y    NA value.x   
5 c          9 value.x    NA value.y   
6 c          9 value.y     9 value.y   
7 d         10 value.x    NA value.y   
8 d         10 value.y    10 value.y 

Some important notes about this:

• We simply filter on name using your if_else() condition. We then apply na.omit() since value could be NA.
• This code can result in different values of value_from for the same cat in the case that value.x and value.y are both equal to amount. This would also happen in your implementation because of the way you used if_else(). If you want value_from to only have one value for each cat, you should use something like na.omit(name[amount == value])[[1]] in the code above.