I would like to use a function to repeat a set of procedures on four columns in a data frame. Ultimately I need a long data frame containing all the output. Here is my data frame:
> sample_data
# A tibble: 10 x 7
REVENUEID AMOUNT YEAR REPORT_CODE PAYMENT_METHOD INBOUND_CHANNEL AMOUNT_CAT
<chr> <dbl> <chr> <chr> <chr> <chr> <fctr>
1 rev-24985629 30 FY18 S Check Mail [25,50)
2 rev-22812413 1 FY16 Q Other Canvassing [0.01,10)
3 rev-23508794 100 FY17 Q Credit card Web [100,250)
4 rev-23506121 300 FY17 S Credit card Mail [250,500)
5 rev-23550444 100 FY17 S Credit card Web [100,250)
6 rev-21508672 25 FY14 J Check Mail [25,50)
7 rev-24981769 500 FY18 S Credit card Web [500,1e+03)
8 rev-23503684 50 FY17 R Check Mail [50,75)
9 rev-24982087 25 FY18 R Check Mail [25,50)
10 rev-24979834 50 FY18 R Credit card Web [50,75)
Here is my code:
AMOUNT_CAT<- sample_data %>% group_by(AMOUNT_CAT,YEAR) %>% summarize(num=n(),total=sum(AMOUNT)) %>% rename(REPORT_VALUE=AMOUNT_CAT) %>% mutate(REPORT_CATEGORY="AMOUNT_CAT")
INBOUND_CHANNEL<- sample_data %>% group_by(INBOUND_CHANNEL,YEAR) %>% summarize(num=n(),total=sum(AMOUNT)) %>% rename(REPORT_VALUE=INBOUND_CHANNEL) %>% mutate(REPORT_CATEGORY="INBOUND_CHANNEL")
PAYMENT_METHOD<- sample_data %>% group_by(PAYMENT_METHOD,YEAR) %>% summarize(num=n(),total=sum(AMOUNT)) %>% rename(REPORT_VALUE=PAYMENT_METHOD) %>% mutate(REPORT_CATEGORY="PAYMENT_METHOD")
REPORT_CODE<- sample_data %>% group_by(REPORT_CODE,YEAR) %>% summarize(num=n(),total=sum(AMOUNT)) %>% rename(REPORT_VALUE=REPORT_CODE) %>% mutate(REPORT_CATEGORY="REPORT_CODE")
final_product<-bind_rows(REPORT_CODE,PAYMENT_METHOD,INBOUND_CHANNEL,AMOUNT_CAT)
Here is the final product of that code:
> final_product
# A tibble: 27 x 5
# Groups: REPORT_VALUE [16]
REPORT_CATEGORY REPORT_VALUE YEAR num total
<chr> <chr> <chr> <int> <dbl>
1 REPORT_CODE J FY14 1 25
2 REPORT_CODE Q FY16 1 1
3 REPORT_CODE Q FY17 1 100
4 REPORT_CODE R FY17 1 50
5 REPORT_CODE R FY18 2 75
6 REPORT_CODE S FY17 2 400
7 REPORT_CODE S FY18 2 530
8 PAYMENT_METHOD Check FY14 1 25
9 PAYMENT_METHOD Check FY17 1 50
10 PAYMENT_METHOD Check FY18 2 55
# ... with 17 more rows
Here is my attempt to condense the code to make it smarter and more efficient (it doesn't work):
cat.list <- c("REPORT_CODE","PAYMENT_METHOD","INBOUND_CHANNEL","AMOUNT_CAT")
repeat_procs <- lapply(cat.list, function(x) x <- sample_data %>% group_by(x,YEAR) %>% summarize(num=n(),total=sum(AMOUNT)) %>% rename(REPORT_VALUE=x) %>% mutate(REPORT_CATEGORY="x")
Can someone please advise me on how to write "smarter" code that doesn't repeat as often?
Thanks!
You need to parse the strings to symbols (rlang::sym) and unquote them in group_by and rename like the following. Another thing to note is that your cat.list is already a string vector, so there is no need to add double quotes around x in mutate:
library(dplyr)
library(rlang)
cat.list <- c("REPORT_CODE","PAYMENT_METHOD","INBOUND_CHANNEL","AMOUNT_CAT")
repeat_procs <- lapply(cat.list, function(x){
final_data <- sample_data %>%
group_by(!!sym(x), YEAR) %>%
summarize(num=n(),total=sum(AMOUNT)) %>%
rename(REPORT_VALUE=!!sym(x)) %>%
mutate(REPORT_CATEGORY=x)
}) %>%
bind_rows()
Result:
> repeat_procs
# A tibble: 27 x 5
# Groups: REPORT_VALUE [16]
REPORT_VALUE YEAR num total REPORT_CATEGORY
<chr> <fctr> <int> <int> <chr>
1 J FY14 1 25 REPORT_CODE
2 Q FY16 1 1 REPORT_CODE
3 Q FY17 1 100 REPORT_CODE
4 R FY17 1 50 REPORT_CODE
5 R FY18 2 75 REPORT_CODE
6 S FY17 2 400 REPORT_CODE
7 S FY18 2 530 REPORT_CODE
8 Check FY14 1 25 PAYMENT_METHOD
9 Check FY17 1 50 PAYMENT_METHOD
10 Check FY18 2 55 PAYMENT_METHOD
# ... with 17 more rows
For more "smarter" code, you need to transform the data to "tidy data" form before grouping and summarizing.
data_tidy <-
tidyr::gather(sample_data, key = "REPORT_CATEGORY", value = "REPORT_VALUE", !! cat.list)
data_tidy
#> REVENUEID AMOUNT YEAR REPORT_CATEGORY REPORT_VALUE
#> 1 rev-24985629 30 FY18 REPORT_CODE S
#> 2 rev-22812413 1 FY16 REPORT_CODE Q
#> 3 rev-23508794 100 FY17 REPORT_CODE Q
#> 4 rev-23506121 300 FY17 REPORT_CODE S
#> 5 rev-23550444 100 FY17 REPORT_CODE S
#> 6 rev-21508672 25 FY14 REPORT_CODE J
#> 7 rev-24981769 500 FY18 REPORT_CODE S
#> 8 rev-23503684 50 FY17 REPORT_CODE R
#> 9 rev-24982087 25 FY18 REPORT_CODE R
#> 10 rev-24979834 50 FY18 REPORT_CODE R
#> 11 rev-24985629 30 FY18 PAYMENT_METHOD Check
#> 12 rev-22812413 1 FY16 PAYMENT_METHOD Other
#> 13 rev-23508794 100 FY17 PAYMENT_METHOD Credit card
#> 14 rev-23506121 300 FY17 PAYMENT_METHOD Credit card
#> 15 rev-23550444 100 FY17 PAYMENT_METHOD Credit card
#> 16 rev-21508672 25 FY14 PAYMENT_METHOD Check
#> 17 rev-24981769 500 FY18 PAYMENT_METHOD Credit card
#> 18 rev-23503684 50 FY17 PAYMENT_METHOD Check
#> 19 rev-24982087 25 FY18 PAYMENT_METHOD Check
#> 20 rev-24979834 50 FY18 PAYMENT_METHOD Credit card
#> 21 rev-24985629 30 FY18 INBOUND_CHANNEL Mail
#> 22 rev-22812413 1 FY16 INBOUND_CHANNEL Canvassing
#> 23 rev-23508794 100 FY17 INBOUND_CHANNEL Web
#> 24 rev-23506121 300 FY17 INBOUND_CHANNEL Mail
#> 25 rev-23550444 100 FY17 INBOUND_CHANNEL Web
#> 26 rev-21508672 25 FY14 INBOUND_CHANNEL Mail
#> 27 rev-24981769 500 FY18 INBOUND_CHANNEL Web
#> 28 rev-23503684 50 FY17 INBOUND_CHANNEL Mail
#> 29 rev-24982087 25 FY18 INBOUND_CHANNEL Mail
#> 30 rev-24979834 50 FY18 INBOUND_CHANNEL Web
#> 31 rev-24985629 30 FY18 AMOUNT_CAT [25,50)
#> 32 rev-22812413 1 FY16 AMOUNT_CAT [0.01,10)
#> 33 rev-23508794 100 FY17 AMOUNT_CAT [100,250)
#> 34 rev-23506121 300 FY17 AMOUNT_CAT [250,500)
#> 35 rev-23550444 100 FY17 AMOUNT_CAT [100,250)
#> 36 rev-21508672 25 FY14 AMOUNT_CAT [25,50)
#> 37 rev-24981769 500 FY18 AMOUNT_CAT [500,1e+03)
#> 38 rev-23503684 50 FY17 AMOUNT_CAT [50,75)
#> 39 rev-24982087 25 FY18 AMOUNT_CAT [25,50)
#> 40 rev-24979834 50 FY18 AMOUNT_CAT [50,75)
data_tidy %>%
group_by(REPORT_CATEGORY, REPORT_VALUE, YEAR) %>%
summarise(num = n(), total = sum(AMOUNT)) %>%
ungroup()
#> # A tibble: 27 x 5
#> REPORT_CATEGORY REPORT_VALUE YEAR num total
#> <chr> <chr> <chr> <int> <int>
#> 1 AMOUNT_CAT [0.01,10) FY16 1 1
#> 2 AMOUNT_CAT [100,250) FY17 2 200
#> 3 AMOUNT_CAT [25,50) FY14 1 25
#> 4 AMOUNT_CAT [25,50) FY18 2 55
#> 5 AMOUNT_CAT [250,500) FY17 1 300
#> 6 AMOUNT_CAT [50,75) FY17 1 50
#> 7 AMOUNT_CAT [50,75) FY18 1 50
#> 8 AMOUNT_CAT [500,1e+03) FY18 1 500
#> 9 INBOUND_CHANNEL Canvassing FY16 1 1
#> 10 INBOUND_CHANNEL Mail FY14 1 25
#> # ... with 17 more rows
A purrr approach added to make your code a little conciser and smarter.
library(tidyverse)
library(rlang)
cat.list <- c("REPORT_CODE","PAYMENT_METHOD","INBOUND_CHANNEL","AMOUNT_CAT")
map_df(cat.list,
function(report_cat) {
sample_data %>%
group_by(!!sym(report_cat), YEAR) %>%
summarize(num=n(),total=sum(AMOUNT)) %>%
rename(REPORT_VALUE = !!sym(report_cat)) %>%
mutate(REPORT_CATEGORY = report_cat)
}
)
As Hadley describes here (about halfway down):
map_df(x, f) is effectively the same as do.call("rbind", lapply(x, f)) but under the hood is much more efficient.
Full disclosure, Thank you @useR for showing me how to use the sym(!!() approach. I had backed myself into a corner using the Programming in Dplyr vignette to construct what I believed was the most up to date approach to functionalizing dplyr.
I had gotten the main dplyr function to run smoothly enough using var <- enquo(var) then !!vars but I could not find a way to then deal with running the quoted names in cat.list through map_df or lapply.
Thank you, useR for teaching me a better way to code in tidyverse
Edit: Thank you, G. Grothendieck for unlocking how to get a list of strings to smoothly be accepted by the dplyr function: here
That allows me to complete the alternative quosured approach I had developed earlier:
report <- function(report_cat){
report_cat <- enquo(report_cat)
sample_data %>%
group_by(!!report_cat, YEAR) %>%
summarize(num=n(),total=sum(AMOUNT)) %>%
rename(REPORT_VALUE = !!report_cat) %>%
mutate(REPORT_CATEGORY := as.character(quote(!!report_cat))[2])
}
report_named <- function(x) {do.call("report", list(as.name(x)))}
map_df(cat.list, report_named)
> map_df(cat.list, report_named)
# A tibble: 27 x 5
# Groups: REPORT_VALUE [16]
REPORT_VALUE YEAR num total REPORT_CATEGORY
<chr> <chr> <int> <int> <chr>
1 J FY14 1 25 REPORT_CODE
2 Q FY16 1 1 REPORT_CODE
3 Q FY17 1 100 REPORT_CODE
4 R FY17 1 50 REPORT_CODE
5 R FY18 2 75 REPORT_CODE
6 S FY17 2 400 REPORT_CODE
7 S FY18 2 530 REPORT_CODE
8 Check FY14 1 25 PAYMENT_METHOD
9 Check FY17 1 50 PAYMENT_METHOD
10 Check FY18 2 55 PAYMENT_METHOD
# ... with 17 more rows
NB: yutannihilation's tidy solution really is the optimal solution IMHO - I was just using this as an opportunity to expand my understanding of how we can carry along the split, apply, combine approach to include dplyr functions.
