VCF into Excel - changing decimal numeric value with dot instead comma into date

Question

[Lukáš]

Hi, i really dont know what to do. I am stuck on filtered vcf data into [using excel 2016]. Unfortunately it was filtered by somebody else so i dont have original vcf file.

I noticed that in vcf data the decimal numbers have dot insted comma. This changed some values like CADD_phred or QUAL into date [format on picture].

Sadly i need to filter my data according QUAL & CADD_phred columns. Is it possible to changed back that data into numeric value without changing cells value?

Even Though my advisor told me that they don't care about it, I am really worried. I got 78 thousands rows of variables but 10 % of it is this badly formated data. So i dont think that my analysis wouldnt be biased if i delete that part.

[literally any help is welcome]

[Pierre Lindenbaum]

Want to know something terrifying? There are people currently analysing whole exome variant data using Excel. I kid you not.

— Daniel MacArthur (@dgmacarthur) October 25, 2012

[Lukáš]

I thought it was silly. Yet now I am extremely frightened to take the next step.

Answer 1

[4galaxy77]

Please, find out where the original VCF is and filter it using a proper piece of software (bcftools most likely). You simply cannot perform a reliable piece of analysis when you use excel to process a vcf - there are so many things that can go wrong and screw up all of your downstream analysis. Also, no offence to your supervisor, but it sounds like they shouldn't be in charge of a project with that kind of attitude towards data curation and software.

I would say there isn't much point at all in preceding with whatever analysis you are planning on doing unless you use a proper piece of software.

[Lukáš]

Thank you. Actually this is exacly what i thought when i noticed that.

Answer 2

[todor.gitchev]

I also recommend seeing how you filter and transform the data correctly, and as mentioned above, the bcftools especially the query and view are very powerful commands https://samtools.github.io/bcftools/bcftools.html. Otherwise, you'll need maybe some more complex script in other languages: Python (pybedtools, pyranges,..), bash (sed, awk), R (vcfR), ...