-2
$\begingroup$

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
$\endgroup$
4
  • 1
    $\begingroup$ What have you tried? This is a straightforward R operation. $\endgroup$
    – Ram RS
    Nov 7, 2023 at 17:53
  • $\begingroup$ it is resolved now, I have updated my code above. $\endgroup$ Nov 8, 2023 at 13:44
  • 1
    $\begingroup$ "I have updated my code above" - Don't do that, add an answer and accept it instead. $\endgroup$
    – Ram RS
    Nov 8, 2023 at 16:15
  • $\begingroup$ Thanks Ram, I will add correct code as answer soon. $\endgroup$ Nov 12, 2023 at 10:04

3 Answers 3

3
$\begingroup$

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
$\endgroup$
3
$\begingroup$

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

$\endgroup$
3
  • $\begingroup$ thanks but it is not giving me CpG ID in first column. $\endgroup$ Nov 8, 2023 at 13:02
  • 1
    $\begingroup$ There you see that you're not investing time into understanding an answer. What this here does is to provide names to the object to be filtered using the inbuilt R filtering operator. If you need the names plus something else then add this. Like c(cpg_name, col$V1). Show some effort. $\endgroup$
    – ATpoint
    Nov 9, 2023 at 6:16
  • $\begingroup$ Thanks @ATpoint, it is sorted now and with your command, I was also not getting column names. $\endgroup$ Nov 12, 2023 at 10:02
1
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.