Extracting unique rows in R data table based on another column

user_14007007 0
Viewed 648

I have a larger data frame that contains 5 years of data. I'd like to remove rows that are duplicated for a given year without removing those rows from later years. Here is an example.

Year  Color    X   Y
2014  red      1   3
2014  red      1   3
2014  blue     1   3
2015  red      1   3
2015  blue     1   3
2015  yellow   1   3

In this example, I'd like to remove the duplicated row for Year 2014 Color Red, without removing the row for 2015 red. I tired the following code:

df <- df[!duplicated(Color), by = (Year)]

This code removes the duplicated 2014 red, but also the 2015 blue and 2015 red.

data.table r
3 Answers

Subset in the j part :

library(data.table)
setDT(df)
df[, .SD[!duplicated(Color)], Year]

#   Year  Color X Y
#1: 2014    red 1 3
#2: 2014   blue 1 3
#3: 2015    red 1 3
#4: 2015   blue 1 3
#5: 2015 yellow 1 3

Another approach is to group by Year and Color and select the first row.

df[, .SD[seq_len(.N) == 1], .(Year, Color)]

Or the most easy one is to select unique rows and specify by :

unique(df, by = c('Year', 'Color'))

data

df <- structure(list(Year = c(2014L, 2014L, 2014L, 2015L, 2015L, 2015L
), Color = c("red", "red", "blue", "red", "blue", "yellow"), 
X = c(1L, 1L, 1L, 1L, 1L, 1L), Y = c(3L, 3L, 3L, 3L, 3L, 
3L)), class = "data.frame", row.names = c(NA, -6L))
user_39629140

Another alternative from dplyr package. Data taken from the solution provided by Ronak Shah.

library(dplyr)
df %>% group_by(Color) %>% 
distinct()

Output

# A tibble: 5 x 4
# Groups:   Color [3]
#   Year Color      X     Y
# <int> <chr>  <int> <int>
#1  2014 red        1     3
#2  2014 blue       1     3
#3  2015 red        1     3
#4  2015 blue       1     3
#5  2015 yellow     1     3
user_133305970

Unless I am missing something, are you not simply asking how to remove duplicates from a data frame?

library(dplyr)
df %>%
   distinct()
user_29845510
Related