Row items as a percentage of a total row item

[kezzer]

Hi. I've got a dataframe of the following pattern:

Date     Item                        Purchased
01/01/08 Fruit                          48
01/01/08 Confectionary                  42
01/01/08 Appliance                      11
01/01/08 Total                         101
01/06/08 Confectionary                  16
01/06/08 Fruit                          19
01/06/08 Appliance                      50
01/06/08 Total                          85         

What I'd like to do is create a column that converts the value in the 'Purchased' row to its equivalent percentage of the total on that day. So Fruit on 01/06/08, represents ~22.35% of the total number of items purchased on 01/06/08 (total being 85). Anyone have any ideas on how to achieve this?

[FJCC]

If your data frame is named DF,

library(dplyr)
DF <- DF |> group_by(Date) |> mutate(Total = sum(Purchased), Perc = Purchased/Total)

[Ven]

Assuming this is what you're looking for.

library(data.table)

# kezzer's table
dt <- data.table(
  Date = as.Date(
    c(
      "2008-01-01", "2008-01-01", "2008-01-01", "2008-01-01",
      "2008-01-06", "2008-01-06", "2008-01-06", "2008-01-06"
    )
  ),
  Item = c(
    "Fruit", "Confectionary", "Appliance", "Total",
    "Confectionary", "Fruit", "Appliance", "Total"
  ),
  Purchased = c(48, 42, 11, 101, 16, 19, 50, 85)
)

# Seperate data; with and without "Total"
dt_a <- dt[Item != "Total"]
dt_b <- dt[Item == "Total"]

# Combine dt_a and dt_b; Define [Percent]; remove unwanted columns
dt_c <- dt_a[dt_b, on = .(Date = Date)][, Percent := paste0(round(Purchased * 100 / i.Purchased, 2), " %")][, `:=` (i.Purchased = NULL, i.Item = NULL)]