r - 从奇怪的 Excel 文件中提取数据

Question

我有一个来自解析器的特定 Excel 文件

 > head(my_data)
# A tibble: 6 x 97
  `Investigator N~ `Admin User` `HTG Run ID` `7e4db22f-7025-~ ...5  `Parser Version`
  <chr>            <chr>        <chr>        <chr>            <chr> <chr>           
1 Experiment Name  MeganOBP1    NA           NA               NA    Date Parsed     
2 Experiment Date  NA           NA           NA               NA    NA              
3 Description      Sequencer: ~ NA           NA               NA    NA              
4 NA               NA           NA           NA               NA    NA              
5 Probe Set        ESOBP        NA           NA               NA    NA              
6 Sample ID        1            2            3                4     5               
# ... with 91 more variables: `5.2000000000000002` <chr>, ...8 <chr>, ...9 <chr>,
#   ...10 <chr>, ...11 <chr>, ...12 <chr>, ...13 <chr>, ...14 <chr>, ...15 <chr>,
#   ...16 <chr>, ...17 <chr>, ...18 <chr>, ...19 <chr>, ...20 <chr>, ...21 <chr>,
#   ...22 <chr>, ...23 <chr>, ...24 <chr>, ...25 <chr>, ...26 <chr>, ...27 <chr>,
#   ...28 <chr>, ...29 <chr>, ...30 <chr>, ...31 <chr>, ...32 <chr>, ...33 <chr>,
#   ...34 <chr>, ...35 <chr>, ...36 <chr>, ...37 <chr>, ...38 <chr>, ...39 <chr>,
#   ...40 <chr>, ...41 <chr>, ...42 <chr>, ...43 <chr>, ...44 <chr>, ...45 <chr>,
#   ...46 <chr>, ...47 <chr>, ...48 <chr>, ...49 <chr>, ...50 <chr>, ...51 <chr>,
#   ...52 <chr>, ...53 <chr>, ...54 <chr>, ...55 <chr>, ...56 <chr>, ...57 <chr>,
#   ...58 <chr>, ...59 <chr>, ...60 <chr>, ...61 <chr>, ...62 <chr>, ...63 <chr>,
#   ...64 <chr>, ...65 <chr>, ...66 <chr>, ...67 <chr>, ...68 <chr>, ...69 <chr>,
#   ...70 <chr>, ...71 <chr>, ...72 <chr>, ...73 <chr>, ...74 <chr>, ...75 <chr>,
#   ...76 <chr>, ...77 <chr>, ...78 <chr>, ...79 <chr>, ...80 <chr>, ...81 <chr>,
#   ...82 <chr>, ...83 <chr>, ...84 <chr>, ...85 <chr>, ...86 <chr>, ...87 <chr>,
#   ...88 <chr>, ...89 <chr>, ...90 <chr>, ...91 <chr>, ...92 <chr>, ...93 <chr>,
#   ...94 <chr>, ...95 <chr>, ...96 <chr>, ...97 <chr>
> 

喜欢
[![在此处输入图像描述][1]][1]
从这些数据中，我只想从 WELL 中提取特定的 ID。列定义如下

> my_specified_IDs
 [1] "A1"  "A2"  "A3"  "A4"  "A5"  "A6"  "A7"  "A8"  "A9"  "A10" "A11" "A12" "B1" 
[14] "B2"  "B3"  "B4"  "B5"  "B6"  "B7"  "B8"  "B9"  "B10" "B11" "B12" "C1"  "C2" 
[27] "C3"  "C4"  "C5"  "C6"  "C7"  "C8"  "C9"  "C10" "C11" "C12" "D1"  "E1"  "E2" 
[40] "E3"  "E4"  "E5"  "E6"  "E7"  "E8"  "E9"  "E10" "E11" "E12" "F1"  "F2"  "F5" 
[53] "F6"  "F7"  "F8"  "F11" "F12" "G1"  "G2"  "G3"  "G4"  "G5"  "G6"  "G7"  "G8" 
[66] "G9"  "G10" "G11" "G12" "H1"  "H2"  "H3"  "H4"  "H5"  "H6"  "H7"  "H8" 
>

我想要的输出是相同格式的 Excel 文件，但在 WELL column 中只有这些 ID。
我想过相交，但这不起作用

Answer 1

根据显示的图像，第一列中有一行具有值“Well”。因此，我们通过 filter 进行子集化将该行重新整形为“长”格式和filter基于 'my_specified_IDs' 并将其用于 select 'my_data' 的列

library(dplyr)
library(tidyr)
my_data %>%
      filter(`Investigator Name` == "Well") %>%
      pivot_longer(everything()) %>%
      filter(name %in% my_specified_IDs) %>%
      pull(name) %>%
      select(my_data, .)

或者另一种选择是使用 select_if

my_data %>%
    select_if(~ any(. %in% my_specified_IDs))

iris 的一个可重复的小例子

head(iris) %>%
    select_if(~ any(. %in% "setosa"))