Cannot summarize in R

Question

I don't know why the summarize function did not work.

Here is my dataset: https://onedrive.live.com/?authkey=%21AMGFcNnnBmsa3Js&id=B09F9559F6A16B6C%2171378&cid=B09F9559F6A16B6C

Here is my code chunk:

orders <- read_excel("Orders.xlsx")
customers <- read_excel("Customers.xlsx")
types <- read_excel("Cookie Types.xlsx")

orders %>% mutate(cost = types$`Cost Per Cookie`*`Units Sold`, 
                   rev = types$`Revenue Per Cookie`*`Units Sold`, 
                   prof = rev - cost) %>%
group_by(Product) %>% summarize(percent = (prof / rev)*100)

and it resulted like this:

and I would like it to be like this:

Any help would be greatly appreciated!

Thank you!

Answer 1

Two issues. First, you need to join the orders and types data together.

Second, summarize requires a summary function, such as sum. Your code is dividing by group but on a per-row basis, so the summary is getting repeated.

So something like:

orders %>% 
  left_join(types, 
            by = c("Product" = "Cookie Type")) %>% 
  mutate(cost = `Cost Per Cookie` * `Units Sold`,
         rev = `Revenue Per Cookie` * `Units Sold`,
         prof = rev - cost) %>% 
  group_by(Product) %>% 
  summarize(percent = (sum(prof) / sum(rev)) * 100)

Result:

# A tibble: 6 × 2
  Product                       percent
  <chr>                           <dbl>
1 Chocolate Chip                   60  
2 Fortune Cookie                   50  
3 Oatmeal Raisin                   56  
4 Snickerdoodle                    62.5
5 Sugar                            58.3
6 White Chocolate Macadamia Nut    54.2