CODEWITHSUNDEEP
rdplyrtidyverse
Wilcar
Wilcar

Reputation: 2513

Using mutate over multiples columns with a for loop to recode values

I need to recode values over multiple columns of a data frame based on another table.

I have to recode the values of multiple columns of a data table using a side table. The values correspond to geographic identifiers that I must replace with place names. So I decided to do a loop but what works outside the loop doesn't work anymore . I can't use mutate in for loop.

My real data contains 274 columns with 38 columns to recode. This columns have many different names (they aren't call places")

my main dataset :

 id <- c(1, 2, 3)
 departure <- c(1, 2, NA)
 arrival <- c(3, 1, 2)
 transit <- c(NA,NA,1)
dataset <- data.frame(id, departure, arrival, transit)

The other table :

geo_id <- c(1, 2, 3)
place_name <- c("Paris", "Nantes", "London")
geocode <- data.frame(geo_id, place_name)

My loop :

var <- c("departure", "arrival", "transit") #the columns that should by recode (must be a vector with my  real data)

for (i in var) {
  print(i)
  dataset <- dataset %>% 
  mutate(i = geocode$place_name[match(i, geocode$geo_id)])

}

mutate create a new column call i ! How to avoid this ?

Upvotes: 2

Views: 425

Answers (4)

tmfmnk
tmfmnk

Reputation: 39858

With dplyr, you can do:

dataset %>%
 mutate_at(vars(one_of(var)), ~ geocode$place_name[match(., geocode$geo_id)])

  id place1 place2 place3
1  1  Paris London   <NA>
2  2 Nantes  Paris   <NA>
3  3   <NA> Nantes  Paris

Or with the addition of tidyr:

dataset %>%
 pivot_longer(one_of(var)) %>%
 left_join(geocode, by = c("value" = "geo_id")) %>%
 select(-value) %>%
 pivot_wider(names_from = name, values_from = place_name)

Upvotes: 4

Rui Barradas
Rui Barradas

Reputation: 76432

Maybe there are simpler ways but the code below works and if the var vector of variables to change is preprocessed as one regex pattern, this code seems to be general, not depending on the number or names of the columns.

Part of it is inspired in this answer to another question. The auxiliary function f is taken from there.

library(dplyr)
library(tidyr)

var_pattern <- paste(var, collapse = "|")

f <- function(.) if(length(unique(.[!is.na(.)])) > 1L) . else first(.[!is.na(.)]) 

dataset %>%
  gather(place, value, -id) %>%
  mutate(place_name = geocode$place_name[value]) %>%
  spread(place, place_name) %>%
  select(-value) %>%
  group_by(id) %>%
  mutate_at(vars(matches(var_pattern)), f) %>%
  ungroup() %>%
  distinct() %>% 
  filter(rowSums(is.na(.)) < 2L) 
## A tibble: 3 x 4
#     id place1 place2 place3
#  <dbl> <fct>  <fct>  <fct> 
#1     1 Paris  London NA    
#2     2 Nantes Paris  NA    
#3     3 NA     Nantes Paris

Upvotes: 0

YOLO
YOLO

Reputation: 21719

Here's one way to do:

# select cols to recode
cols <- c('place1','place2')

# get other columns
other_cols <- setdiff(colnames(dataset), cols)

# recode df
recode_df = sapply(cols, function(x) place_name[dataset[[x]]])

# get all columns together
df = cbind(recode_df, dataset[other_cols])

Upvotes: 0

markhogue
markhogue

Reputation: 1179

I think you want to join the datasets. You can use this dplyr function and drop any unneeded columns. 

comb <- dplyr::left_join(dataset, geocode, by = (c("id" = "geo_id")))
comb

  id place1 place2 place3 place_name
1  1      1      3     NA      Paris
2  2      2      1     NA     Nantes
3  3     NA      2      1     London

Upvotes: 1

Related Questions