如何在不结合名称的情况下``pivot_wider`多列''？

Question

我想通过在不合并名称的情况下组合多个列来将pivot_wider应用于整形的tibble。我的数据包含有关患者药物的信息。给定患者可能会或可能不服用各种药物。这些列包含毒品名称和每日剂量按任意顺序。

问题在于我目前的方法会产生太多的列，因为毒品名称是组合的。请参阅此可重复的示例：

library(tidyverse)

# Let's have data of patients who may or may not take some drugs with a
# corresponding dose:
(
  medication <- tribble(
    ~dob,           ~drug_1,  ~dose_1, ~drug_2, ~dose_2, ~drug_3,  ~dose_3,
    "1970-01-01",   "Drug A", 100,     NA,      NA,      NA,       NA,
    "1980-01-01",   "Drug B", 150,    "Drug A", 200,     NA,       NA,
    "1990-01-01",   NA,       NA,     "Drug C", 500,     "Drug B", 100
  )
)

# The desired arrangement is as follows:
#
# dob        | 'Drug A' | 'Drug B' | 'Drug C'
# -----------|-------------------------------
# 1970-01-01 | 100      | NA       | NA
# 1980-01-01 | 200      | 150      | NA
# 1980-01-01 | NA       | 100      | 500

# The following attempt to pivot wider creates too many columns by combining all the drug names:

medication %>%
  pivot_wider(names_from = starts_with("drug_"),
              values_from = starts_with("dose_"))

# # A tibble: 3 × 10
#   dob        `dose_1_Drug A_NA_NA` `dose_1_Drug B_Drug A_NA` `dose_1_NA_Drug C_Drug B` `dose_2_Drug A_NA_NA` `dose_2_Drug B_Drug A_NA` `dose_2_NA_Drug C_Drug B` `dose_3_Drug A_NA_NA` `dose_3_Drug B_Drug A_NA` `dose_3_NA_Drug C_Drug B`
#   <chr>                      <dbl>                     <dbl>                     <dbl>                 <dbl>                     <dbl>                     <dbl>                 <dbl>                     <dbl>                     <dbl>
# 1 1970-01-01                   100                        NA                        NA                    NA                        NA                        NA                    NA                        NA                        NA
# 2 1980-01-01                    NA                       150                        NA                    NA                       200                        NA                    NA                        NA                        NA
# 3 1990-01-01                    NA                        NA                        NA                    NA                        NA                       500                    NA                        NA                       100

我什至尝试应用pivot_wider函数多次，每次明确说明names_from =“ drug_1”等等，但这会导致“存在“错误。

如上所示，有没有办法实现所需的布置？谢谢。

Answer 1

两个步骤，首先旋转更长：

medication %>% 
  pivot_longer(
    cols = -dob,
    names_to = c(".value", NA),
    names_sep = "_",
    values_drop_na = TRUE
  ) %>% 
  pivot_wider(
    names_from = drug,
    values_from = dose
  )

# A tibble: 3 x 4
  dob        `Drug A` `Drug B` `Drug C`
  <chr>         <dbl>    <dbl>    <dbl>
1 1970-01-01      100       NA       NA
2 1980-01-01      200      150       NA
3 1990-01-01       NA      100      500