Please enable JavaScript.
Coggle requires JavaScript to display documents.
Data Analytics Tools for Biotechnology - Coggle Diagram
Data Analytics Tools
for Biotechnology
What Is Data Analytics?
the disciplined process of turning that raw output into a conclusion. The tools in this course (Excel, SQL, Python, R, Tableau/Power BI) are just different instruments
features
1. raw data
—sequencing reads, plate reading, and lab logbooks
2.clean
- fix errors,fill gaps and standarize
3. Analyze and compare groups
, find patterns and run stats
4. Insight
—genx is upregulated in mice
used in biotech
qPCR raw data -Ct values per well, per sample analytics—fold change between treatment groups
-
ELISA plates: raw data Absorbance readings per well analytics ---Concentration curves, outlier detection
-
Sequencing , raw data -Millions of raw reads per sample
analytics -Which genes are differentially expressed
clinical trials -raw data Patient records across visits
Treatment effect, survival trends
Flow cytometry raw data -Cell counts per marker per sample analytics -Population percentages, group comparisons
excel
advantage
Datasets up to a few hundred thousand rows
Quick, visualize data
Cleaning, sorting, and simple summaries
No installation — it's likely already on your machine
limitations
SQL/python-for millions of rows / coloumns
repeating same analysis -python/R
Combining many related tables — needs SQL
Interactive dashboards to share — needs Power BI/Tableau
Import Data Into Excel
1.Get your file ready ex :Locate the sample dataset shared on the class drive: lab_samples_raw.csv
Open Excel → Data tab
Click the Data tab in the ribbon, then “From Text/CSV”.
3.Select your file
Browse to lab_samples_raw.csv and click Import.
4.Check the preview
Excel shows a preview of your columns — confirm they look correct, then click Load.
5.Save your workbook
File → Save As → name it lab_samples_lecture1.xlsx before making any changes.
data that is not organized properly/messy
1.Duplicate rows
The same sample logged twice, e.g. two rows for Sample_014.
2. Missing values
Blank Ct_value or Concentration cells where a reading was skipped.
3.Inconsistent labels
“Male”, “M”, “male ” (with a trailing space) all meaning the same thing.
4. Obvious outliers
A Ct value of 0 or 999 where a real reading should be.
Remove Duplicate Rows
1.Select your data
Click any cell inside your table.
2.Data tab → Remove Duplicates
Found in the “Data Tools” group of the ribbon.
3.Choose the columns to check
Tick Sample_ID — Excel flags a duplicate whenever it repeats.
4.Confirm and review
Excel reports how many duplicate rows were removed.
Handle Missing Values
Step 1
— Find every blank at once
select data range
home -find and select-go to special blanks
Excel highlights every empty cell in your selection at once.
Step 2
—Decide how to fill them
Formula: =IF(B2="","Missing",B2) — flag instead of guess.
Or leave truly missing readings blank and exclude them later — never invent a number.
why not fill with zero -no reading was taken,maximum possible expression,all exp average, chart, and statistical test built on that column
Standardise Text Entries
=trim(A2)
Removes extra spaces before, after, and between words.
=clean(A2)
Strips invisible non-printing characters from pasted data.
=upper(A2)/=Lower(A2)
Forces consistent capitalisation across a column.
=substitute (A2,M,Male)
Replaces one specific text pattern with another.
(Ctrl+H): for a quick, one-time fix across an entire column — e.g. replacing every “N/A” with a truly blank cell.
Sort & Filter for Quick Quality Checks
Sort
Select your data → Data tab → Sort.
Sort Ct_Value smallest to largest.
Impossible values (0, negative, 999) immediately float to the top or bottom — easy to spot.
filter
Select your header row → Data tab → Filter.
Click the dropdown arrow on any column.
Use “Number Filters → Between” to show only values outside a plausible range (e.g. Ct 15–35).
Common Mistakes to Watch For
NO TO
Editing the raw file directly
Filling blanks with 0 “to be safe”
Removing duplicates on the wrong column
Trusting a column by its name alone
SHOULD DO
Always Save As a new copy before cleaning.
Leave genuinely missing data blank; document your reasoning.
Check Sample_ID specifically, not the whole row.
Always sort/filter once to actually look at the values.