Aggregate dataframe with a calculation across a single column

Question

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 row will contain a column containing the ratio begin/sessions complete/sessions 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 ) JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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 # #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)