How to Find a value between columns

Asked

Viewed 72 times

1

x = data.frame(Índice = c(1,2,3,4,5,6,7,8,9,10),
                Data = as.Date(c("2019-01-22", "2019-01-21", "2019-01-20", "2019-01-18", "2019-01-17", "2019-01-16", "2019-01-15", "2019-01-14", "2019-01-11", "2019-01-10")),
                Valor = c(20,25,28,42,59,18,72,63,18,42), 
                Busca = c(59,18,0,0,42,28,0,18,0,50))

I have a data frame x, and in the column of the name Busca, where the value is greater than "0", I need to search this number in the column Valor and locate the first value equal to the one searched by following the indexes and insert it into a new column in the same index that is in the column Valor.

In this way, for example, the value "18" would be returned in the index "6" in a new column. The value "42" in the index "10" and so on. As an example I leave the same data frame below with the column Encontrado how the search should return.

x = data.frame(Índice = c(1,2,3,4,5,6,7,8,9,10),
                Data = as.Date(c("2019-01-22", "2019-01-21", "2019-01-20", "2019-01-18", "2019-01-17", "2019-01-16", "2019-01-15", "2019-01-14", "2019-01-11", "2019-01-10")),
                Valor = c(20,25,28,42,59,18,72,63,18,42), 
                Busca = c(59,18,0,0,42,28,0,18,0,50),
                Encontrado = c(0,0,0,0,59,18,0,0,18,42))
r

2 answers

1


One can use %in% to create a logical index.
As the new values are equal to the vector Valor, I’ll initialize the vector Encontrado with the vector Valor.
Then I replace only the ones where the vector values Busca are not by zero. 

x$Encontrado <- x$Valor
i <- !x$Valor %in% x$Busca
x$Encontrado[i] <- 0
x
#   Índice       Data Valor Busca Encontrado
#1       1 2019-01-22    20    59          0
#2       2 2019-01-21    25    18          0
#3       3 2019-01-20    28     0         28
#4       4 2019-01-18    42     0         42
#5       5 2019-01-17    59    42         59
#6       6 2019-01-16    18    28         18
#7       7 2019-01-15    72     0          0
#8       8 2019-01-14    63    18          0
#9       9 2019-01-11    18     0         18
#10     10 2019-01-10    42    50         42

Note that this code can be simplified. Instead of explicitly creating the index i, can be

x$Encontrado[!x$Valor %in% x$Busca] <- 0

  • That would solve part of the problem. But note that in the value "Found" I left Found = c(0,0,0,0,59,18,0,0,18,42), the value "28" does not appear equal in your table, because this value is at a date prior to which it was searched, so it would not have to appear. The same thing would happen with "42", which would have to appear only in the last position, because the date from where it is being sought is older. Only the search has to be done from the date found in the "Search" column, following on older dates.

0

Would this be? 

x = data.frame(Índice = c(1,2,3,4,5,6,7,8,9,10),
            Data = as.Date(c("2019-01-22", "2019-01-21", "2019-01-20", "2019-01-18", "2019-01-17", "2019-01-16", "2019-01-15", "2019-01-14", "2019-01-11", "2019-01-10")),
            Valor = c(20,25,28,42,59,18,72,63,18,42),
            Busca = c(59,18,0,0,42,28,0,18,0,50))

all items in the new column equal to 0

x$Encontrado <- 0

indices in which x$Busca > 0, assume x$Valor

x$Encontrado[x$Busca>0] <- x$Valor[x$Busca>0]
x
#   Índice       Data Valor Busca Encontrado
# 1       1 2019-01-22    20    59         20
# 2       2 2019-01-21    25    18         25
# 3       3 2019-01-20    28     0          0
# 4       4 2019-01-18    42     0          0
# 5       5 2019-01-17    59    42         59
# 6       6 2019-01-16    18    28         18
# 7       7 2019-01-15    72     0          0
# 8       8 2019-01-14    63    18         63
# 9       9 2019-01-11    18     0          0
# 10     10 2019-01-10    42    50         42

  • No. Note that I left the "Found" column after the search Found = c (0.0,0,0,59,18,0,18,18,42). Only the values that appear in the "Search" column that would have to appear in the "Found" column and also a very important thing is that they would only appear from the searched index. As the example of the number "42" that would appear only in the last line of the data frame, even though it already appeared in the column "Value" previous to what appeared column "Search". What matters is from the index of the column "Search", what was left behind has no value.

Browser other questions tagged r

You are not signed in. Login or sign up in order to post.