r-从宽的一次重复列到宽的数据轴

Question

假设我有以下宽格式的数据：

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
)

由于ValueX是一个重复变量，在ID组变量中不发生变化，所以我只想将其添加为由ID标识的新行。这将是所需的输出：

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
)

Answer 1

这里有一种通过基R的方法。您可以聚合每个时间的ValueX并获得每个时间的第一个观测值。然后创建一个与原始data名称相同的数据帧，只是rbind，即

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

Answer 2

使用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