Search code examples
rkablekableextra

function for creating a summary table from sales data

I am looming for function to create a summary table where I can pass the table as input parameter and create a a summary like below, i am trying to create with dcast but does't able to make it dynamic for variables.

Sample data frame

target <- c("DT 0", "DT 1", "DT 2", "DT 3", "DT 4", "DT 5", "DT 6", "DT 7", "DT 8")
basic <- c(19,50,79,80,72,30,13,45,52)
beginner <- c(22,82,50,15,51,54,40,17,31)
medium <- c(18,66,29,32,40,34,57,60,84)
projected <- c(38,63,17,64,81,41,14,63,79)
Planned <- c(53,12,73,34,16,77,12,60,88)
df <- data.frame(target,basic,beginner,medium,projected,Planned)
df <- as.data.frame(t(df))

the required output should be like below

enter image description here

Solution

  • That's the best I could come up with, However I couldn't so far erase the DT_ part from the second header since the names should be unique and it throws and error. I will try to figure it out soon.

    library(dplyr)
library(tidyr)
library(kableExtra)

df %>% 
  pivot_longer(- target, names_to = "summary", values_to = "Banking") %>%
  filter(summary %in% c("basic", "Planned"),
         target %in% c("DT 0", "DT 5", "DT 8")) %>%
  pivot_wider(names_from = c("target", "summary"), values_from = Banking) %>%
  mutate(`Sales Review` = "Banking") %>%
  relocate(`Sales Review`) %>%
  kbl() %>%
  kable_classic() %>%
  add_header_above(c(" "= 1, "DT 0" = 2, "DT 5" = 2, "DT 8" = 2))

    Here is the output table.