0

I am trying to build a pipe that it capable of handling various input types. I left_join to a master table that may have an identical column name. dplyr appends the matching column names with .x and .y in the joined table. I want rename the column that originated from the master table (.y) to its original name. The pipe must work for both input1 and input2 (as independent input). Currently my Rename step throws and error. I tried mutate() as well but gives a similar error.

> names(input1.df)
[1] "A"

> names(input2.df)
[1] "A" "B"

>names(MasterTable.df)
[1] "A" "B" "C" "D" "E"

joined.df <- input2.df %>%
  dplyr::left_join(MasterTable.df, by=("A")) %>%
  dplyr::rename(B = ifelse(B.y %in% names(.)) B.y, B) %>%
  dplyr::select(A, B) %>%
  dplyr::mutate(New_Column = ifelse(is.na(B), A, B))
user438383
  • 5,716
  • 8
  • 28
  • 43
JVGen
  • 401
  • 3
  • 10
  • 2
    It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Since we can't run this code it's not clear what's happening. – MrFlick Jan 27 '23 at 15:46

2 Answers2

1

You can use rename_with(.fn=):

input1.df <- data.frame(A=1)
input2.df <- data.frame(A=1, B=2)
MasterTable.df <- data.frame(A=1, B=2, C=3, D=4, E=5)
library(dplyr)
input2.df %>%
  left_join(MasterTable.df, by=("A")) %>%
  rename_with(.fn = ~ gsub("\\.y$", "", .))
#   A B.x B C D E
# 1 1   2 2 3 4 5

and you can select(-ends_with(".x")) if desired.

The ~-func is a bit naive in that it does not check if the non-.y column already exists, but it looks like that isn't a factor in your case.

Perhaps a different approach, though, is to change the suffixes at join time:

input2.df %>%
  left_join(MasterTable.df, by=("A"), suffix=c(".x", ""))
#   A B.x B C D E
# 1 1   2 2 3 4 5
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • 1
    Thank you. Simply using the suffix=c(".x", "") works for me; didn't know this option existed. – JVGen Jan 27 '23 at 17:10
0

Without dataset and output example I'm not sure I'm getting it but I'll give it a try :

Why dont you just rename your .y or .x col after the left joining ?

joined.df <- left_join(MasterTable.df,input2.df, by=("A"))
names(joined.df) <- gsub(".y","",names(joined.df))
names(joined.df) <- gsub(".x","",names(joined.df))
Beeflight31
  • 227
  • 7