CODEWITHSUNDEEP
rdata.table
W Barker
W Barker

Reputation: 312

Selectively alter a column in one data.table based on multiple conditions in another data.table of different dimensions

Example:

Trade <- data.table (
  Ticker = c("ABC", "DEF", "GHI", "GHI", "JKL", "JKL", "MNO"),
  TPrice = c(100, 200, 300, 305, 400, 405, 500),
  Code = c("O", "O", "O", "C", "O", "C", "C")
)

MTM <- data.table (
  Ticker = c("ABC", "DEF", "GHI", "JKL", "MNO"), 
  Flag = c(1, 0, 0, 1, 1), 
  TPrice = 0L
)

I want the TPrice in the MTM data.table to become non-zero only when the same Ticker shown in both the Trade and the MTM data.tables have both a Flag of 1 (in the MTM data.table) and the Code is an "O" (in the Trade data.table). The resulting entry in the MTM TPrice column is the TPrice from the Trade data.table when those conditions are met.

Results I'm looking for:

MTM <- data.table (
  Ticker = c("ABC", "DEF", "GHI", "JKL", "MNO"), 
  Flag = c(1, 0, 0, 1, 1), 
  TPrice = c(100, 0, 0, 400, 0)
)

    Ticker Flag TPrice
1:    ABC    1    100
2:    DEF    0      0
3:    GHI    0      0
4:    JKL    1    400
5:    MNO    1      0

SO Posts Consulted

Either the question was not the same, often because my data tables are of uneven dimensions, or I could not adapt the answer to my problem (still learning R) or I simply did not understand the answer to attempt an adaptation (I asked questions in the comment sections):

Subset a data frame based on another

How extract values of a data.table based on multiple conditions?

Update data.table based on multiple columns and conditions

Efficient way to subset data.table based on value in any of selected columns

How can one work fully generically in data.table in R with column names in variables

Multiple variable filters in r

Filter data.table on same condition for multiple columns

R Data.table divide values in column based on another column

The last SO post seemed the closest to my problem due to the uneven dimensions posited in the question, but I could not adapt the solution to my problem.

I would very much appreciate some help here.

Upvotes: 0

Views: 94

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

You can join the two dataframes and then check for the condition for each value of Ticker.

library(data.table)

Trade[MTM, on = 'Ticker'][, .(Tprice = if(any(Code == 'O' & Flag == 1)) 
           TPrice[Code == 'O' & Flag == 1] else 0), .(Ticker, Flag)]

#   Ticker Flag Tprice
#1:    ABC    1    100
#2:    DEF    0      0
#3:    GHI    0      0
#4:    JKL    1    400
#5:    MNO    1      0

Upvotes: 2

Related Questions