CODEWITHSUNDEEP
rdate
kslayerr
kslayerr

Reputation: 819

Remove duplicated rows from a Merge of two dataframes in r by Date/TIme

I have merged two dataframes by date and time in R (by DateTime). One dataframe is a simple sequence, and the other has data for 6242 obs, but I need data for every hour (even if it is zero)

When I merged, my result duplicated rows that matched, instead of inserting them. Is there an addition to the merge function I can use to keep ALL rows, but not those that have duplicated dates with no information? i.e I want row 1933 NOT 1934.

x <- data.frame (DateTime = seq(as.POSIXct("1986-01-01"),
                            as.POSIXct("2012-04-27"),
                            by=(3600)))
y <- read.csv("TS1.csv", header = FALSE, as.is = TRUE)
names(y) <- c("Date", "Time", "Rainfall")

y$Station<- rep("D1253",length(6242))

#reformat so date is the same
y$Date <- as.Date(y$Date, format = "%m/%d/%Y")
y$DateTime <-  paste(y$Date, y$Time, sep=" ")

>head(y)
    Date  Time Rainfall Station         DateTime
1 1986-01-01 21:00     0.01   D1253 1986-01-01 21:00
2 1986-01-02  9:00     0.01   D1253  1986-01-02 9:00
3 1986-01-02 10:00     0.01   D1253 1986-01-02 10:00
4 1986-01-02 11:00     0.01   D1253 1986-01-02 11:00
5 1986-01-02 12:00     0.01   D1253 1986-01-02 12:00
6 1986-01-02 13:00     0.01   D1253 1986-01-02 13:00

#Combine datasets 
z<- merge(x, y, by='DateTime', all=TRUE) #the all.x=TRUE gives me all NAs

z$Rainfall[is.na(z$Rainfall)] <- 0.00

> head(z)
             DateTime      Date     Time     Rainfall Station
1933   1986-03-14 18:00:00 1986-03-14 18:00     0.01   D1253
1934   1986-03-14 19:00:00       <NA>  <NA>     0.00    <NA>
1935   1986-03-14 19:00:00 1986-03-14 19:00     0.01   D1253
1936   1986-03-14 20:00:00       <NA>  <NA>     0.00    <NA>
1937   1986-03-14 20:00:00 1986-03-14 20:00     0.01   D1253
1938   1986-03-14 21:00:00       <NA>  <NA>     0.00    <NA>
1939   1986-03-14 21:00:00 1986-03-14 21:00     0.09   D1253
1940   1986-03-14 22:00:00       <NA>  <NA>     0.00    <NA>
1941   1986-03-14 22:00:00 1986-03-14 22:00     0.02   D1253
1942   1986-03-14 23:00:00       <NA>  <NA>     0.00    <NA>

Upvotes: 0

Views: 2049

Answers (2)

kslayerr
kslayerr

Reputation: 819

Both dates have to be formatted the same using as.POSIXct

Once that is done, the merge is done correctly with all.x=TRUE

Upvotes: 0

AidanGawronski
AidanGawronski

Reputation: 2085

all.x = TRUE is the right way to go about this:

z <- merge(x, y, by='DateTime', all.x = TRUE)
z[is.na(z)] <- 0 # Fill in the NA's with 0 for the hours with no data

Upvotes: 2

Related Questions