Categories
Development

How to feed a column with a join ? (update join)

I have a dataframe with financials data for an index named S&P500 (date, ticker, price).

I have another dataframe with (date, ticker, Action).

Action == “IN” when a ticker is added to the index at date

Action == “OUT” when a ticker is removed from the index at date

And I want to add 2 new columns to my main df, with a TRUE for the key (date, ticker), when the couple is found in the second df.

Today I have something like :

library(tidyverse)

Ticker = c("DOW","MSCI","BJS")
Action = c("IN","IN","OUT")
Date = c("2019-04-02", "2018-04-04","2010-04-29")

sp500_InOut = data.frame(Ticker, Action, Date)

Ticker = c("DOW", "DOW","MSCI", "MSCI","BJS","BJS")
ref.date = c("2019-04-02", "2018-04-04", "2018-04-04","2010-04-29", "2018-04-04","2010-04-29")
close = c(1,2,3,4,5,6)

sp500_OHLC = data.frame(Ticker, ref.date, close)



#Add In / Out 
sp500_OHLC = sp500_OHLC %>% mutate(IN=FALSE) %>% mutate(OUT=FALSE)

tIN  = sp500_InOut %>% filter(Action=="IN")  %>% select(Date, Ticker)
tOUT = sp500_InOut %>% filter(Action=="OUT") %>% select(Date, Ticker)

Then in order to update the columns sp500_OHLC$IN and sp500_OHLC$OUT I managed to join like this :

#not working
sp500_OHLC %>% left_join(tIN,  by = c("Ticker" = "Ticker", "ref.date" = "Date"))  %>% mutate(sp500_OHLC=tIN$Action)

But I can’t figure out I can now update my columns.

Thanks !!

Leave a Reply

Your email address will not be published. Required fields are marked *