I have two data frames that I want to merge:
df1
:
Date Company Return
1988-09-30 BELSHIPS 0.087
1988-10-31 BELSHIPS 0.021
1988-11-30 BELSHIPS 0.015
1988-12-30 BELSHIPS -0.048
1988-09-30 GOODTECH 0.114
1988-10-31 GOODTECH 0.074
1988-11-30 GOODTECH NA
1988-12-30 GOODTECH NA
1988-09-30 LABOREMUS -0.014
1988-10-31 LABOREMUS 0.024
1988-11-30 LABOREMUS 0.017
1988-12-30 LABOREMUS 0.021
df2
:
Company
BELSHIPS
BIK BOK
FARSTAD SHIPPING
GOODTECH
GYLDENDAL
I want to merge the two data frames by Company, but I only want to include companies that have no NAs in return. The new data frame should therefore look like this:
df3
:
Date Company Return
1988-09-30 BELSHIPS 0.087
1988-10-31 BELSHIPS 0.021
1988-11-30 BELSHIPS 0.015
1988-12-30 BELSHIPS -0.048
Only the company BELSHIPS is included, because GOODTECH has NAs in Return and LABOREMUS is not included in df2
.
I have tried df3 <- merge(df2, df1[!is.na(df1$Return)], by = "Company")
which doesn't work, because it only omits the rows with NAs, not the entire company.
Any suggestions as to how I can fix this?
Base R solution:
# Select companies that have NA
# You can also use unique on this
foo <- df1$Company[is.na(df1$Return)]
# Subset data frame where Company is within df2 and doesn't have NA
subset(df1, Company %in% df2$Company & !Company %in% foo)
# Date Company Return
# 1 1988-09-30 BELSHIPS 0.087
# 2 1988-10-31 BELSHIPS 0.021
# 3 1988-11-30 BELSHIPS 0.015
# 4 1988-12-30 BELSHIPS -0.048
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments