Dictionary style replace multiple items

Question

I have a large data.frame of character data that I want to convert based on what is commonly called a dictionary in other languages.

Currently I am going about it like so:

foo <- data.frame(snp1 = c("AA", "AG", "AA", "AA"), snp2 = c("AA", "AT", "AG", "AA"), snp3 = c(NA, "GG", "GG", "GC"), stringsAsFactors=FALSE)
foo <- replace(foo, foo == "AA", "0101")
foo <- replace(foo, foo == "AC", "0102")
foo <- replace(foo, foo == "AG", "0103")

This works fine, but it is obviously not pretty and seems silly to repeat the replace statement each time I want to replace one item in the data.frame.

Is there a better way to do this since I have a dictionary of approximately 25 key/value pairs?

Maybe this questions could be helpful: [Case Statement Equivalent](http://stackoverflow.com/q/4622060/168747), [How to add a column in a `data.frame`](http://stackoverflow.com/q/4562547/168747), [Data cleaning in Excel sheets](http://stackoverflow.com/q/7374314/168747). — Marek, Sep 26 '11 at 21:44

score 39 · Answer 1 · answered Sep 11 '14 at 14:24

39

If you're open to using packages, plyr is a very popular one and has this handy mapvalues() function that will do just what you're looking for:

foo <- mapvalues(foo, from=c("AA", "AC", "AG"), to=c("0101", "0102", "0103"))

Note that it works for data types of all kinds, not just strings.

answered Sep 11 '14 at 14:24

c.gutierrez

4,154
1
17
13

4

Unfortunately, this throws an _Error in plyr::mapvalues(foo, from = c("AA", "AC", "AG"), to = c("0101", : `x` must be an atomic vector._ This also documented in `?mapvalues`. – Uwe Jan 30 '17 at 09:24
This works absolutely well! Thank you c.gutierrez. – Jane Kathambi Apr 16 '21 at 06:55

Martin Morgan · Accepted Answer · 2011-09-26T20:53:29.720

37

map = setNames(c("0101", "0102", "0103"), c("AA", "AC", "AG"))
foo[] <- map[unlist(foo)]

assuming that map covers all the cases in foo. This would feel less like a 'hack' and be more efficient in both space and time if foo were a matrix (of character()), then

matrix(map[foo], nrow=nrow(foo), dimnames=dimnames(foo))

Both matrix and data frame variants run afoul of R's 2^31-1 limit on vector size when there are millions of SNPs and thousands of samples.

edited Sep 26 '11 at 20:53

answered Sep 25 '11 at 19:30

Martin Morgan

43,010
5
72
104

FYI - if you are using `tidyverse` and have foo as a tibble, you have to coerce it to a data.frame prior to assigning `map[unlist(foo)]`, otherwise the row count of assigned vs existing data will differ. – Scott Jan 19 '21 at 17:43

score 16 · Answer 3 · answered Sep 25 '11 at 18:55

16

Here is a quick solution

dict = list(AA = '0101', AC = '0102', AG = '0103')
foo2 = foo
for (i in 1:3){foo2 <- replace(foo2, foo2 == names(dict[i]), dict[i])}

answered Sep 25 '11 at 18:55

Ramnath

50,746
13
116
150

3

I like this answer because it keeps the keys and values together. Having the keys and values in separate character vectors means that if you get the order of one of the vectors wrong, your dictionary silently mislabels all incorrectly ordered entries. – mgriebe May 22 '14 at 18:32
2

Only difference I would suggest is to use R's vectorized notation on the 3rd line, e.g.: sapply(1:3, function(i) replace(foo2, foo2 == names(dict[i]), dict[i])) – c.gutierrez Sep 11 '14 at 14:26
4

`*apply` functions are NOT the same as vectorized. – Ramnath Sep 11 '14 at 18:27

score 9 · Answer 4 · edited May 23 '17 at 11:47

Note this answer started as an attempt to solve the much simpler problem posted in How to replace all values in data frame with a vector of values?. Unfortunately, this question was closed as duplicate of the actual question. So, I'll try to suggest a solution based on replacing factor levels for both cases, here.

In case there is only a vector (or one data frame column) whose values need to be replaced and there are no objections to use factor we can coerce the vector to factor and change the factor levels as required:

x <- c(1, 1, 4, 4, 5, 5, 1, 1, 2)
x <- factor(x)
x
#[1] 1 1 4 4 5 5 1 1 2
#Levels: 1 2 4 5
replacement_vec <- c("A", "T", "C", "G")
levels(x) <- replacement_vec
x
#[1] A A C C G G A A T
#Levels: A T C G

Using the forcatspackage this can be done in a one-liner:

x <- c(1, 1, 4, 4, 5, 5, 1, 1, 2)
forcats::lvls_revalue(factor(x), replacement_vec)
#[1] A A C C G G A A T
#Levels: A T C G

In case all values of multiple columns of a data frame need to be replaced, the approach can be extended.

foo <- data.frame(snp1 = c("AA", "AG", "AA", "AA"), 
                  snp2 = c("AA", "AT", "AG", "AA"), 
                  snp3 = c(NA, "GG", "GG", "GC"), 
                  stringsAsFactors=FALSE)

level_vec <- c("AA", "AC", "AG", "AT", "GC", "GG")
replacement_vec <- c("0101", "0102", "0103", "0104", "0302", "0303")
foo[] <- lapply(foo, function(x) forcats::lvls_revalue(factor(x, levels = level_vec), 
                                                       replacement_vec))
foo
#  snp1 snp2 snp3
#1 0101 0101 <NA>
#2 0103 0104 0303
#3 0101 0103 0303
#4 0101 0101 0302

Note that level_vec and replacement_vec must have equal lengths.

More importantly, level_vec should be complete , i.e., include all possible values in the affected columns of the original data frame. (Use unique(sort(unlist(foo))) to verify). Otherwise, any missing values will be coerced to <NA>. Note that this is also a requirement for Martin Morgans's answer.

So, if there are only a few different values to be replaced you will be probably better off with one of the other answers, e.g., Ramnath's.

Clément LVD · Answer 5 · 2020-07-21T14:10:41.067

One of the most readable way to replace value in a string or a vector of string with a dictionary is stringr::str_replace_all, from the stringr package. The pattern needed by str_replace_all can be a dictionnary, e.g.,

# 1. Made your dictionnary
dictio_replace= c("AA"= "0101", 
                  "AC"= "0102",
                  "AG"= "0103") # short example of dictionnary.

 # 2. Replace all pattern, according to the dictionary-values (only a single vector of string, or a single string)
 foo$snp1 <- stringr::str_replace_all(string = foo$snp1,
                                      pattern= dictio_replace)  # we only use the 'pattern' option here: 'replacement' is useless since we provide a dictionnary.

Repeat step 2 with foo$snp2 & foo$snp3. If you have more vectors to transform it's a good idea to use another func', in order to replace values in each of the columns/vector in the dataframe without repeating yourself.

score 6 · Answer 6 · answered May 06 '19 at 09:45

We can also use dplyr::case_when

library(dplyr)

foo %>%
   mutate_all(~case_when(. == "AA" ~ "0101", 
                         . == "AC" ~ "0102", 
                         . == "AG" ~ "0103", 
                         TRUE ~ .))

#  snp1 snp2 snp3
#1 0101 0101 <NA>
#2 0103   AT   GG
#3 0101 0103   GG
#4 0101 0101   GC

It checks the condition and replaces with the corresponding value if the condition is TRUE. We can add more conditions if needed and with TRUE ~ . we keep the values as it is if none of the condition is matched. If we want to change them to NA instead we can remove the last line.

foo %>%
  mutate_all(~case_when(. == "AA" ~ "0101", 
                        . == "AC" ~ "0102", 
                        . == "AG" ~ "0103"))

#  snp1 snp2 snp3
#1 0101 0101 <NA>
#2 0103 <NA> <NA>
#3 0101 0103 <NA>
#4 0101 0101 <NA>

This will change the values to NA if none of the above condition is satisfied.

Another option using only base R is to create a lookup dataframe with old and new values, unlist the dataframe, match them with old values, get the corresponding new values and replace.

lookup <- data.frame(old_val = c("AA", "AC", "AG"), 
                     new_val = c("0101", "0102", "0103"))

foo[] <- lookup$new_val[match(unlist(foo), lookup$old_val)]

score 6 · Answer 7 · answered Sep 25 '11 at 18:55

6

Here's something simple that will do the job:

key <- c('AA','AC','AG')
val <- c('0101','0102','0103')

lapply(1:3,FUN = function(i){foo[foo == key[i]] <<- val[i]})
foo

 snp1 snp2 snp3
1 0101 0101 <NA>
2 0103   AT   GG
3 0101 0103   GG
4 0101 0101   GC

lapply will output a list in this case that we don't actually care about. You could assign the result to something if you like and then just discard it. I'm iterating over the indices here, but you could just as easily place the key/vals in a list themselves and iterate over them directly. Note the use of global assignment with <<-.

I tinkered with a way to do this with mapply but my first attempt didn't work, so I switched. I suspect a solution with mapply is possible, though.

answered Sep 25 '11 at 18:55

joran

157,274
30
404
439

2

i wouldn't advise the use of the global assignment operator `< – Ramnath Sep 25 '11 at 18:59
1

@Ramnath Agreed, `< – joran Sep 25 '11 at 19:02
2

This is the only answer that could handle the variant where the original had keys of 0:2 and the task was to convert to equivalent character values. The highest voted answer failed because 0 is not an acceptable index. Ramnaths's and c.gutierrez' answers also failed in my hands. (I didn't test all the answers.) This is the link to the question: https://stackoverflow.com/questions/49504035/how-to-substitute-numeric-value-with-character-across-all-columns-of-a-dataframe – IRTFM Mar 27 '18 at 17:20

score 4 · Answer 8 · answered Dec 06 '18 at 08:34

Using dplyr::recode:

library(dplyr)

mutate_all(foo, funs(recode(., "AA" = "0101", "AC" = "0102", "AG" = "0103",
                            .default = NA_character_)))

#   snp1 snp2 snp3
# 1 0101 0101 <NA>
# 2 0103 <NA> <NA>
# 3 0101 0103 <NA>
# 4 0101 0101 <NA>

score 1 · Answer 9 · edited Dec 09 '15 at 05:51

1

Used @Ramnath's answer above, but made it read (what to be replaced and what to be replaced with) from a file and use gsub rather than replace.

hrw <- read.csv("hgWords.txt", header=T, stringsAsFactor=FALSE, encoding="UTF-8", sep="\t") 

for (i in nrow(hrw)) 
{
document <- gsub(hrw$from[i], hrw$to[i], document, ignore.case=TRUE)
}

hgword.txt contains the following tab separated

"from"  "to"
"AA"    "0101"
"AC"    "0102"
"AG"    "0103"

edited Dec 09 '15 at 05:51

Vinay Prajapati

6,025
4
36
75

answered Dec 09 '15 at 05:13

Fathi Alwosaibi

11
4

Looks like the best option for me, but I cannot make it work some reason. Output makes little sense. – Scientist Jul 13 '20 at 22:11

score 1 · Answer 10 · answered Mar 27 '18 at 04:33

Since it's been a few years since the last answer, and a new question came up tonight on this topic and a moderator closed it, I'll add it here. The poster has a large data frame containing 0, 1, and 2, and wants to change them to AA, AB, and BB respectively.

Use plyr:

> df <- data.frame(matrix(sample(c(NA, c("0","1","2")), 100, replace = TRUE), 10))
> df
     X1   X2   X3 X4   X5   X6   X7   X8   X9  X10
1     1    2 <NA>  2    1    2    0    2    0    2
2     0    2    1  1    2    1    1    0    0    1
3     1    0    2  2    1    0 <NA>    0    1 <NA>
4     1    2 <NA>  2    2    2    1    1    0    1
... to 10th row

> df[] <- lapply(df, as.character)

Create a function over the data frame using revalue to replace multiple terms:

> library(plyr)
> apply(df, 2, function(x) {x <- revalue(x, c("0"="AA","1"="AB","2"="BB")); x})
      X1   X2   X3   X4   X5   X6   X7   X8   X9   X10 
 [1,] "AB" "BB" NA   "BB" "AB" "BB" "AA" "BB" "AA" "BB"
 [2,] "AA" "BB" "AB" "AB" "BB" "AB" "AB" "AA" "AA" "AB"
 [3,] "AB" "AA" "BB" "BB" "AB" "AA" NA   "AA" "AB" NA  
 [4,] "AB" "BB" NA   "BB" "BB" "BB" "AB" "AB" "AA" "AB"
... and so on

It looks like your input is a data.frame and your output is a matrix. I guess you could coerce back at the end, though. — Frank, May 10 '18 at 14:28

Dictionary style replace multiple items

10 Answers10

Linked

Related