R: Merge dataframes, identical ticker leading to incorrect result

Viewed 46

I have 2 seperate dataframes. The first dataframe, df1, have 7 tickers (where two of the tickers are identical) and 7 unique dates that correspond to unique company-specific events.

The second dataframe, df2, have returns for the tickers from 01.01.2000 until 10.09.2020. I want to merge the two dataframes together in order to get a new column in df2 replicating the date of the company-specific event (in a new column) for all observation in df2 for a given ticker.

df2 <- left_join(df2, df1, by = "Ticker")

I do, however, have a problem with the two identical tickers. When I merge them together I get a problem since the tickers are identical, leading to the company-specific event date for one of the events being incorrect. Anyone know how I can handle this?

Thanks in advance! Hope everyone is doing fine

Update: Under you can find information on the 2 dataframes

library(lubridate)
library(quantmod)
df1 <- data.frame(Ticker = c("DGX", "FAF", "TM", "COF", "FB", "MSFT",   "FB"),
Attack_date = ymd(c("2019-05-14", "2019-05-24", "2019-03-29", "2019-07-29", "2019-09-04", "2020-01-22", "2018-03-19")))

tickers <- unique(df1$Ticker)

getSymbols(Symbols = tickers, return.class = "zoo",
       from = "2003-01-02",
       to = "2020-09-07") # Downloading data for all tickers in   breach_2

df2 <- data.frame(Company = character (),
                     Date = as.Date(character()),
                     Adjusted = numeric(),
                     Attack_Date = as.Date(character()),
                     stringsAsFactors = F) # Creating df to "paste" all returns and dates in

for (i in 1:length(tickers)){
df <- cbind(Company = rep(tickers[i], nrow(get(tickers[i]))),
          fortify.zoo(get(tickers[i])) [, c(1,7)])

colnames(df) [c(2,3)] <- c("Date", "Adjusted") # Renaming columns

df2 <- rbind(df2, df)# Binding the rows in df_tickers and df together to create new df 
}                  

After this I want to merge df1 and df2 together by adding the column named "Attack_Date" from df1 to df2.

0 Answers
Related