CODEWITHSUNDEEP
rdplyr
user16335246
user16335246

Reputation:

How to change column value to match row based on column condition

In this dummy dataset, I'd like to change the "value" column for each site to the value where "type" is "home".

a <- structure(list(site = c("a", "a", "b", "b", "c", "c"), value = c(1, 
2, 3, 4, 5, 6), type = c("home", "away", "home", "away", "home", 
"away")), row.names = c(NA, -6L), class = "data.frame")

So, I'd like to end up with this:

site    value    type
a       1        home
a       1        away
b       3        home
b       3        away
c       5        home
c       5        away

Upvotes: 0

Views: 59

Answers (3)

user438383
user438383

Reputation: 6206

This code, for each site, changes the number in the value column to the value when type == "home":

a %>% dplyr::group_by(site) %>% dplyr::mutate(value = first(value[type=="home"])) 

# A tibble: 6 x 3
# Groups:   site [3]
  site  value type 
  <chr> <dbl> <chr>
1 a         1 home 
2 a         1 away 
3 b         3 home 
4 b         3 away 
5 c         5 home 
6 c         5 away

Upvotes: 1

akrun
akrun

Reputation: 886948

We may also use match to find the first position if there is any

library(dplyr)
a %>%
    group_by(site) %>%
     mutate(value = value[match("home", type)]) %>%
     ungroup

-output

# A tibble: 6 x 3
  site  value type 
  <chr> <dbl> <chr>
1 a         1 home 
2 a         1 away 
3 b         3 home 
4 b         3 away 
5 c         5 home 
6 c         5 away

Upvotes: 1

Ian Gow
Ian Gow

Reputation: 3535

When running this on my real data, I'm getting the error ...

One guess: It may be that there isn't a unique value for each site and type equal to "home". One could add distinct() after select(site, value) in the second approach below to ensure that this is the case.

library(dplyr, warn.conflicts = FALSE)

df <-
    structure(list(
        site = c("a", "a", "b", "b", "c", "c"),
        value = c(1, 2, 3, 4, 5, 6),
        type = c("home", "away", "home", "away", "home", "away")
    ),
    row.names = c(NA,-6L),
    class = "data.frame")

# From answer above
df %>% 
    group_by(site) %>% 
    mutate(value = value[type=="home"])
#> # A tibble: 6 x 3
#> # Groups:   site [3]
#>   site  value type 
#>   <chr> <dbl> <chr>
#> 1 a         1 home 
#> 2 a         1 away 
#> 3 b         3 home 
#> 4 b         3 away 
#> 5 c         5 home 
#> 6 c         5 away

# A more "SQL-like" approach
df %>%
    filter(type == "home") %>%
    select(site, value) %>%
    inner_join(df %>% select(site, type), by = "site")
#>   site value type
#> 1    a     1 home
#> 2    a     1 away
#> 3    b     3 home
#> 4    b     3 away
#> 5    c     5 home
#> 6    c     5 away

Created on 2021-07-06 by the reprex package (v2.0.0)

Upvotes: 1

Related Questions