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?
Hybridization REF
to remain in place andGene_Symbol
,Chromosome
andGenomic_Coordinate
on the second row to become the headers of the second to fourth columns respectively. TheTCGA...
sample IDs should remain but only where they correspond to the columns indicated by theBeta_value
in the second row.