Lookup values with multiple criteria

General
1

I have two data frames that show data from the two sides - what amount each companies are declared to each other.

From data frame RSX - Partner_company_ID is equal meaning what shows in the second data frame PSX - ID_company and data frame RSX ID_company is the same meaning from data frame PSX - Partner_company_ID.

My task is that in one data frame I add a certain company's declared values to certain partner.

I find this code to lookup values in Data frame PSX to get the right partners amount in data frame RSX, but I realize some problem.

This code manages in RSX data frame to get the right declared value from PSX data frame, but the problem is that I don't know any function that gets the right company's value from RSX data frame ID_company with the right Partner_company_ID from PSX data frame.

Also there is the problem that partner companies have the ability to repeat ID_companys. For example, from RSX data frame ID_company there would be many partners like the company is two partners of 1 and 2 companies with declared values of 10 EUR and 20 EUR.

RSX <- data.frame(ID_company=c(1,1,2,3,4,5,6), 
              Declared_amount_EUR=c(10,20,30,40,50,60,70),
              Partner_company_ID=c(1,2,3,5,2,7,2))

PSX <- data.frame(ID_company=c(1,2,3,4,5,6),
              Declared_amount_EUR=c(10,20,35,39,55,61),
              Partner_company_ID=c(1,1,2,3,4,5))

PS_EURXX <- rep(NA, 7)

RSX <- data.frame(RSX, PS_EURXX)

RSX$PS_EURXX <- PSX$Declared_amount_EUR[match(RSX$Partner_company_ID, PSX$ID_company)]

Is there any way to make that in the same time I have the ability to manage to get the lookup in the same time with mutiple criteria like a function that checks if the RSX partner company ID is the same as from PSX data frame ID_companys, and at the same time that from RSX ID_company is the same with PSX data frame Partner_company_ID?

I just started learning R language so I will appreciate your help!

2

Not sure I get the full understanding of what you want as there is no resulting example above.

But from this,

You may be looking from *_join function of dplyr :package:

RSX <- data.frame(ID_company=c(1,1,2,3,4,5,6), 
                  Declared_amount_EUR=c(10,20,30,40,50,60,70),
                  Partner_company_ID=c(1,2,3,5,2,7,2))

PSX <- data.frame(ID_company=c(1,2,3,4,5,6),
                  Declared_amount_EUR=c(10,20,35,39,55,61),
                  Partner_company_ID=c(1,1,2,3,4,5))

library(dplyr, warn.conflicts = FALSE)
RSX %>%
  full_join(PSX, by = c(ID_company = "Partner_company_ID", Partner_company_ID = "ID_company")) %>%
  select(ID_company, Partner_company_ID, everything())
#>    ID_company Partner_company_ID Declared_amount_EUR.x
#> 1           1                  1                    10
#> 2           1                  2                    20
#> 3           2                  3                    30
#> 4           3                  5                    40
#> 5           4                  2                    50
#> 6           5                  7                    60
#> 7           6                  2                    70
#> 8           3                  4                    NA
#> 9           4                  5                    NA
#> 10          5                  6                    NA
#>    Declared_amount_EUR.y
#> 1                     10
#> 2                     20
#> 3                     35
#> 4                     NA
#> 5                     NA
#> 6                     NA
#> 7                     NA
#> 8                     39
#> 9                     55
#> 10                    61

Created on 2019-01-06 by the reprex package (v0.2.1)

Here I used a full join, joining by RSX ID to PSX partner, AND RSX partner to PSX ID. You get a row by match + a row by value that don't have match in both (because full_join). .x is value from RSX here, and .y from PSX. (you can use the .suffix = c(".RSX", ".PSX") argument in join function to get more precise.

To understand joint, you could read about it in this chapter of the great R4DS book

Is this isn't what you are looking for, can you provide an example of the results you seek ?

3

Thanks for your suggestions, it really helps and I gather all information I need. I will definately look for advice in future in your recommended book.

1 Like
4

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