Rename columns of a dataframe based on another dataframe except columns not in that dataframe in R

user_8410477 0
Viewed 263

Given two dataframes df1 and df2 as follows:

df1:

df1 <- structure(list(A = 1L, B = 2L, C = 3L, D = 4L, G = 5L), class = "data.frame", row.names = c(NA, 
-1L))

Out:

  A B C D G
1 1 2 3 4 5

df2:

df2 <- structure(list(Col1 = c("A", "B", "C", "D", "X"), Col2 = c("E", 
"Q", "R", "Z", "Y")), class = "data.frame", row.names = c(NA, 
-5L))

Out:

  Col1 Col2
1    A    E
2    B    Q
3    C    R
4    D    Z
5    X    Y

I need to rename columns of df1 using df2, except column G since it not in df2's Col1.

I use df2$Col2[match(names(df1), df2$Col1)] based on the answer from here, but it returns "E" "Q" "R" "Z" NA, as you see column G become NA. I hope it keep the original name.

The expected result:

  E Q R Z G
1 1 2 3 4 5

How could I deal with this issue? Thanks.

dataframe r
2 Answers

By using na.omit(it's little bit messy..)

colnames(df1)[na.omit(match(names(df1), df2$Col1))] <- df2$Col2[na.omit(match(names(df1), df2$Col1))]
df1

  E Q R Z G
1 1 2 3 4 5

I have success to reproduce your error with

df2 <- data.frame(
  Col1 = c("H","I","K","A","B","C","D"),
  Col2 = c("a1","a2","a3","E","Q","R","Z")
)

The problem is location of df2$Col1 and names(df1) in match.

na.omit(match(names(df1), df2$Col1))

gives [1] 4 5 6 7, which index does not exist in df1 that has length 5.

For df1, we should change order of terms in match, na.omit(match(df2$Col1,names(df1))) gives [1] 1 2 3 4

colnames(df1)[na.omit(match(df2$Col1, names(df1)))] <- df2$Col2[na.omit(match(names(df1), df2$Col1))]

This will works.

user_167291750

A solution using the rename_with function from the dplyr package.

library(dplyr)

df3 <- df2 %>%
  filter(Col1 %in% names(df1))

df4 <- df1 %>%
  rename_with(.cols = df3$Col1, .fn = function(x) df3$Col2[df3$Col1 %in% x])

df4
#   E Q R Z G
# 1 1 2 3 4 5
user_76698090
Related