r语言 - 用dplyr修改列中的一行

Question

假设我有这两个标题

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

我想复制

的值

my_t2 %>% select(value)

放到my_t1的一个特定行中，比如中间行(有date == "2021-10-29")。我想避免为每个元素做，一个接一个(即基数R)，这样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 10    20    30    40   
3 2021-10-28 NA    NA    NA    NA   

Answer 1

我们可以使用match,cur_column到replace的第二行与'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)])))

与产出

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

---

如果列是有序的，base R更容易

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

Answer 2

下面是在定义id_groups之后使用coalesce的旋转方式:

更新:更短的代码与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)

第一次回答:

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

Answer 3

插入新行并删除旧行:

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

Answer 4

purrr::reduce:

library(tidyverse)
library(lubridate)
reduce(1:4, function(x,y) {x[x$date == "2021-10-29",y+1] <- my_t2[y,2]; x},
.init=my_t1)
#> # 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

---

另一个解决方案:

library(tidyverse)
library(lubridate)
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