doc - 1 year ago 62
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

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download