Joining and replacing columns multiple times

user_11079320 0
Viewed 53

I have a dataframe with a lot of columns with abbreviations. I'm trying to replace the columns with their full name.

A minimal reproducible example:

category <- data.frame(short = c("TOM", "BAN", "APP", "PEA"),
  name = c("tomato", "banana", "apple", "pear"))

df <- data.frame(col1 = c("TOM", "TOM", "TOM", "APP", "TOM"),
             col2 = c("APP", "TOM", "TOM", "PEA", "PEA"),
             col3 = c("TOM", "PEA", "PEA", "TOM", "BAN"))

  col1 col2 col3
1  TOM  APP  TOM
2  TOM  TOM  PEA
3  TOM  TOM  PEA
4  APP  PEA  TOM
5  TOM  PEA  BAN

Now, I would like my dataframe to just contain the full names of the products. I can get it to work with left_joins, selecting and renaming, but this code is getting out of hand pretty rapidly with a lot of columns.

df2 <- df %>% 
  left_join(category, by = c("col1" = "short")) %>% 
  select(-col1) %>% 
  rename(col1 = name) %>% 
  left_join(category, by = c("col2" = "short")) %>% 
  select(-col2) %>% 
  rename(col2 = name) %>% 
  left_join(category, by = c("col3" = "short")) %>% 
  select(-col3) %>% 
  rename(col3 = name) 

    col1   col2   col3
1 tomato  apple tomato
2 tomato tomato   pear
3 tomato tomato   pear
4  apple   pear tomato
5 tomato   pear banana

I think (hope?) there's a better solution for it, but I'm unable to find it.

left-join r
3 Answers

An option is to create a named vector

library(dplyr)
library(tibble)
v1 <- deframe(category)

and then use that to match and replace the values

df1 <- df %>%
          mutate(across(everything(), ~ v1[.]))

-output

df1
#   col1   col2   col3
#1 tomato  apple tomato
#2 tomato tomato   pear
#3 tomato tomato   pear
#4  apple   pear tomato
#5 tomato   pear banana

It can be also done with recode using similar way

df %>%
     mutate(across(everything(), ~ recode(., !!! v1)))

Or using base R, create the named vector with setNames, loop over the columns with lapply and replace those values and assign it back

v1 <- with(category, setNames(name, short))
df1 <- df
df1[] <- lapply(df, function(x) v1[x])

Or convert to matrix (a matrix is a vector with dim attributes)

df1[1] <- v1[as.matrix(df)]
user_37322710

Another option is using factor

df[] <- factor(
  u <- unlist(df),
  labels = with(category, name[match(sort(unique(u)), short)])
)

or a shorter one via setNames

df[]<-with(category,setNames(name,short))[unlist(df)]

which gives

> df
    col1   col2   col3
1 tomato  apple tomato
2 tomato tomato   pear
3 tomato tomato   pear
4  apple   pear tomato
5 tomato   pear banana
user_121587570

You can get the data in long format such that all the values are in one column which is easy to join with category dataframe and then get data back in wide format.

library(dplyr)
library(tidyr)

df %>%
  mutate(row = row_number()) %>%
  pivot_longer(cols = -row, names_to = 'col', values_to = 'short') %>%
  left_join(category, 'short') %>%
  select(-short) %>%
  pivot_wider(names_from = col, values_from = name)  %>%
  select(-row)

#   col1   col2   col3  
#  <chr>  <chr>  <chr> 
#1 tomato apple  tomato
#2 tomato tomato pear  
#3 tomato tomato pear  
#4 apple  pear   tomato
#5 tomato pear   banana
user_39629140
Related