0

How do you reshape data using cast with an asymmetric function? I have the data

>t
   a b  c
1  1 1 30
2  1 2 25
3  2 1 59
4  2 2  1
5  3 1 12
6  3 2 97
7  4 1 66
8  4 2 43
9  5 1 13
10 5 2 32

For each level x of a I'd like to get the difference

t[t$a==x & t$b==2, 'c'] - t[t$a==x & t$b==1, 'c']

If I wanted a sum, it'd be easy: cast(t, a ~ ., fun.aggregate=sum, value = 'c'). But since difference is asymmetric, I don't know to ensure that the b==1 value would be subtracted from b==2 value and not vice versa.

Thanks!

esa606
  • 370
  • 3
  • 13

2 Answers2

2

You can use the diff function:

library(reshape)
t2 <- t[order(t$b), ] # to make sure '1' comes before '2'
cast(t2, a ~ ., fun.aggregate = diff, value = 'c')

  a (all)
1 1    -5
2 2   -58
3 3    85
4 4   -23
5 5    19
Sven Hohenstein
  • 80,497
  • 17
  • 145
  • 168
  • This works here when `b==1` is always given before `b==2`. But if the order is sloppier with the real data, it will just subtract the second from the first and not necessarily `b==1` from `b==2`. – esa606 Mar 10 '14 at 19:52
  • I guess this wouldn't work if there were an `a` value that didn't have exactly one `b=1` row and exactly one `b=2` row, but definitely the cleanest approach! – josliber Mar 10 '14 at 20:07
1

Here's a slightly more complex example, with multiple rows for the same (a, b) pairing:

dat = read.table(text="   a b  c
1  1 1 30
2  1 2 25
3  2 1 59
4  2 2  1
5  3 1 12
6  3 2 97
7  4 1 66
8  4 2 43
9  5 1 13
10 5 2 32
11 5 2 1", header=T)

You can just perform the grouping on each subset:

dat$a <- factor(dat$a)  # So the groups match
with(dat, tapply(c[b == 2], a[b == 2], sum) - tapply(c[b == 1], a[b == 1], sum))
#   1   2   3   4   5 
#  -5 -58  85 -23  20

With cast:

library(reshape)
casted <- cast(dat, a~b, fun.aggregate=sum, value="c")
data.frame(a=casted$a, diff=casted[["2"]] - casted[["1"]])
#   a diff
# 1 1   -5
# 2 2  -58
# 3 3   85
# 4 4  -23
# 5 5   20
josliber
  • 43,891
  • 12
  • 98
  • 133
  • I also thought of separating the two `b` factors into different columns and then subtracting--just wondering if there's a one-step solution. I guess not though. – esa606 Mar 10 '14 at 19:57