Viðar Ingason Viðar Ingason - 3 months ago 15
R Question

Create new variable based on certain condition

I'm trying to create a new variable based on certain conditions. Below is a sample of the data.

df <- data.frame(country = rep(c("A","B"), each = 9),
year = c(2000,2001,2008,2009,2010,2011,2012,2013,2014,1995,1996,1997,1998,1999,2000,2001,2002,2003),
number = c(1,2,9,10,11,12,13,14,15,1,2,3,4,5,6,7,8,9))


What I want to do is to check when year = 2000 AND number = 1. If this condition is met I want the variable 'When' to be "dot.com" for the year 2000 and all subsequent years for this particular country. If the condition above is not met I want the 'When' variable to be "other".

Here is what I would like to achieve.

structure(list(country = structure(c(1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("A",
"B"), class = "factor"), year = c(2000, 2001, 2008, 2009, 2010,
2011, 2012, 2013, 2014, 1995, 1996, 1997, 1998, 1999, 2000, 2001,
2002, 2003), number = c(1, 2, 9, 10, 11, 12, 13, 14, 15, 1, 2,
3, 4, 5, 6, 7, 8, 9), when = structure(c(1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("dot.com",
"other"), class = "factor")), .Names = c("country", "year", "number",
"when"), row.names = c(NA, -18L), class = "data.frame")

Answer

We can try with data.table

library(data.table)
setDT(df)[,when :=if(any(year==2000 & number==1)) "dot.com" else "other" , by = country]
df
#    country year number    when
# 1:       A 2000      1 dot.com
# 2:       A 2001      2 dot.com
# 3:       A 2008      9 dot.com
# 4:       A 2009     10 dot.com
# 5:       A 2010     11 dot.com
# 6:       A 2011     12 dot.com
# 7:       A 2012     13 dot.com
# 8:       A 2013     14 dot.com
# 9:       A 2014     15 dot.com
#10:       B 1995      1   other
#11:       B 1996      2   other
#12:       B 1997      3   other
#13:       B 1998      4   other
#14:       B 1999      5   other
#15:       B 2000      6   other
#16:       B 2001      7   other
#17:       B 2002      8   other
#18:       B 2003      9   other