Sorting dataframe string by string

Question

I have a dataframe containing several rows with pairs in this format

I only want pairs that are distinct and the order doesn't matter. So for example the dataframe should look like this instead

So I can count how many times the combination 41 and 25 occures.

So I want to run through the whole dataframe and sort each string so the highest values comes first.(or lowest, doesn't matter).

Hope for you help. I'm guessing it's pretty simple, but can't figure it out.

Thanks

I assume you have a character column with this data then?

James
– James

2013-05-22 15:01:04 +00:00
Commented May 22, 2013 at 15:01 — James
– James, Commented May 22, 2013 at 15:01

Matthew Plourde · Accepted Answer · 2013-05-22 15:38:23Z

3

If d is your data.frame and the pairs are strings in one column, then

d[, 1] <- sapply(strsplit(d[, 1], ','), function(x) paste(sort(x), collapse=','))

If the pairs are split between two columns, say columns 1 and 2, then

d[, 1:2] <- t(apply(apply(d[, 1:2], 1, identity), 2, sort))

edited May 22, 2013 at 15:38

answered May 22, 2013 at 15:16

Matthew Plourde

44.8k9 gold badges103 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc in the box · Accepted Answer · 2013-05-22 15:25:12Z

0

This might be embarrassing when seen against the slick coding of @Matthew Plourde, but I don't think you wanted to remove duplicate rows, so here is the solution I came up with:

#Make some data
set.seed(1)
N <- 200
MIN <- 1
MAX <- 8
df <- data.frame(x=paste(round(runif(N, min=MIN, max=MAX)), round(runif(N, min=MIN, max=MAX)), sep=","))

#Split data into 2 components
require(plyr)
tmp <- ldply(strsplit(as.character(df$x), ","))
df$x1 <- as.numeric(tmp[,1])
df$x2 <- as.numeric(tmp[,2])
head(df)

#sort smaller of values to the left
tmp <- t(apply(df[,2:3], 1, sort))
df$x.sort <- apply(tmp, 1, paste, collapse=",")
head(df)

#sort df
ord <- order(df$x.sort)
df <- df[ord,]
head(df, 20)

Which results in:

      x x1 x2 x.sort
167 1,1  1  1    1,1
92  1,2  1  2    1,2
27  1,3  1  3    1,3
71  3,1  3  1    1,3
28  4,1  4  1    1,4
47  1,4  1  4    1,4
55  1,4  1  4    1,4
67  4,1  4  1    1,4
81  4,1  4  1    1,4
116 1,4  1  4    1,4
133 1,4  1  4    1,4
152 5,1  5  1    1,5
10  1,6  1  6    1,6
85  6,1  6  1    1,6
99  7,1  7  1    1,7
109 7,1  7  1    1,7
5   2,2  2  2    2,2
12  2,2  2  2    2,2
22  2,2  2  2    2,2
56  2,2  2  2    2,2

answered May 22, 2013 at 15:25

Marc in the box

12k5 gold badges50 silver badges102 bronze badges

1 Comment

Matias Bruhn Over a year ago

Thanks! Worked like a charm

Collectives™ on Stack Overflow

Sorting dataframe string by string

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related