CODEWITHSUNDEEP
rdplyr
Raul
Raul

Reputation: 269

Sort dataframe into desired format using pivoting

I currently have a data frame with this format:

week    A_Revenue   B_Revenue   C_Revenue   D_Revenue   A_P_pct.chg B_P_pct.chg C_P_pct.chg D_P_pct.chg
34  8465.9  12299.98    10621   1548.375    59.50223    34.06917    41.46715    -3.305127
33  5307.7  9174.35 7507.75 1601.3  NA  NA  NA  NA

And I would like to format it the following way: enter image description here

How can I go about it? I tried tidyr::pivot_longer(2:5) which gives me the Revenue Source as a stand alone column but couldn't get the weekly revenues into their respective columns.

Edit: The full data set contains daily transactions to which I add:

dat$week <- format(dat$Date, format = "%V")

to include the week range for those days. Then I filter the desired weeks with the following code:

dat1 <- dat %>%
  filter(between(week, todays_week - 2, todays_week - 1)) %>%
  group_by(week) %>%
  summarise(A_Revenue = sum(A_Revenue),
            B_Revenue = sum(B_Revenue),
            C_Revenue = sum(C_Revenue),
            D_Revenue = sum(D_Revenue)) %>%
  mutate(A_P_pct.chg = 100 * ((A_Revenue - lag(A_Revenue))/lag(A_Revenue)),
         B_P_pct.chg = 100 * ((B_Revenue - lag(B_Revenue))/lag(B_Revenue)),
         C_P_pct.chg = 100 * ((C_Revenue - lag(C_Revenue))/lag(C_Revenue)),
         D_P_pct.chg = 100 * ((D_Revenue - lag(D_Revenue))/lag(D_Revenue)))

This yields the initial data frame posted which I am attempting to reshape into the image provided for easy comparison.

Upvotes: 1

Views: 54

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388807

Instead of calculating for each column differently you can get the data in long format and calculate sum and pct.change for each name.

library(dplyr)

todays_week = lubridate::week(Sys.Date())

dat %>%
  filter(between(week, todays_week - 2, todays_week - 1)) %>%
  tidyr::pivot_longer(cols = ends_with('Revenue')) %>%
  group_by(name, week) %>%
  summarise(value = sum(value, na.rm = TRUE)) %>%
  mutate(rev_prev_week = lag(value),
         pct_change = (value - rev_prev_week)/rev_prev_week * 100)

Upvotes: 1

Related Questions