1

Let's say I have this table:

week_number;day_of_week;amount
26;Monday;100
26;Tuesday;50
26;Wednesday;90
27;Monday;50
27;Tuesday;10
27;Wednesday;40

I want to convert it to the following using dplyr:

week_number;Monday;Tuesday;Wednesday
26;100;50;90
27;50;10;40

I know how to group by week_number and day_of_week and get the total for each. But I need separate columns for each day.

Thanks

p.d: The workaround I'm currently using relies on reshape and is as follows, but I can't believe the same can't be accomplished using dplyr:

library(reshape)
unpivoted_data_frame <- melt(dataframe, id=c("week_number", "day_of_week"))

final_data_frame <- cast(unpivoted_data_frame, week_number ~ day_of_week, sum)
Nahuel Patiño
  • 89
  • 8

2 Answers2

1

With tidyverse, we can create a sequence column by group and then do the spread

library(dplyr)
library(tidyr)
df1 %>% 
  group_by(day_of_week) %>%
  mutate(rn = row_number()) %>%
  spread(day_of_week, amount) %>%
  select(-rn)
# A tibble: 2 x 4
#  week_number Monday Tuesday Wednesday
#        <int>  <int>   <int>     <int>
#1          26    100      50        90
#2          27     50      10        40
akrun
  • 874,273
  • 37
  • 540
  • 662
1

Is there a reason you have to use only functions from dplyr? Here is a solution using the spread function from tidyr.

library(tidyverse)

dat2 <- dat %>%
  spread(day_of_week, amount)
dat2
# # A tibble: 2 x 4
#   week_number Monday Tuesday Wednesday
#         <dbl>  <dbl>   <dbl>     <dbl>
# 1          26    100      50        90
# 2          27     50      10        40

Data

dat <- tribble(
  ~week_number, ~day_of_week, ~amount,
  26, "Monday", 100,
  26, "Tuesday", 50,
  26, "Wednesday", 90,
  27, "Monday", 50,
  27, "Tuesday", 10,
  27, "Wednesday", 40
)
www
  • 38,575
  • 12
  • 48
  • 84