Match data from two different colums

Good Afternoon all,
I am new here in the Community, it's a pleasure, I hope you can help me with this little issue.

I have a matrix (CD) with date and number but not in a continuous series (just some days per year) and a vector (A) with the continuous series.
How to create a new matrix with all the date and the values correctly located depending by the date?

Example Result:

AD:
2000-1-1 0
2000-1-2 0
2000-1-3 34.13
2000-1-4 0
2000-1-5 125.3
2000-1-6 0
2000-1-7 0
2000-1-8 0
2000-1-9 0.54

A<- seq(as.Date("2000/1/1"), by = "day", length.out = 10)

colC <- c( "2000-1-3","2000-1-5","2000-1-9")
colD <- c(34.13,125.3,0.54)
CD <- data.frame(colC, colD)

AD <- data.frame(colA, #?????)
 

Thank You so much,

Andrea

Hi and welcome to the community Andrea!

You can use the join functions provided by dplyr for such a task.
For your example, I would use

library(dplyr)

A<- seq(as.Date("2000/1/1"), by = "day", length.out = 10)
colC <- c( "2000-1-3","2000-1-5","2000-1-9")
colD <- c(34.13,125.3,0.54)
CD <- data.frame(colC, colD)

A<-data.frame(A) # both objects need to be a DF
CD$colC<-as.Date(CD$colC) #the columns you are going to match should both be in the same format 
AD<-left_join(A,CD,by=c("A"="colC"))  # left join retains all rows in the first df (here A)

AD
#             A   colD
# 1  2000-01-01     NA
# 2  2000-01-02     NA
# 3  2000-01-03  34.13
# 4  2000-01-04     NA
# 5  2000-01-05 125.30
# 6  2000-01-06     NA
# 7  2000-01-07     NA
# 8  2000-01-08     NA
# 9  2000-01-09   0.54
# 10 2000-01-10     NA

If you want to replace the NAs with 0, you can use
AD$colD[is.na(AD$colD)]<-0

Hope that helps!

1 Like

That's amazing, thank you so much Jms :stuck_out_tongue_winking_eye:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.