Search code examples
rcountrangedirection

Count rows if the values are in two columns within a range and same direction using R

I have a table in data table format. I need to perform the following calculation in R.

Table1: 

Cty           Pred      Trend
Adams         -9.193    -15.81
Alexander     -1.143      3.01
Bond           5.95     -14.05
Boone          0.57       3.63
.
.

I need to count the number of "Cty" if the values in "Pred" and "Trend" columns are in the same direction (i.e. negative or positive) and if the values are within +/- 5% of the value in "Trend" column.

Any help is appreciated.

Thanks.

Solution

  • The first conditional checks that both Pred and Trend are in the same direction.

    The second conditional checks that Pred is within 5% of the Trend values.

    The result is the number of cities where both conditions are satisfied (assuming there are no duplicate city names).

    sum(with(df, ((Pred>0) == (Trend>0)) & (abs((Trend-Pred)/Trend)<=0.05)))