In the example dataframe below, I want to subtract the "revenue" of entries in category "D" from the revenue (of the same product) in entries of categories "A", "B" and "C". The actual dataframe has a bunch of other columns and I need to do this many times, so using pivot_wider operations is a bit inneficient. Is there a quick way to do this?
product category revenue
0001 A 78
0001 B 98
0001 C 103
0001 D 95
0002 A 81
: : :
Results I'd like for the example dataframe:
product category revenue
0001 A -7
0001 B 3
0001 C 8
0001 D 95
: : :
What I've been doing so far:
df = df %>% pivot_wider(names_from = category, values_from = revenue)
df[,2:4] = df[,2:4] - df$D %>% as.data.frame()
df = df %>% pivot_longer(2:5, names_to = 'category', values_to = 'revenue')
Is there a better way of doing this?
df |>
mutate(
result = ifelse(category == "D", revenue, revenue - revenue[category == "D"]),
.by = product
)
# product category revenue result
# 1 1 A 78 -17
# 2 1 B 98 3
# 3 1 C 103 8
# 4 1 D 95 95
Using this data:
df = read.table(text = 'product category revenue
0001 A 78
0001 B 98
0001 C 103
0001 D 95', header = T)