Rename a list of variables using an xlsx workbook

Question

Hoping that somene can help me. I am looking to rename some variable names using an xlsx workbook as a datamap in R. How can I use my datamap to rename the variable names in the R dataset to the names defined in the datampay under New_Name. Is this something that is possible?

   > #call dataset
    > df<-datasets::mtcars
    > #head dataset
    > head(mtcars)
                       mpg cyl disp  hp drat    wt  qsec vs am gear carb
    Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
    Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
    Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
    Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
    Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
    Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1
    > #Call datamap
    > datamap <-read_xlsx("...")
    > #head datamap
    > head(datamap)
    # A tibble: 6 x 2
      Original_Name New_Name      
      <chr>         <chr>         
    1 mpg           MilesPerGallon
    2 cyl           Cylinder      
    3 disp          Display       
    4 hp            HorsePower    
    5 drat          D             
    6 wt            W

Thank you in advance for anyone that is able to help

score 1 · Accepted Answer · answered May 03 '18 at 20:44

Once you have read in your xlsx workbook you basically have a vector of old names and a corresponding vector of new names (datamap$Original_Name and datamap$New_Name). Once you look at it that way, this question has many answers here: Rename multiple columns by names

But if you are confident the new names are in the right order, then you could simply do

names(DF) <- datamap$New_Name

Or if you need to line things up

names(DF)[match(datamap$Original_Name, names(DF))] <- datamap$New_Name

Or lots of other ways using packages like data.table, as described in that other question.

score 0 · Answer 2 · answered May 03 '18 at 20:34

0

There are many different ways to do this. I just usually rename the column names. You can solve your problem like so:

colnames(mtcars)[1:6] <- datamap$New_Name

Thought I admit this is not the most robust solution. If you have so many columns that you can't necessarily count and do by index, you might do something fancier like

colnames(mtcars)[colnames(mtcars) %in% datamap$New_Name] <- datamap$New_Name[colnames(mtcars) %in% datamap$New_Name]

answered May 03 '18 at 20:34

AcademicDialysis

168
9

Thank you for your answer this worked, but I thought the other solution was more efficient. Thank you though – Daniel Rawlings May 03 '18 at 20:51

score 0 · Answer 3 · answered May 03 '18 at 20:43

I think, all OP wants is to replace few column names of data frame. The mapping of old column with new column names is defined in a xls. The mapping is already loaded in a tibble.

There can be many ways to achieve desired result but a simple ifelse should be sufficient enough to provide desired result as:

df<-datasets::mtcars

library(readxl)

datamap <-read_xlsx("D:/test.xlsx")


names(df) <- ifelse(names(df) %in% datamap$Original_Name, datamap$New_Name, names(df) )

head(df)
#                   MilesPerGallon Cylinder Display HorsePower    D     W  qsec vs am gear carb
# Mazda RX4                   21.0        6     160        110 3.90 2.620 16.46  0  1    4    4
# Mazda RX4 Wag               21.0        6     160        110 3.90 2.875 17.02  0  1    4    4
# Datsun 710                  22.8        4     108         93 3.85 2.320 18.61  1  1    4    1
# Hornet 4 Drive              21.4        6     258        110 3.08 3.215 19.44  1  0    3    1
# Hornet Sportabout           18.7        8     360        175 3.15 3.440 17.02  0  0    3    2
# Valiant                     18.1        6     225        105 2.76 3.460 20.22  1  0    3    1

Rename a list of variables using an xlsx workbook

3 Answers3