-1

I have transposed and populated a data frame which now looks like this:

enter image description here

Now I am trying to overwrite all of the values in columns Signal1 and Signal 2 in upward direction by grouping them by ID and Date simultaneously. All of the previous rows should be populated by the last value for a given group. The output should look like this:

enter image description here

I tried to approach the problem with dplyr but not sure how to handle it:

df %>% group_by(ID, Date) %>% mutate (...)

Have to perform this operation on many more columns (Signal1 - Signal 30) so hopefully an universal solution is available.

Pavel Kaloferov
  • 19
  • 3
  • 2
    Please provide your example data with using `dput` on your data and copying the resulting text/code into your question, don't use images to show the data – starja Jul 27 '20 at 20:28
  • 1
    maybe this works? `df_new <- df %>% group_by(ID, Date) %>% arrange(Date) %>% mutate(across(starts_with("Signal"), last()))` – starja Jul 27 '20 at 20:34
  • @starja what is the significant of the `last()` can u explain pls? – linkonabe Jul 27 '20 at 21:38
  • @linkonabe I've made a mistake there, it should only be `last`. `last` returns the last value for every column (taking the grouping variable into account), because @Pavel Kaloferov wants to replace all values in a group/Signal column with the last value – starja Jul 27 '20 at 21:46

1 Answers1

1

This should do it -

df %>%
  group_by(ID, Date) %>%
  mutate(
    Signal1 = last(Signal1),
    Signal2 = last(Signal2)
  ) %>%
  ungroup()

EDIT: Didn't see the last part of the question. @starja 's comment is right on the money; to apply to many columns use -

df %>%
  group_by(ID, Date) %>%
  mutate(across(starts_with("Signal"), last)) %>%
  ungroup()
Shree
  • 10,835
  • 1
  • 14
  • 36