Modify a row from a column with dplyr-CodePudding

Say I have these two tibbles

library(dplyr)
library(lubridate)

my_date <- as_date(c("2021-10-30","2021-10-29","2021-10-28"))
my_t1 <- tibble(date = my_date) %>% 
  mutate("col1" = NA, 
         "col2" = NA, 
         "col3" = NA, 
         "col4" = NA)
my_t2 <- tibble("column" =  c("col1","col2","col3","col4"), 
                "value" = c(10,20,30,40))

> my_t1
# A tibble: 3 × 5
  date       col1  col2  col3  col4 
  <date>     <lgl> <lgl> <lgl> <lgl>
1 2021-10-30 NA    NA    NA    NA   
2 2021-10-29 NA    NA    NA    NA   
3 2021-10-28 NA    NA    NA    NA  

> my_t2
# A tibble: 4 × 2
  column value
  <chr>  <dbl>
1 col1      10
2 col2      20
3 col3      30
4 col4      40

And I would like to copy the values of

my_t2 %>% select(value)

into one specific row of my_t1, say the middle row (with date == "2021-10-29"). I would like to avoid doing for each element, one by one (i.e. base R) so that my_t1 becomes

# A tibble: 3 × 5
  date       col1  col2  col3  col4 
  <date>     <lgl> <lgl> <lgl> <lgl>
1 2021-10-30 NA    NA    NA    NA   
2 2021-10-29 10    20    30    40   
3 2021-10-28 NA    NA    NA    NA

CodePudding user response：

We may use match with cur_column to replace the 2nd row with the corresponding 'column' value from 'my_t2'

library(dplyr)
my_t1_new <- my_t1 %>% 
    mutate(across(-date, ~ replace(., date == "2021-10-29", 
         my_t2$value[match(cur_column(), my_t2$column)])))

-output

my_t1_new
# A tibble: 3 × 5
  date        col1  col2  col3  col4
  <date>     <dbl> <dbl> <dbl> <dbl>
1 2021-10-30    NA    NA    NA    NA
2 2021-10-29    10    20    30    40
3 2021-10-28    NA    NA    NA    NA

If the columns are in order, base R is easier

my_t1_new <- as.data.frame(my_t1)
my_t1_new[2, -1] <- my_t2$value

CodePudding user response：

Here's an option where you insert a new row and remove the old:

my_t1 %>% 
  add_row(date = as.Date("2021-10-29"), !!! deframe(my_t2), .before = 2) %>% 
  distinct(date, .keep_all = T)

CodePudding user response：

Here is a pivoting way making use of coalesce after defining id_groups:

Update: Shorter code with help from akrun!

library(dplyr)
library(tidyr)

my_t1 %>%  pivot_longer(
  cols = -date, 
  names_to = "column",     
  values_to = "value"
  ) %>%    
  left_join(my_t2, by="column") %>% 
  transmute(date, column, value = case_when(date == '2021-10-29' ~ coalesce(value.x, value.y), TRUE ~ as.numeric(value.x))) %>% 
  pivot_wider(names_from = column, values_from = value)

First answer:

library(dplyr)
library(tidyr)

my_t1 %>% 
  pivot_longer(
    cols = -date, 
    names_to = "column",
    values_to = "value"
  ) %>% 
  left_join(my_t2, by="column") %>% 
  group_by(id_Group = cumsum(column=="col1")) %>% 
  mutate(value.x = ifelse(id_Group==2, coalesce(value.x, value.y), value.x)) %>% 
  ungroup() %>% 
  select(date, column, value=value.x) %>% 
  pivot_wider(
    names_from = column,
    values_from = value
  )

  date        col1  col2  col3  col4
  <date>     <dbl> <dbl> <dbl> <dbl>
1 2021-10-30    NA    NA    NA    NA
2 2021-10-29    10    20    30    40
3 2021-10-28    NA    NA    NA    NA

CodePudding user response：

Yet another solution:

library(tidyverse)
library(lubridate)

my_date <- as_date(c("2021-10-30","2021-10-29","2021-10-28"))
my_t1 <- tibble(date = my_date) %>% 
  mutate("col1" = NA, 
         "col2" = NA, 
         "col3" = NA, 
         "col4" = NA)
my_t2 <- tibble("column" =  c("col1","col2","col3","col4"), 
                "value" = c(10,20,30,40))

my_t1 %>% 
  bind_rows(
    data.frame(date = as_date("2021-10-29"), 
      pivot_wider(my_t2, names_from = column))) %>% 
  filter(!(date == "2021-10-29" & !complete.cases(.) )) %>% arrange(date)

#> # A tibble: 3 × 5
#>   date        col1  col2  col3  col4
#>   <date>     <dbl> <dbl> <dbl> <dbl>
#> 1 2021-10-28    NA    NA    NA    NA
#> 2 2021-10-29    10    20    30    40
#> 3 2021-10-30    NA    NA    NA    NA