Substitute duplicates with different variables

user_14256885 0
Viewed 53

I am trying to do the following. In my panel dataset I observe an individual at different ages. The data is imprecise though, so I might have the same age repeated for an individual over different years (e.g. [id=1, year=2000, age=40] and [id=1, year=2001, age=40]. I want to identify these observation and substitute them with a different variable, that corresponds to 'year' - 'year of birth'.

So I'd have something like:

id year age year_birth age_dv
1 2000 40 1960 40
1 2001 40 1960 41
1 2002 42 1960 42

Which I want to replace with:

id year age year_birth age_dv
1 2000 40 1960 40
1 2001 41 1960 41
1 2002 42 1960 42

I have done this in Stata before and there I would created a column that identifies duplicates according to id and age (for instance 1 if it's a duplicate) and replace those variables using the condition on this new "tag" column.

Final result looks like:

id year age year_birth age_dv duplicate
1 2000 40 1960 40 0
1 2001 41 1960 41 1
1 2002 42 1960 42 0

Can I do the same in R? Or is there a better way?

duplicates r
2 Answers

With dplyr you could use

library(dplyr)
dd %>% 
  group_by(id) %>% 
  mutate(duplicate=duplicated(age)+0,
         age = year-year_birth)

Tested with

dd <- read.table(text="
id  year    age year_birth  age_dv
1   2000    40  1960    40
1   2001    40  1960    41
1   2002    42  1960    42", header=TRUE)
user_23720640

An option with data.table would be

library(data.table)
setDT(df)[, c('duplicate', 'age') := .(+(duplicated(age)), 
        year - year_birth), .(id)]
df
#   id year age year_birth age_dv duplicate
#1:  1 2000  40       1960     40         0
#2:  1 2001  41       1960     41         1
#3:  1 2002  42       1960     42         0

data

df <- structure(list(id = c(1L, 1L, 1L), year = 2000:2002, age = c(40L, 
40L, 42L), year_birth = c(1960L, 1960L, 1960L), age_dv = 40:42), 
class = "data.frame", row.names = c(NA, -3L))
user_37322710
Related