2

Hi I have two data frames with different lengths:

nrow(artdataframe1)
[1] 78
nrow(spdataframe)
[1] 7607

The two data frames have date columns as yyyymmdd 

> head(artdataframe1)
   artdate artprice
1 19870330  $83.60 
2 19871111 $113.60 
3 19881128  $78.00 
4 19890509  $92.50 
5 19890531  $68.00 
6 19890801 $115.90 
> head(spdataframe)
  SP500close SP500date
1     289.20  19870330
2     291.70  19870331
3     292.39  19870401
4     293.63  19870402
5     300.41  19870403
6     301.95  19870406

I would like to join these two dataframes matching by date. It means the lines from artdataframe1 (nrow78) will join to spdataframe (nrow7607). It means there will be a lot of NA values in the ones that do not match which is fine.

I pulled both of these dataframes from the same .csv and already have removed the NA lines in artdataframe1 with 

artdataframe1 <- artdataframe[!is.na(artdataframe[,1]),]

So that is some back ground. I tried to use this command but It is not matching per the date as I would expect: 

newdf <- merge(spdataframe, artdataframe1, by = intersect(names(SP500date), names(artdate)),
      by.spdataframe = by, by.artdataframe1 = by, all = FALSE, all.spdataframe = all, all.artdataframe1 = all,
      sort = TRUE, suffixes = c(".spdataframe",".artdataframe1"),
      incomparables = NULL)

Any further guidance / assistance would be appreciated.

Thanks

Let me add this, this is the class of each dataframe: 

> str(artdataframe1)
'data.frame':   78 obs. of  2 variables:
 $ artdate : int  19870330 19871111 19881128 19890509 19890531 19890801 19891127 19891130 19900515 19900517 ...
 $ artprice: Factor w/ 74 levels "","$102.10 ",..: 58 10 49 66 36 11 52 69 21 18 ...
> str(spdataframe)
'data.frame':   7607 obs. of  2 variables:
 $ SP500close: num  289 292 292 294 300 ...
 $ SP500date : int  19870330 19870331 19870401 19870402 19870403 19870406 19870407 19870408 19870409 19870410 ...

They are both integers - will this make a difference when merging?

Andrew Bannerman
  • 1,235
  • 2
  • 16
  • 36
  • 1
    `newdf <- merge(spdataframe, artdataframe1, by.x = "artdate", by.y = "SP500date", all = TRUE)` – HubertL Jun 02 '17 at 19:29
  • `spdataframe$artprice <- artdataframe1$artprice[artdataframe1$artdate %in% spdataframe$SP500date]` – M-- Jun 02 '17 at 19:32

1 Answers1

1

You should ceck out dplyr::*_join() functions.

library(dplyr)

full_join(df1, df2, by = c("artdate" = "SP500date"))

#> # A tibble: 11 x 3
#>     artdate artprice SP500close
#>       <int>    <chr>      <dbl>
#>  1 19870330   $83.60     289.20
#>  2 19871111  $113.60         NA
#>  3 19881128   $78.00         NA
#>  4 19890509   $92.50         NA
#>  5 19890531   $68.00         NA
#>  6 19890801  $115.90         NA
#>  7 19870331     <NA>     291.70
#>  8 19870401     <NA>     292.39
#>  9 19870402     <NA>     293.63
#> 10 19870403     <NA>     300.41
#> 11 19870406     <NA>     301.95
austensen
  • 2,857
  • 13
  • 24