0

I'm working on an R project where I'm trying to compare frequencies their respective values. Essentially I have a 11852X3 column data frame with position number in slot 1, a unique value ranging from 1-11852 in the second column, and then the same set of unique values just in different positions in column 3.

Essentially because the values in columns 2 and 3 have overlap I want to find the difference between these two values based on the position number (1st) column on the far left and store it in another data frame. So if the the second column has the value 2017 in position one and then the third column also has 2017 in position one, the new data frame would have an entry of 2017 and then a value of 0 since they have the same position. If column 2 has a value of 5276 in the second position, and column 3 has the value 5276 in position 73 then the new data frame would have a value of 70.

I would love some guidance as to the way on how to do this. Thanks.

user3542207
  • 9
  • 2
  • 6
    Welcome to SO. Please provide a **minimal, self-contained example**. Check these links for general ideas, and how to do it in R: [**here**](http://stackoverflow.com/help/mcve), [**here**](http://www.sscce.org/), [**here**](http://adv-r.had.co.nz/Reproducibility.html), and [**here**](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610). You should also show us the **code you have tried**. Cheers. – Henrik Apr 16 '14 at 17:50

1 Answers1

2

Let me know if the below code works fro you. The code will generate negative values if the number in 3rd column occurs above the number in 2nd column.

#Generate simulated data
n = 20
x <- data.frame(c1 = c(1:n), c2 = sample(n),c3 = sample(n))

#Calculate diff in position by taking difference in order
x$diff = order(x$c3)- order(x$c2)
#Reassign difference to its correct position
x$diff[order(x$c2)] <- x$diff
x
   c1 c2 c3 diff
1   1 12  8    4
2   2 11  5    9
3   3  7  4    6
4   4 15  3   12
5   5 19 12   12
6   6 13  1   12
7   7  9 14   12
8   8 18 16    7
9   9  8  7   -8
10 10 16 20   -2
11 11  6 11    1
12 12  4  6   -9
13 13 14 10   -6
14 14  5 17  -12
15 15 10 18   -2
16 16  1 15  -10
17 17  3 19  -13
18 18  2 13    2
19 19 17  9   -5
20 20 20  2  -10
Rohit Das
  • 1,962
  • 3
  • 14
  • 23