当前位置： 文江博客话题详情

使用突变 - dplyr分裂多列

发布于 2025-02-12 19:28:18 字数 13211 浏览 1 评论 0原文

我正在尝试将多列拆分，但努力进行有效的操作。

我有一个DF：

> dput(df)
structure(list(UNIQUE_PATIENT_ID = c("DIS-1101-1001-E1", "DIS-1101-1002-E1", 
"DIS-1101-1003-E1", "DIS-1101-1004-E1", "DIS-1101-1004-E2", "DIS-1101-1005-E1", 
"DIS-1101-1006-E1", "DIS-1101-1007-E1", "DIS-1101-1008-E1", "DIS-1101-1009-E1", 
"DIS-1101-1010-E1", "DIS-1101-1011-E1", "DIS-1101-1012-E1", "DIS-1101-1013-E1", 
"DIS-1101-1014-E1", "DIS-1101-1015-E1", "DIS-1101-1016-E1", "DIS-1101-1017-E1", 
"DIS-1101-1018-E1", "DIS-1101-1019-E1", "DIS-1101-1020-E1", "DIS-1101-1021-E1", 
"DIS-1101-1022-E1", "DIS-1101-1023-E1", "DIS-1101-1025-E1", "DIS-1101-1026-E1", 
"DIS-1101-1027-E1", "DIS-1101-1028-E1", "DIS-1101-1029-E1", "DIS-1101-1030-E1", 
"DIS-1101-1031-E1", "DIS-1101-1032-E1", "DIS-1101-1033-E1", "DIS-1101-1034-E1", 
"DIS-1101-1035-E1", "DIS-1101-1036-E1", "DIS-1101-1037-E1", "DIS-1101-1038-E1", 
"DIS-1101-1039-E1", "DIS-1101-1040-E1", "DIS-1101-1041-E1", "DIS-1101-1042-E1", 
"DIS-1101-1042-E2", "DIS-1101-1043-E1", "DIS-1101-1044-E1", "DIS-1101-1045-E1", 
"DIS-1101-1046-E1", "DIS-1101-1047-E1", "DIS-1101-1048-E1", "DIS-1101-1049-E1"
), `TIME_POINT_1=HAEMOGLOBIN` = c("BASELINE=123.00;FIRST=117.00", 
"FIRST=92.00", "FIRST=111.00", "BASELINE=125.00;FIRST=113.00", 
"FIRST=124.00", "NULL=NULL", "NULL=NULL", "FIRST=77.00", "FIRST=132.00", 
"FIRST=123.00", "BASELINE=119.00;FIRST=93.00", "BASELINE=163.00;FIRST=141.00", 
"FIRST=137.00", "NULL=NULL", "NULL=NULL", "FIRST=137.00", "FIRST=127.00", 
"BASELINE=NULL;FIRST=101.00;SECOND=105.00", "FIRST=120.00", "FIRST=145.00", 
"FIRST=126.00", "BASELINE=93.00;FIRST=82.00", "BASELINE=114.00;FIRST=95.00", 
"BASELINE=137.00;FIRST=125.00", "BASELINE=157.00;FIRST=112.00", 
"NULL=NULL", "FIRST=127.00", "FIRST=147.00", "BASELINE=80.00;FIRST=92.00", 
"BASELINE=129.00;FIRST=131.00", "FIRST=108.00", "BASELINE=92.00;FIRST=80.00", 
"BASELINE=118.00;FIRST=107.00", "NULL=NULL", "NULL=NULL", "BASELINE=NULL;FIRST=89.00", 
"NULL=NULL", "BASELINE=NULL;FIRST=101.00", "FIRST=151.00", "FIRST=106.00", 
"BASELINE=81.00;FIRST=87.00", "FIRST=94.00;SECOND=85.00", "FIRST=85.00", 
"FIRST=118.00;SECOND=116.00", "FIRST=101.00;SECOND=103.00", "FIRST=114.00", 
"BASELINE=NULL;FIRST=129.00", "BASELINE=123.00;FIRST=105.00", 
"NULL=NULL", "NULL=NULL"), `TIME_POINT_2=WHITE_CELLS` = c("BASELINE=2.90;FIRST=2.40", 
"FIRST=14.80", "FIRST=11.00", "BASELINE=6.90;FIRST=7.30", "FIRST=4.50", 
"NULL=NULL", "NULL=NULL", "FIRST=6.10", "FIRST=7.70", "FIRST=16.20", 
"BASELINE=7.40;FIRST=5.30", "BASELINE=12.80;FIRST=10.30", "FIRST=14.50", 
"NULL=NULL", "NULL=NULL", "FIRST=12.80", "FIRST=3.70", "BASELINE=4.20;FIRST=4.70;SECOND=6.50", 
"FIRST=16.60", "FIRST=9.30", "FIRST=15.90", "BASELINE=31.00;FIRST=31.70", 
"BASELINE=3.70;FIRST=7.00", "BASELINE=14.10;FIRST=7.20", "BASELINE=21.80;FIRST=31.20", 
"NULL=NULL", "FIRST=9.20", "FIRST=5.90", "BASELINE=2.20;FIRST=0.50", 
"BASELINE=10.50;FIRST=10.60", "FIRST=12.00", "BASELINE=14.70;FIRST=11.80", 
"BASELINE=13.40;FIRST=13.00", "NULL=NULL", "NULL=NULL", "BASELINE=NULL;FIRST=18.60", 
"NULL=NULL", "BASELINE=4.30;FIRST=5.90", "FIRST=7.60", "FIRST=3.10", 
"BASELINE=10.10;FIRST=11.40", "FIRST=12.00;SECOND=21.30", "FIRST=9.80", 
"FIRST=29.50;SECOND=4.80", "FIRST=4.60;SECOND=9.90", "FIRST=18.90", 
"BASELINE=15.00;FIRST=13.00", "BASELINE=12.80;FIRST=10.60", "NULL=NULL", 
"NULL=NULL")), row.names = c(NA, -50L), class = c("tbl_df", "tbl", 
"data.frame"))

我想要这个输出：

dput(output)
structure(list(UNIQUE_PATIENT_ID = c("DIS-1101-1001-E1", "DIS-1101-1002-E1", 
"DIS-1101-1003-E1", "DIS-1101-1004-E1", "DIS-1101-1004-E2", "DIS-1101-1005-E1", 
"DIS-1101-1006-E1", "DIS-1101-1007-E1", "DIS-1101-1008-E1", "DIS-1101-1009-E1", 
"DIS-1101-1010-E1", "DIS-1101-1011-E1", "DIS-1101-1012-E1", "DIS-1101-1013-E1", 
"DIS-1101-1014-E1", "DIS-1101-1015-E1", "DIS-1101-1016-E1", "DIS-1101-1017-E1", 
"DIS-1101-1018-E1", "DIS-1101-1019-E1", "DIS-1101-1020-E1", "DIS-1101-1021-E1", 
"DIS-1101-1022-E1", "DIS-1101-1023-E1", "DIS-1101-1025-E1", "DIS-1101-1026-E1", 
"DIS-1101-1027-E1", "DIS-1101-1028-E1", "DIS-1101-1029-E1", "DIS-1101-1030-E1", 
"DIS-1101-1031-E1", "DIS-1101-1032-E1", "DIS-1101-1033-E1", "DIS-1101-1034-E1", 
"DIS-1101-1035-E1", "DIS-1101-1036-E1", "DIS-1101-1037-E1", "DIS-1101-1038-E1", 
"DIS-1101-1039-E1", "DIS-1101-1040-E1", "DIS-1101-1041-E1", "DIS-1101-1042-E1", 
"DIS-1101-1042-E2", "DIS-1101-1043-E1", "DIS-1101-1044-E1", "DIS-1101-1045-E1", 
"DIS-1101-1046-E1", "DIS-1101-1047-E1", "DIS-1101-1048-E1", "DIS-1101-1049-E1"
), Haemoglobin_BASELINE = c("123.00", NA, NA, "125.00", NA, NA, 
NA, NA, NA, NA, "119.00", "163.00", NA, NA, NA, NA, NA, "NULL", 
NA, NA, NA, "93.00", "114.00", "137.00", "157.00", NA, NA, NA, 
"80.00", "129.00", NA, "92.00", "118.00", NA, NA, "NULL", NA, 
"NULL", NA, NA, "81.00", NA, NA, NA, NA, NA, "NULL", "123.00", 
NA, NA), Haemoglobin_FIRST = c("117.00", "92.00", "111.00", "113.00", 
"124.00", NA, NA, "77.00", "132.00", "123.00", "93.00", "141.00", 
"137.00", NA, NA, "137.00", "127.00", "101.00", "120.00", "145.00", 
"126.00", "82.00", "95.00", "125.00", "112.00", NA, "127.00", 
"147.00", "92.00", "131.00", "108.00", "80.00", "107.00", NA, 
NA, "89.00", NA, "101.00", "151.00", "106.00", "87.00", "94.00", 
"85.00", "118.00", "101.00", "114.00", "129.00", "105.00", NA, 
NA), Haemoglobin_SECOND = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, "105.00", NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "85.00", NA, "116.00", "103.00", NA, NA, NA, NA, NA), 
    WBC_BASELINE = c("2.90", NA, NA, "6.90", NA, NA, NA, NA, 
    NA, NA, "7.40", "12.80", NA, NA, NA, NA, NA, "4.20", NA, 
    NA, NA, "31.00", "3.70", "14.10", "21.80", NA, NA, NA, "2.20", 
    "10.50", NA, "14.70", "13.40", NA, NA, "NULL", NA, "4.30", 
    NA, NA, "10.10", NA, NA, NA, NA, NA, "15.00", "12.80", NA, 
    NA), WBC_FIRST = c("2.40", "14.80", "11.00", "7.30", "4.50", 
    NA, NA, "6.10", "7.70", "16.20", "5.30", "10.30", "14.50", 
    NA, NA, "12.80", "3.70", "4.70", "16.60", "9.30", "15.90", 
    "31.70", "7.00", "7.20", "31.20", NA, "9.20", "5.90", "0.50", 
    "10.60", "12.00", "11.80", "13.00", NA, NA, "18.60", NA, 
    "5.90", "7.60", "3.10", "11.40", "12.00", "9.80", "29.50", 
    "4.60", "18.90", "13.00", "10.60", NA, NA), WBC_SECOND = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, "6.50", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "21.30", NA, 
    "4.80", "9.90", NA, NA, NA, NA, NA)), row.names = c(NA, -50L
), class = "data.frame")

我可以实现这一目标，但是我的代码效率低下（我在每个血列上这样做）：

#haemoglobin
haem_ID <- df %>% select(UNIQUE_PATIENT_ID, `TIME_POINT_1=HAEMOGLOBIN`)

haem_sep <- haem_ID %>%
  rename(text = `TIME_POINT_1=HAEMOGLOBIN`) %>%
  separate_rows(text, sep = ';') %>%
  separate(text, c('text', 'value'), sep = '=') %>%
  pivot_wider(names_from = text, values_from = value)

tmp <- haem_sep %>% select(-UNIQUE_PATIENT_ID)
colnames(tmp) <- paste("Haemoglobin", colnames(tmp), sep = "_")
tmp2 <- haem_ID %>% select(UNIQUE_PATIENT_ID)
cbind(tmp2, tmp) -> tmp3
tmp3 %<>% select(-Haemoglobin_NULL)
haem <- tmp3


#white cells
a <- df %>% select(UNIQUE_PATIENT_ID, `TIME_POINT_2=WHITE_CELLS`)

b <- a %>%
  rename(text = `TIME_POINT_2=WHITE_CELLS`) %>%
  separate_rows(text, sep = ';') %>%
  separate(text, c('text', 'value'), sep = '=') %>%
  pivot_wider(names_from = text, values_from = value)

tmp <- b %>% select(-UNIQUE_PATIENT_ID)
colnames(tmp) <- paste("WBC", colnames(tmp), sep = "_")
tmp2 <- a %>% select(UNIQUE_PATIENT_ID)
cbind(tmp2, tmp) -> tmp3
tmp3 %<>% select(-WBC_NULL)
WBC <- tmp3

output <- merge(haem, WBC)

请有人帮助我实现相同但更有效地实现吗？实际上，我进行了20次血液检查，而不仅仅是2次，因此进行硬编码不是理想的。

更新

在这种响应时，我尝试运行建议的代码，但是我遇到了一个错误，但我不知道为什么。

输入数据

structure(list(UNIQUE_PATIENT_ID = c("DIS-1104-1021-E1", "DIS-1107-1010-E1", 
"DIS-1124-1108-E1"), `TIME_POINT_1=HAEMOGLOBIN` = c("BASELINE=98.00;FIRST=90.00;SECOND=81.00;FIRST=85.00", 
"FIRST=120.00;NULL=120.00;NULL=118.00;SECOND=109.00;NULL=186.00", 
"BASELINE=78.00;FIRST=NULL;FIRST=99.00"), `TIME_POINT_2=WHITE_CELLS` = c("BASELINE=0.01;FIRST=NULL;SECOND=0.01;FIRST=0.00", 
"FIRST=15.40;NULL=16.10;NULL=14.50;SECOND=11.40;NULL=2.20;NULL=5.00", 
"BASELINE=85.00;FIRST=NULL;FIRST=209.00"), `TIME_POINT_3=PLATELETS` = c("BASELINE=28.00;FIRST=28.00;SECOND=12.00;FIRST=21.00", 
"FIRST=200.00;NULL=186.00;NULL=203.00;SECOND=243.00;NULL=145.00;NULL=457.00", 
"BASELINE=186.00;FIRST=NULL"), `TIME_POINT_4=NEUTROPHILS` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=13.00;NULL=13.20;NULL=11.10;SECOND=7.70;NULL=1.30;NULL=2.20", 
"BASELINE=7.10;FIRST=2.40;FIRST=NULL"), `TIME_POINT_5=LYMPHOCYTES` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=0.80;NULL=1.60;NULL=1.40;SECOND=1.70;NULL=0.40", "BASELINE=76.80;FIRST=135.00;FIRST=NULL"
), `TIME_POINT_6=MONOCYTES` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=1.50;NULL=1.20;NULL=1.90;SECOND=2.00;NULL=0.40;NULL=0.90", 
"BASELINE=1.40;FIRST=4.20;FIRST=NULL"), `TIME_POINT_7=EOSINOPHILS` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=0.10;NULL=0.00;SECOND=0.00;NULL=0.10;NULL=0.30", "BASELINE=0.10;FIRST=0.40;FIRST=NULL"
), `TIME_POINT_8=FIBRINOGEN` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=6.90;NULL=6.60;NULL=7.10;SECOND=6.60;NULL=6.00;NULL=NULL", 
"BASELINE=6.70;FIRST=NULL"), `TIME_POINT_9=PT` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=19.20;NULL=17.20;NULL=16.10;SECOND=14.40;NULL=14.90;NULL=NULL", 
"BASELINE=13.40;FIRST=NULL"), `TIME_POINT_10=UREA` = c("BASELINE=4.30;FIRST=4.30;SECOND=5.10;FIRST=4.50", 
"FIRST=3.80;NULL=3.70;NULL=4.40;SECOND=2.80;NULL=2.90;NULL=4.10", 
"BASELINE=5.10;FIRST=NULL;FIRST=7.20"), `TIME_POINT_11=CREATININE` = c("BASELINE=67.00;FIRST=55.00;SECOND=61.00;FIRST=60.00", 
"FIRST=44.00;NULL=42.00;NULL=45.00;SECOND=42.00;NULL=33.00", 
"BASELINE=65.00;FIRST=NULL;FIRST=72.00"), `TIME_POINT_12=ALT` = c("BASELINE=9.00;FIRST=11.00;SECOND=8.00;FIRST=5.00", 
"FIRST=20.00;NULL=NULL;NULL=19.00;SECOND=16.00;NULL=16.00;NULL=21.00", 
"BASELINE=64.00;FIRST=NULL;FIRST=92.00"), `TIME_POINT_13=BILIRUBIN` = c("BASELINE=3.00;FIRST=5.00;SECOND=3.00;FIRST=NULL", 
"FIRST=20.00;NULL=NULL;NULL=11.00;SECOND=10.00;NULL=5.00", "BASELINE=7.00;FIRST=NULL;FIRST=10.00"
), `TIME_POINT_14=ALBUMIN` = c("BASELINE=42.00;FIRST=39.00;SECOND=35.00;FIRST=36.00", 
"FIRST=42.00;NULL=NULL;NULL=37.00;SECOND=35.00;NULL=35.00;NULL=39.00", 
"BASELINE=31.00;FIRST=NULL;FIRST=40.00"), `TIME_POINT_15=VITAMIN_D` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_16=CRP` = c("BASELINE=NULL;FIRST=172.00;SECOND=116.00;FIRST=212.00", 
"FIRST=427.00;NULL=420.00;NULL=338.00;SECOND=215.00;NULL=87.00;NULL=64.00", 
"BASELINE=199.00;FIRST=NULL;FIRST=1.00"), `TIME_POINT_17=PCT` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_18=FERRITIN` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;NULL=378.00;SECOND=NULL", "BASELINE=809.00;FIRST=432.00;FIRST=NULL"
), `TIME_POINT_19=TROP_T` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;NULL=3.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_20=D_DIMER` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=1.85;NULL=1.65;SECOND=NULL;NULL=NULL", "BASELINE=749.00;FIRST=486.00;FIRST=NULL"
), `TIME_POINT_21=INR` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_22=BNP_NT` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_23=LDH` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=254.00;FIRST=NULL"
), `TIME_POINT_24=CK` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;NULL=29.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_25=SCD_25` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_26=BASE_EXCESS` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=3.10;NULL=1.50;NULL=NULL;SECOND=NULL", "BASELINE=-3.80;FIRST=NULL"
), `TIME_POINT_27=LACTATE` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=0.90;NULL=1.30;NULL=NULL;SECOND=NULL", "BASELINE=0.60;FIRST=NULL"
)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"
))

代码：

output <- input |> 
  separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*=")) |> 
  pivot_wider(names_from = c("name", "timepoint")) |> 
  select(-ends_with("NULL"))

错误消息：

Error in `fn()`:
! In row 2, can't recycle input of size 5 to size 6.
Run `rlang::last_error()` to see where the error occurred.

谢谢！

原文

I'm trying to split multiple columns, but struggling to do this efficiently.

I have a df:

> dput(df)
structure(list(UNIQUE_PATIENT_ID = c("DIS-1101-1001-E1", "DIS-1101-1002-E1", 
"DIS-1101-1003-E1", "DIS-1101-1004-E1", "DIS-1101-1004-E2", "DIS-1101-1005-E1", 
"DIS-1101-1006-E1", "DIS-1101-1007-E1", "DIS-1101-1008-E1", "DIS-1101-1009-E1", 
"DIS-1101-1010-E1", "DIS-1101-1011-E1", "DIS-1101-1012-E1", "DIS-1101-1013-E1", 
"DIS-1101-1014-E1", "DIS-1101-1015-E1", "DIS-1101-1016-E1", "DIS-1101-1017-E1", 
"DIS-1101-1018-E1", "DIS-1101-1019-E1", "DIS-1101-1020-E1", "DIS-1101-1021-E1", 
"DIS-1101-1022-E1", "DIS-1101-1023-E1", "DIS-1101-1025-E1", "DIS-1101-1026-E1", 
"DIS-1101-1027-E1", "DIS-1101-1028-E1", "DIS-1101-1029-E1", "DIS-1101-1030-E1", 
"DIS-1101-1031-E1", "DIS-1101-1032-E1", "DIS-1101-1033-E1", "DIS-1101-1034-E1", 
"DIS-1101-1035-E1", "DIS-1101-1036-E1", "DIS-1101-1037-E1", "DIS-1101-1038-E1", 
"DIS-1101-1039-E1", "DIS-1101-1040-E1", "DIS-1101-1041-E1", "DIS-1101-1042-E1", 
"DIS-1101-1042-E2", "DIS-1101-1043-E1", "DIS-1101-1044-E1", "DIS-1101-1045-E1", 
"DIS-1101-1046-E1", "DIS-1101-1047-E1", "DIS-1101-1048-E1", "DIS-1101-1049-E1"
), `TIME_POINT_1=HAEMOGLOBIN` = c("BASELINE=123.00;FIRST=117.00", 
"FIRST=92.00", "FIRST=111.00", "BASELINE=125.00;FIRST=113.00", 
"FIRST=124.00", "NULL=NULL", "NULL=NULL", "FIRST=77.00", "FIRST=132.00", 
"FIRST=123.00", "BASELINE=119.00;FIRST=93.00", "BASELINE=163.00;FIRST=141.00", 
"FIRST=137.00", "NULL=NULL", "NULL=NULL", "FIRST=137.00", "FIRST=127.00", 
"BASELINE=NULL;FIRST=101.00;SECOND=105.00", "FIRST=120.00", "FIRST=145.00", 
"FIRST=126.00", "BASELINE=93.00;FIRST=82.00", "BASELINE=114.00;FIRST=95.00", 
"BASELINE=137.00;FIRST=125.00", "BASELINE=157.00;FIRST=112.00", 
"NULL=NULL", "FIRST=127.00", "FIRST=147.00", "BASELINE=80.00;FIRST=92.00", 
"BASELINE=129.00;FIRST=131.00", "FIRST=108.00", "BASELINE=92.00;FIRST=80.00", 
"BASELINE=118.00;FIRST=107.00", "NULL=NULL", "NULL=NULL", "BASELINE=NULL;FIRST=89.00", 
"NULL=NULL", "BASELINE=NULL;FIRST=101.00", "FIRST=151.00", "FIRST=106.00", 
"BASELINE=81.00;FIRST=87.00", "FIRST=94.00;SECOND=85.00", "FIRST=85.00", 
"FIRST=118.00;SECOND=116.00", "FIRST=101.00;SECOND=103.00", "FIRST=114.00", 
"BASELINE=NULL;FIRST=129.00", "BASELINE=123.00;FIRST=105.00", 
"NULL=NULL", "NULL=NULL"), `TIME_POINT_2=WHITE_CELLS` = c("BASELINE=2.90;FIRST=2.40", 
"FIRST=14.80", "FIRST=11.00", "BASELINE=6.90;FIRST=7.30", "FIRST=4.50", 
"NULL=NULL", "NULL=NULL", "FIRST=6.10", "FIRST=7.70", "FIRST=16.20", 
"BASELINE=7.40;FIRST=5.30", "BASELINE=12.80;FIRST=10.30", "FIRST=14.50", 
"NULL=NULL", "NULL=NULL", "FIRST=12.80", "FIRST=3.70", "BASELINE=4.20;FIRST=4.70;SECOND=6.50", 
"FIRST=16.60", "FIRST=9.30", "FIRST=15.90", "BASELINE=31.00;FIRST=31.70", 
"BASELINE=3.70;FIRST=7.00", "BASELINE=14.10;FIRST=7.20", "BASELINE=21.80;FIRST=31.20", 
"NULL=NULL", "FIRST=9.20", "FIRST=5.90", "BASELINE=2.20;FIRST=0.50", 
"BASELINE=10.50;FIRST=10.60", "FIRST=12.00", "BASELINE=14.70;FIRST=11.80", 
"BASELINE=13.40;FIRST=13.00", "NULL=NULL", "NULL=NULL", "BASELINE=NULL;FIRST=18.60", 
"NULL=NULL", "BASELINE=4.30;FIRST=5.90", "FIRST=7.60", "FIRST=3.10", 
"BASELINE=10.10;FIRST=11.40", "FIRST=12.00;SECOND=21.30", "FIRST=9.80", 
"FIRST=29.50;SECOND=4.80", "FIRST=4.60;SECOND=9.90", "FIRST=18.90", 
"BASELINE=15.00;FIRST=13.00", "BASELINE=12.80;FIRST=10.60", "NULL=NULL", 
"NULL=NULL")), row.names = c(NA, -50L), class = c("tbl_df", "tbl", 
"data.frame"))

I want this output:

dput(output)
structure(list(UNIQUE_PATIENT_ID = c("DIS-1101-1001-E1", "DIS-1101-1002-E1", 
"DIS-1101-1003-E1", "DIS-1101-1004-E1", "DIS-1101-1004-E2", "DIS-1101-1005-E1", 
"DIS-1101-1006-E1", "DIS-1101-1007-E1", "DIS-1101-1008-E1", "DIS-1101-1009-E1", 
"DIS-1101-1010-E1", "DIS-1101-1011-E1", "DIS-1101-1012-E1", "DIS-1101-1013-E1", 
"DIS-1101-1014-E1", "DIS-1101-1015-E1", "DIS-1101-1016-E1", "DIS-1101-1017-E1", 
"DIS-1101-1018-E1", "DIS-1101-1019-E1", "DIS-1101-1020-E1", "DIS-1101-1021-E1", 
"DIS-1101-1022-E1", "DIS-1101-1023-E1", "DIS-1101-1025-E1", "DIS-1101-1026-E1", 
"DIS-1101-1027-E1", "DIS-1101-1028-E1", "DIS-1101-1029-E1", "DIS-1101-1030-E1", 
"DIS-1101-1031-E1", "DIS-1101-1032-E1", "DIS-1101-1033-E1", "DIS-1101-1034-E1", 
"DIS-1101-1035-E1", "DIS-1101-1036-E1", "DIS-1101-1037-E1", "DIS-1101-1038-E1", 
"DIS-1101-1039-E1", "DIS-1101-1040-E1", "DIS-1101-1041-E1", "DIS-1101-1042-E1", 
"DIS-1101-1042-E2", "DIS-1101-1043-E1", "DIS-1101-1044-E1", "DIS-1101-1045-E1", 
"DIS-1101-1046-E1", "DIS-1101-1047-E1", "DIS-1101-1048-E1", "DIS-1101-1049-E1"
), Haemoglobin_BASELINE = c("123.00", NA, NA, "125.00", NA, NA, 
NA, NA, NA, NA, "119.00", "163.00", NA, NA, NA, NA, NA, "NULL", 
NA, NA, NA, "93.00", "114.00", "137.00", "157.00", NA, NA, NA, 
"80.00", "129.00", NA, "92.00", "118.00", NA, NA, "NULL", NA, 
"NULL", NA, NA, "81.00", NA, NA, NA, NA, NA, "NULL", "123.00", 
NA, NA), Haemoglobin_FIRST = c("117.00", "92.00", "111.00", "113.00", 
"124.00", NA, NA, "77.00", "132.00", "123.00", "93.00", "141.00", 
"137.00", NA, NA, "137.00", "127.00", "101.00", "120.00", "145.00", 
"126.00", "82.00", "95.00", "125.00", "112.00", NA, "127.00", 
"147.00", "92.00", "131.00", "108.00", "80.00", "107.00", NA, 
NA, "89.00", NA, "101.00", "151.00", "106.00", "87.00", "94.00", 
"85.00", "118.00", "101.00", "114.00", "129.00", "105.00", NA, 
NA), Haemoglobin_SECOND = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, "105.00", NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "85.00", NA, "116.00", "103.00", NA, NA, NA, NA, NA), 
    WBC_BASELINE = c("2.90", NA, NA, "6.90", NA, NA, NA, NA, 
    NA, NA, "7.40", "12.80", NA, NA, NA, NA, NA, "4.20", NA, 
    NA, NA, "31.00", "3.70", "14.10", "21.80", NA, NA, NA, "2.20", 
    "10.50", NA, "14.70", "13.40", NA, NA, "NULL", NA, "4.30", 
    NA, NA, "10.10", NA, NA, NA, NA, NA, "15.00", "12.80", NA, 
    NA), WBC_FIRST = c("2.40", "14.80", "11.00", "7.30", "4.50", 
    NA, NA, "6.10", "7.70", "16.20", "5.30", "10.30", "14.50", 
    NA, NA, "12.80", "3.70", "4.70", "16.60", "9.30", "15.90", 
    "31.70", "7.00", "7.20", "31.20", NA, "9.20", "5.90", "0.50", 
    "10.60", "12.00", "11.80", "13.00", NA, NA, "18.60", NA, 
    "5.90", "7.60", "3.10", "11.40", "12.00", "9.80", "29.50", 
    "4.60", "18.90", "13.00", "10.60", NA, NA), WBC_SECOND = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, "6.50", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "21.30", NA, 
    "4.80", "9.90", NA, NA, NA, NA, NA)), row.names = c(NA, -50L
), class = "data.frame")

I can achieve this, but my code is inefficient (as I am doing this on each blood column):

#haemoglobin
haem_ID <- df %>% select(UNIQUE_PATIENT_ID, `TIME_POINT_1=HAEMOGLOBIN`)

haem_sep <- haem_ID %>%
  rename(text = `TIME_POINT_1=HAEMOGLOBIN`) %>%
  separate_rows(text, sep = ';') %>%
  separate(text, c('text', 'value'), sep = '=') %>%
  pivot_wider(names_from = text, values_from = value)

tmp <- haem_sep %>% select(-UNIQUE_PATIENT_ID)
colnames(tmp) <- paste("Haemoglobin", colnames(tmp), sep = "_")
tmp2 <- haem_ID %>% select(UNIQUE_PATIENT_ID)
cbind(tmp2, tmp) -> tmp3
tmp3 %<>% select(-Haemoglobin_NULL)
haem <- tmp3


#white cells
a <- df %>% select(UNIQUE_PATIENT_ID, `TIME_POINT_2=WHITE_CELLS`)

b <- a %>%
  rename(text = `TIME_POINT_2=WHITE_CELLS`) %>%
  separate_rows(text, sep = ';') %>%
  separate(text, c('text', 'value'), sep = '=') %>%
  pivot_wider(names_from = text, values_from = value)

tmp <- b %>% select(-UNIQUE_PATIENT_ID)
colnames(tmp) <- paste("WBC", colnames(tmp), sep = "_")
tmp2 <- a %>% select(UNIQUE_PATIENT_ID)
cbind(tmp2, tmp) -> tmp3
tmp3 %<>% select(-WBC_NULL)
WBC <- tmp3

output <- merge(haem, WBC)

Pls can someone help me achieve the same but more effectively please? In reality I have 20 blood tests and not just 2, so hard-coding it is not ideal.

UPDATE

Upon the kind responses, I've tried running the suggested code but I'm getting an error and I can't figure out why.

input data

structure(list(UNIQUE_PATIENT_ID = c("DIS-1104-1021-E1", "DIS-1107-1010-E1", 
"DIS-1124-1108-E1"), `TIME_POINT_1=HAEMOGLOBIN` = c("BASELINE=98.00;FIRST=90.00;SECOND=81.00;FIRST=85.00", 
"FIRST=120.00;NULL=120.00;NULL=118.00;SECOND=109.00;NULL=186.00", 
"BASELINE=78.00;FIRST=NULL;FIRST=99.00"), `TIME_POINT_2=WHITE_CELLS` = c("BASELINE=0.01;FIRST=NULL;SECOND=0.01;FIRST=0.00", 
"FIRST=15.40;NULL=16.10;NULL=14.50;SECOND=11.40;NULL=2.20;NULL=5.00", 
"BASELINE=85.00;FIRST=NULL;FIRST=209.00"), `TIME_POINT_3=PLATELETS` = c("BASELINE=28.00;FIRST=28.00;SECOND=12.00;FIRST=21.00", 
"FIRST=200.00;NULL=186.00;NULL=203.00;SECOND=243.00;NULL=145.00;NULL=457.00", 
"BASELINE=186.00;FIRST=NULL"), `TIME_POINT_4=NEUTROPHILS` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=13.00;NULL=13.20;NULL=11.10;SECOND=7.70;NULL=1.30;NULL=2.20", 
"BASELINE=7.10;FIRST=2.40;FIRST=NULL"), `TIME_POINT_5=LYMPHOCYTES` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=0.80;NULL=1.60;NULL=1.40;SECOND=1.70;NULL=0.40", "BASELINE=76.80;FIRST=135.00;FIRST=NULL"
), `TIME_POINT_6=MONOCYTES` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=1.50;NULL=1.20;NULL=1.90;SECOND=2.00;NULL=0.40;NULL=0.90", 
"BASELINE=1.40;FIRST=4.20;FIRST=NULL"), `TIME_POINT_7=EOSINOPHILS` = c("BASELINE=0.00;FIRST=0.00;SECOND=0.00", 
"FIRST=0.10;NULL=0.00;SECOND=0.00;NULL=0.10;NULL=0.30", "BASELINE=0.10;FIRST=0.40;FIRST=NULL"
), `TIME_POINT_8=FIBRINOGEN` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=6.90;NULL=6.60;NULL=7.10;SECOND=6.60;NULL=6.00;NULL=NULL", 
"BASELINE=6.70;FIRST=NULL"), `TIME_POINT_9=PT` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=19.20;NULL=17.20;NULL=16.10;SECOND=14.40;NULL=14.90;NULL=NULL", 
"BASELINE=13.40;FIRST=NULL"), `TIME_POINT_10=UREA` = c("BASELINE=4.30;FIRST=4.30;SECOND=5.10;FIRST=4.50", 
"FIRST=3.80;NULL=3.70;NULL=4.40;SECOND=2.80;NULL=2.90;NULL=4.10", 
"BASELINE=5.10;FIRST=NULL;FIRST=7.20"), `TIME_POINT_11=CREATININE` = c("BASELINE=67.00;FIRST=55.00;SECOND=61.00;FIRST=60.00", 
"FIRST=44.00;NULL=42.00;NULL=45.00;SECOND=42.00;NULL=33.00", 
"BASELINE=65.00;FIRST=NULL;FIRST=72.00"), `TIME_POINT_12=ALT` = c("BASELINE=9.00;FIRST=11.00;SECOND=8.00;FIRST=5.00", 
"FIRST=20.00;NULL=NULL;NULL=19.00;SECOND=16.00;NULL=16.00;NULL=21.00", 
"BASELINE=64.00;FIRST=NULL;FIRST=92.00"), `TIME_POINT_13=BILIRUBIN` = c("BASELINE=3.00;FIRST=5.00;SECOND=3.00;FIRST=NULL", 
"FIRST=20.00;NULL=NULL;NULL=11.00;SECOND=10.00;NULL=5.00", "BASELINE=7.00;FIRST=NULL;FIRST=10.00"
), `TIME_POINT_14=ALBUMIN` = c("BASELINE=42.00;FIRST=39.00;SECOND=35.00;FIRST=36.00", 
"FIRST=42.00;NULL=NULL;NULL=37.00;SECOND=35.00;NULL=35.00;NULL=39.00", 
"BASELINE=31.00;FIRST=NULL;FIRST=40.00"), `TIME_POINT_15=VITAMIN_D` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_16=CRP` = c("BASELINE=NULL;FIRST=172.00;SECOND=116.00;FIRST=212.00", 
"FIRST=427.00;NULL=420.00;NULL=338.00;SECOND=215.00;NULL=87.00;NULL=64.00", 
"BASELINE=199.00;FIRST=NULL;FIRST=1.00"), `TIME_POINT_17=PCT` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_18=FERRITIN` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;NULL=378.00;SECOND=NULL", "BASELINE=809.00;FIRST=432.00;FIRST=NULL"
), `TIME_POINT_19=TROP_T` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;NULL=3.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_20=D_DIMER` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=1.85;NULL=1.65;SECOND=NULL;NULL=NULL", "BASELINE=749.00;FIRST=486.00;FIRST=NULL"
), `TIME_POINT_21=INR` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_22=BNP_NT` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_23=LDH` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=254.00;FIRST=NULL"
), `TIME_POINT_24=CK` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;NULL=29.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_25=SCD_25` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_26=BASE_EXCESS` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=3.10;NULL=1.50;NULL=NULL;SECOND=NULL", "BASELINE=-3.80;FIRST=NULL"
), `TIME_POINT_27=LACTATE` = c("BASELINE=NULL;FIRST=NULL;SECOND=NULL", 
"FIRST=0.90;NULL=1.30;NULL=NULL;SECOND=NULL", "BASELINE=0.60;FIRST=NULL"
)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"
))

Code:

output <- input |> 
  separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*=")) |> 
  pivot_wider(names_from = c("name", "timepoint")) |> 
  select(-ends_with("NULL"))

Error message:

Error in `fn()`:
! In row 2, can't recycle input of size 5 to size 6.
Run `rlang::last_error()` to see where the error occurred.

Thank you!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

┈┾☆殇 2025-02-19 19:28:19

在上面的评论和 @carl的实现的基础上，您可以使用values_fn直接利用嵌套列表结构，从而可以将您从某些代码中保存。我正在使用first（）从dplyr获取第一个元素，但是您还可以根据您喜欢的内容来使用其他功能，〜means（as。数字（。））要采取平均值等。当我们运行数据时，使用pivot_longer，当我们pivot_longer之前，没有不同的分离器。

df |> 
    pivot_longer(-UNIQUE_PATIENT_ID) |> 
    separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
    separate(value, into = c("timepoint", "value"), sep = "=") |> 
    mutate(name = str_remove(name, ".*=")) |>
    filter(!is.na(value) & value != "NULL" & timepoint != "NULL") |>
    pivot_wider(names_from = c("name", "timepoint"), values_fn = ~ first(.)) |>
    select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()))

Building on my comments above and @Carl's implementation, you you could utilize the nested list structure directly using values_fn, which might save you from some lines of code. I am using first() from dplyr to take the first element, but you could as well use other functions depending on what you prefer, ~ mean(as.numeric(.)) to take the mean etc. There is no varying number of separators when we pivot_longer before using separate_rows, when I run the data.

df |> 
    pivot_longer(-UNIQUE_PATIENT_ID) |> 
    separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
    separate(value, into = c("timepoint", "value"), sep = "=") |> 
    mutate(name = str_remove(name, ".*=")) |>
    filter(!is.na(value) & value != "NULL" & timepoint != "NULL") |>
    pivot_wider(names_from = c("name", "timepoint"), values_fn = ~ first(.)) |>
    select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()))
回复 原文
橪书 2025-02-19 19:28:18

最后一个选择行是可选的，具体取决于您要订购的列以及是否希望保留零列：

df |> 
  separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*=")) |> 
  pivot_wider(names_from = c("name", "timepoint")) |> 
  select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()), -contains("NULL"))

使用带有语法列名称的示例数据，即使用“”。可以在干净的r会话中运行以下reprex：

library(tidyverse)

df <- tribble(
  ~UNIQUE_PATIENT_ID,                  ~TIME_POINT_1.HAEMOGLOBIN,              ~TIME_POINT_2.WHITE_CELLS,
  "DIS-1101-1001-E1",             "BASELINE=123.00;FIRST=117.00",             "BASELINE=2.90;FIRST=2.40",
  "DIS-1101-1002-E1",                              "FIRST=92.00",                          "FIRST=14.80",
  "DIS-1101-1003-E1",                             "FIRST=111.00",                          "FIRST=11.00",
  "DIS-1101-1004-E1",             "BASELINE=125.00;FIRST=113.00",             "BASELINE=6.90;FIRST=7.30",
  "DIS-1101-1004-E2",                             "FIRST=124.00",                           "FIRST=4.50",
  "DIS-1101-1005-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1006-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1007-E1",                              "FIRST=77.00",                           "FIRST=6.10",
  "DIS-1101-1008-E1",                             "FIRST=132.00",                           "FIRST=7.70",
  "DIS-1101-1009-E1",                             "FIRST=123.00",                          "FIRST=16.20",
  "DIS-1101-1010-E1",              "BASELINE=119.00;FIRST=93.00",             "BASELINE=7.40;FIRST=5.30",
  "DIS-1101-1011-E1",             "BASELINE=163.00;FIRST=141.00",           "BASELINE=12.80;FIRST=10.30",
  "DIS-1101-1012-E1",                             "FIRST=137.00",                          "FIRST=14.50",
  "DIS-1101-1013-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1014-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1015-E1",                             "FIRST=137.00",                          "FIRST=12.80",
  "DIS-1101-1016-E1",                             "FIRST=127.00",                           "FIRST=3.70",
  "DIS-1101-1017-E1", "BASELINE=NULL;FIRST=101.00;SECOND=105.00", "BASELINE=4.20;FIRST=4.70;SECOND=6.50",
  "DIS-1101-1018-E1",                             "FIRST=120.00",                          "FIRST=16.60",
  "DIS-1101-1019-E1",                             "FIRST=145.00",                           "FIRST=9.30",
  "DIS-1101-1020-E1",                             "FIRST=126.00",                          "FIRST=15.90",
  "DIS-1101-1021-E1",               "BASELINE=93.00;FIRST=82.00",           "BASELINE=31.00;FIRST=31.70",
  "DIS-1101-1022-E1",              "BASELINE=114.00;FIRST=95.00",             "BASELINE=3.70;FIRST=7.00",
  "DIS-1101-1023-E1",             "BASELINE=137.00;FIRST=125.00",            "BASELINE=14.10;FIRST=7.20",
  "DIS-1101-1025-E1",             "BASELINE=157.00;FIRST=112.00",           "BASELINE=21.80;FIRST=31.20",
  "DIS-1101-1026-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1027-E1",                             "FIRST=127.00",                           "FIRST=9.20",
  "DIS-1101-1028-E1",                             "FIRST=147.00",                           "FIRST=5.90",
  "DIS-1101-1029-E1",               "BASELINE=80.00;FIRST=92.00",             "BASELINE=2.20;FIRST=0.50",
  "DIS-1101-1030-E1",             "BASELINE=129.00;FIRST=131.00",           "BASELINE=10.50;FIRST=10.60",
  "DIS-1101-1031-E1",                             "FIRST=108.00",                          "FIRST=12.00",
  "DIS-1101-1032-E1",               "BASELINE=92.00;FIRST=80.00",           "BASELINE=14.70;FIRST=11.80",
  "DIS-1101-1033-E1",             "BASELINE=118.00;FIRST=107.00",           "BASELINE=13.40;FIRST=13.00",
  "DIS-1101-1034-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1035-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1036-E1",                "BASELINE=NULL;FIRST=89.00",            "BASELINE=NULL;FIRST=18.60",
  "DIS-1101-1037-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1038-E1",               "BASELINE=NULL;FIRST=101.00",             "BASELINE=4.30;FIRST=5.90",
  "DIS-1101-1039-E1",                             "FIRST=151.00",                           "FIRST=7.60",
  "DIS-1101-1040-E1",                             "FIRST=106.00",                           "FIRST=3.10",
  "DIS-1101-1041-E1",               "BASELINE=81.00;FIRST=87.00",           "BASELINE=10.10;FIRST=11.40",
  "DIS-1101-1042-E1",                 "FIRST=94.00;SECOND=85.00",             "FIRST=12.00;SECOND=21.30",
  "DIS-1101-1042-E2",                              "FIRST=85.00",                           "FIRST=9.80",
  "DIS-1101-1043-E1",               "FIRST=118.00;SECOND=116.00",              "FIRST=29.50;SECOND=4.80",
  "DIS-1101-1044-E1",               "FIRST=101.00;SECOND=103.00",               "FIRST=4.60;SECOND=9.90",
  "DIS-1101-1045-E1",                             "FIRST=114.00",                          "FIRST=18.90",
  "DIS-1101-1046-E1",               "BASELINE=NULL;FIRST=129.00",           "BASELINE=15.00;FIRST=13.00",
  "DIS-1101-1047-E1",             "BASELINE=123.00;FIRST=105.00",           "BASELINE=12.80;FIRST=10.60",
  "DIS-1101-1048-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1049-E1",                                "NULL=NULL",                            "NULL=NULL"
  )

df |> 
  separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*\\.")) |> 
  pivot_wider(names_from = c("name", "timepoint")) |> 
  select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()), -contains("NULL"))

#> # A tibble: 50 × 7
#>    UNIQUE_PATIENT_ID HAEMOGLOBIN_BASELINE HAEMOGLOBIN_FIRST HAEMOGLOBIN_SECOND
#>    <chr>             <chr>                <chr>             <chr>             
#>  1 DIS-1101-1001-E1  123.00               117.00            <NA>              
#>  2 DIS-1101-1002-E1  <NA>                 92.00             <NA>              
#>  3 DIS-1101-1003-E1  <NA>                 111.00            <NA>              
#>  4 DIS-1101-1004-E1  125.00               113.00            <NA>              
#>  5 DIS-1101-1004-E2  <NA>                 124.00            <NA>              
#>  6 DIS-1101-1005-E1  <NA>                 <NA>              <NA>              
#>  7 DIS-1101-1006-E1  <NA>                 <NA>              <NA>              
#>  8 DIS-1101-1007-E1  <NA>                 77.00             <NA>              
#>  9 DIS-1101-1008-E1  <NA>                 132.00            <NA>              
#> 10 DIS-1101-1009-E1  <NA>                 123.00            <NA>              
#> # … with 40 more rows, and 3 more variables: WHITE_CELLS_BASELINE <chr>,
#> #   WHITE_CELLS_FIRST <chr>, WHITE_CELLS_SECOND <chr>

在2022-07-04上由 reprex软件包（v2.0.1）

**更新的示例2数据集更新

以处理变化跨列的分隔符数量，我建议通过列> 迭代的函数。这克服了axtim_rows在较宽数据集中的错误，这不是示例数据集中的问题。

使用模式匹配，我还仅提取引用“基线”，“第一个”或“第二”的字符串，其中它们与值相关联。

正如@harre所指出的那样，由于某些列包含多个，例如，例如，我随后使用slice_head在使用pivot_wider之前使用了第一个。取而代之的是，您可以使用slice_tail进行最后一个，也可以根据需求进行均值等。

您需要在扩展数据集中进行进一步测试，但希望这会有所帮助！

library(tidyverse)

df <- structure(list(UNIQUE_PATIENT_ID = c(
  "DIS-1104-1021-E1", "DIS-1107-1010-E1",
  "DIS-1124-1108-E1"
), `TIME_POINT_1=HAEMOGLOBIN` = c(
  "BASELINE=98.00;FIRST=90.00;SECOND=81.00;FIRST=85.00",
  "FIRST=120.00;NULL=120.00;NULL=118.00;SECOND=109.00;NULL=186.00",
  "BASELINE=78.00;FIRST=NULL;FIRST=99.00"
), `TIME_POINT_2=WHITE_CELLS` = c(
  "BASELINE=0.01;FIRST=NULL;SECOND=0.01;FIRST=0.00",
  "FIRST=15.40;NULL=16.10;NULL=14.50;SECOND=11.40;NULL=2.20;NULL=5.00",
  "BASELINE=85.00;FIRST=NULL;FIRST=209.00"
), `TIME_POINT_3=PLATELETS` = c(
  "BASELINE=28.00;FIRST=28.00;SECOND=12.00;FIRST=21.00",
  "FIRST=200.00;NULL=186.00;NULL=203.00;SECOND=243.00;NULL=145.00;NULL=457.00",
  "BASELINE=186.00;FIRST=NULL"
), `TIME_POINT_4=NEUTROPHILS` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=13.00;NULL=13.20;NULL=11.10;SECOND=7.70;NULL=1.30;NULL=2.20",
  "BASELINE=7.10;FIRST=2.40;FIRST=NULL"
), `TIME_POINT_5=LYMPHOCYTES` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=0.80;NULL=1.60;NULL=1.40;SECOND=1.70;NULL=0.40", "BASELINE=76.80;FIRST=135.00;FIRST=NULL"
), `TIME_POINT_6=MONOCYTES` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=1.50;NULL=1.20;NULL=1.90;SECOND=2.00;NULL=0.40;NULL=0.90",
  "BASELINE=1.40;FIRST=4.20;FIRST=NULL"
), `TIME_POINT_7=EOSINOPHILS` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=0.10;NULL=0.00;SECOND=0.00;NULL=0.10;NULL=0.30", "BASELINE=0.10;FIRST=0.40;FIRST=NULL"
), `TIME_POINT_8=FIBRINOGEN` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=6.90;NULL=6.60;NULL=7.10;SECOND=6.60;NULL=6.00;NULL=NULL",
  "BASELINE=6.70;FIRST=NULL"
), `TIME_POINT_9=PT` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=19.20;NULL=17.20;NULL=16.10;SECOND=14.40;NULL=14.90;NULL=NULL",
  "BASELINE=13.40;FIRST=NULL"
), `TIME_POINT_10=UREA` = c(
  "BASELINE=4.30;FIRST=4.30;SECOND=5.10;FIRST=4.50",
  "FIRST=3.80;NULL=3.70;NULL=4.40;SECOND=2.80;NULL=2.90;NULL=4.10",
  "BASELINE=5.10;FIRST=NULL;FIRST=7.20"
), `TIME_POINT_11=CREATININE` = c(
  "BASELINE=67.00;FIRST=55.00;SECOND=61.00;FIRST=60.00",
  "FIRST=44.00;NULL=42.00;NULL=45.00;SECOND=42.00;NULL=33.00",
  "BASELINE=65.00;FIRST=NULL;FIRST=72.00"
), `TIME_POINT_12=ALT` = c(
  "BASELINE=9.00;FIRST=11.00;SECOND=8.00;FIRST=5.00",
  "FIRST=20.00;NULL=NULL;NULL=19.00;SECOND=16.00;NULL=16.00;NULL=21.00",
  "BASELINE=64.00;FIRST=NULL;FIRST=92.00"
), `TIME_POINT_13=BILIRUBIN` = c(
  "BASELINE=3.00;FIRST=5.00;SECOND=3.00;FIRST=NULL",
  "FIRST=20.00;NULL=NULL;NULL=11.00;SECOND=10.00;NULL=5.00", "BASELINE=7.00;FIRST=NULL;FIRST=10.00"
), `TIME_POINT_14=ALBUMIN` = c(
  "BASELINE=42.00;FIRST=39.00;SECOND=35.00;FIRST=36.00",
  "FIRST=42.00;NULL=NULL;NULL=37.00;SECOND=35.00;NULL=35.00;NULL=39.00",
  "BASELINE=31.00;FIRST=NULL;FIRST=40.00"
), `TIME_POINT_15=VITAMIN_D` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_16=CRP` = c(
  "BASELINE=NULL;FIRST=172.00;SECOND=116.00;FIRST=212.00",
  "FIRST=427.00;NULL=420.00;NULL=338.00;SECOND=215.00;NULL=87.00;NULL=64.00",
  "BASELINE=199.00;FIRST=NULL;FIRST=1.00"
), `TIME_POINT_17=PCT` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_18=FERRITIN` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;NULL=378.00;SECOND=NULL", "BASELINE=809.00;FIRST=432.00;FIRST=NULL"
), `TIME_POINT_19=TROP_T` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;NULL=3.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_20=D_DIMER` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=1.85;NULL=1.65;SECOND=NULL;NULL=NULL", "BASELINE=749.00;FIRST=486.00;FIRST=NULL"
), `TIME_POINT_21=INR` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_22=BNP_NT` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_23=LDH` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=254.00;FIRST=NULL"
), `TIME_POINT_24=CK` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;NULL=29.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_25=SCD_25` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_26=BASE_EXCESS` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=3.10;NULL=1.50;NULL=NULL;SECOND=NULL", "BASELINE=-3.80;FIRST=NULL"
), `TIME_POINT_27=LACTATE` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=0.90;NULL=1.30;NULL=NULL;SECOND=NULL", "BASELINE=0.60;FIRST=NULL"
)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))



pattern <- c("FIRST=\\d{2,3}.\\d{0,2}|SECOND=\\d{2,3}.\\d{0,2}|BASELINE=\\d{2,3}.\\d{0,2}")

# Function to iteratively separate by column
separate_iteratively <- function(col){
  separate_rows(df2, col, sep = ";") |> 
    select(1, col)
}

# Extract only if BASELINE, FIRST or SECOND with an associate number
df2 <- df |> 
  mutate(across(-UNIQUE_PATIENT_ID, ~ str_extract_all(., pattern) |> 
                  map_chr(~str_c(., collapse = ";"))))

# Separate & rearrange
df3 <- map_dfr(2:ncol(df2), separate_iteratively) |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  filter(!is.na(value) & !value == "") |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*=")) |> 
  arrange(UNIQUE_PATIENT_ID, name, timepoint) |> 
  group_by(UNIQUE_PATIENT_ID, name, timepoint) |> 
  slice_head(n = 1) |> # Or use slice_tail
  pivot_wider(names_from = c("name", "timepoint"))|> 
  select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()))

df3
#> # A tibble: 3 × 35
#> # Groups:   UNIQUE_PATIENT_ID [3]
#>   UNIQUE_PATIENT_ID ALBUMIN_BASELINE ALBUMIN_FIRST ALBUMIN_SECOND ALT_BASELINE
#>   <chr>             <chr>            <chr>         <chr>          <chr>       
#> 1 DIS-1104-1021-E1  42.00            39.00         35.00          <NA>        
#> 2 DIS-1107-1010-E1  <NA>             42.00         35.00          <NA>        
#> 3 DIS-1124-1108-E1  31.00            40.00         <NA>           64.00       
#> # … with 30 more variables: ALT_FIRST <chr>, ALT_SECOND <chr>,
#> #   BILIRUBIN_FIRST <chr>, BILIRUBIN_SECOND <chr>, CREATININE_BASELINE <chr>,
#> #   CREATININE_FIRST <chr>, CREATININE_SECOND <chr>, CRP_BASELINE <chr>,
#> #   CRP_FIRST <chr>, CRP_SECOND <chr>, D_DIMER_BASELINE <chr>,
#> #   D_DIMER_FIRST <chr>, FERRITIN_BASELINE <chr>, FERRITIN_FIRST <chr>,
#> #   HAEMOGLOBIN_BASELINE <chr>, HAEMOGLOBIN_FIRST <chr>,
#> #   HAEMOGLOBIN_SECOND <chr>, LDH_FIRST <chr>, LYMPHOCYTES_BASELINE <chr>, …


# Just to illustrate the issue with varying separators
tribble(~v1, ~v2,
        "a;a", "b;b",
        "a;a", "b;b;b",
        ) |> 
  separate_rows(everything(), sep = ";")
#> Error in `fn()`:
#> ! In row 2, can't recycle input of size 2 to size 3.

# Works
tribble(~v1, ~v2,
        "a;a", "b;b",
        "a;a", "b;b",
) |> 
  separate_rows(everything(), sep = ";")
#> # A tibble: 4 × 2
#>   v1    v2   
#>   <chr> <chr>
#> 1 a     b    
#> 2 a     b    
#> 3 a     b    
#> 4 a     b

由

The last select line is optional depending on how you want the columns ordered and whether or not you wish to keep the NULL columns:

df |> 
  separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*=")) |> 
  pivot_wider(names_from = c("name", "timepoint")) |> 
  select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()), -contains("NULL"))

Using the example data with syntactic columns names, i.e. using a "." instead of "="), the following reprex may be run in a clean R session:

library(tidyverse)

df <- tribble(
  ~UNIQUE_PATIENT_ID,                  ~TIME_POINT_1.HAEMOGLOBIN,              ~TIME_POINT_2.WHITE_CELLS,
  "DIS-1101-1001-E1",             "BASELINE=123.00;FIRST=117.00",             "BASELINE=2.90;FIRST=2.40",
  "DIS-1101-1002-E1",                              "FIRST=92.00",                          "FIRST=14.80",
  "DIS-1101-1003-E1",                             "FIRST=111.00",                          "FIRST=11.00",
  "DIS-1101-1004-E1",             "BASELINE=125.00;FIRST=113.00",             "BASELINE=6.90;FIRST=7.30",
  "DIS-1101-1004-E2",                             "FIRST=124.00",                           "FIRST=4.50",
  "DIS-1101-1005-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1006-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1007-E1",                              "FIRST=77.00",                           "FIRST=6.10",
  "DIS-1101-1008-E1",                             "FIRST=132.00",                           "FIRST=7.70",
  "DIS-1101-1009-E1",                             "FIRST=123.00",                          "FIRST=16.20",
  "DIS-1101-1010-E1",              "BASELINE=119.00;FIRST=93.00",             "BASELINE=7.40;FIRST=5.30",
  "DIS-1101-1011-E1",             "BASELINE=163.00;FIRST=141.00",           "BASELINE=12.80;FIRST=10.30",
  "DIS-1101-1012-E1",                             "FIRST=137.00",                          "FIRST=14.50",
  "DIS-1101-1013-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1014-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1015-E1",                             "FIRST=137.00",                          "FIRST=12.80",
  "DIS-1101-1016-E1",                             "FIRST=127.00",                           "FIRST=3.70",
  "DIS-1101-1017-E1", "BASELINE=NULL;FIRST=101.00;SECOND=105.00", "BASELINE=4.20;FIRST=4.70;SECOND=6.50",
  "DIS-1101-1018-E1",                             "FIRST=120.00",                          "FIRST=16.60",
  "DIS-1101-1019-E1",                             "FIRST=145.00",                           "FIRST=9.30",
  "DIS-1101-1020-E1",                             "FIRST=126.00",                          "FIRST=15.90",
  "DIS-1101-1021-E1",               "BASELINE=93.00;FIRST=82.00",           "BASELINE=31.00;FIRST=31.70",
  "DIS-1101-1022-E1",              "BASELINE=114.00;FIRST=95.00",             "BASELINE=3.70;FIRST=7.00",
  "DIS-1101-1023-E1",             "BASELINE=137.00;FIRST=125.00",            "BASELINE=14.10;FIRST=7.20",
  "DIS-1101-1025-E1",             "BASELINE=157.00;FIRST=112.00",           "BASELINE=21.80;FIRST=31.20",
  "DIS-1101-1026-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1027-E1",                             "FIRST=127.00",                           "FIRST=9.20",
  "DIS-1101-1028-E1",                             "FIRST=147.00",                           "FIRST=5.90",
  "DIS-1101-1029-E1",               "BASELINE=80.00;FIRST=92.00",             "BASELINE=2.20;FIRST=0.50",
  "DIS-1101-1030-E1",             "BASELINE=129.00;FIRST=131.00",           "BASELINE=10.50;FIRST=10.60",
  "DIS-1101-1031-E1",                             "FIRST=108.00",                          "FIRST=12.00",
  "DIS-1101-1032-E1",               "BASELINE=92.00;FIRST=80.00",           "BASELINE=14.70;FIRST=11.80",
  "DIS-1101-1033-E1",             "BASELINE=118.00;FIRST=107.00",           "BASELINE=13.40;FIRST=13.00",
  "DIS-1101-1034-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1035-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1036-E1",                "BASELINE=NULL;FIRST=89.00",            "BASELINE=NULL;FIRST=18.60",
  "DIS-1101-1037-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1038-E1",               "BASELINE=NULL;FIRST=101.00",             "BASELINE=4.30;FIRST=5.90",
  "DIS-1101-1039-E1",                             "FIRST=151.00",                           "FIRST=7.60",
  "DIS-1101-1040-E1",                             "FIRST=106.00",                           "FIRST=3.10",
  "DIS-1101-1041-E1",               "BASELINE=81.00;FIRST=87.00",           "BASELINE=10.10;FIRST=11.40",
  "DIS-1101-1042-E1",                 "FIRST=94.00;SECOND=85.00",             "FIRST=12.00;SECOND=21.30",
  "DIS-1101-1042-E2",                              "FIRST=85.00",                           "FIRST=9.80",
  "DIS-1101-1043-E1",               "FIRST=118.00;SECOND=116.00",              "FIRST=29.50;SECOND=4.80",
  "DIS-1101-1044-E1",               "FIRST=101.00;SECOND=103.00",               "FIRST=4.60;SECOND=9.90",
  "DIS-1101-1045-E1",                             "FIRST=114.00",                          "FIRST=18.90",
  "DIS-1101-1046-E1",               "BASELINE=NULL;FIRST=129.00",           "BASELINE=15.00;FIRST=13.00",
  "DIS-1101-1047-E1",             "BASELINE=123.00;FIRST=105.00",           "BASELINE=12.80;FIRST=10.60",
  "DIS-1101-1048-E1",                                "NULL=NULL",                            "NULL=NULL",
  "DIS-1101-1049-E1",                                "NULL=NULL",                            "NULL=NULL"
  )

df |> 
  separate_rows(-UNIQUE_PATIENT_ID, sep = ";") |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*\\.")) |> 
  pivot_wider(names_from = c("name", "timepoint")) |> 
  select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()), -contains("NULL"))

#> # A tibble: 50 × 7
#>    UNIQUE_PATIENT_ID HAEMOGLOBIN_BASELINE HAEMOGLOBIN_FIRST HAEMOGLOBIN_SECOND
#>    <chr>             <chr>                <chr>             <chr>             
#>  1 DIS-1101-1001-E1  123.00               117.00            <NA>              
#>  2 DIS-1101-1002-E1  <NA>                 92.00             <NA>              
#>  3 DIS-1101-1003-E1  <NA>                 111.00            <NA>              
#>  4 DIS-1101-1004-E1  125.00               113.00            <NA>              
#>  5 DIS-1101-1004-E2  <NA>                 124.00            <NA>              
#>  6 DIS-1101-1005-E1  <NA>                 <NA>              <NA>              
#>  7 DIS-1101-1006-E1  <NA>                 <NA>              <NA>              
#>  8 DIS-1101-1007-E1  <NA>                 77.00             <NA>              
#>  9 DIS-1101-1008-E1  <NA>                 132.00            <NA>              
#> 10 DIS-1101-1009-E1  <NA>                 123.00            <NA>              
#> # … with 40 more rows, and 3 more variables: WHITE_CELLS_BASELINE <chr>,
#> #   WHITE_CELLS_FIRST <chr>, WHITE_CELLS_SECOND <chr>

Created on 2022-07-04 by the reprex package (v2.0.1)

** UPDATE FOR YOUR WIDER SAMPLE2 DATASET **

To handle the varying number of separators across columns I've suggested a function to separate_rows iteratively by column. This overcomes the separate_rows error in the wider dataset that wasn't an issue in the sample dataset.

Using pattern matching, I've also extracted only strings referencing "BASELINE", "FIRST" or "SECOND" where these are associated with a value.

And as @harre notes, because some columns contain multiple, e.g. FIRSTs, I've subsequently taken the first of these using slice_head before using pivot_wider. You could instead take the last using slice_tail or take the mean etc. depending on the requirement.

You'll need to test further on your extended dataset, but hope this helps!

library(tidyverse)

df <- structure(list(UNIQUE_PATIENT_ID = c(
  "DIS-1104-1021-E1", "DIS-1107-1010-E1",
  "DIS-1124-1108-E1"
), `TIME_POINT_1=HAEMOGLOBIN` = c(
  "BASELINE=98.00;FIRST=90.00;SECOND=81.00;FIRST=85.00",
  "FIRST=120.00;NULL=120.00;NULL=118.00;SECOND=109.00;NULL=186.00",
  "BASELINE=78.00;FIRST=NULL;FIRST=99.00"
), `TIME_POINT_2=WHITE_CELLS` = c(
  "BASELINE=0.01;FIRST=NULL;SECOND=0.01;FIRST=0.00",
  "FIRST=15.40;NULL=16.10;NULL=14.50;SECOND=11.40;NULL=2.20;NULL=5.00",
  "BASELINE=85.00;FIRST=NULL;FIRST=209.00"
), `TIME_POINT_3=PLATELETS` = c(
  "BASELINE=28.00;FIRST=28.00;SECOND=12.00;FIRST=21.00",
  "FIRST=200.00;NULL=186.00;NULL=203.00;SECOND=243.00;NULL=145.00;NULL=457.00",
  "BASELINE=186.00;FIRST=NULL"
), `TIME_POINT_4=NEUTROPHILS` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=13.00;NULL=13.20;NULL=11.10;SECOND=7.70;NULL=1.30;NULL=2.20",
  "BASELINE=7.10;FIRST=2.40;FIRST=NULL"
), `TIME_POINT_5=LYMPHOCYTES` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=0.80;NULL=1.60;NULL=1.40;SECOND=1.70;NULL=0.40", "BASELINE=76.80;FIRST=135.00;FIRST=NULL"
), `TIME_POINT_6=MONOCYTES` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=1.50;NULL=1.20;NULL=1.90;SECOND=2.00;NULL=0.40;NULL=0.90",
  "BASELINE=1.40;FIRST=4.20;FIRST=NULL"
), `TIME_POINT_7=EOSINOPHILS` = c(
  "BASELINE=0.00;FIRST=0.00;SECOND=0.00",
  "FIRST=0.10;NULL=0.00;SECOND=0.00;NULL=0.10;NULL=0.30", "BASELINE=0.10;FIRST=0.40;FIRST=NULL"
), `TIME_POINT_8=FIBRINOGEN` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=6.90;NULL=6.60;NULL=7.10;SECOND=6.60;NULL=6.00;NULL=NULL",
  "BASELINE=6.70;FIRST=NULL"
), `TIME_POINT_9=PT` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=19.20;NULL=17.20;NULL=16.10;SECOND=14.40;NULL=14.90;NULL=NULL",
  "BASELINE=13.40;FIRST=NULL"
), `TIME_POINT_10=UREA` = c(
  "BASELINE=4.30;FIRST=4.30;SECOND=5.10;FIRST=4.50",
  "FIRST=3.80;NULL=3.70;NULL=4.40;SECOND=2.80;NULL=2.90;NULL=4.10",
  "BASELINE=5.10;FIRST=NULL;FIRST=7.20"
), `TIME_POINT_11=CREATININE` = c(
  "BASELINE=67.00;FIRST=55.00;SECOND=61.00;FIRST=60.00",
  "FIRST=44.00;NULL=42.00;NULL=45.00;SECOND=42.00;NULL=33.00",
  "BASELINE=65.00;FIRST=NULL;FIRST=72.00"
), `TIME_POINT_12=ALT` = c(
  "BASELINE=9.00;FIRST=11.00;SECOND=8.00;FIRST=5.00",
  "FIRST=20.00;NULL=NULL;NULL=19.00;SECOND=16.00;NULL=16.00;NULL=21.00",
  "BASELINE=64.00;FIRST=NULL;FIRST=92.00"
), `TIME_POINT_13=BILIRUBIN` = c(
  "BASELINE=3.00;FIRST=5.00;SECOND=3.00;FIRST=NULL",
  "FIRST=20.00;NULL=NULL;NULL=11.00;SECOND=10.00;NULL=5.00", "BASELINE=7.00;FIRST=NULL;FIRST=10.00"
), `TIME_POINT_14=ALBUMIN` = c(
  "BASELINE=42.00;FIRST=39.00;SECOND=35.00;FIRST=36.00",
  "FIRST=42.00;NULL=NULL;NULL=37.00;SECOND=35.00;NULL=35.00;NULL=39.00",
  "BASELINE=31.00;FIRST=NULL;FIRST=40.00"
), `TIME_POINT_15=VITAMIN_D` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_16=CRP` = c(
  "BASELINE=NULL;FIRST=172.00;SECOND=116.00;FIRST=212.00",
  "FIRST=427.00;NULL=420.00;NULL=338.00;SECOND=215.00;NULL=87.00;NULL=64.00",
  "BASELINE=199.00;FIRST=NULL;FIRST=1.00"
), `TIME_POINT_17=PCT` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_18=FERRITIN` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;NULL=378.00;SECOND=NULL", "BASELINE=809.00;FIRST=432.00;FIRST=NULL"
), `TIME_POINT_19=TROP_T` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;NULL=3.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_20=D_DIMER` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=1.85;NULL=1.65;SECOND=NULL;NULL=NULL", "BASELINE=749.00;FIRST=486.00;FIRST=NULL"
), `TIME_POINT_21=INR` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_22=BNP_NT` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_23=LDH` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=254.00;FIRST=NULL"
), `TIME_POINT_24=CK` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;NULL=29.00;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_25=SCD_25` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=NULL;NULL=NULL;SECOND=NULL", "BASELINE=NULL;FIRST=NULL"
), `TIME_POINT_26=BASE_EXCESS` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=3.10;NULL=1.50;NULL=NULL;SECOND=NULL", "BASELINE=-3.80;FIRST=NULL"
), `TIME_POINT_27=LACTATE` = c(
  "BASELINE=NULL;FIRST=NULL;SECOND=NULL",
  "FIRST=0.90;NULL=1.30;NULL=NULL;SECOND=NULL", "BASELINE=0.60;FIRST=NULL"
)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))



pattern <- c("FIRST=\\d{2,3}.\\d{0,2}|SECOND=\\d{2,3}.\\d{0,2}|BASELINE=\\d{2,3}.\\d{0,2}")

# Function to iteratively separate by column
separate_iteratively <- function(col){
  separate_rows(df2, col, sep = ";") |> 
    select(1, col)
}

# Extract only if BASELINE, FIRST or SECOND with an associate number
df2 <- df |> 
  mutate(across(-UNIQUE_PATIENT_ID, ~ str_extract_all(., pattern) |> 
                  map_chr(~str_c(., collapse = ";"))))

# Separate & rearrange
df3 <- map_dfr(2:ncol(df2), separate_iteratively) |> 
  pivot_longer(-UNIQUE_PATIENT_ID) |> 
  filter(!is.na(value) & !value == "") |> 
  separate(value, into = c("timepoint", "value"), sep = "=") |> 
  mutate(name = str_remove(name, ".*=")) |> 
  arrange(UNIQUE_PATIENT_ID, name, timepoint) |> 
  group_by(UNIQUE_PATIENT_ID, name, timepoint) |> 
  slice_head(n = 1) |> # Or use slice_tail
  pivot_wider(names_from = c("name", "timepoint"))|> 
  select(UNIQUE_PATIENT_ID, sort(tidyselect::peek_vars()))

df3
#> # A tibble: 3 × 35
#> # Groups:   UNIQUE_PATIENT_ID [3]
#>   UNIQUE_PATIENT_ID ALBUMIN_BASELINE ALBUMIN_FIRST ALBUMIN_SECOND ALT_BASELINE
#>   <chr>             <chr>            <chr>         <chr>          <chr>       
#> 1 DIS-1104-1021-E1  42.00            39.00         35.00          <NA>        
#> 2 DIS-1107-1010-E1  <NA>             42.00         35.00          <NA>        
#> 3 DIS-1124-1108-E1  31.00            40.00         <NA>           64.00       
#> # … with 30 more variables: ALT_FIRST <chr>, ALT_SECOND <chr>,
#> #   BILIRUBIN_FIRST <chr>, BILIRUBIN_SECOND <chr>, CREATININE_BASELINE <chr>,
#> #   CREATININE_FIRST <chr>, CREATININE_SECOND <chr>, CRP_BASELINE <chr>,
#> #   CRP_FIRST <chr>, CRP_SECOND <chr>, D_DIMER_BASELINE <chr>,
#> #   D_DIMER_FIRST <chr>, FERRITIN_BASELINE <chr>, FERRITIN_FIRST <chr>,
#> #   HAEMOGLOBIN_BASELINE <chr>, HAEMOGLOBIN_FIRST <chr>,
#> #   HAEMOGLOBIN_SECOND <chr>, LDH_FIRST <chr>, LYMPHOCYTES_BASELINE <chr>, …


# Just to illustrate the issue with varying separators
tribble(~v1, ~v2,
        "a;a", "b;b",
        "a;a", "b;b;b",
        ) |> 
  separate_rows(everything(), sep = ";")
#> Error in `fn()`:
#> ! In row 2, can't recycle input of size 2 to size 3.

# Works
tribble(~v1, ~v2,
        "a;a", "b;b",
        "a;a", "b;b",
) |> 
  separate_rows(everything(), sep = ";")
#> # A tibble: 4 × 2
#>   v1    v2   
#>   <chr> <chr>
#> 1 a     b    
#> 2 a     b    
#> 3 a     b    
#> 4 a     b

Created on 2022-07-04 by the reprex package (v2.0.1)

回复 原文
~没有更多了~

关于作者

只等公子

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文