VCF into Excel - changing decimal numeric value with dot instead comma into date
2
0
Entering edit mode
2.9 years ago
Lukáš ▴ 50

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].

enter image description here

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]

Excel • 1.3k views
ADD COMMENT
2
Entering edit mode

ADD REPLY
0
Entering edit mode

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

ADD REPLY
3
Entering edit mode
2.9 years ago
4galaxy77 2.9k

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.

ADD COMMENT
0
Entering edit mode

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

ADD REPLY
0
Entering edit mode
2.9 years ago

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), ...

ADD COMMENT

Login before adding your answer.

Traffic: 2369 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6