Create new column based on other columns and date conditioning

Question

I wanna create a new column based on the data in column 2 - 4, conditioning if the date column is before or after the previous row. My data looks as follows:

date city1 city2 city3 2022-01-25 Paris London Berlin 2022-01-28 Paris London Berlin 2022-02-04 Paris London Berlin 2022-01-26 Paris London Berlin 2022-02-08 Paris London Berlin 2022-02-02 Paris London Berlin 2022-02-04 Paris London Berlin 2022-02-06 Paris London Berlin

The expected output looks as follow:

date city1 city2 city3 NewColumn 2022-01-25 Paris London Berlin Paris 2022-01-28 Paris London Berlin Paris 2022-02-04 Paris London Berlin Paris 2022-01-26 Paris London Berlin London 2022-02-08 Paris London Berlin London 2022-02-02 Paris London Berlin Berlin 2022-02-04 Paris London Berlin Berlin 2022-02-06 Paris London Berlin Berlin

This is what I have tried:

for (i in 2:4){ new_data <- data %>% mutate(NewColumn = ifelse( as.Date(date) > lag(as.Date(date)), data[,i], data[,i+1]))}

But this only generates:

date city1 city2 city3 NewColumn 2022-01-25 Paris London Berlin <NA> 2022-01-28 Paris London Berlin Berlin 2022-02-04 Paris London Berlin Berlin 2022-01-26 Paris London Berlin London 2022-02-08 Paris London Berlin London 2022-02-02 Paris London Berlin Berlin 2022-02-04 Paris London Berlin Berlin 2022-02-06 Paris London Berlin Berlin

How can I solve this? Any advice?

tidyr::fill(NewColumn, .direction = "up") would be a quick fix. — Julian
– Julian, Commented Aug 14, 2022 at 8:55
Could you illustrate this further, given my sample code. I don¨t get it to work. — Kalle Blomkvist
– Kalle Blomkvist, Commented Aug 14, 2022 at 8:59
Hi @KalleBlomkvist, I rolled back your edit removing your question text — even though your question has been answered, leaving it here will help others with similar questions in the future. If you really want to remove it, you could delete it (rather than editing it to remove all the content), but I would encourage you to leave it up. — zephryl
– zephryl, Commented Aug 15, 2022 at 5:40

Just James · Accepted Answer · 2022-08-14 11:34:58Z

Using dplyr. Creating a custom group, we can use it to mutate the new values into the column

library(dplyr) df |> group_by(grp = cumsum(c(1, diff(lubridate::ymd(date))) < 0) + 1) |> rowwise() |> mutate(NewColumn = c_across(city1:city3)[grp]) |> ungroup() |> select(-grp)

 date city1 city2 city3 NewColumn <chr> <chr> <chr> <chr> <chr> 1 2022-01-25 Paris London Berlin Paris 2 2022-01-28 Paris London Berlin Paris 3 2022-02-04 Paris London Berlin Paris 4 2022-01-26 Paris London Berlin London 5 2022-02-08 Paris London Berlin London 6 2022-02-02 Paris London Berlin Berlin 7 2022-02-04 Paris London Berlin Berlin 8 2022-02-06 Paris London Berlin Berlin

Mohamed Desouky · Accepted Answer · 2022-08-14 09:53:48Z

You can try this

j <- 2 lag_d <- c(0 , df$date) for(i in 1:(length(lag_d)-1)){ if(lag_d[i+1] > lag_d[i]) df$NewColumn[i] <- df[i,j] else { j <- j + 1 df$NewColumn[i] <- df[i,j] } }

Output

 date city1 city2 city3 NewColumn 1 2022-01-25 Paris London Berlin Paris 2 2022-01-28 Paris London Berlin Paris 3 2022-02-04 Paris London Berlin Paris 4 2022-01-26 Paris London Berlin London 5 2022-02-08 Paris London Berlin London 6 2022-02-02 Paris London Berlin Berlin 7 2022-02-04 Paris London Berlin Berlin 8 2022-02-06 Paris London Berlin Berlin

Collectives™ on Stack Overflow

Create new column based on other columns and date conditioning

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related