How to arrange two data frame in R

Question

I have two data frames in R as follows:

 dat1<-read.table (text=" ID Surname Name Class
    12  Smith   Bani B
    14  Smith   Adami C
    13  Rose    Road AB
    11  Smith   Cherch AC
    10  Sarah   Naim D

    ", header=TRUE)

    dat2<-read.table (text=" ID Surname Name Class
    12  Smith   Bani A
    14  Smith   Adami B
    13  Rose    Road AB
    11  Smith   Cherch AC
    10  Sarah   Naim D

    ", header=TRUE)

I arrange like this separately

k1 <- arrange(dat1, Surname)
k2 <- arrange(dat2, Surname)

Now I merge dat1 and dat2 followed by arranging

k3 <- merge(dat1, dat2, by = "ID")
k4 <- arrange(k3, Surname.x)

I want to get exactly the arrangement that I get for K1 or K2 for the surname when I merge dat1 and dat2. Is it possible?

score 1 · Accepted Answer · answered Jun 20 '21 at 02:40

You can use dplyr::inner_join to get the same order.

dplyr::inner_join(k1, k2, by = 'ID')

#  ID Surname.x Name.x Class.x Surname.y Name.y Class.y
#1 13      Rose   Road      AB      Rose   Road      AB
#2 10     Sarah   Naim       D     Sarah   Naim       D
#3 12     Smith   Bani       B     Smith   Bani       A
#4 14     Smith  Adami       C     Smith  Adami       B
#5 11     Smith Cherch      AC     Smith Cherch      AC

score 1 · Answer 2 · answered Jun 20 '21 at 19:19

We could consider using base R (R 4.1.0) by creating a sequence column in one of the datasets before the merge and then do the order

merge(transform(k1, rn = seq_along(ID)), k2, by = 'ID') |> 
     {\(dat) dat[order(dat$rn), setdiff(names(dat), 'rn')]}()
  ID Surname.x Name.x Class.x Surname.y Name.y Class.y
4 13      Rose   Road      AB      Rose   Road      AB
1 10     Sarah   Naim       D     Sarah   Naim       D
3 12     Smith   Bani       B     Smith   Bani       A
5 14     Smith  Adami       C     Smith  Adami       B
2 11     Smith Cherch      AC     Smith Cherch      AC

How to arrange two data frame in R

2 Answers2