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

Question

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 ?

Have you tried mutate_at? This looks like what it's designed for — camille
– camille, Commented Jan 5, 2020 at 17:00
@camille 1 Error in check_dot_cols(.vars, .cols) : l'argument ".vars" est manquant, avec aucune valeur par défaut — Wilcar
– Wilcar, Commented Jan 5, 2020 at 17:04

tmfmnk · Accepted Answer · 2020-01-05 17:24:20Z

4

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)

edited Jan 5, 2020 at 17:24

answered Jan 5, 2020 at 17:16

tmfmnk

40.4k4 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Wilcar Over a year ago

In my real dataset, variables don't begin by place. I have a vector with varables names...

tmfmnk Over a year ago

Updated it accordingly :)

Wilcar Over a year ago

In my real dataset, I have 274 colmuns, colmuns to recode and other columns are not ordered.

tmfmnk Over a year ago

It is applying it only on columns in your vector. Isn't this what you are interested in?

tmfmnk Over a year ago

Then both possibilities should work. What is the issue?

|

markhogue · Accepted Answer · 2020-01-05 17:06:13Z

1

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

answered Jan 5, 2020 at 17:06

markhogue

1,2091 gold badge8 silver badges19 bronze badges

1 Comment

Wilcar Over a year ago

I need to replace id values by names over 38 columns in reality

YOLO · Accepted Answer · 2020-01-05 17:22:10Z

0

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])

answered Jan 5, 2020 at 17:22

YOLO

22k5 gold badges25 silver badges42 bronze badges

Comments

Rui Barradas · Accepted Answer · 2020-01-05 17:24:00Z

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

Collectives™ on Stack Overflow

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

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related