R Create table using mean of multiple columns from other table

Question

I have a table:

> head(TiposMotivA)
  Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21
1  5  4  4  4  6  6  7  6  4   6   6   6   4   4   4   4   6   7   4   4   6
2  5  4  4  5  5  5  5  5  5   5   7   5   4   3   1   6   6   5   6   7   7
3  4  5  4  4  5  4  5  4  5   4   5   4   5   4   5   4   5   4   5   4   5
4  5  5  7  7  4  6  6  6  7   7   6   7   7   6   6   7   4   7   6   6   7
5  6  1  7  6  7  7  7  7  7   7   6   7   2   2   3   6   3   7   7   7   7
6  4  4  3  3  4  5  4  3  4   7   6   6   4   4   6   4   5   7   6   6   7

Here is its put:

> dput(head(TiposMotivA))
structure(list(Q1 = c(5L, 5L, 4L, 5L, 6L, 4L), Q2 = c(4L, 4L, 
5L, 5L, 1L, 4L), Q3 = c(4L, 4L, 4L, 7L, 7L, 3L), Q4 = c(4L, 5L, 
4L, 7L, 6L, 3L), Q5 = c(6L, 5L, 5L, 4L, 7L, 4L), Q6 = c(6L, 5L, 
4L, 6L, 7L, 5L), Q7 = c(7L, 5L, 5L, 6L, 7L, 4L), Q8 = c(6L, 5L, 
4L, 6L, 7L, 3L), Q9 = c(4L, 5L, 5L, 7L, 7L, 4L), Q10 = c(6L, 
5L, 4L, 7L, 7L, 7L), Q11 = c(6L, 7L, 5L, 6L, 6L, 6L), Q12 = c(6L, 
5L, 4L, 7L, 7L, 6L), Q13 = c(4L, 4L, 5L, 7L, 2L, 4L), Q14 = c(4L, 
3L, 4L, 6L, 2L, 4L), Q15 = c(4L, 1L, 5L, 6L, 3L, 6L), Q16 = c(4L, 
6L, 4L, 7L, 6L, 4L), Q17 = c(6L, 6L, 5L, 4L, 3L, 5L), Q18 = c(7L, 
5L, 4L, 7L, 7L, 7L), Q19 = c(4L, 6L, 5L, 6L, 7L, 6L), Q20 = c(4L, 
7L, 4L, 6L, 7L, 6L), Q21 = c(6L, 7L, 5L, 7L, 7L, 7L)), .Names = c("Q1", 
"Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", 
"Q12", "Q13", "Q14", "Q15", "Q16", "Q17", "Q18", "Q19", "Q20", 
"Q21"), row.names = c(NA, 6L), class = "data.frame")

What I need new is to create another table by using this table columns. It must have the follow structure: Column A values come from mean of columns Q1 and Q11 Column B values come from mean of columns Q10 and Q21

The final result is expected to be:

     A    B
1  5.5    6
2    6    6
3  4.5  4.5
4  5.5    7
5    6    7
6    5    7

To help you understand, the calculation is:

1A = (6 + 5) / 2
1B = (6 + 6) / 2
And so on...

Is there a fuction I can use to do this?

I thought this one would do the trick, but I was mistaken...

> c(mean(c(TiposMotivA$Q1,TiposMotivA$Q11)),mean(c(TiposMotivA$Q11,TiposMotivA$Q21)))
[1] 5.645161 6.395161

But instead of calculating mean for each row it took mean from all values in each row and then calculated the mean. Then I tried this:

Teste$A <- tapply(TiposMotivA$Q1,TiposMotivA$Q11,mean)
Teste$B <- tapply(TiposMotivA$Q10,TiposMotivA$Q21,mean)

No success again... I know I must be far from what I need, so I ask for help... Any clues are gonna be very appreciated!

akrun · Accepted Answer · 2015-02-01 04:26:38Z

5

A solution with dplyr:

library(dplyr)                  #load library
new_df <- df %>%
          #use mutate to create the mean columns                   
          mutate(A=(Q1+Q11)/2, B=(Q10+Q21)/2 ) %>% 
          select(A,B)             #only select A and B which you need

Or

new_df <- transmute(df, A=(Q1+Q11)/2, B=(Q10+Q21)/2)

Output

new_df
    A   B
1 5.5 6.0
2 6.0 6.0
3 4.5 4.5
4 5.5 7.0
5 6.0 7.0
6 5.0 7.0

edited Feb 1, 2015 at 4:26

akrun

891k38 gold badges590 silver badges700 bronze badges

answered Jan 31, 2015 at 23:58

LyzandeR

38k12 gold badges84 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LyzandeR Over a year ago

Happy to have helped :)

Enrique · Accepted Answer · 2015-02-01 00:04:58Z

2

Here is one way to do it without using any external library, but a little bit more combersome and hard to read.

data.frame(A=(TiposMotivA$Q1+TiposMotivA$Q11)/2, B=(TiposMotivA$Q10+TiposMotivA$Q21)/2)

answered Feb 1, 2015 at 0:04

Enrique

10.2k7 gold badges53 silver badges59 bronze badges

Comments

akrun · Accepted Answer · 2015-02-01 05:08:34Z

Here is an option using data.table

library(data.table)
#if the columns are to be created in the same dataset 
setDT(TiposMotiva)[,c('A', 'B'):= list((Q1+Q11)/2, (Q10+Q21)/2)]
#if you need another dataset with the newly created columns
DTNew <- setDT(TiposMotiva)[, list(A=(Q1+Q11)/2, B=(Q10+Q21)/2)]

Or using rowMeans in base R. This would be useful if there are NAs

 as.data.frame(sapply(list(TiposMotiva[c('Q1', 'Q11')], 
               TiposMotiva[c('Q10', 'Q21')]), rowMeans, na.rm=TRUE))

Or if this needs to be done for many columns (i.e. getting the mean value for each row from corresponding n columns), we could use Reduce after placing the subset dataset in a list. Here, the "means" will be between columns 1 and 11, 2 and 12, 3 and 13 etc. (NOTE: In the given dataset, the pattern is not specific (i.e. Q1 and Q11, Q10 and Q21))

n <- 2
Reduce(`+`, list(TiposMotiva[1:10], TiposMotiva[11:20]))/n

Or

f1 <- function(x,y) colMeans(rbind(x,y), na.rm=TRUE)
df1 <- setNames(TiposMotiva[1:10], LETTERS[1:10])
df1[] <- Map(f1,  TiposMotiva[1:10], TiposMotiva[11:20])

Collectives™ on Stack Overflow

R Create table using mean of multiple columns from other table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related