CODEWITHSUNDEEP
r
user9285150
user9285150

Reputation:

Sorting and calculating sum and rank with new columns in R

I have 200 columns and want to calculate mean and rank and then generate columns. Here is an example of data

df<-read.table(text="Q1a    Q2a Q3b Q4c Q5a Q6c Q7b
1   2   4   2   2   0   1
3   2   1   2   2   1   1
4   3   2   1   1   1   1",h=T)

I want to sum a, b and c for each row, and then sum them together. Next I want to calculate the rank for each row. I want to generate the following table:

Q1a Q2a Q3b Q4c Q5a Q6c Q7b a   b   c   Total   Rank
1   2   4   2   2   0   1   5   5   2   12  2
3   2   1   2   2   1   1   7   2   3   12  2
4   3   2   1   1   1   1   8   3   2   13  1

Upvotes: 3

Views: 307

Answers (2)

Prem
Prem

Reputation: 11955

library(dplyr)

df %>%
  cbind(sapply(c('a', 'b', 'c'), function(x) rowSums(.[, grep(x, names(.)), drop=FALSE]))) %>%
  mutate(Total = a + b + c, 
         Rank = match(Total, sort(Total, decreasing = T)))

Output is:

  Q1a Q2a Q3b Q4c Q5a Q6c Q7b a b c Total Rank
1   1   2   4   2   2   0   1 5 5 2    12    2
2   3   2   1   2   2   1   1 7 2 3    12    2
3   4   3   2   1   1   1   1 8 3 2    13    1

Sample data:

df <- structure(list(Q1a = c(1L, 3L, 4L), Q2a = c(2L, 2L, 3L), Q3b = c(4L, 
1L, 2L), Q4c = c(2L, 2L, 1L), Q5a = c(2L, 2L, 1L), Q6c = c(0L, 
1L, 1L), Q7b = c(1L, 1L, 1L)), class = "data.frame", row.names = c(NA, 
-3L))

Upvotes: 4

DJV
DJV

Reputation: 4863

You can also go with the tidyverse approach. However, it is longer. 

library(tidyverse)

df %>%
    rownames_to_column(var = "ID") %>%
    gather(question, value, -ID) %>% 
    mutate(type = substr(question, 3,3)) %>% 
    group_by(ID, type) %>% 
    summarise(sumType = sum(value, na.rm = TRUE)) %>% 
    as.data.frame() %>%
    spread(type, sumType) %>% 
    mutate(Total = a+b+c, 
           Rank =  match(Total, sort(Total, decreasing = T)))

Results: 

  ID a b c Total Rank
1  1 5 5 2    12    2
2  2 7 2 3    12    2
3  3 8 3 2    13    1

Upvotes: 1

Related Questions