Pivot from wide one time-repeated column to wide

Question

Suppose I have the following data in that wide format:

data = tibble::tribble(
  ~ID,  ~Time,  ~Value,  ~ValueX,
  "A", 1, 11, 41,
  "A", 2, 12, 42,
  "A", 3, 13, 43,
  "B", 1, 21, 41,
  "B", 2, 22, 42,
  "B", 3, 23, 43,
  "C", 1, 31, 41,
  "C", 2, 32, 42,
  "C", 3, 33, 43
)

Since ValueX is a repeated variable that does not vary within ID group variable, I just want to add it as new rows identified by ID. This will be the desired output:

data.desired = tibble::tribble(
  ~ID,  ~Time,  ~Value,
  "A", 1, 11,
  "A", 2, 12,
  "A", 3, 13,
  "B", 1, 21,
  "B", 2, 22,
  "B", 3, 23,
  "C", 1, 31,
  "C", 2, 32,
  "C", 3, 33,
  "ValueX", 1, 41,
  "ValueX", 2, 42,
  "ValueX", 3, 41
)

It does not. Cause any regular way to reshape would lead me to two identifier variables, e.g. a combination of ```ID``` plus ```ID_new``` with a new value variable ```Value_new```, making ```ValueX``` be repeated across grouping variable. — Cristhian, Jul 22 '20 at 13:55

score 1 · Accepted Answer · answered Jul 22 '20 at 13:57

Here is a way via base R. You can aggregate ValueX per Time and get the first observation each. Then create a data frame with same names as your original data and simply rbind, i.e.

rbind(data[-ncol(data)], 
      setNames(data.frame('ValueX', aggregate(ValueX ~ Time, data, head, 1)),
               names(data[-ncol(data)])))

# A tibble: 12 x 3
#   ID      Time Value
#   <chr>  <dbl> <dbl>
# 1 A          1    11
# 2 A          2    12
# 3 A          3    13
# 4 B          1    21
# 5 B          2    22
# 6 B          3    23
# 7 C          1    31
# 8 C          2    32
# 9 C          3    33
#10 ValueX     1    41
#11 ValueX     2    42
#12 ValueX     3    43

Nice! It does what I need. I guess I can make the same in a ```dplyr``` way. Thank you! — Cristhian, Jul 22 '20 at 14:00

score 0 · Answer 2 · answered Jul 22 '20 at 14:44

use tidyverse

addCase <- distinct(data, Time, ValueX) %>% 
  pivot_longer(-Time, names_to = "ID", values_to = "Value") 

data %>% 
  select(-ValueX) %>% 
  add_case(addCase)

# A tibble: 12 x 3
   ID      Time Value
   <chr>  <dbl> <dbl>
 1 A          1    11
 2 A          2    12
 3 A          3    13
 4 B          1    21
 5 B          2    22
 6 B          3    23
 7 C          1    31
 8 C          2    32
 9 C          3    33
10 ValueX     1    41
11 ValueX     2    42
12 ValueX     3    43

Pivot from wide one time-repeated column to wide

2 Answers2