Aggregate columns into new ones depending on row value in r

Question

I'm searching for a way to aggregate rows, based on date and ID, but arranging the values depending on one of te colums' values.

Basically I want to convert this:

       DATE       ID    V1    V2    SHIFT
1    2020-05-21    A    21    34    morning
2    2020-05-21    A    6     12    afternoon
3    2020-05-20    A    19    15    morning
4    2020-05-20    A    7     16    afternoon
5    2020-05-20    B    17    20    morning
6    2020-05-20    B    5     11    afternoon

Into this:

       DATE       ID    V1_mor    V2_mor    V1_aft    V2_aft
1    2020-05-21    A      21        34         6        12
2    2020-05-20    A      19        15         7        16
3    2020-05-20    B      17        20         5        11

I've tried the route of making a loop and adding a bunch of "if" statements, but I don't want to get caught in bad practice.

The goal is so I can later add another column with a calculation of the style of (v1_mor * v2_aft) / v2_mor

Did you make a mistake with "B"? And this looks like a simple reshaping task, not aggregation. — Edward, May 21 '20 at 10:54

Martin Gal · Accepted Answer · 2020-05-21T11:30:42.543

Your goal can be achieved by using pivot_wider() from library tidyr:

library(tidyverse)

df %>%
  pivot_wider(names_from = SHIFT, values_from = c(V1, V2))

This gives

# A tibble: 3 x 6
# Groups:   DATE [2]
  DATE       ID    V1_morning V1_afternoon V2_morning V2_afternoon
  <date>     <chr>      <dbl>        <dbl>      <dbl>        <dbl>
1 2020-05-21 A             21            6         34           12
2 2020-05-20 A             19            7         15           16
3 2020-05-20 B             17            5         20           11

Data

df <- read_table2("DATE       ID    V1    V2    SHIFT
2020-05-21    A    21    34    morning
2020-05-21    A    6     12    afternoon
2020-05-20    A    19    15    morning
2020-05-20    A    7     16    afternoon
2020-05-20    B    17    20    morning
2020-05-20    B    5     11    afternoon")

Aggregate columns into new ones depending on row value in r

1 Answers1

Data