0

I am trying to find a way rename vaules in a column in one dataframes using another dataframe as a reference. My dataframes are much larger than this but here's a rundown:

This is my main dataframe:

df1
 Company   ID      Charge
[1] A      1234     100
[2] A      1478     150
[3] B      5678     100
[4] B      3333     200
[5] C      2468     200

This is my reference dataframe:

Reference Table 
 Company      ID
[1] Alpha     1234
[2] Alpha     1478
[3] Beta      5678
[4] Beta      3333
[5] Gamma     2468

What I am trying to do replace the company names in Dataframe One with the company names in the Reference Table based on their ID number. I considered a "find and replace" function but my actual dataframe is rather large so I am hoping to find a way to automate a process without having to input all ID numbers in the code.

Here is the final out-put that I'm hoping my Dataframe would look like:

df1
 Company      ID       Charge
[1] Alpha     1234     100
[2] Alpha     1478     150
[3] Beta      5678     100
[4] Beta      3333     200
[5] Gamma     2468     200

I could really use any advice/input. Thanks!

CuRious Coder
  • 27
  • 4
  • Left join (https://stackoverflow.com/q/1299871/3358272, https://stackoverflow.com/q/5706437/3358272, (data.table) https://stackoverflow.com/q/34598139/3358272) and then "coalesce" (either literally `dplyr::coalesce` or `data.table::fcoalesce`, or a manual approximation of it). In dplyr, this would be something like `left_join(df1, Ref, by="ID", suffix=c("", ".y")) %>% mutate(Company = coalesce(Company.y, Company))`. – r2evans Jun 21 '23 at 18:02
  • Or with more recent `dplyr` versions `df1 |> rows_update(reference_table, by = "ID")` – Gregor Thomas Jun 21 '23 at 18:05
  • Ahhh, I haven't become familiar enough with the new verbs, thanks @GregorThomas. I was about to post mine as an answer, but I think it'd be better with the "this-year dplyr-canonical". Thanks! – r2evans Jun 21 '23 at 18:06
  • 1
    Too late, dupe-hammered ;) – Gregor Thomas Jun 21 '23 at 18:07
  • Your other question is great for this, it's appropriate. – r2evans Jun 21 '23 at 18:07

0 Answers0