Submittal Data: Fill & Download for Free

Relational database is not suitable for DNA storage. Neither do column-oriented database nor NoSQL database. Here is why.Relational databases are suitable for storage of highly structured, fixed, limited sets/tuples and their relations. For a regular relational database (row-oriented DBMS) the number of columns is much smaller (usually limited by thousands) than the number of bases in DNA sequence which is 100k or even 3 billion bases for a full genome (6 billion letters). Also, the database latency, complication of selects and other problems will prevent the storage of individual bases in separate columns. And storing of the complete sequence as one BLOB/CLOB is no better than storing of a regular image or a text document in one RDBMS record (so, why to have additional latency and administration overhead of a RDBMS at all in that case?). Column-oriented database have a different issue: locality of reference. Although we'll be able to extract one particular base from the set of genomes fast, the extracting of a sequence of length N will require N disk seeks, which is very expensive. Imagine just a simple operation of scanning through the genome: there will be 100k or 3 billion (in case of full genome) disk seeks in just one file pass (because in column-oriented database data has been stored in columns while adjacent bases will be far from each other, in different blocks, even sectors on the disk, so for extracting of one genome the disk will spend a lon time in seeks). Even SSD is not a solution (not speaking about the price of storing petabytes of data, when just one full genome in FASTA file is 200GB).So, traditional databases (row-based and even column-based) are not suitable for DNA sequencing.I doubt that NoSQL in general (like BigTable and the like) are suitable for the same reasons (latency and complexity of combining individual bases into sequences) as well. It is like storing a document with individual letters scattered through a cluster. Or it is like storing a large image, even a movie with individual pixels scattered through different disk sectors, even computers (as with BigTable cluster implementations). However, the above-mentioned does not prevent use of RDBMS or NoSQL for traditional utility purposes: for example as a store for genomes metadata, the owners of the files, access permissions, paths tothe files, submitters data, workflow history etc.The question is: what type of database to use with genome sequences? (Learned at school, there existed hierarchical databases, network databases, RDBMS, OODB, key-value storages, and some of them are nowadays combined under fashionable NoSQL term). Neither seems to reflect the structure of huge DNA sequence files. But wait. Why bother?The obvious solution is using already existing hierarchical database known to us as the file system. Good file-systems (such as XFS, JFS, ext4 etc) already support B-tree indexing (so extracts by name is fast, in logarithmic time, storage is optimized for huge files etc), while the adjacent bases are stored near each other in the same disk block, even in adjacent bytes, so one sequence read will use the same disk rotation without any seeks. As a matter of fact, developing aisconvert , I found that reading of two 100k bases files and finding a Half-IBD in algorithmic way takes just a few seconds (disk reads are fast). I doubt that pre-selecting data from RDBMS or another database with all the latencies and overheads will give even a comparable performance, especially processing all genomes against each other.Another consideration is that different formats from different companies and non-for-profit organizations are hard to normalize. The formats listed in the question are just very limited outputs from the commercial companies, while companies may want to store the original, more complete files (such as FASTQ/FASTA), the direct outputs from sequencers. I'm assuming that the discussed DNA database will want to include free genomes which are availbale on the Internet and which exist in numerous formats. So, companies who want to have more data in their databases (aggregators) will rather have hundreds of different formats with different sets of metadata and different dimensions, and some metadata (such as the quality score of detection of a SNP) may be very useful for some algorithm or an application (for example, to ignore a low threshold SNP of one company which is below the acceprance level of another company - to have a common base). So, the source data (the original data received as files) should probably be stored in the ideal DNA database been accessible by algorithms/APIs anyway.So, we can consider the original files as the source data and treat it in the same way as big organizations treat their OLTP (operational data) database. For easier analytics and Data Mining those big companies usually use a separate, de-normalized OLAP database containing the same data in a different representation. They frequently organize it as a separate Data Warehouse. And the same approach can be applicable for DNA sequencing. It may be even suggested (if the number of data dimensions is big) to use the same terminology as in BI: slicing, dicing, drill-down, roll-up, pivoting data etc (the same operations are done with multi-dimensional data in organizations working with Big Data).However, as I can imagine, the permanent storage of the same data (petabytes) in different views may be too expensive, especially for non-for-profit organizations such as university laboratories with limited budgets (also backup should be stored!), so the source files (hierarchical database) could be (IMHO) the best option, together with the set of libraries and APIs working with those source files, providing different views, extracts and optionally storing data subsets in local cache for individual applications and analytics. BTW there are libraries such as BioJava, BioPython, BioPerl etc which provide such access/API.

You get duplicate values in Pivot tables results, because of data formatting is not consistent. For example if the data is numeric in a column, and there is some data whose formatting is Text. So, just use the feature Text to Columns. Take the following steps;Select the data column in your source dataClick on Data > Text to ColumnsSelect the Data type “Delimited”Select the Finish ButtonFinally go to your Pivot Table, Right Click on Header of Pivot Table, and select “Refresh”. Your Pivot Table data will be refreshed and all the duplicate values will be consolidated.If you want to talk live, I'm part of an experiment where we are trying out free, instant chat sessions between a problem submitter and an excel expert like me. Feel free to try it! Or you can continue here, that's fine too.

Anonymous because I still work on this team.I roll out of bed at 9am and squint at my work email on my phone. My whole team, with the obvious exception of me, are early birds and the flurry of emails flood my inbox well before I’m awake. There are 3 emails asking for different cuts of the data (the business analysts can handle that, pivot tables always keep people happy), another email from the dev team updating us on the 3-month late progress on publishing data from their dev systems to a shared database, and a dozen other emails auto generated from the sprint management system that the “BI team” is using to track their work.Shielding my face from the year-round drizzle on my way to work, I remind myself that I am one of the lucky ones to hold the highly coveted title of Data Scientist in an equally coveted company for millennials. I get into the office and see that the initial data ask email threads have grown, the SDM (Software Dev Manager) wants the analyst to see if feature X is causing our KPIs to increase by checking their correlation. I have an internal debate whether to jump into the thread and point out for the nth time that correlation is not causation, and finally settled for just rolling my eyes at my laptop since it was only yesterday when the same SDM did not grasp the meaning of a control when testing incremental KPI impact of a new feature.I put on my noise-canceling headphones and work on cleaning up the newest dump of data in preparation for an analysis I have to do. The auto-complete feature in my Python IDE stopped working; I have another internal debate about whether I should move back to R Studio but Python is The Cool Language right now for data scientists so I stick with my Python IDE but my variable and dataframe names get shorter and shorter. I see a column with two values ‘SUBMIT’ and ‘SUBMITT’ and wonder if a SDE (Software Dev Engineer) forgot how to spell, but the frequency of ‘SUBMITT’ is too high for it to be a typo. I make a mental note to hunt down the SDE in question.I take a break after battling with timestamps and time zones. We have a new operations manager who joined the team and I’m walking him through some data. He nods his way through most of it before ending with we really need to make the week format align for these two reports. I reply without looking up that you’ll need to take that up with the business analyst who owns reporting.3pm! Weekly business review! We talk about magical machine learning/ dynamic scheduling solutions that are currently coded as if-else statements. I scribble down in my notepad to bring up this idea of developing a random forest model to replace the current if-else model to my manager. Speaking of random forest model, a principal product manager once asked me with a straight face why I can’t build a random forest model using SQL. I had no snarky comeback and instead spent the rest of that day wallowing in my apparent wrong choice of field.Operation leads want me to run some pivot tables on clickstream data, which is stored as daily CSV files. I push the request to the analyst but he hems and haws because he still can’t use the groupby function in Python despite claiming to have completed an intermediate course in data science.I cannot bring myself to do more data cleaning at 4pm so I do some brainless data pipeline maintenance like setting up alarms for the new jobs. We are also running short on borrowed favors from the dev team to debug our janky data pipeline; I need to convince my manager that data pipelines don’t fix themselves and we really need a data engineer.There are no good TV shows I like going on , so I re-watch a couple of episodes from one of my favorite TV shows Numb3rs, where this genius mathematician helps his FBI solve crimes using math. I get reminded once again how beautiful and elegant it is to be able to convert our deterministically random world into math equations and statistical models to answer difficult questions and predict certain seemingly random behaviors, and I go sleep consoled that maybe I am in the right field after all.