I currently have a data set that looks like this:
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)
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:
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?