Tables and summaries

Once you have a clean data frame, the next job is to summarise it. A frequency table tells you how many patients fall in each category. A cross-tabulation shows how two categorical variables relate. A grouped summary reports the centre and spread of a numeric measurement within each group. These three moves cover most of what a descriptive analysis needs, and base R and pandas both do them in one or two lines. This lesson assumes you finished 0.1 and 0.2, so you are comfortable with vectors, data frames, and filtering.

The running dataset is a small diabetes audit across three Klinik Kesihatan sites in Negeri Sembilan. Each row is one patient, with their site, treatment arm (Metformin or a Lifestyle programme), sex, age, and latest HbA1c. We add a binary controlled flag, set to 'yes' when HbA1c is below 7.0%. Twenty patients is small for a real audit, but it keeps every count checkable by hand.

Frequency tables

A frequency table counts how often each category appears. In R, table() takes a vector and returns the count for every distinct value. In pandas, .value_counts() does the same for a column. Reach for this first with any categorical variable: it shows you the categories that actually occur, their balance, and any typo that split one group into two.

Worked example 1: site counts and shares

We want the number of patients at each Klinik Kesihatan site, then the same figures as percentages. Counts answer "how many". Proportions answer "what share". Divide each count by the total to get a proportion between 0 and 1, then multiply by 100 for a percentage. In R, wrap the table in prop.table(). In pandas, pass normalize=True to .value_counts().

site <- c('Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang')
arm <- c(rep('Metformin', 10), rep('Lifestyle', 10))
sex <- c('F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M')
age <- c(54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51)
hba1c <- c(6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0)
controlled <- ifelse(hba1c < 7, 'yes', 'no')
clinic <- data.frame(site, arm, sex, age, hba1c, controlled, stringsAsFactors = FALSE)

counts <- # tabulate the site column

counts <- table(clinic$site)
counts
prop <- prop.table(counts)
round(prop, 3)
round(100 * prop, 1)
addmargins(counts)   # appends the total
#  Rasah Senawang Seremban
#      6        7        7   (Sum = 20)

import pandas as pd
import numpy as np
site = ['Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang']
arm = ['Metformin'] * 10 + ['Lifestyle'] * 10
sex = ['F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M']
age = [54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51]
hba1c = [6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0]
clinic = pd.DataFrame({'site': site, 'arm': arm, 'sex': sex, 'age': age, 'hba1c': hba1c})
clinic['controlled'] = np.where(clinic['hba1c'] < 7, 'yes', 'no')

counts = # tabulate the site column

counts = clinic['site'].value_counts()
print(counts)
prop = clinic['site'].value_counts(normalize=True)
print(prop.round(3))
print((100 * prop).round(1))
# Seremban 7, Senawang 7, Rasah 6  ->  35.0%, 35.0%, 30.0%

Report the count and the percentage together. A percentage alone hides the sample size behind it. "30%" means something different when it rests on 6 patients than when it rests on 600.

Cross-tabulating two variables

A cross-tabulation, also called a contingency table, counts the combinations of two categorical variables. Here we ask how control status breaks down by treatment arm. In R, give table() two arguments. In pandas, use pd.crosstab(). The result is a grid: arms down the rows, control status across the columns, and the count of patients in each cell.

Row versus column proportions

A grid of raw counts is hard to compare when the groups differ in size. Proportions fix that, but you have to choose a direction, and the direction changes the question you answer.

• Row proportions divide each cell by its row total. They answer "within this arm, what share reached control?" Use prop.table(tab, 1) in R, or normalize='index' in pandas. Each row sums to 1.
• Column proportions divide each cell by its column total. They answer "among the controlled patients, what share were on Metformin?" Use prop.table(tab, 2) in R, or normalize='columns' in pandas. Each column sums to 1.

Pick the direction that matches your question. For a treatment comparison you almost always want row proportions, because the row is the group you assigned and the column is the outcome you measured. Reading the wrong margin flips the meaning and quietly misstates the result.

site <- c('Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang')
arm <- c(rep('Metformin', 10), rep('Lifestyle', 10))
sex <- c('F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M')
age <- c(54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51)
hba1c <- c(6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0)
controlled <- ifelse(hba1c < 7, 'yes', 'no')
clinic <- data.frame(site, arm, sex, age, hba1c, controlled, stringsAsFactors = FALSE)

tab <- # cross-tabulate arm and controlled

tab <- table(clinic$arm, clinic$controlled)
tab
round(prop.table(tab, 1), 3)   # row %: within each arm
round(prop.table(tab, 2), 3)   # col %: within each status
addmargins(tab)
# Metformin: 4 no, 6 yes  ->  row 0.40 / 0.60 reached control
# Lifestyle: 7 no, 3 yes  ->  row 0.70 / 0.30 reached control

import pandas as pd
import numpy as np
site = ['Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang']
arm = ['Metformin'] * 10 + ['Lifestyle'] * 10
sex = ['F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M']
age = [54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51]
hba1c = [6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0]
clinic = pd.DataFrame({'site': site, 'arm': arm, 'sex': sex, 'age': age, 'hba1c': hba1c})
clinic['controlled'] = np.where(clinic['hba1c'] < 7, 'yes', 'no')

tab = # cross-tabulate arm and controlled

tab = pd.crosstab(clinic['arm'], clinic['controlled'])
print(tab)
print(pd.crosstab(clinic['arm'], clinic['controlled'], normalize='index').round(3))
print(pd.crosstab(clinic['arm'], clinic['controlled'], normalize='columns').round(3))
print(pd.crosstab(clinic['arm'], clinic['controlled'], margins=True))
# row %: Metformin 0.60 controlled vs Lifestyle 0.30 controlled

Numeric summaries by group

For a numeric measurement like HbA1c or age, the summary is a centre and a spread, usually the mean and the standard deviation, reported separately for each group. In base R, tapply() applies a function to a numeric vector split by a grouping factor, and aggregate() does the same through a formula and can return several statistics at once. In pandas, .groupby() followed by .agg() lists the statistics you want.

site <- c('Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang')
arm <- c(rep('Metformin', 10), rep('Lifestyle', 10))
sex <- c('F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M')
age <- c(54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51)
hba1c <- c(6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0)
controlled <- ifelse(hba1c < 7, 'yes', 'no')
clinic <- data.frame(site, arm, sex, age, hba1c, controlled, stringsAsFactors = FALSE)

tapply(clinic$hba1c, clinic$arm, mean)

tapply(clinic$hba1c, clinic$arm, mean)
aggregate(hba1c ~ arm, data = clinic,
          FUN = function(x) round(c(mean = mean(x), sd = sd(x), n = length(x)), 2))
# Metformin: mean 7.03, sd 0.69, n 10
# Lifestyle: mean 7.47, sd 0.72, n 10

import pandas as pd
import numpy as np
site = ['Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang']
arm = ['Metformin'] * 10 + ['Lifestyle'] * 10
sex = ['F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M']
age = [54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51]
hba1c = [6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0]
clinic = pd.DataFrame({'site': site, 'arm': arm, 'sex': sex, 'age': age, 'hba1c': hba1c})
clinic['controlled'] = np.where(clinic['hba1c'] < 7, 'yes', 'no')

clinic.groupby('arm')['hba1c'].mean()

summary = clinic.groupby('arm')['hba1c'].agg(['mean', 'std', 'count']).round(2)
print(summary)
#               mean   std  count
# arm
# Lifestyle     7.47  0.72     10
# Metformin     7.03  0.69     10

The Metformin arm sits about 0.4 percentage points lower on HbA1c on average, with similar spread in both arms. That is a descriptive observation, not yet a test. Later modules turn this gap into a confidence interval and a p-value; the summary table is where every such analysis starts.

The "Table 1" idea

Most clinical papers open with a "Table 1": the baseline characteristics of each study group, set side by side. It is the two moves above stacked together. Categorical variables (sex, site) get counts and percentages. Numeric variables (age, HbA1c) get a mean and SD, or a median and interquartile range when the data are skewed. You build it one variable at a time with the same table(), aggregate(), and groupby() calls you already have.

Dedicated packages assemble a publication-ready Table 1 in a single call: gtsummary or tableone in R, and tableone in Python. They are convenient, but they only stack the base summaries you just wrote. Everything in this lesson uses base tools, so you understand what each cell means before you let a package format it.

Worked example 2: a two-line Table 1

Suppose a reviewer asks for the sex split of the whole cohort and the baseline age in each arm. That is one frequency table plus one grouped numeric summary, the exact pattern a Table 1 repeats down the page.

site <- c('Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang')
arm <- c(rep('Metformin', 10), rep('Lifestyle', 10))
sex <- c('F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M')
age <- c(54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51)
hba1c <- c(6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0)
controlled <- ifelse(hba1c < 7, 'yes', 'no')
clinic <- data.frame(site, arm, sex, age, hba1c, controlled, stringsAsFactors = FALSE)

table(clinic$sex)
# then summarise age by arm

table(clinic$sex)
round(prop.table(table(clinic$sex)), 3)
aggregate(age ~ arm, data = clinic,
          FUN = function(x) round(c(mean = mean(x), sd = sd(x)), 1))
# sex: 10 F (0.5), 10 M (0.5)
# age: Metformin mean 56.3, Lifestyle mean 54.7

import pandas as pd
import numpy as np
site = ['Seremban','Seremban','Senawang','Senawang','Rasah','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang','Rasah','Seremban','Senawang']
arm = ['Metformin'] * 10 + ['Lifestyle'] * 10
sex = ['F','M','F','M','F','M','M','F','F','M','F','M','F','M','F','M','F','M','F','M']
age = [54,61,47,58,63,50,55,60,49,66,52,59,45,57,62,48,64,53,56,51]
hba1c = [6.5,7.8,6.2,7.1,6.9,8.4,6.8,7.5,6.4,6.7,7.2,8.1,6.6,7.9,7.4,6.3,8.6,7.7,6.9,8.0]
clinic = pd.DataFrame({'site': site, 'arm': arm, 'sex': sex, 'age': age, 'hba1c': hba1c})
clinic['controlled'] = np.where(clinic['hba1c'] < 7, 'yes', 'no')

clinic['sex'].value_counts()
# then summarise age by arm

print(clinic['sex'].value_counts())
print(clinic['sex'].value_counts(normalize=True).round(3))
print(clinic.groupby('arm')['age'].agg(['mean', 'std']).round(1))
# sex: F 10 (0.5), M 10 (0.5)
# age: Metformin 56.3, Lifestyle 54.7

Rounding and presenting numbers

Raw output carries more digits than a report should. A proportion printed as 0.66666667 is noise past the second decimal. Round percentages to one decimal place, means to the precision of the measurement (HbA1c to one decimal, age to whole years), and proportions to two or three decimals. Use round(x, 1) in R and .round(1) in pandas. Round for display only, as the last step. If you round numbers and then keep computing on them, the error compounds.

Common mistakes

• Reading the wrong margin of a cross-tab. Row and column proportions answer different questions and rarely match. Decide the denominator ("out of which group?") before you pick prop.table(tab, 1) or (tab, 2).
• Quoting a percentage without its count. "30%" hides whether it rests on 20 patients or 2,000. Always carry the count alongside, like "6/20 (30%)".
• Treating a numeric code as a category, or the reverse. If site is stored as 1, 2, 3, then mean(site) returns a meaningless 1.95. Tabulate codes; average measurements. Convert codes to labels or factors first.
• Rounding too early. Round only at the display step. Rounding a proportion and then multiplying it through later arithmetic propagates the error.
• Forgetting missing values. By default table() and .value_counts() drop NA / NaN silently, so the percentages add to 100% of the non-missing rows, not of everyone. Use table(x, useNA = 'ifany') or .value_counts(dropna=False) when missingness matters.

Tips

• For a treatment-versus-outcome cross-tab, default to row proportions: the row is the group you assigned, the column is what you observed.
• Add margins while you are checking your work (addmargins() in R, margins=True in pandas). The totals catch a miscount or a dropped row immediately.
• Use mean and SD for roughly symmetric measurements, but switch to median and interquartile range when a variable is skewed, such as length of stay or cost. The summary should fit the shape of the data.
• Keep one labelling scheme for each variable across the whole analysis. If an arm is 'Metformin' in one table and 'metformin' in another, your tables will not line up and counts will split.
• You do not need gtsummary or tableone to produce a correct Table 1. They format faster, but the base calls in this lesson give the same numbers and show you what every cell means.