Chapter 3: Carry out science laboratory research data analysis

3.1 Data organization

Data organisation is the work that turns the raw records of a study (notebook pages, instrument printouts, questionnaire sheets, interview recordings) into one structured dataset that can be analysed without error. It has three stages taken in order: formatting, cleaning and coding. Formatting gives every value a fixed place and type, cleaning finds and corrects errors without changing real results, and coding converts categories, responses and sample names into short symbols that software can count and protect.

Two rules apply to all three stages. First, the raw data are never overwritten: one untouched copy is kept as the record of what was measured, and every change is made on a working copy. Second, every change is written down, so that another researcher can follow the path from the raw record to the number used in the analysis. A technician who cannot show that path cannot defend a result when a supervisor, assessor or reviewer asks for it.

3.1.1 Data formatting

Data formatting is the arrangement of values in a table so that each row is one observation, each column is one variable, and each cell holds one value of a fixed type. A water-testing laboratory that records pH, turbidity, nitrate and coliform counts for twelve boreholes in a wet and a dry season produces 24 observations. If these are typed as a tidy table, a statistical package can read them directly. If they are typed as a report with merged cells and notes inside the numbers, hours go into repairing the sheet before any analysis can start.

Rules for a tidy data table

• One row, one observation: Each water sample, specimen or measurement occupies its own row. Two readings of the same sample taken on different days are two rows, not two numbers in one cell.
• One column, one variable: A column holds only one kind of information, such as nitrate concentration. Do not put nitrate and its unit, or two variables, in the same column.
• One header row: Use short, unique names with no spaces and put the unit in the name (Nitrate_mg_L, Turbidity_NTU). Units then do not appear inside the cells, which keeps every number a number.
• One value per cell: Never merge cells, never use colour or bold as the only carrier of information, and never place comments in a numeric cell. A comment goes in its own Notes column.
• Consistent type and precision: A numeric column contains only numbers, recorded to the precision of the method (a pH meter reading to 0.01 gives two decimal places throughout). A date column contains only dates.
• Dates in a fixed order: Write dates as year-month-day (2026-04-14). This order sorts correctly and cannot be confused between day-first and month-first readings.
• No blank rows or columns: A blank row can end a table for the software. Missing values are marked consistently, as described under data cleaning.

Example of a tidy table for the borehole survey:

Variable types and scales of measurement

The scale on which a variable is measured decides how it is formatted and which analysis is allowed later. A column of borehole names is not averaged, and a column of nitrate values is not treated as a list of labels.

Wide and long layouts

In a wide layout each borehole has one row and each season has its own columns (pH_wet, pH_dry). In a long layout each borehole appears twice and a Season column says which reading is which. Most statistical software expects the long layout, because a new season or a new variable adds rows or columns without changing the structure. The wide layout is convenient for a paired comparison in a spreadsheet. Keep the long table as the master and build a wide table from it only when a procedure asks for it.

Spreadsheet tools that enforce the format

A spreadsheet can prevent errors at the moment of entry. The tools below are in current versions of Microsoft Excel and in most spreadsheet programs under similar names.

Examples of validation rules for the borehole table:

The data dictionary

A data dictionary is a separate sheet that describes every column so that the table can be understood without its author. It lists the variable name, a plain description, the unit, the type, the allowed values and the method or instrument used.

Naming, storing and protecting files

• File names: Use a fixed pattern with the project, the stage, the date and a version, for example BHsurvey_raw_2026-09-10_v01.xlsx. Do not use spaces or words such as "final" or "new", because they stop meaning anything within a week.
• Raw copy: Save the raw file as read-only in its own folder, and never edit it again.
• Folder structure: Separate folders for raw data, working data, analysis and outputs keep stages from mixing.
• Backups: Keep three copies on two different media with one copy stored away from the laboratory (the 3-2-1 rule). A single flash disk is not a backup.
• Open formats: Also save the final table as a CSV file, which any software can read in future.
• Confidential data: A file with personal details is password protected, and only the people named in the ethical approval open it.

3.1.2 Data cleaning

Data cleaning is the screening of a formatted dataset to find values that are wrong, missing, repeated or inconsistent, and the correction or documented handling of each one. Its aim is a dataset that records what was measured, not one that looks neat. A value is never changed because it spoils a trend. It is changed only when evidence, such as the notebook or the instrument printout, shows that the entry differs from what was measured.

Types of data problems

Steps in cleaning a dataset

1. Copy the raw file and work only on the copy, leaving the raw file untouched.
2. Screen every column: sort it, filter it, and list its minimum, maximum and distinct entries. A column that should be numeric but sorts text to the top contains a typing slip.
3. Diagnose each problem as an error, a true extreme value or a missing value. Look at the source record before deciding.
4. Treat the problem by the rule that fits: correct the entry, convert the unit, remove a confirmed duplicate, or mark the value missing.
5. Write every action in the cleaning log at the time it is taken.
6. Screen again after the changes, because a correction can create a new problem, such as a pasted value that is stored as text.
7. Save the cleaned file under a new version name and lock it before analysis begins.

The cleaning log

The cleaning log is a table that records each change, so that the cleaned file can be traced back to the raw file.

Handling missing values

A value can be missing because the sample was lost, the instrument failed, the result was invalid, the question did not apply, or the result was below the limit the method can detect. These reasons are recorded, because they are different problems. In a spreadsheet a missing value is a blank cell or the letters NA, used the same way everywhere. A number such as 0 or -99 typed into a numeric column is read as data and will distort every mean unless it is filtered out.

Common ways of handling missing values:

• Repeat the measurement: If the sample or the extract still exists, the best treatment is to measure again. This is only possible in the laboratory, which is why gaps are looked for early.
• Analyse the cases that are complete: Rows with a missing value are left out of the calculation that needs it. This is simple and honest, but it reduces the sample size.
• Use the available values for each calculation: Each statistic uses every value that exists for the variables it needs, so different statistics may rest on different numbers of rows. The number used is reported each time.
• Substitute the mean of the other values: The gap is filled with the mean of the remaining values. This is allowed only for a small number of gaps, and it makes the spread of the data look smaller than it is.
• Estimate from neighbours or a model: For a time series, a missing value can be estimated from the values before and after it. More advanced imputation uses a statistical model and is described and justified in the report.

Worked example (soil-testing laboratory): the pH of five plots was measured as 6.2, 6.5, missing, 6.4 and 6.3.

Mean of the four values present = (6.2 + 6.5 + 6.4 + 6.3) ÷ 4 = 25.4 ÷ 4 = 6.35

Standard deviation of the four values (sample, n - 1) = 0.129

If 6.35 is substituted for the gap, the mean stays 6.35 but the standard deviation of the five values falls to 0.112. The spread has shrunk by about 13 per cent because the new value sits exactly at the centre, so any test that uses it will be slightly too confident. The log must state that one value was substituted and why.

Values below the detection limit

A result reported as "less than 0.01 mg/L" is not zero and not missing: the method could not distinguish it from nothing. Enter it as text in a separate column or mark it with a flag column (Below_DL = Yes), and keep the detection limit in the data dictionary. Some laboratories substitute half the detection limit for the calculation, and a few use specialised statistical methods. Whichever is chosen is written in the methods and applied to all values below the limit.

Detecting and treating outliers

An outlier is a value that lies far from the rest of its group. It may be a recording error, a real extreme or a sign of a procedural fault, so it is investigated, not deleted. For a small group of replicates (3 to 10 values) analytical laboratories commonly use the Dixon Q test.

Q = (gap between the suspect value and its nearest neighbour) ÷ (range of all values)

Critical values of Q at 95 per cent confidence for one suspect value:

If the calculated Q is larger than the critical value for n, the suspect value is flagged as a statistical outlier. The test is applied once to a set, assumes data that are roughly normal, and does not prove that the value is wrong. Larger sets use other tests, such as the Grubbs test, or a box plot.

Worked example (food-quality laboratory): five replicate moisture contents of one lot of maize flour, in per cent, are 12.1, 12.3, 12.2, 12.4 and 13.4.

Arrange in order: 12.1, 12.2, 12.3, 12.4, 13.4

Gap = 13.4 - 12.4 = 1.0

Range = 13.4 - 12.1 = 1.3

Q = 1.0 ÷ 1.3 = 0.769

The critical value for n = 5 is 0.710. Because 0.769 is greater than 0.710, the value 13.4 is flagged. The technician then checks the notebook for a cause such as a sample left uncovered before weighing or a wrong crucible number. If a cause is found, the value is excluded or corrected and the reason is logged. If no cause is found, the value stays in the dataset and the report gives the mean both ways.

Mean with all five values = 62.4 ÷ 5 = 12.48 per cent

Mean without 13.4 = 49.0 ÷ 4 = 12.25 per cent

The difference of 0.23 percentage points shows how much one value moves a small group, which is why the decision is documented.

3.1.3 Data coding

Data coding is the assignment of numbers, short labels or theme names to categories, responses, text passages and samples so that they can be counted, compared and, where needed, anonymised. A column that holds "Wet" and "Dry" can be analysed, but a code of 1 and 2, or 0 and 1, lets the software use it in a calculation. Coding rules are written in a codebook before data entry, not invented while typing.

Coding closed-ended and categorical data

• Binary variables: Two categories are coded 0 and 1, for example 0 for "complies" and 1 for "does not comply". State which number is which in the codebook.
• Ordered scales: A five-point agreement scale is coded Strongly disagree = 1, Disagree = 2, Neutral = 3, Agree = 4, Strongly agree = 5, so that a higher number always means stronger agreement.
• Categories without order: Soil type with clay, loam and sand is stored as a label or as arbitrary codes 1, 2 and 3. The numbers are names only, and an average of them has no meaning.
• Dummy variables: Software that needs numbers for a categorical variable uses one column fewer than the number of categories. With sand as the reference, one column is Clay (1 if clay, otherwise 0) and one is Loam (1 if loam, otherwise 0), and a sand sample has 0 in both.
• Reverse coding: A negatively worded item, such as "Waste labelling is a waste of time", is reversed so that all items point the same way. On a 1 to 5 scale the reversed score is 6 minus the original, so an answer of 2 becomes 4.
• Missing and not applicable: Use the blank cell or NA convention from cleaning, not a number inside the scale.

Coding sample identifiers

Sample codes carry information and can hide it. In an industrial quality-control laboratory a code such as L2-1103-B07 can mean production line 2, produced on 11 March, batch 07. When the analyst must not know which treatment or supplier a sample came from, the sample is given a random code and the key linking the code to the source is stored by another person until the analysis is finished. This is called blind coding and it prevents expectation from influencing the reading. A good code is unique, short, free of spaces and free of names of people or places that would reveal a participant.

Coding qualitative data

Qualitative data are words, such as interview transcripts, open answers and observation notes. Coding labels the passages that carry an idea, so that the ideas can be grouped and counted.

1. Read all transcripts through once without coding, to get a sense of the whole.
2. Code line by line, giving each passage that carries an idea a short label in the participant's own words where possible.
3. List the codes and write a definition for each, so that one code means one thing.
4. Group related codes into categories and then into themes, a theme being a pattern that answers part of a research question.
5. Write the codebook with each code, its definition, what it includes and excludes, and a quoted example.
6. Apply the codebook to every transcript, adding a new code only when no existing code fits, and then returning to earlier transcripts to check them against it.
7. Review the themes against the data and the research questions, merging or splitting codes that do not work.
8. Check the reliability of the coding, as described below.

Example (research laboratory, interviews with analysts about why results reach the client late):

The codebook

Checking coding reliability

Two people who code the same sample of excerpts should reach similar results. The simplest measure is percentage agreement, but it counts agreement that happens by chance. Cohen's kappa corrects for chance:

kappa = (Po - Pe) ÷ (1 - Pe)

where Po is the observed proportion of agreement and Pe is the proportion expected by chance from each coder's totals.

Worked example: two coders classify 20 excerpts as Theme A or Theme B. Both choose A for 9 excerpts and both choose B for 7. Coder 1 chooses A and coder 2 chooses B for 2 excerpts, and the reverse for 2 excerpts.

Po = (9 + 7) ÷ 20 = 0.80

Coder 1 chose A 11 times and B 9 times. Coder 2 chose A 11 times and B 9 times.

Pe = (11 ÷ 20) × (11 ÷ 20) + (9 ÷ 20) × (9 ÷ 20) = 0.3025 + 0.2025 = 0.505

kappa = (0.80 - 0.505) ÷ (1 - 0.505) = 0.295 ÷ 0.495 = 0.596

Agreement of 80 per cent looks high, but after correcting for chance the kappa is about 0.60, which many researchers describe as moderate. Researchers often aim for 0.61 or more, the band Landis and Koch called substantial. The coders discuss each disagreement, sharpen the code definitions in the codebook, and code a fresh sample again.

Tools for coding

A spreadsheet with one column for the excerpt and one for the code is enough for a small study. Dedicated qualitative software, such as NVivo or the free Taguette, stores the codes, retrieves every passage under a code and keeps the codebook with the data. Whatever tool is used, the codebook and a copy of the coded data are stored with the dataset.