Data frames, import, and tidy medical data

In lesson 0.1 you stored one measurement at a time in a vector: a column of ages, a column of blood-pressure readings. Real clinical data does not arrive one column at a time. It arrives as a table, where each patient has an age, a sex, a diagnosis, and a lab result, all lined up on the same row. The structure that holds that table in R and in Python is the data frame, and almost everything you do for the rest of this course starts by loading data into one.

This lesson builds a small patient table from scratch, looks inside it, pulls out the columns and rows you want, and handles the two things that trip up every beginner: categorical variables and missing values. By the end you can take a sheet of clinic records and get it into a shape your analysis can use.

What a data frame is

A data frame is a rectangular table. Each row is one observation, and each column is one variable. In a clinic dataset a row is usually one patient or one visit, and the columns are the things you recorded: age in years, sex, diagnosis, a systolic blood pressure. Every value within a column shares the same type, so the age column holds numbers and the sex column holds text, yet different columns can hold different types. Unlike a matrix, a data frame can mix numeric and text columns in the same row.

Picture a spreadsheet with rules. The header row names the variables. Every data row has an entry in every column, even when that entry is marked missing. You refer to things by column name rather than by cell coordinate, which is what makes the code below readable.

Building one inline, and reading a CSV

Most of the time you load a data frame from a file. A comma-separated values file (CSV) is the common export format for clinic systems, and you read it with read.csv() in R or pd.read_csv() in Python. You will use those two functions constantly once you are working on your own machine with your own files.

This browser runs the code without a file system, so we build the table inline instead, with data.frame() in R and pandas.DataFrame() in Python. The logic is the same one a CSV reader applies: name each column, give it a vector of values of equal length, and the function lines them up into rows. Reading a CSV just does this for you from disk. The starter block below shows the read.csv() line you would use for real; the solution builds the same table inline so it runs live here.

Worked example 1: a clinic table from Seremban

Here are six patients from a Klinik Kesihatan in Seremban, with age, sex, recorded diagnosis, and a systolic blood pressure (sbp) in mmHg. One reading was not taken, so it is missing. Build the frame, then inspect it four ways.

patients <- data.frame(
  patient_id = 1:6,
  age        = c(58, 64, 45, 71, 39, 67),
  sex        = c("F", "M", "F", "M", "F", "M"),
  diagnosis  = c("Diabetes", "Hypertension", "Diabetes",
                 "Hypertension", "Asthma", "Diabetes"),
  sbp        = c(142, 158, NA, 165, 120, 150),
  stringsAsFactors = FALSE
)

# On your own machine you would read a file instead:
# patients <- read.csv("klinik_seremban.csv")
# We build it inline above so the code runs live in the browser.

head(patients, 3)   # first 3 rows
dim(patients)       # rows, columns -> 6 5
str(patients)       # type of each column
summary(patients)   # per-column profile

import pandas as pd
import numpy as np

patients = pd.DataFrame({
    "patient_id": [1, 2, 3, 4, 5, 6],
    "age":        [58, 64, 45, 71, 39, 67],
    "sex":        ["F", "M", "F", "M", "F", "M"],
    "diagnosis":  ["Diabetes", "Hypertension", "Diabetes",
                   "Hypertension", "Asthma", "Diabetes"],
    "sbp":        [142, 158, np.nan, 165, 120, 150],
})

# On your own machine you would read a file instead:
# patients = pd.read_csv("klinik_seremban.csv")
# We build it inline above so the code runs live in the browser.

print(patients.head(3))   # first 3 rows
print(patients.shape)     # (rows, columns) -> (6, 5)
print(patients.dtypes)    # type of each column
print(patients.describe())  # per-column profile

Read the four checks as a routine you run on every new dataset. head() shows the top rows so you can eyeball the table without printing all of it. dim() reports rows by columns (6 by 5 here), and shape does the same in pandas. str() in R and dtypes in pandas give the type of each column, which is how you catch a number that was read in as text. summary() in R and describe() in pandas give a quick per-column profile: range and mean for the numeric columns, counts for the rest.

Tidy data: one row, one column, one value

How you lay out the table decides how hard the analysis is. The tidy-data rule is three short statements: one row per observation, one column per variable, one value per cell. A tidy clinic table gives each patient their own row, keeps age and sbp in separate columns, and never crams two readings into a single cell.

Untidy data breaks at least one of those rules. A column header like sbp_visit1 sbp_visit2 sbp_visit3 packs the visit number into the column names, when visit is a variable that deserves its own column. A cell holding "142/90" stores two values at once. Tidy data is worth the reshaping effort because every function in R and pandas expects it: filtering, grouping, and plotting all assume one variable per column.

Categorical variables: factors and the category type

Some columns are not numbers and not free text. They take one of a fixed set of labels: sex is F or M, a blood-pressure category is Normal, Elevated, or High. R calls such a column a factor, and pandas calls it the category dtype. Declaring a column categorical tells the software the full set of allowed labels (the levels) and, when you ask for it, the order they run in.

Order matters for any scale that has a direction. A severity scale runs Mild, Moderate, Severe; a kidney stage runs 1 through 5. If you leave such a column as plain text, the software sorts it alphabetically, which scrambles the clinical order. An ordered factor fixes the sequence so that comparisons and sorts respect it. Below, a blood-pressure category is stored as an ordered factor with the levels in clinical order, not alphabetical order.

bpcat <- c("Normal", "High", "Elevated", "High", "Normal", "Elevated")

bp <- factor(bpcat, levels = c(...), ordered = TRUE)

bp <- factor(bpcat,
             levels = c("Normal", "Elevated", "High"),
             ordered = TRUE)
levels(bp)        # "Normal" "Elevated" "High"
table(bp)         # count per level
bp[2] > bp[1]     # High > Normal -> TRUE

import pandas as pd
bpcat = ["Normal", "High", "Elevated", "High", "Normal", "Elevated"]

bp = pd.Categorical(bpcat, categories=[...], ordered=True)

bp = pd.Categorical(bpcat,
                    categories=["Normal", "Elevated", "High"],
                    ordered=True)
print(bp.categories)   # Index(['Normal', 'Elevated', 'High'])
print(bp.max())        # High -> levels ordered, not alphabetical

Missing values: NA in R, NaN in pandas

Clinic data has gaps. A blood pressure was never taken, a form field was left blank, a lab result is still pending. R marks an absent value as NA and pandas marks it as NaN. Both mean the same thing: the value is unknown, which is different from zero and different from an empty string.

A missing value spreads through arithmetic. The mean of a column that contains one unknown is itself unknown, so in R mean(sbp) returns NA until you tell it to drop the gaps with na.rm = TRUE. Pandas takes the opposite default and skips NaN automatically in .mean(), while plain NumPy does not. Knowing which tool skips and which propagates saves you from quietly wrong averages.

sbp <- c(142, 158, NA, 165, 120, 150)

mean(sbp)               # what does this give?
mean(sbp, na.rm = TRUE) # and this?

mean(sbp)               # NA: one value is unknown
mean(sbp, na.rm = TRUE) # 147: gaps dropped first
is.na(sbp)              # which entries are missing
sum(is.na(sbp))         # how many missing -> 1

import numpy as np
import pandas as pd
sbp = pd.Series([142, 158, np.nan, 165, 120, 150])

print(sbp.mean())   # pandas default?
print(sbp.isna().sum())

print(sbp.mean())                 # 147.0: pandas skips NaN
print(sbp.isna().sum())           # how many missing -> 1
print(np.mean([142, 158, np.nan,
               165, 120, 150]))   # nan: NumPy does NOT skip

Worked example 2: select, filter, and summarise

The everyday job with a data frame is to pull out the part you care about and describe it. Two operations cover most of it. Selecting picks columns; filtering picks rows that meet a condition. Suppose you want the older patients at the Seremban clinic, those over 60, and their average systolic pressure. Select the columns you need, filter the rows on age, then summarise the sbp column, remembering the missing value.

patients <- data.frame(
  patient_id = 1:6,
  age        = c(58, 64, 45, 71, 39, 67),
  sex        = c("F", "M", "F", "M", "F", "M"),
  diagnosis  = c("Diabetes", "Hypertension", "Diabetes",
                 "Hypertension", "Asthma", "Diabetes"),
  sbp        = c(142, 158, NA, 165, 120, 150),
  stringsAsFactors = FALSE
)

older <- patients[patients$age > 60, ]   # rows where age > 60
mean(older$sbp, ...)                     # remember the NA

patients[, c("age", "sbp")]   # select two columns
older <- patients[patients$age > 60, ]   # filter rows
older
mean(older$sbp, na.rm = TRUE)            # 157.67

import pandas as pd
import numpy as np

patients = pd.DataFrame({
    "patient_id": [1, 2, 3, 4, 5, 6],
    "age":        [58, 64, 45, 71, 39, 67],
    "sex":        ["F", "M", "F", "M", "F", "M"],
    "diagnosis":  ["Diabetes", "Hypertension", "Diabetes",
                   "Hypertension", "Asthma", "Diabetes"],
    "sbp":        [142, 158, np.nan, 165, 120, 150],
})

older = patients[patients["age"] > 60]   # rows where age > 60
print(older["sbp"].mean())

print(patients[["age", "sbp"]])          # select two columns
older = patients[patients["age"] > 60]   # filter rows
print(older)
print(older["sbp"].mean())               # 157.67

Three patients are over 60: the ones aged 64, 71, and 67. Their systolic readings are 158, 165, and 150, so the mean is 157.67 mmHg. Notice the syntax difference in R. The square brackets take two slots, df[rows, columns], so patients[patients$age > 60, ] keeps the comma with an empty column slot to mean "all columns." Pandas filters with a boolean mask inside a single pair of brackets. Both read the same way once you have seen them a few times.

Common mistakes

• Trusting a number column stored as text. A unit left in a cell, like "150 mmHg", turns the whole sbp column into text. Then mean() fails or gives nonsense. Always check str() or dtypes before you compute, and clean the column to numeric first.
• Forgetting na.rm = TRUE in R. One missing value makes mean(), sd(), and sum() all return NA. The data is fine; you just need to tell R to drop the gaps. Pandas skips NaN by default, so the same code behaves differently across the two languages.
• Dropping the comma in R's row filter. Rows and columns sit in two slots, df[rows, columns]. Writing patients[patients$age > 60] without the comma asks for columns, not rows, and returns the wrong thing. Keep the comma: patients[patients$age > 60, ].
• Leaving an ordered scale as plain text. Severity or stage stored as text sorts alphabetically, so "High" comes before "Normal" and charts read backwards. Declare it an ordered factor (R) or ordered category (pandas) with the levels in clinical order.

Tips

• Run the same four checks on every new dataset: head(), dim()/shape, str()/dtypes, and summary()/describe(). Thirty seconds here catches most import problems.
• Build a tiny data frame inline, like the six-row table above, whenever you want to test a piece of code. It runs instantly and you can see the expected answer by hand.
• Decide what counts as missing before you import, so blanks, "NA", and "9999" all map to a real NA rather than slipping through as text or as a fake value.
• Name factor levels in clinical order the moment you create the column. Fixing the order later, after you have built tables and plots, means redoing all of them.
• Keep the same column names across your whole project. If sbp is sbp in one file and SBP in another, every join and filter has to special-case it.