1

I currently have a data set that looks like this:

INPUT

Hybridization REF   TCGA-FA-8693-01A-11D-2399-05    TCGA-FA-8693-01A-11D-2399-05    TCGA-FA-8693-01A-11D-2399-05    TCGA-FA-8693-01A-11D-2399-05    TCGA-FA-A4BB-01A-11D-A31Y-05    TCGA-FA-A4BB-01A-11D-A31Y-05    TCGA-FA-A4BB-01A-11D-A31Y-05    TCGA-FA-A4BB-01A-11D-A31Y-05
Composite Element REF   Beta_value  Gene_Symbol Chromosome  Genomic_Coordinate  Beta_value  Gene_Symbol Chromosome  Genomic_Coordinate
cg00000029  0.856505141 RBL2    16  53468112    0.334665026 RBL2    16  53468112
cg00000108  NA  C3orf35 3   37459206    NA  C3orf35 3   37459206
cg00000109  NA  FNDC3B  3   171916037   NA  FNDC3B  3   171916037

The data set is much bigger and is almost 10 GB in size. So too big to do in R for example.

However, a lot of the columns are effectively duplicates. For example, I only need to keep one of each of the columns titled (second row) Gene_Symbol, Chromosome and Genomic_Coordinate. The individual Beta_value columns need to stay because they are different for each sample. Sample IDs are on the first row. So an example desired output of the above is:

DESIRED OUTPUT

Hybridization REF   Gene_Symbol Chromosome  Genomic_Coordinate  TCGA-FA-8693-01A-11D-2399-05    TCGA-FA-A4BB-01A-11D-A31Y-05
cg00000029  RBL2    16  53468112    0.856505141 0.334665026
cg00000108  C3orf35 3   37459206    NA  NA
cg00000109  FNDC3B  3   171916037   NA  NA

Note that I've shuffled the column headers in the first row to remove redundant information.

What's the most efficient way of doing that with bash?

Improve this question
6
  • Your columns are separated by tabs (cool; that's easy), but as I understand it, you also want to remove some rows from each multi-line record. Or is it just that you want the two lines at the very top of the file rearranged? Those are very different problems...
    – mattdm
    Apr 18, 2016 at 20:49
  • I'd like Hybridization REF to remain in place and Gene_Symbol, Chromosome and Genomic_Coordinate on the second row to become the headers of the second to fourth columns respectively. The TCGA... sample IDs should remain but only where they correspond to the columns indicated by the Beta_value in the second row.
    – user3313178
    Apr 18, 2016 at 21:01
  • The format of the data seems to comprise two rows as the column headers. I want to get rid of that format as above
    – user3313178
    Apr 18, 2016 at 21:03
  • I have no idea about genomics — are the TCGA-FA-8693-01A-11D-2399-05 , etc, lines headers or are they data?
    – mattdm
    Apr 18, 2016 at 21:14
  • And do the headers only appear at the top of the file, or every N lines?
    – mattdm
    Apr 18, 2016 at 21:15

1 Answer 1

Reset to default
2

Assuming your headers are just the two lines at the top of the file, I would just fix them manually. (Easy way: pipe tail -n +3 to skip the first two lines; then concatenate with a file with your desired headers.)

Then, the rest you can do with cut, which defaults to splitting on [TAB] characters, which is super-convenient because that's what you have already. Just pipe to

cut -f1,3,6

or whatever (in that example, selecting the first, third, and sixth column).

Improve this answer
6
  • Thanks. For someone relatively new to unix, could you let me know the exact code to transform my input to the desired output? I'm not sure that hard coding the column numbers will do though because I wish to iterate through multiple files all of which will have variable numbers of samples/columns.
    – user3313178
    Apr 18, 2016 at 22:46
  • So you want to match on column title? With issues like selecting between which of duplicate titles, and the desired rearrangement, you're quickly approaching the complexity where spending a few hours to learn the basics of Python or Perl will probably pay off.
    – mattdm
    Apr 18, 2016 at 22:50
  • Okay. I'd be happy to use Python (again not familiar) for this task?
    – user3313178
    Apr 18, 2016 at 22:52
  • My point is that I don't think it's a few lines of clever shell. I mean, there probably is some, but that's almost a game for codegolf.stackexchange.com. The best thing to do is to write a little program to do it. The bad news is that I'm not going to do that. The good news is that if you do, you'll find it incredibly useful for all sorts of things in the future. But if you really want to work on a shell hack, awk is probably your best bet (it's a small programming language in itself). See stackoverflow.com/questions/2129123/rearrange-columns-using-cut
    – mattdm
    Apr 18, 2016 at 23:03
  • Okay thanks for the information. Should I stick to asking my question on stackoverflow?
    – user3313178
    Apr 18, 2016 at 23:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .