Below 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:
MOUNT_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
This is my attempt to condense the code to make it more efficient. (This code 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 tell me how to write "more efficient" code that doesn't repeat that often?