Quick search in data.table or quick subset

I have a DF with 800k+ rows with repeated (random) values. For each row I need to take a value and find an index of a new row(s) with same value. E.g. "asd" - where else do I see it? The index of the current row is NOT needed.

My current solution: subset a DF and create a temp frame/table by removing current row. Problem - it takes a minute per 1000 iterations. So 800+k rows will take me 13 hours to run. Any ideas? Thanks!

Running on original DF (not subsetted) is < 1 second, but as you can imagine it gives me the index of the current row.

Edit: My real-life DF is more than 1 column. Example below is simplified. I need to take V1[1] and get row numbers of other V1 with value of V1[1], then repeat for V1[2] and so on for each row

library(fastmatch)
library(stringi)
set.seed(12345)
V1 = stringi::stri_rand_strings(800000, 3)
df0 = as.data.table(V1)
mapped = matrix("",nrow=800000)

print(Sys.time())
for (i in 1:1000) {
  tmp_df = df0[-i,] #This takes very long time!!!
  mapped[i] = fmatch(df0$V1[i],tmp_df$V1)
}
print(Sys.time())

View(mapped)

Solution

Data:

library("data.table")
set.seed(12345)
V1 = stringi::stri_rand_strings(80, 3)
df0 <- data.table( sample(V1, 100, replace = TRUE ))

Code:

df0[, id := list(list(.I)), by = V1]  # integer id

Output:

head(df0, 10)
#     V1          id
# 1: iuR      1,2,21
# 2: iuR      1,2,21
# 3: KXc           3
# 4: LwA           4
# 5: pYn           5
# 6: qoN        6,66
# 7: 5Xt           7
# 8: wBH        8,77
# 9: V9r     9,39,54
# 10: 9ks 10,28,42,48

EDIT - Removed Current Index:

df0[, id2 := 1:.N ]
df0[, id := list(list(unlist(id)[ unlist(id) != .I  ] )), by = id2 ]
df0[, id2 := NULL ]
df0[ lengths(id) > 0, ]
head( df0, 10 )
#     V1       id
# 1: iuR     2,21
# 2: iuR     1,21
# 3: KXc         
# 4: LwA         
# 5: pYn         
# 6: qoN       66
# 7: 5Xt         
# 8: wBH       77
# 9: V9r    39,54
# 10: 9ks 28,42,48