12

I would like to combine three columns in one of my data sets into one with variable name "al_anim" and remove any duplicates, rank the values (animal ids) from lowest to highest, and re-number each animal from 1 to N under the variable name "new_id".

 anim1 <- c(1456,2569,5489,1456,4587)
 anim2 <- c(6531,6987,6987,15487,6531)
 anim3 <- c(4587,6548,7894,3215,8542)
 mydf <- data.frame(anim1,anim2,anim3)

Any help would be very much appreciated!

Baz

baz
  • 6,817
  • 11
  • 36
  • 37

1 Answers1

21

Using mydf from your example:

mydf <- data.frame(anim1, anim2, anim3)

Stack the data:

sdf <- stack(mydf)

Then compute the unique elements using unique()

uni <- unique(sdf[, "values"])

and then this will get them a new animal id

new_id <- as.numeric(as.factor(sort(uni)))

which would give:

> new_id
 [1]  1  2  3  4  5  6  7  8  9 10 11

However that is totally trivial; seq_along(uni) gets you there far more easily. So I wonder if you want

newdf <- data.frame(anim = sort(uni), new_id = seq_along(uni))
merge(sdf, newdf, by.x = "values", by.y = "anim")

which gives:

> merge(sdf, newdf, by.x = "values", by.y = "anim")
   values   ind new_id
1    1456 anim1      1
2    1456 anim1      1
3    2569 anim1      2
4    3215 anim3      3
5    4587 anim1      4
6    4587 anim3      4
7    5489 anim1      5
8    6531 anim2      6
9    6531 anim2      6
10   6548 anim3      7
11   6987 anim2      8
12   6987 anim2      8
13   7894 anim3      9
14   8542 anim3     10
15  15487 anim2     11

There is an amount of ambiguity in your Question which could be alleviated by giving an expected result/output.

Gavin Simpson
  • 170,508
  • 25
  • 396
  • 453
  • Thanks for you help and suggestions. I will improve on that next time! Thanks again! – baz Sep 13 '11 at 09:17
  • @baz If it answered your question, then please consider accepting it (big tick to the left of the answer). That way, people who have the same or a similar problem as you, and who come across this Q&A will know the problem was solved. The is more information in the [FAQ](http://stackoverflow.com/faq#howtoask) – Gavin Simpson Sep 14 '11 at 08:14