How to update information for a variable depending on other variable in r?

user_11529172 0
Viewed 84

df_input is the data frame I have and I want to transform it into df_output.

For instance, as the year 2001-2003 is assembly=1 and we have a winner in 2001. It means we have a winner as long as the assembly doesn't change.

    df_input <- data.frame(winner  = c(1,0,0,0,2,0,0,0,1,0,0,0,0),
                           party = c("A",0,0,0,"B",0,0,0,"C",0,0,0,0), 
                           assembly= c(1,1,1,2,2,2,3,3,3,3,4,4,4), 
                           year = c(2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013))    
    
    df_output <- data.frame(winner  = c(1,1,1,0,2,2,0,0,1,1,0,0,0),
                            party = c("A","A","A",0,"B","B",0,0,"C","C",0,0,0),
                            assembly= c(1,1,1,2,2,2,3,3,3,3,4,4,4), 
                            year = c(2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013))

How can I update the same information as in the winner column depending on "assembly"?

EDIT: How to do it if there is an additional string variable, "party"? Look at the edit:

I get the following error after implementing this code:

    df_output <- df_input %>%
      mutate(df_input$party = if_else(is.na(df_input$party)==FALSE, df_input$party, NA_real_)) %>%
      group_by(assembly) %>%
      fill(df_input$party) %>%
      ungroup() %>%
      replace_na(list(df_input$party = 0))

Error:


Error: unexpected '=' in:
"      ungroup() %>%
      replace_na(list(df_input$party ="
dplyr data-manipulation r
3 Answers

One option would be to use tidyr::fill like so:

library(dplyr)
library(tidyr)   

df_input %>%
  mutate(winner = if_else(winner > 0, winner, NA_real_)) %>% 
  group_by(assembly) %>% 
  fill(winner) %>% 
  ungroup() %>% 
  replace_na(list(winner = 0))
#> # A tibble: 13 × 3
#>    winner assembly  year
#>     <dbl>    <dbl> <dbl>
#>  1      1        1  2001
#>  2      1        1  2002
#>  3      1        1  2003
#>  4      0        2  2004
#>  5      2        2  2005
#>  6      2        2  2006
#>  7      0        3  2007
#>  8      0        3  2008
#>  9      1        3  2009
#> 10      1        3  2010
#> 11      0        4  2011
#> 12      0        4  2012
#> 13      0        4  2013
user_129938610

Here is a base R way with cumsum and ave.
Note the use of the new lambda function \(x) introduced in R 4.1.0. If it gives an error, use the older function(x).

with(df_input, ave(winner, assembly, FUN = \(x){
  y <- cumsum(x != 0) != 0
  if(any(y)) x[y] <- x[min(which(y))]
  x
}))
# [1] 1 1 1 0 2 2 0 0 1 1 0 0 0

Just assign the result back to column winner.

df_output <- df_input
df_output$winner <- with(df_output, ave(winner, assembly, FUN = \(x){
  y <- cumsum(x != 0) != 0
  if(any(y)) x[y] <- x[min(which(y))]
  x
}))

Edit

Following Henrik's comment, here is the much simpler cummax solution.

with(df_input, ave(winner, assembly, FUN = cummax))
user_82454060

Update: See comments:

library(dplyr)
df_input %>% 
  group_by(assembly) %>% 
  mutate(winner = case_when(first(winner) > 0 ~ first(winner),
                            lag(winner, default=0) > winner ~ lag(winner),
                            TRUE ~ winner))

   winner assembly  year
    <dbl>    <dbl> <dbl>
 1      1        1  2001
 2      1        1  2002
 3      1        1  2003
 4      0        2  2004
 5      2        2  2005
 6      2        2  2006
 7      0        3  2007
 8      0        3  2008
 9      1        3  2009
10      1        3  2010
11      0        4  2011
12      0        4  2012
13      0        4  2013

First answer(not accounting for row 3) We can make use of lag function after grouping by assembly

library(dplyr)
df_input %>% 
  group_by(assembly) %>% 
  mutate(winner = ifelse(lag(winner, default = 0) > winner, lag(winner), winner))


Groups:   assembly [4]
   winner assembly  year
    <dbl>    <dbl> <dbl>
 1      1        1  2001
 2      1        1  2002
 3      0        1  2003
 4      0        2  2004
 5      2        2  2005
 6      2        2  2006
 7      0        3  2007
 8      0        3  2008
 9      1        3  2009
10      1        3  2010
11      0        4  2011
12      0        4  2012
13      0        4  2013
user_133216470
Related