Create column with a certain week value by group

Question

I would like to create a column, by group, with a certain week's value from another column.

In this example New_column is created with the Number from the 2nd week for each group.

Group  Week  Number   New_column
 A      1      19         8
 A      2       8         8
 A      3      21         8
 A      4       5         8

 B      1       4        12
 B      2      12        12
 B      3      18        12
 B      4      15        12

 C      1       9         4
 C      2       4         4
 C      3      10         4
 C      4       2         4

I've used this method, which works, but I feel is a really messy way to do it:

library(dplyr)

df <- df %>%
  group_by(Group) %>%
  mutate(New_column = ifelse(Week == 2, Number, NA))

df <- df %>%
  group_by(Group) %>%
  mutate(New_column = sum(New_column, na.rm = T))

Try df %>% group_by(Group) %>% mutate(New_column = Number[Week == 2]) — Darren Tsai

Ric S Ric S · Accepted Answer · 2020-06-04T13:09:27

There are several solution possible, depending on what you need specifically. With your specific sample data, however, all of them give the same result

1) It identifies the week number from column Week, even if the dataframe is not sorted

df %>%
  group_by(Group) %>%
  mutate(New_column = Number[Week == 2])

However, if the weeks do not start from 1, this solution will still try to find the case only where Week == 2

2) If df is already sorted by Week inside each group, you could use

df %>% 
  group_by(Group) %>% 
  mutate(New_column = Number[2])

This solution does not take the week Number in which Week == 2, but rather the second week within each group, regardless of its actual Week value.

3) If df is not sorted by week, you could do it with

df %>% 
  group_by(Group) %>% 
  arrange(Week, .by_group = TRUE) %>% 
  mutate(New_column = Number[2])

and uses the same rationale as solution 2)

Create column with a certain week value by group

1 Answers