Major Merging Issues

General
1

I am merging two data sets. I have made sure that they both have a column that has the same name.

       wti$date <- wti$ï..Date
       pa$date <- pa$ï..Date

I have tried both of these codes only to have the y become all NAs.

       wti_pa_df1 <- merge(wti, pa, by="date", all = TRUE)

      wti_pa_m1 <- merge(wti, pa, by.x= c("date"),
               by.y= c("date"), all.x = TRUE, all.y = TRUE)

      wti_pa_m2 <-merge(wti, pa, by.x= c("Price"),
              by.y= c("Approving"), all.x = TRUE, all.y = TRUE)

      wti_pa_m2 <-merge(wti, pa, by.x= c("Price"),
              by.y= c("Approving"), all = TRUE)

I have been trying to merge this code for 4+ hours. I have watched numerous videos but something keeps going wrong.

I'm not sure what I am doing wrong as I am following everything that I have found. This is for a class and I'm not going to pass if I can't merge this one data set.

Thanks!

dput(head(wti,20))
dput(head(pa,20))
2

Please post the output of

dput(head(wti,20))

and

dput(head(pa,20))

This will allow other people to have copies of some of your data. That will make it much easier to help you.

The first thing I would check is whether the date columns of the two data sets have the same class. Are they both character or both Date?

3

When I hover over it says both are column characters.

4

Untitled
Untitled1884×648 250 KB

Here is a screenshot.

5

Images are not very helpful. Can you please post the output of dput() as I asked above?

6 
dput(head(wti,20))

dput(head(pa,20))

I hope that works. I had edited my original post above with it.

7

You have to post the output you get when running those commands in your R console, not the commands itself.

8

Sorry!

 structure(list(ï..Date = c("1/3/2022", "12/1/2021", "11/1/2021", 
"10/1/2021", "9/1/2021", "8/2/2021", "7/6/2021", "6/1/2021", 
"5/3/2021", "4/1/2021", "3/1/2021", "2/3/2021", "1/21/2021", 
"1/4/2021", "12/1/2020", "11/5/2020", "10/16/2020", "9/30/2020", 
"9/14/2020", "8/31/2020"), End.Date = c("1/16/2022", "12/16/2021", 
"11/16/2021", "10/19/2021", "9/17/2021", "8/17/2021", "7/21/2021", 
"6/18/2021", "5/18/2021", "4/21/2021", "3/15/2021", "2/18/2021", 
"2/2/2021", "1/15/2021", "12/17/2020", "11/19/2020", "10/27/2020", 
"10/15/2020", "9/28/2020", "9/13/2020"), Approving = c(40L, 43L, 
42L, 42L, 43L, 49L, 50L, 56L, 54L, 57L, 54L, 56L, 57L, 34L, 39L, 
43L, 46L, 43L, 46L, 42L), Disapproving = c(56L, 51L, 55L, 52L, 
55L, 48L, 45L, 42L, 40L, 40L, 42L, 40L, 37L, 62L, 57L, 55L, 52L, 
55L, 52L, 56L), Unsure.NoData = c(4L, 6L, 3L, 6L, 2L, 3L, 5L, 
2L, 6L, 3L, 4L, 4L, 6L, 4L, 4L, 2L, 2L, 2L, 2L, 2L), Date = c("1/3/2022", 
"12/1/2021", "11/1/2021", "10/1/2021", "9/1/2021", "8/2/2021", 
"7/6/2021", "6/1/2021", "5/3/2021", "4/1/2021", "3/1/2021", "2/3/2021",

"1/21/2021", "1/4/2021", "12/1/2020", "11/5/2020", "10/16/2020",
"9/30/2020", "9/14/2020", "8/31/2020")), row.names = c(NA, 20L
), class = "data.frame")

structure(list(ï..Date = c("3/6/2022", "2/27/2022", "2/20/2022", 
"2/13/2022", "2/6/2022", "1/30/2022", "1/23/2022", "1/16/2022", 
"1/9/2022", "1/2/2022", "12/26/2021", "12/19/2021", "12/12/2021", 
"12/5/2021", "11/28/2021", "11/21/2021", "11/14/2021", "11/7/2021", 
"10/31/2021", "10/24/2021"), Price = c(107.38, 115.68, 91.59, 
 91.07, 93.1, 92.31, 86.82, 85.14, 83.82, 78.9, 75.21, 73.79, 
70.86, 71.67, 66.26, 68.15, 76.1, 80.79, 81.27, 83.57), Open = c(122.25, 
94.99, 91.75, 93.91, 91.82, 87.45, 84.91, 84.32, 78.88, 75.69, 
73.38, 70.07, 72.04, 67.02, 69.23, 75.75, 80.66, 81.13, 83.36, 
83.98), High = c(130.33, 116.57, 100.54, 95.82, 94.66, 93.17, 
88.84, 87.91, 84.45, 80.47, 77.44, 73.95, 73, 73.34, 72.93, 79.23, 
81.81, 84.97, 84.88, 85.41), Low = c(103.87, 94.43, 90.06, 89.03, 
88.41, 86.34, 81.9, 82.78, 77.83, 74.27, 72.57, 66.04, 69.39, 
66.72, 62.43, 67.4, 75.37, 79.78, 78.25, 80.58), Vol. = c("-", 
"2.43M", "1.35M", "1.52M", "1.90M", "1.66M", "1.91M", "307.18K", 
"1.66M", "1.57M", "1.03M", "972.44K", "1.28M", "2.15M", "3.12M", 
 "2.15M", "958.31K", "2.32M", "2.62M", "2.57M"), Change.. = c("-7.17%", 
"26.30%", "0.57%", "-2.18%", "0.86%", "6.32%", "1.97%", "1.57%", 
"6.24%", "4.91%", "1.92%", "4.13%", "-1.13%", "8.16%", "-2.77%", 
"-10.45%", "-5.81%", "-0.59%", "-2.75%", "-0.23%"), Date = c("3/6/2022", 
 "2/27/2022", "2/20/2022", "2/13/2022", "2/6/2022", "1/30/2022", 
"1/23/2022", "1/16/2022", "1/9/2022", "1/2/2022", "12/26/2021", 
"12/19/2021", "12/12/2021", "12/5/2021", "11/28/2021", "11/21/2021", 
"11/14/2021", "11/7/2021", "10/31/2021", "10/24/2021")), row.names = c(NA, 
20L), class = "data.frame")
10

In the code below, I test whether there are any common Dates between wti and pa and I find there are not. I then change two dates in wti to match dates in pa and do a merge. The merge results in a data frame with two rows, as expected. Your full data set may well have some matching dates and using only a subset of the data may have caused the apparent lack of matches. However, you should check and see if that is the cause of your merge problems.

wti <- structure(list(ï..Date = c("1/3/2022", "12/1/2021", "11/1/2021", 
                                  "10/1/2021", "9/1/2021", "8/2/2021", "7/6/2021", "6/1/2021", 
                                  "5/3/2021", "4/1/2021", "3/1/2021", "2/3/2021", "1/21/2021", 
                                  "1/4/2021", "12/1/2020", "11/5/2020", "10/16/2020", "9/30/2020", 
                                  "9/14/2020", "8/31/2020"), 
                       End.Date = c("1/16/2022", "12/16/2021", 
                                    "11/16/2021", "10/19/2021", "9/17/2021", "8/17/2021", "7/21/2021", 
                                    "6/18/2021", "5/18/2021", "4/21/2021", "3/15/2021", "2/18/2021", 
                                    "2/2/2021", "1/15/2021", "12/17/2020", "11/19/2020", "10/27/2020", 
                                    "10/15/2020", "9/28/2020", "9/13/2020"), 
                       Approving = c(40L, 43L, 42L, 42L, 43L, 49L, 50L, 56L, 54L, 57L, 54L, 56L, 57L, 34L, 39L, 43L, 46L, 43L, 46L, 42L), 
                       Disapproving = c(56L, 51L, 55L, 52L, 
                                        55L, 48L, 45L, 42L, 40L, 40L, 42L, 40L, 37L, 62L, 57L, 55L, 52L, 
                                        55L, 52L, 56L), 
                       Unsure.NoData = c(4L, 6L, 3L, 6L, 2L, 3L, 5L, 
                                         2L, 6L, 3L, 4L, 4L, 6L, 4L, 4L, 2L, 2L, 2L, 2L, 2L), 
                       Date = c("1/3/2022", "12/1/2021", "11/1/2021", "10/1/2021", "9/1/2021", "8/2/2021", 
                                "7/6/2021", "6/1/2021", "5/3/2021", "4/1/2021", "3/1/2021", "2/3/2021", 
                                
                                "1/21/2021", "1/4/2021", "12/1/2020", "11/5/2020", "10/16/2020",
                                "9/30/2020", "9/14/2020", "8/31/2020")), 
                 row.names = c(NA, 20L
                 ), class = "data.frame")
pa <- structure(list(ï..Date = c("3/6/2022", "2/27/2022", "2/20/2022", 
                                 "2/13/2022", "2/6/2022", "1/30/2022", "1/23/2022", "1/16/2022", 
                                 "1/9/2022", "1/2/2022", "12/26/2021", "12/19/2021", "12/12/2021", 
                                 "12/5/2021", "11/28/2021", "11/21/2021", "11/14/2021", "11/7/2021", 
                                 "10/31/2021", "10/24/2021"), 
                      Price = c(107.38, 115.68, 91.59, 
                                91.07, 93.1, 92.31, 86.82, 85.14, 83.82, 78.9, 75.21, 73.79, 
                                70.86, 71.67, 66.26, 68.15, 76.1, 80.79, 81.27, 83.57), 
                      Open = c(122.25, 94.99, 91.75, 93.91, 91.82, 87.45, 84.91, 84.32, 78.88, 75.69, 
                               73.38, 70.07, 72.04, 67.02, 69.23, 75.75, 80.66, 81.13, 83.36, 
                               83.98), 
                      High = c(130.33, 116.57, 100.54, 95.82, 94.66, 93.17, 88.84, 87.91, 84.45, 80.47, 77.44, 73.95, 73, 73.34, 72.93, 79.23, 
                               81.81, 84.97, 84.88, 85.41), 
                      Low = c(103.87, 94.43, 90.06, 89.03, 88.41, 86.34, 81.9, 82.78, 77.83, 74.27, 72.57, 66.04, 69.39, 
                              66.72, 62.43, 67.4, 75.37, 79.78, 78.25, 80.58), 
                      Vol. = c("-", "2.43M", "1.35M", "1.52M", "1.90M", "1.66M", "1.91M", "307.18K", "1.66M", "1.57M", "1.03M", "972.44K", "1.28M", "2.15M", "3.12M", "2.15M", "958.31K", "2.32M", "2.62M", "2.57M"), 
                      Change.. = c("-7.17%", "26.30%", "0.57%", "-2.18%", "0.86%", "6.32%", "1.97%", "1.57%", "6.24%", "4.91%", "1.92%", "4.13%", "-1.13%", "8.16%", "-2.77%", "-10.45%", "-5.81%", "-0.59%", "-2.75%", "-0.23%"), 
                      Date = c("3/6/2022", "2/27/2022", "2/20/2022", "2/13/2022", "2/6/2022", "1/30/2022", "1/23/2022", "1/16/2022", "1/9/2022", "1/2/2022", "12/26/2021", "12/19/2021", "12/12/2021", "12/5/2021", "11/28/2021", "11/21/2021", "11/14/2021", "11/7/2021", "10/31/2021", "10/24/2021")), 
                row.names = c(NA, 20L), class = "data.frame")



#Check if any dates in wti are also in pa
any(wti$Date %in% pa$Date)
#> [1] FALSE
#Confirm by inspection that there are no matches.
wti$Date
#>  [1] "1/3/2022"   "12/1/2021"  "11/1/2021"  "10/1/2021"  "9/1/2021"  
#>  [6] "8/2/2021"   "7/6/2021"   "6/1/2021"   "5/3/2021"   "4/1/2021"  
#> [11] "3/1/2021"   "2/3/2021"   "1/21/2021"  "1/4/2021"   "12/1/2020" 
#> [16] "11/5/2020"  "10/16/2020" "9/30/2020"  "9/14/2020"  "8/31/2020"
pa$Date
#>  [1] "3/6/2022"   "2/27/2022"  "2/20/2022"  "2/13/2022"  "2/6/2022"  
#>  [6] "1/30/2022"  "1/23/2022"  "1/16/2022"  "1/9/2022"   "1/2/2022"  
#> [11] "12/26/2021" "12/19/2021" "12/12/2021" "12/5/2021"  "11/28/2021"
#> [16] "11/21/2021" "11/14/2021" "11/7/2021"  "10/31/2021" "10/24/2021"
wti[1,"Date"] <- "1/2/2022"
wti[4,"Date"] <- "10/31/2021"
MergedData <- merge(wti,pa,by="Date")
MergedData
#>         Date ï..Date.x   End.Date Approving Disapproving Unsure.NoData
#> 1   1/2/2022  1/3/2022  1/16/2022        40           56             4
#> 2 10/31/2021 10/1/2021 10/19/2021        42           52             6
#>    ï..Date.y Price  Open  High   Low  Vol. Change..
#> 1   1/2/2022 78.90 75.69 80.47 74.27 1.57M    4.91%
#> 2 10/31/2021 81.27 83.36 84.88 78.25 2.62M   -2.75%

Created on 2022-05-01 by the reprex package (v2.0.1)

11

This topic was automatically closed 21 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.