AmagicalFishy AmagicalFishy - 3 months ago 8
R Question

Subtract from one column based on matching values of a second?

I have two data tables:

A

animal number
dog 11
cat 7
pig 5


B

animal number
dog 1
cat 2
pig 2
pig 3
cat 4
dog 2


I would like to subtract the numbers in A from the numbers in B, based on the matching animal, to get the result...

-10
-5
-3
-2
-3
-9


I'm sure I can do this without having to write a loop, but have no idea how to do it. I've done quite a bit of searching, but however I'm doing so is not fruiting any results. (Is this where something like how
apply
is used?)

Answer

Suppose your data frames are A and B, using match is one possibility:

B$number - A$number[match(B$animal, A$animal)]
# [1] -10  -5  -3  -2  -3  -9

Should you want to append this to B, use

B$diff <- B$number - A$number[match(B$animal, A$animal)]

#  animal number diff
#1    dog      1  -10
#2    cat      2   -5
#3    pig      2   -3
#4    pig      3   -2
#5    cat      4   -3
#6    dog      2   -9