I need to create a calculation in R but the calculation changes depending on what the number is

Question

I have a huge xlsx file filled with keyword rankings and I need to calculate the visibility for the rankings. Excel can't handle such a large file, so I'm learning R.

Anyway, to calculate the visibility of a keyword, I need to have this formula:

Rank * % of search volume.

The % depends on the rank, whilst arbitrary I use:

1 - 90%
2 - 80%
3 - 70%
4 - 60%
5 - 50%
6 - 40%
7 - 30%
8 - 20%
9 - 10%
10 - 5%
11+ - 0%

When I could work in excel, I would have the above chart in a sheet, and do a vlookup of the rank, and * by the search volume. For example:

Keyword  |rank |search volume |visibility    
keyword1 |1    |1,000         |900   
Keyword2 |5    |50,000        |25,000   
Keyword3 |12   |3,500         |0

How would I go about doing this in R? I'm very new to R, have tried a few things with for loops but nothing has worked.

Thanks in advance

Hi, would you paste a `dput` of a few rows of your data and show the expected output? See e.g. http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — m-dz, Feb 13 '17 at 11:03
Use the `cut` function to create another column with the rank-classes you want to use. Then write a function that takes the rank-class and other inputs as arguments and do the calculation with this function. — snaut, Feb 13 '17 at 11:06
I just looked up cut, it will work to an extent. Whilst I can "cut" ranks 11+ and return 0 for visibility, I still need to calculate ranks 1-10 individually. Is there a quicker way to do this? Thanks for your help. — jceg316, Feb 13 '17 at 11:14

score 1 · Answer 1 · answered Feb 14 '17 at 11:55

With an initial data frame named rank_data like this..

  keyword rank search_volume
1       A    1          1000
2       B    5         50000
3       C   12          3500

You could create a small function and append the results to a new column using the mutate function within the dplyr library.

library(dplyr)

visFunc <- function(rank, search_volume){

    if(rank == 1){
        rank_pct <- 0.90
    }

    else if(rank == 5){
        rank_pct <- 0.50
    }

    else if(rank > 10){
        rank_pct <- 0
    }

    return(rank_pct * search_volume)

}

visabilityData <- mutate(rank_data, visability = mapply(visFunc, rank,search_volume))

That returns a new data frame which contains a visibility column..

keyword rank search_volume visability
1       A    1          1000        900
2       B    5         50000      25000
3       C   12          3500          0

Obviously tweak the function as you see fit.

score 0 · Answer 2 · answered Mar 01 '17 at 14:41

Thanks for your responses but I managed to figure it out.

I made a spreadsheet which has rank in column A, visibility % in column B, imported this and merged rank in this spreadsheet with rank in the main spreadsheet. Then I could do all my calculations.

I need to create a calculation in R but the calculation changes depending on what the number is

2 Answers2