How to rename column names by using an alias/synonym dictionary in R?

user_13084789 0
Viewed 200

I am reading a lot on this wonderful website, but this is actually my very first question. So bear with me!

I am a PhD student working with ecology data (species abundance data of different localities) from different scientists. I want to improve my data management and stop hand-editing my raw data and instead do all my changes with a script. The taxonomy of species can be a mess, meaning: different synonyms or aliases for the same species. Also, since I use data from different scientists, I have to deal with different abbreviations of species, different order of columns and also typos. You name it. I now wonder if there is a good way to rename my column names by using an alias/synonym dictionary. I spend several hours researching, but couldn't find an answer that really fits my problem.

Here is a small example of my data:

df1 <- tibble(depth_cm = c(1.5:4.5), n_pachyderma_d = c(70.2:73.2), n_pachyderma_s = c(10.1:13.1), g_quinqueloba = c(2.4:5.4))
dict <- tibble(new_name = c("turborotalita_quinqueloba", "neogloboquadrina_incompta", "neogloboquadrina_pachyderma"),  alias1 = c("t_quinqueloba", "neogloboquadrina_pachyderma_dextral", "globigerina_pachyderma"), alias2 = c("g_quinqueloba", "n_pachyderma_d", "n_pachyderma_s"), alias3 = c(NA, "n_incompta", "n_pachyderma"))

df1 is my actual raw data sheet with depth values and abundances of different species. 

> df1
# A tibble: 4 x 4
  depth_cm n_pachyderma_d n_pachyderma_s g_quinqueloba
     <dbl>          <dbl>          <dbl>         <dbl>
1      1.5           70.2           10.1           2.4
2      2.5           71.2           11.1           3.4
3      3.5           72.2           12.1           4.4
4      4.5           73.2           13.1           5.4

And dict is my dictionary which I want to use. Be aware that there might be different numbers of aliases for a specific species. I want to update this dictionary with new aliases as soon as I encounter them.

> dict
# A tibble: 3 x 4
  new_name                    alias1                              alias2         alias3      
  <chr>                       <chr>                               <chr>          <chr>       
1 turborotalita_quinqueloba   t_quinqueloba                       g_quinqueloba  NA          
2 neogloboquadrina_incompta   neogloboquadrina_pachyderma_dextral n_pachyderma_d n_incompta  
3 neogloboquadrina_pachyderma globigerina_pachyderma              n_pachyderma_s n_pachyderma

I now want to rename the species column names of df1 with the new_name by looking up the column name in dict. However, since I am still learning R, I have no good idea how to approach this issue. But basically, I want to check every column name with the dictionary then rename the column with the proper species name. Is there a smart approach for this? My main goal is to automate the import and the standardization of different datasets.

I am really looking forward for every suggestion or help!

tibble r dictionary
1 Answers

We can reshape the 'dict' to 'long' format

library(dplyr)
library(tidyr)
new_dict <- dict %>%
      pivot_longer(cols = starts_with('alias')) %>%
      filter(value %in% names(df1)) %>% 
      select(-name)

and use that to rename the columns in 'df1'

df2 <- df1 %>% 
          rename_at(vars(new_dict$value), ~ new_dict$new_name)

df2
# A tibble: 4 x 4
#  depth_cm neogloboquadrina_incompta neogloboquadrina_pachyderma turborotalita_quinqueloba
#     <dbl>                     <dbl>                       <dbl>                     <dbl>
#1      1.5                      70.2                        10.1                       2.4
#2      2.5                      71.2                        11.1                       3.4
#3      3.5                      72.2                        12.1                       4.4
#4      4.5                      73.2                        13.1                       5.4
user_37322710
Related