<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/the-ten-commandments-for-a-well-formatted-database/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rtask.thinkr.fr/blog/the-ten-commandments-for-a-well-formatted-database/"> (en) The R Task Force</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><strong>Our diligent readers already know how important having well formatted data is for efficient statistical analyses. Here we gathered some advice on how to make a well structured database, in order to perform accurate analyses, and avoid driving your fellow analysts crazy.</strong></p>
<p>We are very grateful to <a rel="nofollow" href="https://marionlouveaux.free.fr/" target="_blank">Marion Louveaux, bio-image analyst</a> for this translation of <a rel="nofollow" href="https://thinkr.fr/base-de-donnees-reussie" target="_blank">french version “Une base de données réussie” on our ThinkR website</a>.</p>
<p>An example is worth a thousand words. Let’s imagine you are a medical intern working on an experimental assay (any resemblance to people who actually existed …). We could have given the following title to our article: “How to make a database when you are a medical intern knowing nothing”. However this exercise may have a broader application: indeed our advice applies to anyone.</p>
<p>Let’s say that you already have your medical question and your protocol, and that you “just” have to fill in your database. So, now what? As the proverb says, there are a thousand ways of doing it wrong, and often only one to do it right. Let’s be honest: if your database is not well ordered, then any further analyses will be hindered. Don’t worry, <a rel="nofollow" href="https://www.thinkr.fr/" target="_blank">ThinkR</a> is here to help you create a clear database. Here are ten commandments to avoid classical traps.</p>
<h2>Commandment 1: all your data shall fit into one single dataframe</h2>
<p>Spreading your data over several spreadsheets or several files is <strong>not</strong> an option. All your data should fit in one single dataframe.</p>
<h2>Commandment 2: Thou shalt respect a precise formatting</h2>
<p>For a perfect database, follow this rule: the table must be filled cell by cell, <strong>starting from the upper left corner</strong> and, of course, <strong>without skipping line(s)</strong>.</p>
<p><img src="https://i1.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig1.png?w=100%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 3: A line = a statistical individual</h2>
<p>It is not always easy to define what a statistical individual is. In a nutshell, <strong>an answer to a survey corresponds to one individual</strong> in your experimental protocol. If a physical person answers two times, you can enter these on two lines.</p>
<h2>Commandment 4: A column = a variable</h2>
<p><strong>A variable corresponds to a metric from your statistical individual</strong> (e.g. a characteristic that you can measure). For instance, the gender, the age or the colour of the eyes. For these categorical variables, where each answer corresponds to one level or another, but not several levels at the same time, each variable corresponds to one column. Do not create one column for “man” and one column for “woman”, each filled with yes/no, but a single one alternating men and women accross the different lines.</p>
<p><img src="https://i1.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig2.png?w=60%25&ssl=1" data-recalc-dims="1"></p>
<p>When several levels can be taken by the same individual, use several columns. For instance, if your patients have several symptoms (hurting eyes/nose/neck…), you will have to create one column per symptom, and fill them with yes or no.</p>
<p><img src="https://i2.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig3.png?w=100%25&ssl=1" data-recalc-dims="1"></p>
<p><img src="https://i0.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig4.png?w=100%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 5: Thou shalt not encode thy qualitative variables</h2>
<p>Avoid as much as possible encoding qualitative variables: a 1/2 coding for the gender is useless, prefer the use of “woman” and “man” for a better readability. If you really can’t prevent yourself from using a code, then stick to 1/0 to mean presence/absence or yes/no. Not more !</p>
<p><img src="https://i2.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig5.png?w=60%25&ssl=1" data-recalc-dims="1"></p>
<p>For Excel lovers: don’t code your variables using colors or bold/italic/underlined. No “control in blue, test case in red”: nothing worse than such a format to loose information when exporting those data.</p>
<p><img src="https://i2.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig6.png?w=60%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 6: Thy database shall only contain data</h2>
<p>It is not unusual to see databases with aestethic formatting, containing titles, blank spaces, annotations, explanations and much more. Nothing is worse than this type of format for causing difficulties when importing the data into R. Repeat after me: <strong>the database must contain ONLY data</strong>.</p>
<p><img src="https://i0.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig7.png?w=60%25&ssl=1" data-recalc-dims="1"></p>
<p>If you really need to make comments, you can always add a special column for them.</p>
<p><img src="https://i0.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig8.png?w=75%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 7: Homogeneous thou shalt be</h2>
<p>For reading, as well as for the analysis, a homogeneous notation is essential. For instance, don’t mix “boy”, “man” and “masculine”. Choose one and stick to it all across your database… including the exact spelling: R is case and blank sensitive. “Man 1” will not be the same as “man 1”, nor as “man1” or “man_1”. For column names, forget about “m1 glycemia”, “glucose rate month3” and “glycemia month6”. Rather use “glycemia_month_1”, “glycemia_month_3” and “glycemia_month_6”! When data are missing, leave a blank space.</p>
<p><img src="https://i1.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig9.png?w=65%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 8: Thy numerical variables with respect thou shalt treat</h2>
<p>You have numerical variables in your database. Fair enough, but please keep them numerical! For instance, the patient’s age must be a number, not “25 yrs” or “25 years old”, just “25”.</p>
<p><img src="https://i2.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig10.png?w=75%25&ssl=1" data-recalc-dims="1"></p>
<p>For the dates, choose the universal notation YYYY-MM-DD, natively recognized by R. And don’t forget to respect Commandment 7 by standardizing the format.</p>
<p><img src="https://i2.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig11.png?w=75%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 9: Anonymous thy database thou shalt keep</h2>
<p>Don’t forget to make your database anonymous (especially if you are medical intern). When communicating, sharing or publishing your results, including the identities of people is not an option. Give them numbers, and keep the correspondance in another database with the names, medical files, … and all other information that is not relevant for statistical analysis.</p>
<p><img src="https://i2.wp.com/rtask.thinkr.fr/wp-content/uploads/dbgood_fig12.png?w=85%25&ssl=1" data-recalc-dims="1"></p>
<h2>Commandment 10: Human readable thy database shall be</h2>
<p>Throughout the creation of your database never forget that <strong>your data must be perfectly readable by the computer… but also by a human reader!</strong> You database must be understandable and readable, either by you or a colleague. Give meaningful names to your variables, so that you understand yourself in 6 months when reopening the database. And avoid falling into the trap of simplicity: rather use “glycemia_month1”, “glycemia_month2” and “glycemia_month3” than G1, G2 and G3.</p>
<p><img src="https://i0.wp.com/thinkr.fr/wp-content/uploads/dbgood_fig13.png?w=100%25&ssl=1" data-recalc-dims="1"></p>
<h2>Acknowledgements</h2>
<p>I would like to thank <a rel="nofollow" href="https://www.cos.uni-heidelberg.de/index.php/v.jones?l=_e" target="_blank">Victor Jones</a> for reviewing and correcting this translation.<br>
Translated by <a rel="nofollow" href="https://marionlouveaux.free.fr/" target="_blank">Marion Louveaux, bio-image analyst</a></p>
<p>The post <a rel="nofollow" href="https://rtask.thinkr.fr/blog/the-ten-commandments-for-a-well-formatted-database/" target="_blank">The Ten Commandments for a well-formatted database</a> appeared first on <a rel="nofollow" href="https://rtask.thinkr.fr/" target="_blank">(en) The R Task Force</a>.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://rtask.thinkr.fr/blog/the-ten-commandments-for-a-well-formatted-database/"> (en) The R Task Force</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/zsN8CYV3hIs" height="1"></a></div>