Recode column based on two other vectors

Question

This is my dataset:

df = structure(list(from = c(0, 0, 0, 0, 38, 43, 49, 54), to = c(43, 
54, 56, 62, 62, 62, 62, 62), count = c(342, 181, 194, 386, 200, 
480, 214, 176), group = c("keiner", "keiner", "keiner", "keiner", 
"paid", "paid", "owned", "earned")), class = c("tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -8L))

My Problem is that the columns from and to need to be ranked (the ranking has to be done for the two columns from and to), since the visualisation library requires that and also needs to start with an index of 0. Thats why I build two vectors, one (ranking) with a ranking of each unique value of the two columns, the other (uniquevalues) with original unique values of the dataset.

ranking <- dplyr::dense_rank(unique(c(df$from, df$to))) - 1 ### Start Index at 0, "recode" variables
uniquevalues <- unique(c(df$from, df$to))

Now I have to recode the original dataset. The columns to and from have to receive the values from ranking, according to the corresponding value of uniquevalues.

The only option I came around with was to create a dataframe of the the two vectors and loop over each row, but I would really like to have a vectorized solution for this. Can anyone help me?

This:

  <dbl> <dbl> <dbl> <chr> 
1     0    43   342 keiner
2     0    54   181 keiner
3     0    56   194 keiner
4     0    62   386 keiner
5    38    62   200 paid  
6    43    62   480 paid  
7    49    62   214 owned 
8    54    62   176 earned

should become this:

   from    to count group 
  <dbl> <dbl> <dbl> <chr> 
1     0     2   342 keiner
2     0     4   181 keiner
3     0     5   194 keiner
4     0     6   386 keiner
5     1     6   200 paid  
6     2     6   480 paid  
7     3     6   214 owned 
8     4     6   176 earned

Answer 1

We could unlist the values and match them with uniquevalues

df[1:2] <- match(unlist(df[1:2]), uniquevalues) - 1

df

#   from    to count group 
#  <dbl> <dbl> <dbl> <chr> 
#1     0     2   342 keiner
#2     0     4   181 keiner
#3     0     5   194 keiner
#4     0     6   386 keiner
#5     1     6   200 paid  
#6     2     6   480 paid  
#7     3     6   214 owned 
#8     4     6   176 earned

Or using column names instead of index.

df[c("from", "to")] <- match(unlist(df[c("from", "to")]), uniquevalues) - 1

Answer 2

Another solution converting to factor and back.

f <- unique(unlist(df1[1:2]))

df[1:2] <- lapply(df[1:2], function(x) {
  as.integer(as.character(factor(x, levels=f, labels=1:length(f) - 1)))
  })
df
# # A tibble: 8 x 4
#  from    to  count group 
# <fct> <fct> <dbl> <chr> 
# 1   0     2    342 keiner
# 2   0     4    181 keiner
# 3   0     5    194 keiner
# 4   0     6    386 keiner
# 5   1     6    200 paid  
# 6   2     6    480 paid  
# 7   3     6    214 owned 
# 8   4     6    176 earned

Answer 3

I would use mapvalues function. Like this

library(plyr)
df[ , 1:2] <- mapvalues(unlist(df[ , 1:2]),
                        from= uniquevalues,
                        to= ranking)
df
#   from    to count group 
#  <dbl> <dbl> <dbl> <chr> 
#1     0     2   342 keiner
#2     0     4   181 keiner
#3     0     5   194 keiner
#4     0     6   386 keiner
#5     1     6   200 paid  
#6     2     6   480 paid  
#7     3     6   214 owned 
#8     4     6   176 earned