Hi @paulll,
Welcome to the RStudio Community Forum.
I think I understand what you are requesting. See the following Reproducible Example:
# This dataframe code was corrected.
tracker_category <- data.frame(Tracker = c("Tracker1", "Tracker2", "Tracker3", "Tracker4","Tracker5","Tracker6"),
c1 = c("Analytics", "Crash", "Location", "Identification", "Analytics", "Ads"),
c2 = c("Ads", "Analytics", "Location", "Analytics", "Identification", "Ads"),
c3 = c("Identification", "Analytics", "Ads", "Ads", "Analytics", "Location"),
c4 = c("url1.com","ur2.com","url3.com","url4.com","url5.com","url6.com"),
sum_tracker = c(1,20,100,0,5,76))
tracker_category
#> Tracker c1 c2 c3 c4 sum_tracker
#> 1 Tracker1 Analytics Ads Identification url1.com 1
#> 2 Tracker2 Crash Analytics Analytics ur2.com 20
#> 3 Tracker3 Location Location Ads url3.com 100
#> 4 Tracker4 Identification Analytics Ads url4.com 0
#> 5 Tracker5 Analytics Identification Analytics url5.com 5
#> 6 Tracker6 Ads Ads Location url6.com 76
suppressPackageStartupMessages(library(tidyverse))
long.df <- pivot_longer(tracker_category, cols=c("c1","c2","c3"),
names_to="origin_col",
values_to="category")
long.df %>%
group_by(Tracker, category) %>%
summarise(num=n(),
val=mean(sum_tracker)) %>%
mutate(product=num*val) -> mid.df
#> `summarise()` has grouped output by 'Tracker'. You can override using the `.groups` argument.
mid.df
#> # A tibble: 14 x 5
#> # Groups: Tracker [6]
#> Tracker category num val product
#> <chr> <chr> <int> <dbl> <dbl>
#> 1 Tracker1 Ads 1 1 1
#> 2 Tracker1 Analytics 1 1 1
#> 3 Tracker1 Identification 1 1 1
#> 4 Tracker2 Analytics 2 20 40
#> 5 Tracker2 Crash 1 20 20
#> 6 Tracker3 Ads 1 100 100
#> 7 Tracker3 Location 2 100 200
#> 8 Tracker4 Ads 1 0 0
#> 9 Tracker4 Analytics 1 0 0
#> 10 Tracker4 Identification 1 0 0
#> 11 Tracker5 Analytics 2 5 10
#> 12 Tracker5 Identification 1 5 5
#> 13 Tracker6 Ads 2 76 152
#> 14 Tracker6 Location 1 76 76
mid.df %>%
group_by(category) %>%
summarise(final = sum(product)) -> last.df
last.df
#> # A tibble: 5 x 2
#> category final
#> <chr> <dbl>
#> 1 Ads 253
#> 2 Analytics 51
#> 3 Crash 20
#> 4 Identification 6
#> 5 Location 276
Created on 2021-06-03 by the reprex package (v2.0.0)