Combination of reshaping and aggregating a data.frame

Question

I'm still somewhat new so please forgive any missteps, but I currently have a data.frame that looks like this.

Outcome1  Outcome2  Num_Occurances Name
False       False       2          John Doe
False       True        2          John Doe
True        False       4          John Doe
True        True        2          John Doe
False       True        1          Sally Doe
True        False       1          Sally Doe

I would like to both reshape and aggregate the data into a wider format, to eventually look like this:

successful_outcome2 is a sum of the True values in Outcome2
Total_Occurances is the sum of the Num_Occurances for each name
successful_outcome1 is where both Outcome1 and Outcome2 are True
Total_Occurances_Outcome1 is the summation of all True responses in the Outcome1 category

Name        successful_Outcome2  Total_Occurances  successful_Outcome1    Total_Occurances_Outcome1 
John Doe           4                  10                   2                        6
Sally Doe          1                   2                   0                        1

I understand that the dcast function can be used to melt and recast data into a wide format, but the combination of the different outcomes is throwing me for a loop. Any help would be appreciated!

score 2 · Accepted Answer · answered Apr 08 '20 at 08:34

A base R solution with aggregate + transform, i.e.,

dfout <- aggregate(.~Name,
                   transform(df,
                             successful_outcome2 = Outcome2*Num_Occurances,
                             Total_Occurances = Num_Occurances,
                             successful_Outcome1 = Outcome1*Outcome2*Num_Occurances,
                             Total_Occurances_Outcome1 = Outcome1*Num_Occurances),
                   sum)[-(2:4)]

yielding

> dfout
       Name successful_outcome2 Total_Occurances successful_Outcome1 Total_Occurances_Outcome1
1  John Doe                   4               10                   2                         6
2 Sally Doe                   1                2                   0                         1

DATA

df <- structure(list(Outcome1 = c(FALSE, FALSE, TRUE, TRUE, FALSE, 
TRUE), Outcome2 = c(FALSE, TRUE, FALSE, TRUE, TRUE, FALSE), Num_Occurances = c(2L, 
2L, 4L, 2L, 1L, 1L), Name = c("John Doe", "John Doe", "John Doe", 
"John Doe", "Sally Doe", "Sally Doe")), class = "data.frame", row.names = c(NA, 
-6L))

score 0 · Answer 2 · answered Apr 08 '20 at 08:15

Using dplyr :

library(dplyr)

df %>%
 mutate_at(vars(starts_with('Outcome')), as.logical) %>%
 group_by(Name) %>%
 summarise(successful_Outcome2 = sum(Num_Occurances[Outcome2]),
           Total_Occurances = sum(Num_Occurances), 
           successful_outcome1 = sum(Num_Occurances[Outcome1 & Outcome2]), 
            Total_Occurances_Outcome1 = sum(Num_Occurances[Outcome1]))


#  Name     successful_Outcome2 Total_Occurances successful_outcome1 Total_Occurances_Outcome1
#  <fct>                  <int>            <int>               <int>                     <int>
#1 JohnDoe                    4               10                   2                         6
#2 SallyDoe                   1                2                   0                         1

score 0 · Answer 3 · answered Apr 08 '20 at 08:17

One way by expanding the data frame first, then just summarising everything:

library(dplyr)
library(tidyr)

df[rep(1:nrow(df), df$Num_Occurances), -3] %>%
  group_by(Name) %>%
  summarise(successful_outcome2=sum(Outcome2),
            Total_Occurances=n(),
            successful_outcome1=sum(Outcome1 & Outcome2),
         Total_Occurances_Outcome1=sum(Outcome1))
# A tibble: 2 x 5
  Name  successful_outcome2 Total_Occurances successful_outcome1 Total_Occurances_Outcome1
  <chr>                <int>            <int>              <int>                     <int>
1 John Doe                 4               10                   2                        6
2 Sally Doe                1                2                   0                        1

Data:

df <- structure(list(Outcome1 = c(FALSE, FALSE, TRUE, TRUE, FALSE, 
TRUE), Outcome2 = c(FALSE, TRUE, FALSE, TRUE, TRUE, FALSE), Num_Occurances = c(2L, 
2L, 4L, 2L, 1L, 1L), Name = c("John Doe", "John Doe", "John Doe", 
"John Doe", "Sally Doe", "Sally Doe")), class = "data.frame", row.names = c(NA, 
-6L))

Combination of reshaping and aggregating a data.frame

3 Answers3