How to subset column based on specific row value in R?

user_15021889 0
Viewed 362

I have to dataframe df1 and trying to extract the column where row (Ensembl_ID) no. 5 (ENSG00000000460) value is less than 0.9 (<-0.9). This means that if the row 5 containing values lesser than 0.9 then it must be used as criteria to extract all the column that satisfy the condition in that row.

df1 <- data.frame(
  Ensembl_ID = c("ENSG00000000003", "ENSG00000000005", "ENSG00000000419", 
                 "ENSG00000000457", "ENSG00000000460", "ENSG00000000938", 
                 "ENSG00000000971", "ENSG00000001036", "ENSG00000001084", 
                 "ENSG00000001167" ), 
  `logFC 1` = c(-0.834165161710272, 1.02199443531549, 
                -0.558658947885705, -0.390114219973209, -1.23551839713296, 
                3.11429434221998, 0.283932163407262, -1.16908518620064, 
                -0.597054772455507, -0.593624543273255), 
  `logFC 2` = c(-1.18531035488942, 0.423719727339646, -1.23261719368372, 
                0.0855281133529292, -1.52366830232278, 3.36692586561211, 
                1.00323690950956, -0.000211248816114964, -4.74738483548391, 
                -0.318176231083024), 
  `logFC 3` = c(-0.262659255267546, 1.3962481061442, -0.548673555705647, 
                -0.0149651083306594, -1.45458689193089, 2.54126941463459, 
                1.17711308509307, -1.19425284921181, 1.17788731755683, 
                -0.367897054652365 ), 
  `logFC 4` = c(-0.840752912305256, 0.536548846040064, -0.277409459604357, 
                -0.241073614962264, -0.875313153342293, 1.61789645804321, 
                0.412287101096504, -1.11846661523232, -2.6274528854429, 
                -0.760452698231182), 
  `logFC 5` = c(-0.968784779247286, -0.502809694119192, -0.231526399163731, 
                -0.530038395734114, -0.706006018337411, 3.58264357077653, 
                -0.127521010699219, 0.270523387217103, 1.68335644352003, 
                -0.314902131571829), 
  `logFC 6` = c(-0.481754175843152, -0.440784040523259, -0.532975340622715, 
                -0.182089795101371, -0.564807490336052, 1.74119896504534, 
                -0.96169805631325, -0.721782763145306, -0.433459827401695, 
                -0.727495835245995 ), 
  `logFC 7` = c(-0.889343429110847, 1.07937149728343, -0.215144871523998, 
                -0.92234350748557, -0.832108253417702, 2.02456082994848, 
                -0.0434322861759954, -0.523126561938426, -0.556984056084809, 
                -0.740331742513503), 
  `logFC 8` = c(-0.858141567384178, 1.87728717064375, -0.381047638414538, 
                -0.613568289061259, -1.92838339196505, 2.23393705735665, 
                0.635389543483408, -0.466053620529111, -1.50483745357134, 
                -1.33400859143521), 
  `logFC 9` = c(-0.486388736112514, 0.789390852922639, -0.869434195504952, 
                -0.70405854858187, -1.16488184095428, 2.91497178849082, 
                -2.10331904053714, -0.571130459068143, -0.219526004620518, 
                -0.301435496557957)
)

I tried

output <- df1[,c(TRUE, df1[df1$Ensembl_ID=="ENSG00000000460",-1 ]< -0.9)]

but it returns logical vector instead column, any idea why? or is there is any other way to do?

Thank you in advance. Thanks

tidyverse r
3 Answers

You can subset the column names.

cols <- names(df1)[-1][df1[df1$Ensembl_ID=="ENSG00000000460", -1] < -0.9]
cols
#[1] "logFC.1" "logFC.2" "logFC.3" "logFC.8" "logFC.9"

To subset the entire column you can use df1[cols].

dplyr way to get cols -

library(dplyr)

cols <- df1 %>%
  filter(Ensembl_ID=="ENSG00000000460") %>%
  select(where(~. < -0.9)) %>%
  names()
user_39629140

You could do this:

library(dplyr)
library(tidyr)

df1 %>% 
    pivot_longer(
        cols = -Ensembl_ID,
        names_to = "names",
        values_to = "values"
    ) %>% 
    filter(values < -0.9) %>% 
    pivot_wider(
        names_from = names, 
        values_from = values
    ) %>% 
    filter(Ensembl_ID == "ENSG00000000460") %>% 
    select(where(~!all(is.na(.x))))

Output:

# A tibble: 1 x 6
  Ensembl_ID      logFC.2 logFC.1 logFC.3 logFC.8 logFC.9
  <chr>             <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1 ENSG00000000460   -1.52   -1.24   -1.45   -1.93   -1.16
user_133216470

Using dplyr

library(dplyr)
df1 %>% 
   slice(match("ENSG00000000460", Ensembl_ID)) %>%
   select(where(~. <  -0.9)) %>% 
   colnames
[1] "logFC.1" "logFC.2" "logFC.3" "logFC.8" "logFC.9"
user_37322710
Related