How to use R to check data consistency (make sure no contradiction between case and value)?

Question

Let's say I have:

Person   Movie    Rating
Sally    Titanic  4
Bill     Titanic  4
Rob      Titanic  4
Sue      Cars     8
Alex     Cars     **9**
Bob      Cars     8

As you can see, there is a contradiction for Alex. All the same movies should have the same ranking, but there was a data error entry for Alex. How can I use R to solve this? I've been thinking about it for a while, but I can't figure it out. Do I have to just do it manually in excel or something? Is there a command on R that will return all the cases where there are data contradictions between two columns?

Perhaps I could have R do a boolean check if all the Movie cases match the first rating of its first iteration? For all that returns "no," I can go look at it manually? How would I write this function?

Thanks

Why should a movie always have the same ranking for each person? (What if Alex just liked Cars more than Sue or Bob?) — David Robinson, Dec 13 '14 at 20:29
You want to remove "Alex" or fix his `Rating` value? Also, are you always assuming that the majority are correct? — David Arenburg, Dec 13 '14 at 20:39

score 4 · Answer 1 · answered Dec 13 '14 at 21:26

Here's a data.table solution

Define the function

Myfunc <- function(x) {
  temp <- table(x)  
  names(temp)[which.max(temp)]
}

library(data.table)

Create a column with the correct rating (by reference)

setDT(df)[, CorrectRating := Myfunc(Rating), Movie][]
#    Person   Movie Rating CorrectRating
# 1:  Sally Titanic      4             4
# 2:   Bill Titanic      4             4
# 3:    Rob Titanic      4             4
# 4:    Sue    Cars      8             8
# 5:   Alex    Cars      9             8
# 6:    Bob    Cars      8             8

Or If you want to remove the "bad" ratings

df[Rating == CorrectRating][]
#    Person   Movie Rating CorrectRating
# 1:  Sally Titanic      4             4
# 2:   Bill Titanic      4             4
# 3:    Rob Titanic      4             4
# 4:    Sue    Cars      8             8
# 5:    Bob    Cars      8             8

score 2 · Answer 2 · edited May 23 '17 at 11:50

It looks like, within each group defined by "Movie", you're looking for any instances of Rating that are not the same as the most common value.

You can solve this using dplyr (which is good at "group by one column, then perform an operation within each group), along with the "Mode" function defined in this answer that finds the most common item in a vector:

Mode <- function(x) {
  ux <- unique(x)
  ux[which.max(tabulate(match(x, ux)))]
}

library(dplyr)
dat %>% group_by(Movie) %>% filter(Rating != Mode(Rating))

This finds all the cases where a row does not agree with the rest of the group. If you instead want to remove them, you can do:

newdat <- dat %>% group_by(Movie) %>% filter(Rating == Mode(Rating))

If you want to fix them, do

newdat <- dat %>% group_by(Movie) %>% mutate(Rating = Mode(Rating))

You can test the above with a reproducible version of your data:

dat <- data.frame(Person = c("Sally", "Bill", "Rob", "Sue", "Alex", "Bob"),
                  Movie = rep(c("Titanic", "Cars"), each = 3),
                  Rating = c(4, 4, 4, 8, 9, 8))

score 1 · Answer 3 · answered Dec 13 '14 at 21:03

If the goal is to see if all the values within a group are the same (or if there are some differences) then this can be a simple application of tapply (or aggregate, etc.) used with a function like var (or compute the range). If all the values are the same then the variance and range will be 0. If it is any other value (outside of rounding error) then there must be a value that is different. The which function can help identify the group/individual.

tapply(dat$Rating, dat$Movie, FUN=var)
which(.Last.value > 0.00001)
tapply(dat$Rating, dat$Movie, FUN=function(x)diff(range(x)))
which(.Last.value != 0)

which( abs(dat$Rating - ave(dat$Rating, dat$Movie)) > 0)
which.max( abs(dat$Rating - ave(dat$Rating, dat$Movie)) )
dat[.Last.value,]

score 0 · Answer 4 · edited May 23 '17 at 12:28

I would add a variable for mode so I can see if there is anything weird going on with the data, like missing data, text, many different answers instead of the rare anomaly,etc. I used "x" as your dataset

# one of many functions to find mode, could use any other
modefunc <- function(x){
  names(table(x))[table(x)==max(table(x))]
}

# add variable for mode split by Movie
x$mode <- ave(x = x$Rating,x$Movie,FUN = modefunc)

# do whatever you want with the records that are different
x[x$Rating != x$mode, ]

If you want another function for mode, try other functions for mode

How to use R to check data consistency (make sure no contradiction between case and value)?

4 Answers4

Linked