Nikhil Kumar Nikhil Kumar -4 years ago 95
R Question

How can we perform condition checking between two tables in R

Suppose if I consider two tables Transaction and account
where

transaction$account_id
contains factors and unique values in
account$account_id


> transaction
trans_id account_id amount
1 100 500
2 101 200
3 102 100
4 100 600
5 100 700
6 100 900
7 101 1000
8 101 10000
9 102 20000
10 101 5000
> account
account_id Total.amnt notrans transavg
100 2700 4 675
101 16200 4 4050
102 20100 2 10050


Now my question is, how to find whether
amount
in
transaction
table is greater than
transavg
in
account
table for each
account_id
.

And store it in a variable with factors as 1 if greater than
transavg
and 0 if lesser than
transavg
. And what packages do I need to use.

Answer Source

We can use match to compare the account_id and then get the corresponding amount from the account table and then compare it with amount in transaction table. This will return a boolean output which can be converted to integer using as.integer.

transaction$flag <- as.integer(transaction$amount > 
          account$transavg[match(transaction$account_id, account$account_id)])

transaction

#   trans_id account_id amount flag
#1         1        100    500    0
#2         2        101    200    0
#3         3        102    100    0
#4         4        100    600    0
#5         5        100    700    1
#6         6        100    900    1
#7         7        101   1000    0
#8         8        101  10000    1
#9         9        102  20000    1
#10       10        101   5000    1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download