Filtering columns from large file based on a column list

Question

I have list of samples as a txt file and want to only keep these samples from this large file; column name is samples name in the large file.

library(readr)
library(data.table)

df1 <- read_csv("control.csv", col_types = cols(Chromosome = col_skip(), Start = col_skip(), End = col_skip(), Strand = col_skip()))

df1[1:5,1:5]
# A tibble: 5 × 5
  ID          DN54 DC146 DDN44 DC179
  <chr>      <dbl> <dbl> <dbl> <dbl>
1 cg26928153 0.848 0.879 0.872 0.897
2 cg16269199 0.731 0.732 0.759 0.775
3 cg13869341 0.842 0.863 0.819 0.896
4 cg24669183 0.824 0.866 0.822 0.910
5 cg26679879 0.425 0.363 0.420 0.358

Answer 1

You can save time and memory by preprocessing your file before loading it into R.

Use miller toolkit to manipulate csv or tab-delimited files using column names.

Below is a one-liner that reads csv file, and writes columns specified in option -f, in the order (-o) listed on the command line:

mlr --csv cut -o -f col3,col1 in.csv > out.csv

To read the list of columns from a file, 1 column name per line:

mlr --csv cut -o -f $(paste -s -d, columns.txt) in.csv > out.csv

See also:

Note that miller can be easily installed, for example, using conda, specifically miniconda, like so:

conda create --channel conda-forge --name miller miller

Answer 2

It's really just count[,col$V1]. Please learn about basic subsetting of columns and rows, it's simple yet powerful.

Answer 3

Below code works perfectly of me.

library(readr)
library(data.table)

df1 <- read_csv("control.csv", col_types = cols(Chromosome = col_skip(), Start = col_skip(), End = col_skip(), Strand = col_skip()))

df1[1:5,1:5]
# A tibble: 5 × 5
  ID          DN54 DC146 DDN44 DC179
  <chr>      <dbl> <dbl> <dbl> <dbl>
1 cg26928153 0.848 0.879 0.872 0.897
2 cg16269199 0.731 0.732 0.759 0.775
3 cg13869341 0.842 0.863 0.819 0.896
4 cg24669183 0.824 0.866 0.822 0.910
5 cg26679879 0.425 0.363 0.420 0.358

col <- unlist(fread("samples.csv", header=F))
col <- c("ID", col)
extracted <- df1[,col, with=F]

write.table(extracted, file="control.beta.txt",sep=",", quote=F, col.names=TRUE,row.names=FALSE)

df <- read_csv("control.beta.txt")
df[1:5,1:5]
# A tibble: 5 × 5
  ID         DC103R DC110R DC115 DC120R
  <chr>       <dbl>  <dbl> <dbl>  <dbl>
1 cg26928153  0.929  0.885 0.862  0.877
2 cg16269199  0.836  0.826 0.604  0.739
3 cg13869341  0.826  0.903 0.830  0.841
4 cg24669183  0.767  0.719 0.808  0.812
5 cg26679879  0.388  0.366 0.428  0.360