How to lookup for a single value in two different datasets that have two columns in common (based on a condition)

user_14409100 0
Viewed 67

I have two different datasets as follows: enter image description here

I need to add the call volume, put volume, and total volume columns in dataset #2 if and only if: Both ID and Date columns in both datasets match. I am separating calls, puts, and totals, based on the values in column 3 in dataset #1 (C for calls, P for puts, and T for total).

I am running this code but it isn't working (showing an example of calls only, same rule applies for puts and totals).

dataset2$call_volume <- if(dataset1$optiontype== "C")
{ dataset1$volume [ match (
                          interaction(dataset2$ID,dataset2$date),
                          interaction(dataset1$ID,dataset1$date)
                                         )]}

Does anyone have a suggestion of how I could proceed with the code? Many thanks!

> dput(dataset1)
structure(list(ID = c(44652, 44652, 44652, 56266, 56266, 56266, 
44652, 44652, 44652, 56266, 56266, 56266), date = c("1997/01/02", 
"1997/01/02", "1997/01/02", "1997/01/02", "1997/01/02", "1997/01/02", 
"1997/01/03", "1997/01/03", "1997/01/03", "1997/01/03", "1997/01/03", 
"1997/01/03"), `option type (C,P,T: for calls, puts, and total)` = c("C", 
"P", "T", "C", "P", "T", "C", "P", "T", "C", "P", "T"), volume = c(34, 
250, 284, 30, 0, 30, 1443, 211, 1654, 4490, 826, 5316)), row.names = c(NA, 
-12L), class = c("tbl_df", "tbl", "data.frame"))

> dput(dataset2)
structure(list(ID = c(44652, 44652, 44652, 56266, 56266, 56266
), date = c("1997/01/02", "1997/01/03", "1997/01/04", "1997/01/02", 
"1997/01/03", "1997/01/04"), `call volume` = c(NA, NA, NA, NA, 
NA, NA), `put volume` = c(NA, NA, NA, NA, NA, NA), `total volume` = c(NA, 
NA, NA, NA, NA, NA)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

Update: I have many other columns in both datasets that are quite different from one another, the only common are the ones shown in the picture and dataset below.

matching match r lookup
2 Answers

I think this is a bit of an x/y problem. I figure you are actually trying to pivot dataset1 into wide format to populate dataset2. After this you can left_join the two frames.

library(tidyr)
library(dplyr)

names(dataset1)[3] <- "option_type"

dataset2 %>% 
  dplyr::select(-`call volume`, -`put volume`, -`total volume`) %>%
  left_join(dataset1 %>% 
  tidyr::pivot_wider(names_from = "option_type", values_from = "volume") %>%
  rename("Call Volume" = C, "Put Volume" = P, "Total Volume" = `T`),
  by = c("ID", "date"))
#> # A tibble: 6 x 5
#>      ID date       `Call Volume` `Put Volume` `Total Volume`
#>   <dbl> <chr>              <dbl>        <dbl>          <dbl>
#> 1 44652 1997/01/02            34          250            284
#> 2 44652 1997/01/03          1443          211           1654
#> 3 44652 1997/01/04            NA           NA             NA
#> 4 56266 1997/01/02            30            0             30
#> 5 56266 1997/01/03          4490          826           5316
#> 6 56266 1997/01/04            NA           NA             NA

Created on 2020-10-07 by the reprex package (v0.3.0)

user_125003150

If I understood correctly, you want dataset2 with values from dataset1 if it matches or NA if no match.

If that is the case, you need to use left_join.

If not, please update you question with desired output.

library(tidyverse)

d1 <- structure(list(ID = c(44652, 44652, 44652, 56266, 56266, 56266, 
                            44652, 44652, 44652, 56266, 56266, 56266), date = c("1997/01/02", 
                                                                                "1997/01/02", "1997/01/02", "1997/01/02", "1997/01/02", "1997/01/02", 
                                                                                "1997/01/03", "1997/01/03", "1997/01/03", "1997/01/03", "1997/01/03", 
                                                                                "1997/01/03"), `option type (C,P,T: for calls, puts, and total)` = c("C", 
                                                                                                                                                     "P", "T", "C", "P", "T", "C", "P", "T", "C", "P", "T"), volume = c(34, 
                                                                                                                                                                                                                        250, 284, 30, 0, 30, 1443, 211, 1654, 4490, 826, 5316)), row.names = c(NA, 
                                                                                                                                                                                                                                                                                               -12L), class = c("tbl_df", "tbl", "data.frame"))


d2 <- structure(list(ID = c(44652, 44652, 44652, 56266, 56266, 56266
), date = c("1997/01/02", "1997/01/03", "1997/01/04", "1997/01/02", 
            "1997/01/03", "1997/01/04"), `call volume` = c(NA, NA, NA, NA, 
                                                           NA, NA), `put volume` = c(NA, NA, NA, NA, NA, NA), `total volume` = c(NA, 
                                                                                                                                 NA, NA, NA, NA, NA)), row.names = c(NA, -6L), class = c("tbl_df", 
                                                                                                                                                                                         "tbl", "data.frame"))

d1_longer <- d1 %>%
  pivot_wider(names_from = `option type (C,P,T: for calls, puts, and total)`, values_from = volume) %>%
  rename(`call volume` = `C`, `put volume` = `P`, `total volume` = `T`)

d2 %>%
  select(ID, date) %>%
  left_join(d1_longer)
#> Joining, by = c("ID", "date")
#> # A tibble: 6 x 5
#>      ID date       `call volume` `put volume` `total volume`
#>   <dbl> <chr>              <dbl>        <dbl>          <dbl>
#> 1 44652 1997/01/02            34          250            284
#> 2 44652 1997/01/03          1443          211           1654
#> 3 44652 1997/01/04            NA           NA             NA
#> 4 56266 1997/01/02            30            0             30
#> 5 56266 1997/01/03          4490          826           5316
#> 6 56266 1997/01/04            NA           NA             NA

Created on 2020-10-07 by the reprex package (v0.3.0)

user_13703030
Related