CODEWITHSUNDEEP
r
NewUsr_stat
NewUsr_stat

Reputation: 2583

Replace elements in a big data.frame

I have two data.frames that look like this:
A: 

 Column1      Column2      Column3        Column4     
    A            N            P              Q         
    S            W            Q              P     
    WE           J            S              L     
    G            I            u              E

B: 

 Column1      Column2         
    A            No                    
    N            WJ               
    W            B             
    G            H

I would like to replace in df A all the corresponding elements occurring in B[,1] with the relative in B[,2]. In other words, element "A" (B[,1]) in df A has to be replaced with No (B[,2]).
Desired output:

C: 

 Column1      Column2      Column3        Column4     
    No           WJ           P              Q         
    S            B            Q              P     
    WE           J            S              L     
    H            I            u              E

The df A contains around 14.000 rows and 3.000 columns.

Can anyone help me please?

Upvotes: 1

Views: 198

Answers (2)

akrun
akrun

Reputation: 887078

Another option is to use Map from base R

C <- A
C[] <- Map(function(x,y) {val <- y[,2][match(x, y[,1])]
                  ifelse(is.na(val), x, val)}, A, list(B))
C
#   Column1 Column2 Column3 Column4
#1      No      WJ       P       Q
#2       S       B       Q       P
#3      WE       J       S       L
#4       H       I       u       E

Upvotes: 1

Rentrop
Rentrop

Reputation: 21497

You can do this via data.table using B as a lookup-table.

require(data.table)
setDT(A)
setDT(B)


setkey(B,Column1)

# Basically this loop joins A and B on A$k and B$Column1
# Then it replaces all matched vales in A$k by B$Column2
# where k iterates over all columns of A
for(k in names(A)){
  setkeyv(A,k)
  A[B, c(k) := i.Column2] # i.Column2 because A also has a column names Column2
}

Result:

> A
   Column1 Column2 Column3 Column4
1:       H       I       u       E
2:      WE       J       S       L
3:       S       B       Q       P
4:      No      WJ       P       Q

Caution: The Order of the rows was changed. The result is ordered by Column 4 (aka A has Column4 as key)

Upvotes: 4

Related Questions