Comparing value of a certain row with all previous rows in data.table

user_11740629 0
Viewed 376

I'm having a dataset containing firms involving in a certain category of products. Dataset looks like this:

df <- data.table(year=c(1979,1979,1980,1980,1980,1981,1981,1982,1982,1982,1982),
                 category = c("A","A","B","C","A","D","C","F","F","A","B"))

I want to create a new variable as follows: If a firm enters into a new category that it has not been previously engaged in previous years (not the same year), then that entry is labeld as "NEW", otherwise it will be labeld as "OLD".

As such, the desired outcome will be:

    year   category   Newness
 1: 1979        A     NEW
 2: 1979        A     NEW
 3: 1980        B     NEW
 4: 1980        C     NEW
 5: 1980        A     OLD
 6: 1981        D     NEW
 7: 1981        C     OLD
 8: 1982        F     NEW
 9: 1982        F     NEW
10: 1982        A     OLD
11: 1982        B     OLD

I'm inclined to use data.table as I have over 1.5 million observations, and want to be able to replicate the solution by grouping by firm IDs.

Any help would be greatly appreciated, and thank you in advance.

data.table grouping r group-by
5 Answers

We can assign the first year as "NEW" for each category.

library(data.table)
df[, Newness := c("NEW", "OLD")[(match(year, unique(year)) > 1) + 1], category]
df

#    year category Newness
# 1: 1979        A     NEW
# 2: 1979        A     NEW
# 3: 1980        B     NEW
# 4: 1980        C     NEW
# 5: 1980        A     OLD
# 6: 1981        D     NEW
# 7: 1981        C     OLD
# 8: 1982        F     NEW
# 9: 1982        F     NEW
#10: 1982        A     OLD
#11: 1982        B     OLD

Similarly, in dplyr this can be written as : 

library(dplyr)
df %>%
  group_by(category) %>%
  mutate(Newness =  c("NEW", "OLD")[(match(year, unique(year)) > 1) + 1])
user_39629140

You could use duplicated + ifelse in base R:

transform(df,Newness = ifelse(duplicated(category)==duplicated(df),"New","Old"))
    year category Newness
 1: 1979        A     New
 2: 1979        A     New
 3: 1980        B     New
 4: 1980        C     New
 5: 1980        A     Old
 6: 1981        D     New
 7: 1981        C     Old
 8: 1982        F     New
 9: 1982        F     New
10: 1982        A     Old
11: 1982        B     Old

in data.table you will do:

library(data.table)
df[,Newness := ifelse(duplicated(.SD)==duplicated(category),"New","Old")]
df
    year category Newness
 1: 1979        A     New
 2: 1979        A     New
 3: 1980        B     New
 4: 1980        C     New
 5: 1980        A     Old
 6: 1981        D     New
 7: 1981        C     Old
 8: 1982        F     New
 9: 1982        F     New
10: 1982        A     Old
11: 1982        B     Old
user_83802720

You could solve your problem as follows:

# Method 1:
setDT(df, key = "year")[, Newness := fifelse(year == year[1L], "NEW", "OLD"), category]  

# Method 2
setDT(df, key = "year")[, Newness := c("NEW", "OLD")[match(year, year[1L], 2)], category]

#      year category Newness
# 1:   1979        A     NEW
# 2:   1979        A     NEW
# 3:   1980        B     NEW
# 4:   1980        C     NEW
# 5:   1980        A     OLD
# 6:   1981        D     NEW
# 7:   1981        C     OLD
# 8:   1982        F     NEW
# 9:   1982        F     NEW
# 10:  1982        A     OLD
# 11:  1982        B     OLD
user_108488980

Another data.table option:

df[, Newness := "OLD"][
    unique(df, by="category"), on=.(year, category), Newness := "NEW"]

timing code:

library(data.table)
set.seed(0L)
nr <- 1.5e6
df <- data.table(year=sample(1970:2019, nr, TRUE), category=sample(1e4, nr, TRUE))
setkey(df, year, category)

mtd0 <- function()
    df[, Newness := c("NEW", "OLD")[(match(year, unique(year)) > 1) + 1], category]

mtd1 <- function() 
    df[, Newness := ifelse(duplicated(.SD)==duplicated(category),"New","Old")]

mtd2 <- function()
    df[, Newness := "OLD"][
        unique(df, by="category"), on=.(year, category), Newness := "NEW"]

microbenchmark::microbenchmark(times=3L,
    mtd0(), mtd1(), mtd2())

timings:

Unit: milliseconds
   expr      min       lq      mean   median       uq      max neval
 mtd0() 154.6129 167.5908 182.70500 180.5687 196.7511 212.9334     3
 mtd1() 343.3772 375.0303 395.08653 406.6835 420.9412 435.1989     3
 mtd2()  41.4178  42.0520  45.40527  42.6862  47.3990  52.1118     3
user_19894800

Not an answer, but since efficiency was a concern, I thought of posting the comparison between different methods. This is run on a patent database I'm working on.

> Ronak <- function()
+   df[, Newness := c("NEW", "OLD")[(match(year, unique(year)) > 1) + 1], category]
> B._Christian1 <- function()
+   df[, Newness := fifelse(year == year[1L], "NEW", "OLD"), category]
> B._Christian2 <- function()
+   df[, Newness := c("NEW", "OLD")[match(year, year[1L], 2)], category]
> Onyambu <- function()
+   df[,Newness := ifelse(duplicated(.SD)==duplicated(category),"New","Old")]
> chinsoon12 <- function()
+   df[, Newness := "OLD"][unique(df, by="category"), on=.(year, category),
+                                     Newness := "NEW"]
> 
> microbenchmark::microbenchmark(times=3L,
+                                Ronak(), B._Christian1(), B._Christian2(), Onyambu(), chinsoon12())
Unit: milliseconds
            expr       min        lq      mean    median        uq       max neval
         Ronak()  482.6191  482.7456  484.3963  482.8720  485.2849  487.6977     3
 B._Christian1()  240.3175  242.9452  243.9646  245.5729  245.7881  246.0033     3
 B._Christian2()  274.8113  278.3835  279.7271  281.9557  282.1850  282.4142     3
       Onyambu() 2374.6428 2377.0848 2379.3771 2379.5267 2381.7442 2383.9617     3
    chinsoon12()  200.6551  200.8337  202.5799  201.0123  203.5423  206.0723     3

Thanks all again.

user_117406290
Related