doc doc - 3 months ago 4
R Question

Join and order columns

Example:

a b p.a p.b
1 2 x y
3 4 c d
2 1 y x
5 6 f e
1 1 x x


I would like to join
p.a
and
p.b
(property of
a
and
b
) so that if there occurs a pair of
a
and
b
all pairs of
b
and
a
should be turned around to be pairs of
a
and
b
and the order of the properties should be preserved and then joined into a new column. So only if the first two column change, the last two columns also should change. I know that I can use
paste
to join two columns but I'm not sure how to get the order of the column entries right.

The result should be as follows:

a b p.a p.b joined
1 2 x y xy
3 4 c d cd
1 2 x y xy
5 6 f e fe
1 1 x x xx


It is not important, whether the rows for
a
and
b
are
1 2
or
2 1
, but if there is
1 2
there should be no
2 1
and vice versa

Answer

We can use pmin/pmax to get the elements in the right order and then paste them

library(dplyr)
df1 %>% 
   mutate(a1 = pmin(a,b), b1 = pmax(a,b), p.a1 = pmin(p.a, p.b),
      p.b1 = pmax(p.a, p.b), joined = paste0(p.a1, p.b1)) %>%
   select(-a, -b, -p.a, -p.b) %>%
   rename(a=a1, b= b1, p.a= p.a1, p.b = p.b1)
#   a b p.a p.b joined
#1 1 2   x   y     xy
#2 3 4   c   d     cd
#3 1 2   x   y     xy

Or we can use base R

lst <- lapply(seq(1, ncol(df1), by = 2), function(i) {
           x1 <- df1[i:(i+1)]
           list(do.call(pmin, x1), do.call(pmax, x1))})
df1[1:2] <- lst[[1]]
df1[3:4] <- lst[[2]]
df1$joined <- do.call(paste0, df1[3:4]) 
df1
#   a b p.a p.b joined
#1 1 2   x   y     xy
#2 3 4   c   d     cd
#3 1 2   x   y     xy

Update

Using the updated dataset, we can loop through the rows, get the order based on the 1st two column elements, and order the columns based on that.

df2 <- do.call(rbind, lapply(seq_len(nrow(df1)), function(i) {
               x1 <- df1[i,]
               i1 <- order(unlist(x1[1:2]))
               x1[1:2] <- unlist(x1[1:2])[i1]
               x1[3:4] <- unlist(x1[3:4])[i1]
               x1}))

df2$joined <- do.call(paste0, df2[3:4]) 
df2
#  a b p.a p.b joined
#1 1 2   x   y     xy
#2 3 4   c   d     cd
#3 1 2   x   y     xy
#4 5 6   f   e     fe
#5 1 1   x   x     xx