CODEWITHSUNDEEP
rkablekableextra
user14176250
user14176250

Reputation: 55

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

Upvotes: 1

Views: 658

Answers (3)

Ramkumar Pillai
Ramkumar Pillai

Reputation: 174

#HackerRank Solution

library(dplyr)
df <- tibble::tribble(
~Region, ~Representative, ~Item, ~Units, ~Price, ~Total_sales,
"West","Paul","Juicer",19,80,1520,
"Central","Peter","Ceiling fan",2,100,200,
"West","Connor","Juicer",17,80,1360,
"Central","David","Ceiling fan",8,100,800,
"Central","Thomas","Dish washer",12,300,3600,
"East","Richard","Juicer",20,80,1600,
"East","John","Toaster",5,50,250,
"East","Jared","Ceiling fan",16,100,1600,
"East","Adam","Washing machine",6,600,3600,
"Central","Mark","Ceiling fan",7,100,700,
"Central","Jimmy","Washing machine",13,600,7800,
"East","Stuart","Washing machine",15,600,9000,
"West","Aishley","Dish washer",10,300,3000,
"East","Rafel","Toaster",18,50,900,
"East","Rafel","Juicer",18,50,900,
"Central","Jaminee","Washing machine",4,600,2400
)
totals <- df %>% 
group_by(Region, Representative) %>% 
summarize(total_sale = sum(as.numeric(Total_sales))) %>% 
arrange(desc(total_sale)) %>% 
slice_head(n = 1)

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388982

You can use :

library(dplyr)
library(tidyr)

df %>%
  select(target, basic, Planned) %>%
  pivot_wider(names_from = target, values_from = c(basic, Planned), 
              names_glue = '{target}_{.value}') %>%
  select(order(stringr::str_extract(names(.), 'DT \\d+')))

If you are using the transposed version of your data, use the following code.

df %>%
  rownames_to_column('val') %>%
  filter(val %in% c('target', 'basic', 'Planned')) %>%
  t %>%
  as.data.frame() %>%
  setNames(.[1, ]) %>%
  slice(-1) %>%
  pivot_wider(names_from = target, values_from = c(basic, Planned), 
              names_glue = '{target}_{.value}') %>%
  select(order(stringr::str_extract(names(.), 'DT \\d+')))

R dataframes can't have multiple headings, if you want the output to look exactly as shown you might look into functions like knitr::kable or gt::gt and other functions to display pretty tables.

Upvotes: 1

Anoushiravan R
Anoushiravan R

Reputation: 21908

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.

Upvotes: 0

Related Questions