0

Hello I have 2 dataframes

DF1

COL1 COL2 COLX
SEQ6 A 1
SEQ2 B 2
SEQ4 C 3
SEQ1 D 4
SEQ7 D 5

DF2

COL1 COL3
SEQ1 Q
SEQ2 L
SEQ3 O
SEQ4 O
SEQ5 M
SEQ6 L
SEQ7 Y
SEQ8 G

and the idea is to change the rownames of the DF1 according to rownames in DF2. And then sort the rownames in the same way as DF2

The expected result would be :

COL1 COL2 COLX
SEQ1 D 4
SEQ2 B 2
SEQ3 NA NA
SEQ4 C 1
SEQ5 NA NA
SEQ6 A 1
SEQ7 D 5
SEQ8 NA NA

Thank you for your help

chippycentra
  • 3,396
  • 1
  • 6
  • 24

1 Answers1

1

You can use match from base like:

DF2$COL2 <- DF1$COL2[match(DF2$COL1, DF1$COL1)]
DF2[-2]
#  COL1 COL2
#1 SEQ1    D
#2 SEQ2    B
#3 SEQ3 <NA>
#4 SEQ4    C
#5 SEQ5 <NA>
#6 SEQ6    A
#7 SEQ7    D
#8 SEQ8 <NA>

In case you have more columns:

cbind(DF2, DF1[match(DF2$COL1, DF1$COL1),])
#     COL1 COL3 COL2 COL1 COL2
#4    SEQ1    Q    D SEQ1    D
#2    SEQ2    L    B SEQ2    B
#NA   SEQ3    O <NA> <NA> <NA>
#3    SEQ4    O    C SEQ4    C
#NA.1 SEQ5    M <NA> <NA> <NA>
#1    SEQ6    L    A SEQ6    A
#5    SEQ7    Y    D SEQ7    D
#NA.2 SEQ8    G <NA> <NA> <NA>

Data:

DF1 <- read.table(header=TRUE, text="COL1 COL2
SEQ6 A
SEQ2 B
SEQ4 C
SEQ1 D
SEQ7 D")

DF2 <- read.table(header=TRUE, text="COL1 COL3
SEQ1 Q
SEQ2 L
SEQ3 O
SEQ4 O
SEQ5 M
SEQ6 L
SEQ7 Y
SEQ8 G")
GKi
  • 37,245
  • 2
  • 26
  • 48
  • and what If I have more Columns in DF1? Because I see that you include DF1$COL2 in the code but in the real df I can have 12-15 more columns. i updated with one more column to explain – chippycentra May 25 '20 at 10:53
  • Than use `cbind`. I made an update. – GKi May 25 '20 at 10:56