Create boolean column in R dataframe that returns true if another columns value changes row-to-row

user_5573294 0
Viewed 768

Title is wordy, but my problem is difficult to explain in a single snippet, so I've come prepared with a reproducible example. Below is a subset of my dataframe:

> zed
   possessionName possessionMarket
1          Eagles   Boston College
2     Blue Devils             Duke
3            <NA>             <NA>
4     Blue Devils             Duke
5          Eagles   Boston College
6     Blue Devils             Duke
7     Blue Devils             Duke
8          Eagles   Boston College
9          Eagles   Boston College
10         Eagles   Boston College
11           <NA>             <NA>
12    Blue Devils             Duke
13    Blue Devils             Duke
14           <NA>             <NA>
15         Eagles   Boston College

> dput(zed)
structure(list(possessionName = c("Eagles", "Blue Devils", NA, 
"Blue Devils", "Eagles", "Blue Devils", "Blue Devils", "Eagles", 
"Eagles", "Eagles", NA, "Blue Devils", "Blue Devils", NA, "Eagles"
), possessionMarket = c("Boston College", "Duke", NA, "Duke", 
"Boston College", "Duke", "Duke", "Boston College", "Boston College", 
"Boston College", NA, "Duke", "Duke", NA, "Boston College")), class = "data.frame", row.names = c(NA, 
15L))

Here is the output that I am seeking: 

> zed
   possessionName possessionMarket outputCol
1          Eagles   Boston College         1
2     Blue Devils             Duke         1
3            <NA>             <NA>         0
4     Blue Devils             Duke         0
5          Eagles   Boston College         1
6     Blue Devils             Duke         1
7     Blue Devils             Duke         0
8          Eagles   Boston College         1
9          Eagles   Boston College         0
10         Eagles   Boston College         0
11           <NA>             <NA>         0
12    Blue Devils             Duke         1
13    Blue Devils             Duke         0
14           <NA>             <NA>         0
15         Eagles   Boston College         1

Problem: I would like the first row in the newCol to be a 1. For all subsequent rows, I'd like the value to be == 1 if the school name changed from one row to the next. For example, row 1 was Eagles and row 2 was Blue Devils, so row 2 should have a 1 in the newCol column.

In the instance that there is an , I'd like that row to be a zero.

Notice how in rows 2-4, the school name did not change even though there was an NA, so row 4 == 0. However, in rows 10-12, the data went from Eagles to Blue Devils (albeit with an NA in between), so row 12 should be a 1.

Usually I would do something along the lines of this:

zed <- zed %>% 
  dplyr::mutate(newCol = ifelse(possessionName[2:length(possessionName)] != possessionName[1:(length(possessionName)-1)], 1, 0))

...however this approach is foiled due to the presense of NAs. Is there a better way for me to go about this? I am actively working on this and hope to post an answer before one is posted, but could use some help. Thanks in advance !!!

dplyr data-manipulation r
2 Answers

One way is to fill the NAs with previous NAs using na.locf from zoo and do the comparison 

library(zoo)

zed$possessionName <- na.locf(zed$possessionName)
zed$output_col <- c(1, +(zed$possessionName[-1] != zed$possessionName[-nrow(zed)]))


zed
#   possessionName possessionMarket output_col
#1          Eagles   Boston College          1
#2     Blue Devils             Duke          1
#3     Blue Devils             <NA>          0
#4     Blue Devils             Duke          0
#5          Eagles   Boston College          1
#6     Blue Devils             Duke          1
#7     Blue Devils             Duke          0
#8          Eagles   Boston College          1
#9          Eagles   Boston College          0
#10         Eagles   Boston College          0
#11         Eagles             <NA>          0
#12    Blue Devils             Duke          1
#13    Blue Devils             Duke          0
#14    Blue Devils             <NA>          0
#15         Eagles   Boston College          1

But since you are using tidyverse we can use fill and lag

library(tidyverse)

zed %>%
  fill(possessionName) %>%
   mutate(output_col = c(1, +(possessionName != lag(possessionName))[-1]))
user_39629140

So this works using the zoo package to first get rid of the NAs, and then do the offset-by-one-row comparison.

zed <- zed %>%
    dplyr::mutate(possIdTemp = zoo::na.locf(possessionName)) %>%
    dplyr::mutate(isNewPossession = c(1, ifelse(possIdTemp[2:length(possIdTemp)] != possIdTemp[1:(length(possIdTemp)-1)], 1, 0)))

Please I am open to a better solution to this, in particular i don't like the line of code c(1, ifelse(possIdTemp[2:length(possIdTemp)] != possIdTemp[1:(length(possIdTemp)-1)], 1, 0)) - thank you!

user_55732940
Related