CODEWITHSUNDEEP
rdplyr
chopin_is_the_best
chopin_is_the_best

Reputation: 2101

Aggregate dataframe with a calculation across a single column

My initial dataframe looks:

library(tidyverse)

df <- tibble::tribble(
        ~element,     ~label, ~value,
            "aa", "sessions",    196,
            "bb", "sessions",    865,
            "aa",    "begin",     59,
            "bb",    "begin",    123,
            "aa", "complete",      5,
            "bb", "complete",      5
        )

I want to aggregate like, in a new dataframe:

for each element aa and bb.

Looking like: 

df_agg <- tibble::tribble(
                          ~label_2,         ~aa,         ~bb,
               "begin_to_sessions", 0.301020408, 0.142196532,
            "complete_to_sessions", 0.005780347, 0.005780347
            )

Upvotes: 1

Views: 52

Answers (2)

tmfmnk
tmfmnk

Reputation: 39858

With tidyverse, you can also do:

df %>%
 filter(label != "sessions") %>%
 full_join(df %>%
 filter(label == "sessions"), by = c("element" = "element")) %>%
 group_by(element, label.x) %>%
 transmute(label = paste(label.x, "to", label.y, sep = "_"),
           res = value.x/value.y) %>%
 ungroup() %>%
 select(-label.x) %>%
 spread(element, res)

  label                    aa      bb
  <chr>                 <dbl>   <dbl>
1 begin_to_sessions    0.301  0.142  
2 complete_to_sessions 0.0255 0.00578

Upvotes: 0

akrun
akrun

Reputation: 887078

It can be done with first spread it to 'wide' format, get the ratios, gather to 'long' format and spread it back to 'wide' format

library(tidyverse)
df %>% 
    spread(label, value) %>%
    transmute(element,
              begin_to_sessions = begin/sessions, 
              complete_to_sessions = complete/sessions) %>% 
    gather(label_2, val, -element) %>% 
    spread(element, val)

Or using mutate_at (in case there are many columns)

df %>% 
    spread(label, value) %>% 
    mutate_at(vars(begin, complete), list(~ ./sessions)) %>% 
    select(-sessions) %>% 
    rename_at(vars(begin, complete), ~ paste0(., "_to_sessions")) %>% 
    gather(label_2, val, -element) %>% 
    spread(element, val)
# A tibble: 2 x 3
#  label_2                  aa      bb
#  <chr>                 <dbl>   <dbl>
#1 begin_to_sessions    0.301  0.142  
#2 complete_to_sessions 0.0255 0.00578

We can also avoid multiple gather/spread by doing a group_by division extracting the 'value' corresponding to 'sessions' string in 'label', filter out the rows having 'sessions' in 'label' and then do a single spread at the end 

df %>%
  group_by(element) %>% 
  mutate(value = value/value[label ==  "sessions"]) %>% 
  ungroup %>%
  filter(label != "sessions") %>% 
  transmute(element, value, label2 = paste0(label, "_to_sessions")) %>% 
  spread(element, value)

Upvotes: 2

Related Questions