Trying to add a computed column to a table

Question

I have a table in R:

ftable(final_problem ~ Condition + usedVisualCue, data = data)

                        final_problem   0 0.6
Condition usedVisualCue                      
1         0                             3  10
          1                            12 131
2         0                            63  90
          1                             0   0
3         0                            21  46
          1                            17  60
4         0                             0   0
          1                            20 132

I would like to add another column down the right hand side of this table. Each entry in the additional column should be 0.6 times the entry in the 0.6 column divided by the sum of the entries in the 0 and 0.6 columns. Here is what it should look like:

                        final_problem   0 0.6  mean
Condition usedVisualCue                       
1         0                             3  10  0.46
          1                            12 131  0.55
2         0                            63  90  0.35
          1                             0   0   0.0
3         0                            21  46  0.41
          1                            17  60  0.47
4         0                             0   0   0.0
          1                            20 132  0.52

The new column is the mean of final_problem across each category of Condition and usedVisualCue.

Does anyone know a way to add this column?

This table is the next best thing:

aggregate(final_problem ~ Condition + usedVisualCue, data = data, mean)

  Condition usedVisualCue final_problem
1         1             0     0.4615385
2         2             0     0.3529412
3         3             0     0.4119403
4         1             1     0.5496503
5         3             1     0.4675325
6         4             1     0.5210526

For a minimal dataset:

smallData <- head(data,4)

dput(smallData)

structure(list(id = c(18L, 21L, 25L, 27L), Condition = c(1L, 
1L, 1L, 1L), choice = c(0L, 0L, 0L, 0L), correct = c(1L, 1L, 
1L, 0L), plus = c(0.06, 0.06, 0.06, 0.06), fee_for_reminder = c(0, 
0, 0, 0), problem1 = c(0.03, 0.03, 0, 0), problem2 = c(0.03, 
0.03, 0.03, 0), problem3 = c(0.03, 0.03, 0.03, 0), problem4 = c(0.03, 
0.03, 0, 0), problem5 = c(0, 0, 0, 0), final_problem = c(0.6, 
0.6, 0.6, 0), gender = c(1L, 0L, 0L, 0L), age = c(28L, 26L, 28L, 
36L), dup = c(0L, 0L, 0L, 0L), Total_Amount_Earned = c(0.6, 0.6, 
0.6, 0), Total_Amount_Earned_if.forced.to.pay.for.cue = c(0.57, 
0.57, 0.57, -0.03), `filter_$` = c(1L, 1L, 1L, 1L), usedVisualCue = c(0, 
0, 0, 0)), .Names = c("id", "Condition", "choice", "correct", 
"plus", "fee_for_reminder", "problem1", "problem2", "problem3", 
"problem4", "problem5", "final_problem", "gender", "age", "dup", 
"Total_Amount_Earned", "Total_Amount_Earned_if.forced.to.pay.for.cue", 
"filter_$", "usedVisualCue"), row.names = c(NA, -4L), class = c("tbl_df", 
"tbl", "data.frame"))


ftable(final_problem ~ Condition + usedVisualCue, data = smallData)

                        final_problem 0 0.6
Condition usedVisualCue                    
1         0                           1   3

It should look like

                        final_problem 0 0.6 mean
Condition usedVisualCue                    
1         0                           1   3 0.45

Here is the output of the aggregate command:

aggregate(final_problem ~ Condition + usedVisualCue, data = smallData, mean)

  Condition usedVisualCue final_problem
1         1             0          0.45

Please provide [reproducible example](http://stackoverflow.com/questions/5963269) and what you have tried so far and failed. — Sotos, Feb 08 '18 at 15:07
I've attempted to add a minimal reproducible example. I've tried the aggregate and ftable commands without achieving the desired result. — user2154068, Feb 08 '18 at 18:49

Julien Navarre · Accepted Answer · 2018-02-09T10:17:13.687

0

You can use the spread function from tidyr to get proper columns

df <- as.data.frame(ftable(final_problem ~ Condition + usedVisualCue, data = data))
df <- spread(df, final_problem, Freq)
within(df, mean <-  `0.6` * 0.6 / (`0` + `0.6`))

Or in a tidyverse style :

library(tidyverse)
data %>%
  group_by(Condition, usedVisualCue) %>%
  count(final_problem) %>%
  spread(final_problem, n) %>%
  mutate(mean = `0.6` * 0.6 / (`0` + `0.6`))

edited Feb 09 '18 at 10:17

answered Feb 08 '18 at 15:09

Julien Navarre

7,653
3
42
69

My data frame is data. However each row in data is one observation so, for example, the 3 in Condition=1 and usedVisualCut=0 in the table represents three lines in data. So there is no column in data I can refer to. It seems like I need to be able to refer to the columns of the table itself, and I haven't been able to figure out how to do that. – user2154068 Feb 08 '18 at 18:00
I updated my answer, though do you want to hard code the `* 0.6` or do you want to compute the `mean` function on every group ? – Julien Navarre Feb 09 '18 at 10:21

Trying to add a computed column to a table

1 Answers1