Ranking of one variable by weighted value of another variable?

Question

Super R beginner here. I am trying to get rankings of a certain variable by weighted values of a another column/variable. For example, I have a dataset shown below:

State <- rep(c("MN", "MN", "OR", "OR", "ME", "ME", "CO", "CO", "HI", "HI"), each = 3)
PopA <- c("145", "215", "200", "300", "177", "155", "2013", "89", "102", "3451", 
          "565", "805", "204", "650", "975", "145", "2045", "789", "226", "398", 
          "763","346","987","1236","765","876","95","45","3457","4557")
PopB <- c("190", "7410", "523", "963", "1254", "235", "3140", "4041", "896", "7458",
          "105", "40", "5673", "638", "1444", "673", "257", "4211", "869", "245", 
          "8545","8553","8853","234","635","963","3456","6754","234","2244")
inc1 <- c("55000", "67000", "34000", "17000", "135000", "98000", "54000", "55000", "102000", "170000",
          "75000", "12000", "345000", "23000", "13000", "78000", "112000", "48000", "45000", "89000", 
          "10000", "12000", "16000", "23000", "98000", "96000", "34000", "65000", "59000", "39000" ) 
inc2 <- c("23000", "98000", "45000", "92000", "87000", "55000", "29000", "65000", "59000", "155000", 
          "65000", "23000", "95000", "134000", "76000", "69000", "45000", "95000", "230000", "125000",
          "48000", "97000", "65000", "23000", "16000", "76000", "34500", "76000", "98000", "35000")
data <- data.frame(State, PopA, PopB, inc1, inc2)

I am trying to get 4 new columns named Overall_rank1_PopA, Overall_rank2_PopB, Rank_by_state1_PopA, and Rank_by_state2_PopB. In these columns I want to get a ranking of inc1 and inc2 by weighted population A and weighted population B for the overall dataset and then also grouped by state. I want to do this by weighted percentiles (weighted quantiles?) of popA and popB.

Currently, I have:

ranking <- data %>%
  arrange(inc1, inc2) %>%
  mutate(overall_rank1 = rank(inc1, ties.method = "average"), overall_rank2 = rank(inc2, ties.method = "average"))

ranking2 <- ranking %>%
  group_by(State)%>%
  mutate(state_rank1 = rank(inc1, ties.method = "average"), 
         state_rank2 = rank(inc2, ties.method = "average"))

However, this only gives me ordinal, non weighted rankings.

Does anyone have an idea on how to do this?

percentile r rank ranking ranking functions

acoger · Answer

Step1: remove all of the quotation marks around the integers in your original data frame (these were making them act as characters, which wouldn't be ranked properly)
Step2: make new columns for the weighted population increases
data %>% mutate(popAGrowth = inc1/PopA) %>% mutate(popBGrowth = inc2/PopB) -> data

Step3: rank each row by the amount of growth (1st rank being the highest percent growth)
data %>% mutate(popAGrowthRank = rank(-popAGrowth)) -> data
data %>% mutate(popBGrowthRank = rank(-popBGrowth)) -> data

Step4: rank each state based on the "popAGrowth" and "popBGrowth"
data %>% group_by(State) %>% mutate(stateRank1 = rank(-popAGrowth), stateRank2 = rank(-popBGrowth))

I hope this helped!
(you can use "select()" in another pipe if you want to discard the weighting columns I made)

Ranking of one variable by weighted value of another variable?

One Answer

Add your own answers!

Ask a Question