Today (close to 2021), many companies and research organisations are still working with Excel spreadsheets or similar versions. Despite the fact that spreadsheets were (and still are) excellent tools for providing reactive computational models, the thing is that several major issues concern the statistical and computing community. The main problems found when working with spreadsheets are related to disorganization and errors. Simple research on Google can give you hundreds of examples but I would like to share one I found on BBC News called Excel: Why using Microsoft's tool caused Covid-19 results to be lost in which 16,000 positive corona-virus cases were unreported in the UK owing to a spreadsheets formatting error.
For this learning project, I have been asked to convert a Comma-Separated Value (CSV) file into Variant Call Format. A CSV file is a simple text file that uses coma to separate values. This format has been used for decades to store tabular data in which lines have the same number of fields. Excel spreadsheets or similar can interpret this data and show you as a data frame (what we see when we open a simple Excel document).
If we open a CSV file with any text editor we will see something like this:
Domain,Origin,Gene,GRCh38 coordinates;ref/alt allele,Transcript,CDS change and protein change,Predicted consequences,Population germline allele frequency (1KG | gnomAD),Genomics England germline allele frequency,VAF,Alt allele/total read depth,Genotype,COSMIC ID,ClinVar ID,ClinVar review status,ClinVar clinical significance,Gene-level actionability,Variant-level actionability,Gene mode of action,PharmGKB_ID
2,somatic,AXIN2,17:65558019;C>T,ENST00000307078,c.602G>A;p.(Arg201Lys),missense_variant, - | <0.00005,,0.46,46/99,,,,,,,,tumor suppressor gene,
2,somatic,FLT4,5:180630768;C>T,ENST00000261937,c.187G>A;p.(Gly63Arg),missense_variant, - | <0.00005,,0.53,34/64,,,,,,,,oncogene,
2,somatic,PTPRB,12:70539697;T>A,ENST00000334414,c.5706A>T;p.(Lys1902Asn),missense_variant,- | -,,0.49,51/105,,,,,,,,tumor suppressor gene,
2,somatic,WT1,11:32396364;G>T,ENST00000332351,c.1142C>A;p.(Ser381*),stop_gained,- | -,,0.11,Oct-91,,COSM27307;COSM1317326;COSM5879249,,,,,,both,
2,somatic,WT1,11:32396372;AAG>A,ENST00000332351,c.1132_1133delCT;,frameshift_variant,- | -,,0.21,20/97,,COSM96861,,,,,,both,
3,somatic,ACVR2B,3:38482270;C>T,ENST00000352511,c.1147C>T;p.(Arg383Cys),missense_variant,- | -,,0.11,Oct-88,,COSM5449864;COSM5449863,,,,,,,
3,somatic,AHNAK,11:62522261;A>T,ENST00000378024,c.12156T>A;p.(His4052Gln),missense_variant;(SR)(PON), - | - ,,0.13,Dec-92,,COSM4411983;COSM4411984,,,,,,,
3,somatic,AHNAK2,14:104945249;A>G,ENST00000333244,c.10202T>C;p.(Met3401Thr),missense_variant;(SR)(PON),- | -,,0.1,Sep-93,,,,,,,,,
3,somatic,AHNAK2,14:104943909;T>C,ENST00000333244,c.11542A>G;p.(Thr3848Ala),missense_variant;(SR)(PON), - | <0.00005,,0.1,Oct-97,,,,,,,,,
When this file is opened by Excel, each line represents one row and each value separated by comma are allocated in separated columns.
On the other hand, while CSV files are used in a very varied context, VCF are specific format of text files used for storing gene sequence variation. Comparing CSV with VCF, this is completely new being developed to resolve data storage issues in large-scale genotyping projects such as the 1000 Genomes Project.
A simple example of a VCF file is shown below (taken from Wikipedia).
##fileformat=VCFv4.3
##fileDate=20090805
##source=myImputationProgramV3.1
##reference=file:///seq/references/1000GenomesPilot-NCBI36.fasta
##contig=<ID=20,length=62435964,assembly=B36,md5=f126cdf8a6e0c7f379d618ff66beb2da,species="Homo sapiens",taxonomy=x>
##phasing=partial
##INFO=<ID=NS,Number=1,Type=Integer,Description="Number of Samples With Data">
##INFO=<ID=DP,Number=1,Type=Integer,Description="Total Depth">
##INFO=<ID=AF,Number=A,Type=Float,Description="Allele Frequency">
##INFO=<ID=AA,Number=1,Type=String,Description="Ancestral Allele">
##INFO=<ID=DB,Number=0,Type=Flag,Description="dbSNP membership, build 129">
##INFO=<ID=H2,Number=0,Type=Flag,Description="HapMap2 membership">
##FILTER=<ID=q10,Description="Quality below 10">
##FILTER=<ID=s50,Description="Less than 50% of samples have data">
##FORMAT=<ID=GT,Number=1,Type=String,Description="Genotype">
##FORMAT=<ID=GQ,Number=1,Type=Integer,Description="Genotype Quality">
##FORMAT=<ID=DP,Number=1,Type=Integer,Description="Read Depth">
##FORMAT=<ID=HQ,Number=2,Type=Integer,Description="Haplotype Quality">
#CHROM POS ID REF ALT QUAL FILTER INFO FORMAT NA00001 NA00002 NA00003
20 14370 rs6054257 G A 29 PASS NS=3;DP=14;AF=0.5;DB;H2 GT:GQ:DP:HQ 0|0:48:1:51,51 1|0:48:8:51,51 1/1:43:5:.,.
20 17330 . T A 3 q10 NS=3;DP=11;AF=0.017 GT:GQ:DP:HQ 0|0:49:3:58,50 0|1:3:5:65,3 0/0:41:3
20 1110696 rs6040355 A G,T 67 PASS NS=2;DP=10;AF=0.333,0.667;AA=T;DB GT:GQ:DP:HQ 1|2:21:6:23,27 2|1:2:0:18,2 2/2:35:4
20 1230237 . T . 47 PASS NS=3;DP=13;AA=T GT:GQ:DP:HQ 0|0:54:7:56,60 0|0:48:4:51,51 0/0:61:2
20 1234567 microsat1 GTC G,GTCT 50 PASS NS=3;DP=9;AA=G GT:GQ:DP 0/1:35:4 0/2:17:2 1/1:40:3
To convert a CSV file into a VCF file, I first needed to understand the minimum structure and necessary requirements for creating these files. For this conversion, the VCFv4.3.tex version was consulted. A VCF file starts with meta-information data (lines prefixed with '##'), continues with a header line (prefixed with “#”), and concludes with data lines, each containing information about position in the genome and genotype information (text fields separated by tabs). Empty values are not allowed, so a dot ('.') is used instead. For specifying a new line, both LF (“\n”) and CR+LF (“\r\n”) are allowed.
Meta-information lines are key=value pairs, and 'fileformat' is always required. The rest of the meta-information lines are optional.
As can be seen in the example, the 8 header line names (CHROM, POS, ID, REF, ALT, QUAL, FILTER, and INFO) are mandatory. Additional columns for extra information are allowed, and the format for each column follows a strict code that can be read in the documentation. Now, I will explain how the conversion was carried out.
When I faced this project, the first consideration was what kind of program I wanted to create for the conversion. Did we want a program that would only convert the CSV provided by my supervisor, or did we want a more flexible code that could recognize the vast majority of CSVs that store gene sequence variation? This question is not trivial, as the complexity of the code increases significantly between the two.
If we were to write a script that only converted the provided CSV (or a very similar one), we would need to modify the script every time we were required to do a similar conversion in the future. Thinking practically and aiming for efficiency (and also following the Best Practices for Scientific Computing), the most appropriate approach would have been to develop a simple interface that allowed anyone without informatics knowledge to convert the document themselves. With my current skills in programming, this would have been a challenging but acceptable project.
Since this is a learning project, I decided to start by creating a simple script that only converts the provided CSV into a VCF file. This is the one I will explain in this post. A more advanced one has been proposed as a paired collaboration assessment in the Module Programming of the Master's in Genomic Medicine.