Remove duplicate year rows by groups

user_12709265 0
Viewed 139

I have a data.table of the following form:-

data <- data.table(group = rep(1:3, each = 4), 
    year = c(2011:2014, rep(2011:2012, each = 2),
             2012, 2012, 2013, 2014), value = 1:12)

This is only an abstract of my data.

So group 2 has 2 values for 2011 and 2012. And group 3 has 2 values for the year 2012. I want to just keep the first row for all the duplicated years.

So, in effect, my data.table will become the following:-

data <- data.table(group = c(rep(1, 4), rep(2, 2), rep(3, 3)),
                   year = c(2011:2014, 2011, 2012, 2012, 2013, 2014),
                   value = c(1:5, 7, 9, 11, 12))

How can I achieve this? Thanks in advance.

data.table r
5 Answers

Try this data.table option with duplicated

> data[!duplicated(cbind(group, year))]
   group year value
1:     1 2011     1
2:     1 2012     2
3:     1 2013     3
4:     1 2014     4
5:     2 2011     5
6:     2 2012     7
7:     3 2012     9
8:     3 2013    11
9:     3 2014    12
user_121587570

For data.tables you can pass by argument to unique -

library(data.table)

unique(data, by = c('group', 'year'))

#   group year value
#1:     1 2011     1
#2:     1 2012     2
#3:     1 2013     3
#4:     1 2014     4
#5:     2 2011     5
#6:     2 2012     7
#7:     3 2012     9
#8:     3 2013    11
#9:     3 2014    12
user_39629140

Using base R

subset(data, !duplicated(cbind(group, year)))
user_37322710

One solution would be to use distinct from dplyr like so:

library(dplyr)
data %>% 
  distinct(group, year, .keep_all = TRUE)

Output:

   group year value
1:     1 2011     1
2:     1 2012     2
3:     1 2013     3
4:     1 2014     4
5:     2 2011     5
6:     2 2012     7
7:     3 2012     9
8:     3 2013    11
9:     3 2014    12
user_148022850

This should do the trick:

library(tidyverse)
data %>% 
  group_by(group, year) %>% 
  filter(!duplicated(group, year))
user_162816730
Related