2

I have a data set and I need to extract certain columns using linux for example

I have the following columns

1,2,3,4,5,6,7,8,9,10

and I want to keep col 1 then select col 2 and skip 3,4,5 the and select col 6 and skip 7,8,9 and select col 10 and so one so I want to skip 3 columns each time

here is part of the data

Composite Element REF   Beta_value  Gene_Symbol Chromosome  Genomic_Coordinate  Beta_value  Gene_Symbol Chromosome  Genomic_Coordinate
cg00000029  0.188204193 RBL2    16  53468112    0.166988369 RBL2    16  53468112
cg00000108  NA  C3orf35 3   37459206    NA  C3orf35 3   37459206
cg00000109  NA  FNDC3B  3   171916037   NA  FNDC3B  3   171916037
cg00000165  0.111512805 NA  1   91194674    0.542004657 NA  1   91194674
cg00000236  0.938647003 VDAC3   8   42263294    0.935845645 VDAC3   8   42263294
cg00000289  0.633428798 ACTN1   14  69341139    0.733281344 ACTN1   14  69341139
cg00000292  0.65556936  ATP2A1  16  28890100    0.358096457 ATP2A1  16  28890100
cg00000321  0.191278127 SFRP1   8   41167802    0.509407035 SFRP1   8   41167802
cg00000363  0.135746609 NA  1   230560793   0.120685168 NA  1   230560793
cg00000622  0.017719646 NIPA2   15  23034447    0.018611505 NIPA2   15  23034447
cg00000658  0.891647766 MAN1B1  9   139997924   0.904048927 MAN1B1  9   139997924
cg00000714  0.061226142 TSEN34  19  54695678    0.071011447 TSEN34  19  54695678
cg00000721  0.950283919 LRRC16A 6   25282779    0.952309685 LRRC16A 6   25282779
cg00000734  0.058200906 CNBP    3   128902377   0.05981105  CNBP    3   128902377
cg00000769  0.025102512 DDX55   12  124086477   0.028169739 DDX55   12  124086477

I need to keep the first column "Composite Element REF" and then select only the Beta_Value so it is column 2 then column 6 and so on and the total number of columns is 1373

Thank you

  • are the number of columns unknown, or definitely 10? – Jeff Schaller May 10 '17 at 18:54
  • just from a pattern point of view, you're keeping column 1, then starting a pattern of "keep a column, skip 3 columns" (repeat)? – Jeff Schaller May 10 '17 at 18:55
  • can you provide more extended input text? It's unclear how exactly should look your expected result – RomanPerekhrest May 10 '17 at 19:11
  • there are 1373 columns in the data set. – Marwah Soliman May 10 '17 at 21:01
  • @JeffSchaller yes i keep column 1 and start from column 2 repeat for the 1373 columns – Marwah Soliman May 10 '17 at 21:05
1

[This is based on you - current - space separated sample data]

You could use an array slice in perl, selecting the indices using grep and some modulo arithmetic:

perl -anle 'print join "\t", $F[0], @F[grep { !($_ % 4 - 1) } 1..$#F]' file
| improve this answer | |
3

Your question is lacking a bit of details. I assume it is something found in either a text file or from a pipe.

The quick and dirty use of filtering columns would be awk

Since your question is hold basic, I will focus on that:

$ echo "1,2,3,4,5,6,7,8,9,10" |awk -F, '{print $1 "," $2 "," $6 "," $10;}'
1,2,6,10

The -F, is required to declare the comma as a field separator.
$0 would be the whole input
$1 the first column
$2 the second
...

If your input data differs from your example, please be more specific.

| improve this answer | |
  • i have 1373 columns so this won't work for all the columns – Marwah Soliman May 10 '17 at 21:03
1

For an awk-based solution:

awk -v FS=, -v OFS=, '{ printf $1; for(i=2;i<=NF;i+=4) { printf OFS $i }; print "";}' marwah.input
| improve this answer | |
  • If the data is not comma-separated, then just remove the -v ... assignments. – Jeff Schaller May 10 '17 at 21:18
0
sed -E 's/(,[^,]*)(,[^,]*){3}/\1/g' yourfile.txt

will keep columns 1,2,6,10,14,18 and so on. I hope this is what you expect.

| improve this answer | |
  • where should i put the file name? – Marwah Soliman May 10 '17 at 21:04
  • At the end, after the script. See man sed – Philippos May 11 '17 at 3:24
  • @MarwahSoliman Did it work? Something still wrong? – Philippos May 12 '17 at 7:48
  • I used the perl solution and it worked I will test your code and let you know – Marwah Soliman May 12 '17 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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