Creating an ID variable based on 3 common variables in R

Question

I have the following dataframe (below) which is a subset of my full dataset. I need to define the same ID for each entries with the same LAT/LONG and landed date. I previously ordered LANDEDDATE from the earliest to latest date.

 > dput(df2)
structure(list(LATITUDE = c(43.35, 43.35, 43.35, 43.35, 43.35, 
43.35, 43.35, 43.35, 43.5166, 43.5166, 43.5166, 43.5166, 43.5166, 
43.5166, 43.5166, 42.9833, 42.9833, 42.9833, 42.9666, 42.9666
), LONGITUDE = c(-60.6163, -60.6165, -60.7167, -60.7166, -60.7163, 
-60.716, -60.7169, -60.7166, -59.9169, -59.9168, -59.9169, -59.9166, 
-59.9166, -59.916, -59.916, -61.8333, -61.8333, -61.8333, -61.9161, 
-61.9161), LANDEDDATE = structure(c(11171, 11171, 11183, 11183, 
11183, 11183, 11183, 11183, 11192, 11192, 11192, 11192, 11192, 
11192, 11192, 11210, 11210, 11210, 11210, 11210), class = "Date"), 
    sppCODE = c(251L, 251L, 251L, 251L, 251L, 251L, 251L, 251L, 
    251L, 251L, 251L, 251L, 251L, 256L, 251L, 256L, 252L, 251L, 
    251L, 252L), LIVEW = c(0.337, 0.471, 0.238, 0.772, 0.178, 
    0.416, 0.535, 0.356, 0.442, 0.663, 0.442, 0.497, 0.276, 0.032, 
    0.828, 0.035, 0.011, 1.224, 1.025, 0.072), SPECIES = structure(c(7L, 
    7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 
    7L, 7L, 7L, 7L), .Label = c("Albacore Tuna", "Bigeye Tuna", 
    "Bluefin Tuna", "Mako", "Porbeagle", "Shark, UNSP", "Swordfish", 
    "Tuna, UNSP", "White Marlin", "Yellowfin Tuna"), class = "factor")), .Names = c("LATITUDE", 
"LONGITUDE", "LANDEDDATE", "sppCODE", "LIVEW", "SPECIES"), row.names = c(19L, 
20L, 13L, 14L, 15L, 16L, 17L, 18L, 6L, 7L, 8L, 9L, 10L, 11L, 
12L, 1L, 2L, 3L, 4L, 5L), class = "data.frame")

This is what I've done so far but I can't figure out the rest.

    df2$setID<-"NA" #I created an empty setID.
    > head(df2)
   LATITUDE LONGITUDE LANDEDDATE sppCODE LIVEW   SPECIES setID
19    43.35  -60.6163 2000-08-02     251 0.337 Swordfish    NA
20    43.35  -60.6165 2000-08-02     251 0.471 Swordfish    NA
13    43.35  -60.7167 2000-08-14     251 0.238 Swordfish    NA
14    43.35  -60.7166 2000-08-14     251 0.772 Swordfish    NA
15    43.35  -60.7163 2000-08-14     251 0.178 Swordfish    NA
16    43.35  -60.7160 2000-08-14     251 0.416 Swordfish    NA
unique<-df2[which(!duplicated(df2[,1:3])),] #This is each entry that are NOT duplicate
unique2$setID<-1:13 # Ranked from 1:13
> head(unique) #looks like that
   LATITUDE LONGITUDE LANDEDDATE sppCODE LIVEW   SPECIES setID
19    43.35  -60.6163 2000-08-02     251 0.337 Swordfish     1
20    43.35  -60.6165 2000-08-02     251 0.471 Swordfish     2
13    43.35  -60.7167 2000-08-14     251 0.238 Swordfish     3
14    43.35  -60.7166 2000-08-14     251 0.772 Swordfish     4
15    43.35  -60.7163 2000-08-14     251 0.178 Swordfish     5
16    43.35  -60.7160 2000-08-14     251 0.416 Swordfish     6
rep<-df2[which(duplicated(df2[,1:3])),] #This is all my replicates

I need to allocate the setID of my unique dataframe to the corresponding sets (replicates with same LAT/LONG and LANDEDDATE) in the rep dataframe. Any advices would be appreciated!

Answer 1

You can use interaction

within(df2, {id = interaction(LATITUDE,LONGITUDE,LANDEDDATE)})
  LATITUDE LONGITUDE LANDEDDATE sppCODE LIVEW   SPECIES                        id
19    43.35  -60.6163 2000-08-02     251 0.337 Swordfish 43.35.-60.6163.2000-08-02
20    43.35  -60.6165 2000-08-02     251 0.471 Swordfish 43.35.-60.6165.2000-08-02
13    43.35  -60.7167 2000-08-14     251 0.238 Swordfish 43.35.-60.7167.2000-08-14
14    43.35  -60.7166 2000-08-14     251 0.772 Swordfish 43.35.-60.7166.2000-08-14
15    43.35  -60.7163 2000-08-14     251 0.178 Swordfish 43.35.-60.7163.2000-08-14
.......

EDIT add drop =TRUE option(more efficient)(@Matthew comment) , and change separator(smarter)

res1 <- within(df2, {id = interaction(
                           sprintf("%5.2f", LATITUDE),
                           sprintf("%5.2f", LONGITUDE),
                           LANDEDDATE,drop=TRUE,sep=':')})

res1
   LATITUDE LONGITUDE LANDEDDATE sppCODE LIVEW   SPECIES                      id
19  43.3500  -60.6163 2000-08-02     251 0.337 Swordfish 43.35:-60.62:2000-08-02
20  43.3500  -60.6165 2000-08-02     251 0.471 Swordfish 43.35:-60.62:2000-08-02
13  43.3500  -60.7167 2000-08-14     251 0.238 Swordfish 43.35:-60.72:2000-08-14
14  43.3500  -60.7166 2000-08-14     251 0.772 Swordfish 43.35:-60.72:2000-08-14
15  43.3500  -60.7163 2000-08-14     251 0.178 Swordfish 43.35:-60.72:2000-08-14
16  43.3500  -60.7160 2000-08-14     251 0.416 Swordfish 43.35:-60.72:2000-08-14
17  43.3500  -60.7169 2000-08-14     251 0.535 Swordfish 43.35:-60.72:2000-08-14
18  43.3500  -60.7166 2000-08-14     251 0.356 Swordfish 43.35:-60.72:2000-08-14
6   43.5166  -59.9169 2000-08-23     251 0.442 Swordfish 43.52:-59.92:2000-08-23
7   43.5166  -59.9168 2000-08-23     251 0.663 Swordfish 43.52:-59.92:2000-08-23

Answer 2

That sounds like a lot of wrangling!

Why not create a "key" with something like paste

paste(df2$LONGITUDE, df2$LATITUDE, df2$LANDEDDATE)

then you can use factor and coerce it back to its underlying integers:

df2$setID <- as.integer(factor(paste(df2$LONGITUDE, df2$LATITUDE, df2$LANDEDDATE)))

You could also do this with data.table if your data is large (or even if its not!).

library(data.table)
df2 <- data.table(df2, key=c('LATITUDE', 'LONGITUDE', 'LANDEDDATE'))

df2[, setID := (.GRP), by=c('LATITUDE', 'LONGITUDE', 'LANDEDDATE')]