6 ms·
Data Organization in Spreadsheets
- yannis 8y agoA technique I use, is to enter the data in an sqlite3 database, using SQLiteStudio and use spreadsheets for importing, exporting and presentation only.
- wodenokoto 8y ago> save the data in plain text files. Not if you are working in excel! It can corrupt all sorts of unicode data when exporting to csv.
- phonon 8y agoExcel has a CSV UTF-8 export option...use it!
- cm2187 8y agoExcel has many other sins. If you have a long numeric ID in a csv file, it will import it as a floating point and truncate (round) the ID.
- phonon 8y agoLearn how to use the "From Text" wizard importer...
- cm2187 8y agoOnce you have are aware of it there are ways around that. The problem is that this will surprise I think pretty much anyone who hasn't been bitten yet. When you double click on a csv file, it just opens it. Then you have some weird behavior of things not matching when they should.
- smhenderson 8y agoYeah, it's a pain. Zip Codes that begin with zero, FedEx tracking numbers, as you mentioned any long numbers. All of these gotchas have a work-around but they can be very tedious to look for and address. The worst part is that when you think you've fixed whatever needed fixing, save the CSV file and then remember something you missed and reopen it all of your first round changes are lost. I've gotten in the habit of saving a copy to Excel first, then to CSV again. If I realize I still have a bug in my CSV copy I reopen the Excel version so any earlier formatting edits I've made are still intact.
- reitanqild 8y agoI've yet to find a reliable way to prevent Excel from destroying dates (or seeing dates in other data) though. In this regard I prefer LibreOffice. (Or SQLite, if I'm the only person working on it.) I might have missed something obvious though so feel free to point it out. Google failed me though.
- phonon 8y agoUse From Text or Get & Transform...you can tell Excel how to Type each column. https://professor-excel.com/import-csv-text-files-excel/ https://professor-excel.com/import-csv-text-files-excel/
- cascom 8y ago>"Spreadsheets are often used as a multipurpose tool for data entry, storage, analysis, and visualization. Most spreadsheet programs allow users to perform all of these tasks, however we believe that spreadsheets are best suited to data entry and storage, and that analysis and visualization should happen separately." in every organization I've worked in, its usually the exact opposite - spreadsheets are used to do analysis and create reports while the source data is coming from other systems (SAP, Salesforce, quickbooks, etc)
- zokier 8y agoWhile I don't necessarily agree with the author there, I don't think there is contradiction between their and your statements. Tools are not necessarily commonly used for what they are best suited for. Also the context is bit different; research vs businesses
- sonofgod 8y agoSometimes the spreadsheets are the output of one system, but the input to the next: the spreadsheets of the Office of National Statistics in the UK are the final, and official output of a long chain of number wrangling, but when it comes to re-consuming them to power their new data-driven website it's a real challenge -- I wound up working on a project to allow programmatic consumption of these complex spreadsheets. (https://github.com/sensiblecodeio/databaker https://github.com/sensiblecodeio/databaker for the top-level project; https://github.com/sensiblecodeio/xypath https://github.com/sensiblecodeio/xypath for the library I created). Get in touch if you're interested -- dave.mckee@gmail.com
- arxpoetica 8y agoTwo questions: 1. What do people use (currently) for data standardization? 2. What do you do if it's not in tabular format, but is something like JSON/noSQL?
- meej 8y agoI use RDF and standard open schemas like schema.org or VIVO-ISF, with local schema extensions as needed. The datasets I need to standardize tend to be things like scholarly research networking data or data about healthcare clinics and providers, which tend to be highly connected with some sparsely populated fields, both of which lend themselves well to a graph-based model. Right now I use the Karma Data Integration tool (http://usc-isi-i2.github.io/karma/ http://usc-isi-i2.github.io/karma/) to transform my source data to RDF triples. It can handle data in a variety of shapes, not just tabular, though it does struggle with say, highly-nested XML data. I want to try LinkedPipes ETL (https://etl.linkedpipes.com/ https://etl.linkedpipes.com/) on my next project, whatever it might be.
- alexyaseen 8y agoAt risk of shamelessly promoting, we built Parabola (https://parabola.io https://parabola.io) with many of these same best practices in mind. HN discussion: https://news.ycombinator.com/item?id=17147272 https://news.ycombinator.com/item?id=17147272
- arxpoetica 8y agoAh, yes, I remember Parabola.
- stult 8y agoI used to give a "data science" (read: data munging) training at a large accounting firm, and this article seems to track my recommendations almost exactly. The crazy things people do with spreadsheets never ceases to amaze me.
- bagrow 8y agoThere's a interesting recent paper, also cited by these folks, about errors in gene expression data. Spreadsheets (or at least Excel) are a major culprit, interpreting gene SEP15 as September 15, for example. This is dangerous when it happens in only part of the data, like some columns. Gene name errors are widespread in the scientific literature Genome Biology 2016 17:177 https://doi.org/10.1186/s13059-016-1044-7 https://doi.org/10.1186/s13059-016-1044-7 (Open Access)
- anonymouslee 8y agoHadley Wickham's tidy data is also an excellent read: http://vita.had.co.nz/papers/tidy-data.html http://vita.had.co.nz/papers/tidy-data.html I work closely with social scientists and it's common in those disciplines to embed narrative and presentation data in spreadsheets.. articles like this and the Good Enough Practices in Scientific Computing (http://journals.plos.org/ploscompbiol/article?id=10.1371/journal.pcbi.1005510 http://journals.plos.org/ploscompbiol/article?id=10.1371/jou...) are nice summaries of good practices.
- floki999 8y agoCan’t believe this was accepted as a paper...