如何将多个列合并为一个列并创建另一个指示来源的列？

由于多种原因，您的初始数据格式很笨拙......

• 尴尬的列名
• 数据值以相反的顺序显示
• 混合字符和数值

...因此，要到达您想去的地方，需要采取很多步骤。

这里有一种方法可以做到这一点。还会有其他人。我在每个步骤的开头都添加了评论，以说明我在做什么。如果您想详细调查以了解每一步会发生什么，只需打破管道即可。

d %>% 
  # Temporary, to allow combining into a single column
  mutate(across(starts_with("weight"), as.character)) %>% 
  # Pivot longer, discarding awkward column name suffixes
  pivot_longer(
    cols = c(starts_with("height"), starts_with("weight")),
    names_pattern = "(height|weight)_(.)",
    names_to = c("name", NA)
  ) %>% 
  # Discard missing values
  filter(!is.na(value)) %>% 
  # Define appointment number
  group_by(record_id, name) %>% 
  mutate(appt_num = 1 + n() - row_number()) %>% 
  arrange(record_id, name, appt_num) %>% 
  # Pivot to required format
  pivot_wider(
    names_from = name,
    values_from = value,
    id_cols = c(record_id, appt_num)
  ) %>% 
  # Convert weight back to numeric
  mutate(weight = as.numeric(weight)) %>% 
  # Remove grouping
  ungroup()
# A tibble: 6 × 4
  record_id appt_num height weight
      <dbl>    <dbl> <chr>   <dbl>
1        10        1 5ft6in    152
2        10        2 5ft6in    153
3        10        3 5ft6in    154
4        11        1 5.11      135
5        11        2 5.11      131
6        11        3 5.11      138

数据框示例：

library(dplyr)
library(tidyr)

df <- data.frame(record_id=c(rep(10,3),rep(11,3)),
           height_1=c(NA_character_,NA_character_,"5ft6in",NA_character_,NA_character_,"5.11"),
           height_1_v1=c(NA_character_,"5ft6in",NA_character_,NA_character_,"5.11",NA_character_),
           height_1_v1_v1=c("5ft6in",NA_character_,NA_character_,"5.11",NA_character_,NA_character_),
           weight_1=c(NA_character_,NA_character_,"152",NA_character_,NA_character_,"135"),
           weight_1_v1=c(NA_character_,"153",NA_character_,NA_character_,"131",NA_character_),
           weight_1_v1_v1=c("154",NA_character_,NA_character_,"138",NA_character_,NA_character_))


  record_id height_1 height_1_v1 height_1_v1_v1 weight_1 weight_1_v1 weight_1_v1_v1
1        10     <NA>        <NA>         5ft6in     <NA>        <NA>            154
2        10     <NA>      5ft6in           <NA>     <NA>         153           <NA>
3        10   5ft6in        <NA>           <NA>      152        <NA>           <NA>
4        11     <NA>        <NA>           5.11     <NA>        <NA>            138
5        11     <NA>        5.11           <NA>     <NA>         131           <NA>
6        11     5.11        <NA>           <NA>      135        <NA>           <NA>

使用pivot_longer转换 data.frame，排除record_id列并删除所有具有 NA 值的行。使用case_when命令为appt_num列赋值。删除列中的多余文本，并使用 pivot_wider 再次转换 data.frame。

df %>% pivot_longer(-c(record_id),names_to="col",values_to="text") %>% filter(!is.na(text)) %>% 
  mutate(appt_num=case_when(
    grepl("1_v1_v1",col) ~ 3,
    grepl("1_v1",col) ~ 2,
    TRUE ~ 1
  )) %>% mutate(col=gsub("_v1","",col)) %>% mutate(col=gsub("_1","",col)) %>% 
  pivot_wider(names_from = "col", values_from = "text") %>% arrange(record_id,appt_num)

# A tibble: 6 × 4
  record_id appt_num height weight
      <dbl>    <dbl> <chr>  <chr> 
1        10        1 5ft6in 152   
2        10        2 5ft6in 153   
3        10        3 5ft6in 154   
4        11        1 5.11   135   
5        11        2 5.11   131   
6        11        3 5.11   138 

更新：

如果有更多的列遵循相同的结构，则使用 stringr 库中的str_count。

library(stringr)

df %>% pivot_longer(-c(record_id),names_to="col",values_to="text") %>% filter(!is.na(text)) %>%
  mutate(appt_num=str_count(col,"_v1") + 1) %>%
  mutate(col=gsub("_v1","",col)) %>% mutate(col=gsub("_1","",col)) %>% 
  pivot_wider(names_from = "col", values_from = "text") %>% arrange(record_id,appt_num)

使用以下命令：

df %>%
    split.default(str_remove(names(.), "_\\d.*"))%>%
    map_df(~coalesce(!!!.))%>%
    mutate(aptt_num = row_number(), .by=record_id)

# A tibble: 6 × 4
  height record_id weight aptt_num
  <chr>      <int>  <int>    <int>
1 5ft6in        10    154        1
2 5ft6in        10    153        2
3 5ft6in        10    152        3
4 5.11          11    138        1
5 5.11          11    131        2
6 5.11          11    135        3

---

df %>%
    pack(height = starts_with('height'), weight = starts_with('weight'))%>%
    map_df(~ if(is.list(.x))coalesce(!!!.x) else .x) %>%
    mutate(apt_num = row_number(), .by = record_id)

# A tibble: 6 × 4
  record_id height weight apt_num
      <int> <chr>   <int>   <int>
1        10 5ft6in    154       1
2        10 5ft6in    153       2
3        10 5ft6in    152       3
4        11 5.11      138       1
5        11 5.11      131       2
6        11 5.11      135       3