delete rows for duplicate variable in R

Question

I have panel data with duplicate years, but I want to delete the row where job value is smaller:

id  name    year    job
1   Jane    1990    100
1   Jane    1992    200
1   Jane    1993    300
1   Jane    1993    1
1   Jane    1997    400
1   Jane    1997    2
2   Tom     1990    400
2   Tom     1992    500
2   Tom     1993    700
2   Tom     1993    1
2   Tom     1997    900
2   Tom     1997    3

I would want the following:

id  name    year    job
1   Jane    1990    100
1   Jane    1992    200
1   Jane    1993    1
1   Jane    1997    2
2   Tom     1990    400
2   Tom     1992    500
2   Tom     1993    1
2   Tom     1997    3

Would there be a way to do this?

possible duplicate of [removing duplicate units from data frame](http://stackoverflow.com/questions/15489185/removing-duplicate-units-from-data-frame) — RockScience, Apr 04 '14 at 10:15
without bad play on words, it seems to me that this question is a duplicate that should be removed ;) — RockScience, Apr 04 '14 at 10:15

score 2 · Answer 1 · answered Apr 04 '14 at 09:50

you have different possibilities for instance with plyr and dplyr :

# plyr
ddply(tab, .(id, name, year), summarise, job=min(job))
# dplyr
tabg <- group_by(tab, id, name, year)
summarise(tabg, job=min(job))
# basic fonction
aggregate(tab[,"job", drop=FALSE], tab[,3:1], min)

user1981275 · Answer 2 · 2014-04-04T09:52:15.977

0

You could use base R with the function order, as suggested by James:

> tab[order(tab$job),][! duplicated(tab[order(tab$job), c('id', 'year')], fromLast=T), ]
   id name year job
1   1 Jane 1990 100
2   1 Jane 1992 200
3   1 Jane 1993 300
5   1 Jane 1997 400
7   2  Tom 1990 400
8   2  Tom 1992 500
9   2  Tom 1993 700
11  2  Tom 1997 900

edited Apr 04 '14 at 09:52

answered Apr 04 '14 at 09:44

user1981275

13,002
8
72
101

score 0 · Answer 3 · answered Apr 04 '14 at 09:46

You can use ddply for this:

x <- read.table(textConnection("id  name    year    job
 1   Jane    1990    100
 1   Jane    1992    200
 1   Jane    1993    300
 1   Jane    1993    1
 1   Jane    1997    400
 1   Jane    1997    2
 2   Tom     1990    400
 2   Tom     1992    500
 2   Tom     1993    700
 2   Tom     1993    1
 2   Tom     1997    900
 2   Tom     1997    3"),header=T)

library(plyr)
ddply(x,c("id","name","year"),summarise, job=max(job))
  id name year job
1  1 Jane 1990 100
2  1 Jane 1992 200
3  1 Jane 1993 300
4  1 Jane 1997 400
5  2  Tom 1990 400
6  2  Tom 1992 500
7  2  Tom 1993 700
8  2  Tom 1997 900

Note that I have obtained what you asked for in the description. Your example output contradicts this. If you do want your example output, use min instead of max.

score 0 · Answer 4 · answered Apr 04 '14 at 09:49

0

If your data is data frame df

library(data.table)

dt <- as.data.table(df)
dt[, .SD[which.min(job)], by = list(id, name, year)]

answered Apr 04 '14 at 09:49

danas.zuokas

4,551
4
29
39

delete rows for duplicate variable in R

4 Answers4