Merging files with different columns, rows, and date order

Question

I am merging two data sets RH.data and CC (see below).

>head(RH.data)
  date       RH
1 2005-05-01 71.1
2 2005-05-02 47.0
3 2005-05-03 58.6
4 2005-05-04 44.2
5 2005-05-05 41.8
6 2005-05-06 61.3

> head(cc)
X id       date case year month   temp
1   1 2005-05-01    1 2005     5     98             
2   1 2005-05-02    0 2005     5     62              
3   1 2005-05-05    0 2005     5     78                         
4   2 2005-05-01    1 2005     5     64  
5   2 2005-05-06    0 2005     5     75              
6   2 2005-05-04    0 2005     5     98   
7   2 2005-05-02    0 2005     5     62  
8   3 2005-05-03    1 2005     5     88

I am trying to merge them by date using the code

    merge(CC, RH.data, by="date", all=T)

However when I run this code, the date changes and my data is replaced with NA

   date  X id case year month   temp     RH
1 12904 NA NA   NA   NA    NA     NA     71.1
2 12905 NA NA   NA   NA    NA     NA     47.0 
3 12906 NA NA   NA   NA    NA     NA     58.6
4 12907 NA NA   NA   NA    NA     NA     44.2

I need the order of CC to stay the same and simply for the values of RH to input where the date is the same. What code would allow me to do this?

Welcome to StackOverflow. Please take a look at this post on [creating a great example in R](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). It would be helpful if yo use `dput` to provide examples. Copying the results of `dput(head(RH))` and `dput(head(cc))`. Note also that there is a typo in your `merge` function. You want `merge(cc, RH, by="date", all=T)` according to your previous code. — lmo, Feb 23 '17 at 19:01

Samuel · Answer 1 · 2017-02-23T19:26:39.180

Try this solution using the tidyverse packages:

library(tidyverse)

date = c("2005-05-01", "2005-05-02", "2005-05-03", "2005-05-04", "2005-05-05", "2005-05-06")
RH = c(71.1, 47.0, 58.6, 44.2, 41.8, 61.3)

RH <- data_frame(date, RH)

X <- 1:8
id <- c(1, 1, 1, 2, 2, 2, 2, 3)
date <- c("2005-05-01", "2005-05-02", "2005-05-05", "2005-05-01", "2005-05-06", "2005-05-04", "2005-05-02", "2005-05-03")
case <- c(1, 0, 0, 1, 0, 0, 0, 1)
year <- 2005
month <- 5
temp <- c(98, 62, 78, 64, 75, 98, 62, 88)

cc <- data_frame(X, id, date, case, year, month, temp)

joined <- right_join(RH, cc, by = "date")

You should also consider "tidying" your dataset, see http://tidyr.tidyverse.org.

score 0 · Accepted Answer · answered Feb 23 '17 at 20:24

0

Assuming you'd like to add corresponding RH values to your cc data.frame, the following should do it.

cc$RH <- NA
for(i in 1:nrow(RH.data)) cc$RH[cc$date==RH.data$date[i]] <- RH.data$RH[i]

answered Feb 23 '17 at 20:24

Matt Tyers

2,125
1
14
23

Merging files with different columns, rows, and date order

2 Answers2