0

I have this mapping table.


df <- data.frame(L = c(1, 2, 3, 4, 5, 6, 7, 8, 9),
                  fli.1 = c(0, 500, 1000, 2000, 3000, 5000, 10000, 20000, 50000),
                  fli.2 = c(0, 500, 1000, 2000, 3000, 5000, 10000, 20000, 50000),
                  fli.3 = c(0, 500, 1000, 2000, 3000, 5000, 10000, 20000, 50000),
                  fli.4 = c(0, 500, 1000, 2000, 3000, 5000, 10000, 20000, 50000),
                  fli.5 = c(0, 500, 1000, 2000, 3000, 5000, 10000, 20000, 50000))

I have another dataframe which is

d <- data.frame(quantity = c(300, 368, 568, 20, 1000, 37659, 45000, 2500, 4500, 78453, 1200, 1589), fli = c("fli.1", "fli.1", "fli.4", "fli.5", "fli.2", "fli.2", "fli.5", "fli.1", "fli.2", "fli.2", "fli.3", "fli.4"))

i need to create another vector in dataframe d such that it is taking values from dataframe df. vertically look for less than quantity and horizontally match fli.

for e.g. for 37659 it would be 20000 which is 8th row and from column fli.1

This is just an demonstration.

I need to apply it for huge dataset.

i tried creating a matrix but it takes time. i need something faster.

user438383
  • 5,716
  • 8
  • 28
  • 43
Mohit
  • 51
  • 4
  • For future reference, you can edit your question to add the second data.frame. I've just done this for you. – SymbolixAU Jun 13 '23 at 03:27
  • This looks very much like a reshape/pivot from wide to long (https://stackoverflow.com/q/2185252/3358272, https://stackoverflow.com/q/68058000/3358272) and a merge/join (https://stackoverflow.com/q/1299871/3358272, https://stackoverflow.com/q/5706437/3358272, (data.table) https://stackoverflow.com/q/34598139/3358272). – r2evans Jun 13 '23 at 14:02
  • I am still looking for a better answer. – Mohit Jun 29 '23 at 17:42

1 Answers1

0

If you have the memory (and assuming each fli column is ordered) you could try:

library(data.table)
setDT(df)
setDT(d)
tmp <- melt(df[, !"L"], id.vars = NULL, measure.vars = patterns("^fli"))
d[, It := tmp[.SD, on = .(variable = fli, value <= quantity), mult = "last", j = x.value]]
rm(tmp)

#     quantity    fli    It
#        <num> <char> <num>
#  1:      300  fli.1     0
#  2:      368  fli.1     0
#  3:      568  fli.4   500
#  4:       20  fli.5     0
#  5:     1000  fli.2  1000
#  6:    37659  fli.2 20000
#  7:    45000  fli.5 20000
#  8:     2500  fli.1  2000
#  9:     4500  fli.2  3000
# 10:    78453  fli.2 50000
# 11:     1200  fli.3  1000
# 12:     1589  fli.4  1000

If the data is not ordered this should work:

d[, It := tmp[.SD, on = .(variable = fli, value = quantity), roll = Inf, j = x.value]]
s_baldur
  • 29,441
  • 4
  • 36
  • 69
  • this code works for me. But it takes too much run time. in my question d has fewer entries but i am applying it to 2000000 entries. – Mohit Jun 29 '23 at 17:35
  • Could you please help me on one more stuff. Now instead of the value if I need the index, for e.g. It should display 1,1,2,1,3,8... etc. in the table above in df the values are same in every column but some times it is different. – Mohit Aug 17 '23 at 14:26