11 Pandas DataFrame

(CSE331) Python for Data Science

Author
Affiliation

Md Rasel Biswas

IASDS, University of Dhaka

A pandas DataFrame stores data in a table with labelled rows and columns. Different columns can hold different types, such as numbers and text.

Many operations will feel familiar from R’s tibble and dplyr, although pandas has its own syntax and indexing rules. We will practise with the Palmer Penguins data.

import pandas as pd
import numpy as np

1 Creating a DataFrame

A dictionary of equal-length lists can supply the columns:

employee_dict = {
    "eid": [214, 174, 126, 227, 151, 175, 193, 146],
    "name": ["Drew", "Faye", "Beth", "Chad", "Hana", "Gary", "Alex", "Emma"],
    "age": [25, 19, 42, 31, 25, 28, 31, 25],
    "rate": [11.50, 12.30, 14.60, 12.50, None, 15.60, 13.50, 14.30],
    "hours": [38, 32, 40, 29, 40, 36, 24, 20],
}
employee_df = pd.DataFrame(employee_dict)
employee_df.head()
eid name age rate hours
0 214 Drew 25 11.5 38
1 174 Faye 19 12.3 32
2 126 Beth 42 14.6 40
3 227 Chad 31 12.5 29
4 151 Hana 25 NaN 40

The default row labels are 0, 1, 2, and so on. The unrecorded rate is represented as a missing value.

2 Importing Data

2.2 Other formats: reference

# Excel: openpyxl is commonly used for .xlsx; xlrd for older .xls files.
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")
dfs = pd.read_excel("data.xlsx", sheet_name=None)  # dictionary of all sheets

# Stata
df = pd.read_stata("data.dta")

# SPSS and SAS: requires pyreadstat
import pyreadstat
df, metadata = pyreadstat.read_sav("data.sav")
df, metadata = pyreadstat.read_sas7bdat("data.sas7bdat")

# R files: requires pyreadr; result is a mapping of object names to data
import pyreadr
result = pyreadr.read_r("file.RData")

These optional readers are needed only for the corresponding file formats.

3 Penguins Data

Use the course CSV. If it is unavailable, download the Palmer Penguins source CSV and save it as data/penguins.csv relative to the working directory.

The examples use the source column names, with lowercase sex labels male and female. Each row represents one penguin.

penguins = pd.read_csv("data/penguins.csv")
penguins.head()
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 male 2007
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 female 2007
3 Adelie Torgersen NaN NaN NaN NaN NaN 2007
4 Adelie Torgersen 36.7 19.3 193.0 3450.0 female 2007

Inspect column types and nonmissing counts:

penguins.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 344 entries, 0 to 343
Data columns (total 8 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   species            344 non-null    object 
 1   island             344 non-null    object 
 2   bill_length_mm     342 non-null    float64
 3   bill_depth_mm      342 non-null    float64
 4   flipper_length_mm  342 non-null    float64
 5   body_mass_g        342 non-null    float64
 6   sex                333 non-null    object 
 7   year               344 non-null    int64  
dtypes: float64(4), int64(1), object(3)
memory usage: 21.6+ KB

Summarize the numeric columns:

penguins.describe()
bill_length_mm bill_depth_mm flipper_length_mm body_mass_g year
count 342.000000 342.000000 342.000000 342.000000 344.000000
mean 43.921930 17.151170 200.915205 4201.754386 2008.029070
std 5.459584 1.974793 14.061714 801.954536 0.818356
min 32.100000 13.100000 172.000000 2700.000000 2007.000000
25% 39.225000 15.600000 190.000000 3550.000000 2007.000000
50% 44.450000 17.300000 197.000000 4050.000000 2008.000000
75% 48.500000 18.700000 213.000000 4750.000000 2009.000000
max 59.600000 21.500000 231.000000 6300.000000 2009.000000

In describe(), count excludes missing values and std is the sample standard deviation. The source CSV also includes a year column; a numeric summary is not necessarily meaningful for every numeric variable.

4 Accessing Rows and Columns

Use .iloc[] for integer positions and .loc[] for labels:

print(penguins.iloc[2, 3])        # third row, fourth column
print(penguins.loc[4, "island"]) # row labelled 4, column named island
18.0
Torgersen

Here the default row labels happen to match their positions. After filtering or sorting, that may no longer be true.

df.iloc[row_positions, column_positions]
df.loc[row_labels_or_condition, column_labels]

Omitting the column selector means all columns. For example, df.loc[1] selects the row labelled 1.

4.1 Selecting columns

One pair of brackets selects a Series, a labelled one-dimensional object:

species = penguins["species"]
species.head()
0    Adelie
1    Adelie
2    Adelie
3    Adelie
4    Adelie
Name: species, dtype: object

A list of column names selects a DataFrame, even for one column:

penguins[["species"]].head()
species
0 Adelie
1 Adelie
2 Adelie
3 Adelie
4 Adelie 
penguins[["species", "island", "sex", "bill_length_mm", "body_mass_g"]].head()
species island sex bill_length_mm body_mass_g
0 Adelie Torgersen male 39.1 3750.0
1 Adelie Torgersen female 39.5 3800.0
2 Adelie Torgersen female 40.3 3250.0
3 Adelie Torgersen NaN NaN NaN
4 Adelie Torgersen female 36.7 3450.0

The same selection can be written using .loc:

penguins.loc[:, ["species", "sex"]].head()
species sex
0 Adelie male
1 Adelie female
2 Adelie female
3 Adelie NaN
4 Adelie female

4.2 Label slices versus position slices

Label slices include both endpoints when present. Position slices exclude the stop position.

penguins.loc[1:3, "bill_length_mm":"body_mass_g"]
bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
1 39.5 17.4 186.0 3800.0
2 40.3 18.0 195.0 3250.0
3 NaN NaN NaN NaN

This selects labels 1, 2, and 3, and the columns from bill_length_mm through body_mass_g in their existing order.

penguins.iloc[1:3, 2:6]
bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
1 39.5 17.4 186.0 3800.0
2 40.3 18.0 195.0 3250.0

This selects row positions 1 and 2, and column positions 2 through 5.

5 Filtering Rows

A Boolean condition selects rows where it is true:

males = penguins[penguins["sex"] == "male"]
males.shape
(168, 8)

Combine conditions using & or |, with parentheses around each comparison:

# Male penguins with body mass above 3500 g
selected = (penguins["sex"] == "male") & (penguins["body_mass_g"] > 3500)
penguins.loc[selected, ["species", "sex", "body_mass_g"]].head()
species sex body_mass_g
0 Adelie male 3750.0
5 Adelie male 3650.0
7 Adelie male 4675.0
13 Adelie male 3800.0
14 Adelie male 4400.0

An alternative is .query():

penguins.query("sex == 'female' and body_mass_g > 3500").head()
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
6 Adelie Torgersen 38.9 17.8 181.0 3625.0 female 2007
15 Adelie Torgersen 36.6 17.8 185.0 3700.0 female 2007
22 Adelie Biscoe 35.9 19.2 189.0 3800.0 female 2007
25 Adelie Biscoe 35.3 18.9 187.0 3800.0 female 2007

The word and is allowed inside a query string. For ordinary Series comparisons outside that string, use &.

6 Arranging and Slicing Rows

6.1 Sorting

penguins.sort_values(by="body_mass_g", ascending=False).head()
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
169 Gentoo Biscoe 49.2 15.2 221.0 6300.0 male 2007
185 Gentoo Biscoe 59.6 17.0 230.0 6050.0 male 2007
269 Gentoo Biscoe 48.8 16.2 222.0 6000.0 male 2009
229 Gentoo Biscoe 51.1 16.3 220.0 6000.0 male 2008
231 Gentoo Biscoe 45.2 16.4 223.0 5950.0 male 2008 
penguins.sort_values(
    by=["species", "bill_length_mm"],
    ascending=[True, False],
).head()
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
19 Adelie Torgersen 46.0 21.5 194.0 4200.0 male 2007
73 Adelie Torgersen 45.8 18.9 197.0 4150.0 male 2008
111 Adelie Biscoe 45.6 20.3 191.0 4600.0 male 2009
43 Adelie Dream 44.1 19.7 196.0 4400.0 male 2007
129 Adelie Torgersen 44.1 18.0 210.0 4000.0 male 2009

The second example sorts species alphabetically, then bill length from largest to smallest within each species. These calls return sorted results; they do not change penguins unless the result is assigned. Missing values are placed last by default.

6.2 Selecting positions and sampling rows

penguins.iloc[:6]  # first six rows
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 male 2007
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 female 2007
3 Adelie Torgersen NaN NaN NaN NaN NaN 2007
4 Adelie Torgersen 36.7 19.3 193.0 3450.0 female 2007
5 Adelie Torgersen 39.3 20.6 190.0 3650.0 male 2007 
penguins.iloc[9:15]  # 10th through 15th rows, counting from 1
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
9 Adelie Torgersen 42.0 20.2 190.0 4250.0 NaN 2007
10 Adelie Torgersen 37.8 17.1 186.0 3300.0 NaN 2007
11 Adelie Torgersen 37.8 17.3 180.0 3700.0 NaN 2007
12 Adelie Torgersen 41.1 17.6 182.0 3200.0 female 2007
13 Adelie Torgersen 38.6 21.2 191.0 3800.0 male 2007
14 Adelie Torgersen 34.6 21.1 198.0 4400.0 male 2007

R uses one-based positions; Python uses zero-based positions. The stop value in .iloc slicing is excluded.

penguins.sample(n=5, random_state=42)
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
194 Gentoo Biscoe 45.3 13.7 210.0 4300.0 female 2008
157 Gentoo Biscoe 46.5 13.5 210.0 4550.0 female 2007
225 Gentoo Biscoe 46.5 14.8 217.0 5200.0 female 2008
208 Gentoo Biscoe 43.8 13.9 208.0 4300.0 female 2008
318 Chinstrap Dream 50.9 19.1 196.0 3550.0 male 2008

random_state makes this sample reproducible for the same data and setup. Row sampling is without replacement by default.

7 Pulling a Column

masses = penguins["body_mass_g"]             # Series
species_list = penguins["species"].tolist()  # Python list
print(type(masses), type(species_list))
print(species_list[:6])
<class 'pandas.core.series.Series'> <class 'list'>
['Adelie', 'Adelie', 'Adelie', 'Adelie', 'Adelie', 'Adelie']

8 Creating and Modifying Columns

Use a working copy so that the imported data remain available:

peng = penguins.copy()
peng = peng.assign(bill_ratio=peng["bill_length_mm"] / peng["bill_depth_mm"])
peng[["bill_length_mm", "bill_depth_mm", "bill_ratio"]].head()
bill_length_mm bill_depth_mm bill_ratio
0 39.1 18.7 2.090909
1 39.5 17.4 2.270115
2 40.3 18.0 2.238889
3 NaN NaN NaN
4 36.7 19.3 1.901554

Direct assignment is another way to create or replace a column:

peng["bill_ratio"] = peng["bill_length_mm"] / peng["bill_depth_mm"]

8.1 Recoding with np.select()

np.select() chooses the result for the first true condition, similar to case_when() in dplyr.

conditions = [
    peng["body_mass_g"].isna(),
    peng["body_mass_g"] < 3500,
    peng["body_mass_g"] < 4500,
    peng["body_mass_g"] >= 4500,
]
choices = ["missing", "light", "medium", "heavy"]
peng["mass_category"] = np.select(conditions, choices, default="unknown")
peng[["body_mass_g", "mass_category"]].head(10)
body_mass_g mass_category
0 3750.0 medium
1 3800.0 medium
2 3250.0 light
3 NaN missing
4 3450.0 light
5 3650.0 medium
6 3625.0 medium
7 4675.0 heavy
8 3475.0 light
9 4250.0 medium

Values below 3500 are classified before the next condition is considered. Thus medium covers \(3500 \leq x < 4500\). Use .isna() to identify missing values rather than an equality comparison.

8.2 Binning with pd.cut()

For numeric intervals, pd.cut() is a concise alternative:

peng["mass_bin"] = pd.cut(
    peng["body_mass_g"],
    bins=[0, 3500, 4500, np.inf],
    labels=["light", "medium", "heavy"],
    right=False,
)
peng[["body_mass_g", "mass_bin"]].head(10)
body_mass_g mass_bin
0 3750.0 medium
1 3800.0 medium
2 3250.0 light
3 NaN NaN
4 3450.0 light
5 3650.0 medium
6 3625.0 medium
7 4675.0 heavy
8 3475.0 light
9 4250.0 medium

right=False creates intervals [0, 3500), [3500, 4500), and [4500, infinity). For the positive masses in this dataset, the boundaries match the np.select() example. Unlike its explicit "missing" label, pd.cut() leaves missing input as missing. Values outside the supplied bins also become missing.

9 Renaming Columns

peng = peng.rename(columns={
    "bill_length_mm": "bill_length",
    "bill_depth_mm": "bill_depth",
    "body_mass_g": "mass_g",
})
peng.columns
Index(['species', 'island', 'bill_length', 'bill_depth', 'flipper_length_mm',
       'mass_g', 'sex', 'year', 'bill_ratio', 'mass_category', 'mass_bin'],
      dtype='object')

From here onward, examples using peng use the shorter names. The original penguins still uses the imported names.

10 Counting Values

penguins["species"].value_counts()
species
Adelie       152
Gentoo       124
Chinstrap     68
Name: count, dtype: int64

Count combinations of categories and return a DataFrame:

penguins.value_counts(["species", "sex"]).reset_index(name="n")
species sex n
0 Adelie female 73
1 Adelie male 73
2 Gentoo male 61
3 Gentoo female 58
4 Chinstrap male 34
5 Chinstrap female 34

Missing categories are omitted by default. Use dropna=False in value_counts() when they should be included.

11 Grouping and Aggregation

groupby().agg() combines the roles of group_by() and summarise():

peng.groupby("species", as_index=False).agg(
    n=("mass_g", "size"),
    n_mass=("mass_g", "count"),
    mean_bill_length=("bill_length", "mean"),
    mean_mass=("mass_g", "mean"),
)
species n n_mass mean_bill_length mean_mass
0 Adelie 152 151 38.791391 3700.662252
1 Chinstrap 68 68 48.833824 3733.088235
2 Gentoo 124 123 47.504878 5076.016260

Each output name is paired with (column, summary_function). as_index=False keeps the grouping label as a regular column.

  • size counts all rows in the group; count counts nonmissing entries in the selected column.
  • mean, std, and median exclude missing values. std uses denominator \(n-1\) by default.
  • Missing grouping labels are excluded by default; groupby(..., dropna=False) includes them.
peng.groupby(["species", "island"], as_index=False).agg(
    n=("mass_g", "size"),
    mean_mass=("mass_g", "mean"),
    sd_mass=("mass_g", "std"),
    median_bill=("bill_length", "median"),
).sort_values(by="n", ascending=False)
species island n mean_mass sd_mass median_bill
4 Gentoo Biscoe 124 5076.016260 504.116237 47.30
3 Chinstrap Dream 68 3733.088235 384.335081 49.55
1 Adelie Dream 56 3688.392857 455.146437 38.55
2 Adelie Torgersen 52 3706.372549 445.107940 38.90
0 Adelie Biscoe 44 3709.659091 487.733722 38.70

11.1 Keeping whole groups

Keep species with at least 100 recorded masses:

grouped = peng.groupby("species").filter(
    lambda group: group["mass_g"].notna().sum() >= 100
)
grouped["species"].unique()
array(['Adelie', 'Gentoo'], dtype=object)

Here .filter() keeps every row of a qualifying group. It does not select rows individually. The function receives one group at a time and returns a Boolean.

12 Row and Column Summaries

Select numeric columns before calculating their means:

peng.select_dtypes("number").mean()
bill_length            43.921930
bill_depth             17.151170
flipper_length_mm     200.915205
mass_g               4201.754386
year                 2008.029070
bill_ratio              2.605649
dtype: float64

A small table makes the axis argument easy to see:

M = pd.DataFrame(np.arange(12).reshape(3, 4))
M
0 1 2 3
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11 
M.sum(axis=0)  # one sum per column
0    12
1    15
2    18
3    21
dtype: int64
M.sum(axis=1)  # one sum per row
0     6
1    22
2    38
dtype: int64

13 Method Chaining

A chain combines several operations into one readable sequence. Parentheses allow the expression to span multiple lines.

summary = (
    peng
    .dropna(subset=["mass_g", "bill_length", "bill_depth"])
    .assign(bill_ratio=lambda df: df["bill_length"] / df["bill_depth"])
    .groupby("species", as_index=False)
    .agg(
        mean_mass=("mass_g", "mean"),
        mean_bill_ratio=("bill_ratio", "mean"),
    )
)
summary
species mean_mass mean_bill_ratio
0 Adelie 3700.662252 2.119726
1 Chinstrap 3733.088235 2.653756
2 Gentoo 5076.016260 3.175592

This first removes rows missing any required measurement, then calculates the ratio and group summaries. The lambda in assign() uses the DataFrame at that stage of the chain. This example assumes positive bill depths, as in these data.

14 Combining DataFrames

pd.concat() stacks rows or places columns side by side. It aligns labels on the other axis, which matters when the inputs differ.

14.1 Stacking rows

a = penguins.iloc[:2]
b = penguins.iloc[2:4]
pd.concat([a, b], axis=0, ignore_index=True)
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 male 2007
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 female 2007
3 Adelie Torgersen NaN NaN NaN NaN NaN 2007

axis=0 adds rows and aligns columns by name. ignore_index=True gives the result a fresh row index. Columns absent from one input receive missing values for those rows; dtypes may change to accommodate the combined data.

14.2 Placing columns side by side

x = penguins[["bill_length_mm"]]
y = penguins[["bill_depth_mm"]]
pd.concat([x, y], axis=1).head()
bill_length_mm bill_depth_mm
0 39.1 18.7
1 39.5 17.4
2 40.3 18.0
3 NaN NaN
4 36.7 19.3

axis=1 adds columns and aligns rows by index label, not simply by their current position. Here both inputs come from the same rows, so they align directly.

15 Useful R Equivalents

tidyverse pandas
select() df[["col1", "col2"]]
filter() df[condition] or df.query()
arrange() df.sort_values()
mutate() Column assignment or df.assign()
case_when() np.select()
Numeric interval binning pd.cut()
rename() df.rename(columns={...})
pull() df["col"] or .tolist()
slice() df.iloc[...]
group_by() + summarise() df.groupby().agg()

These are practical analogies; indexing and missing-value behaviour still need attention.

16 Exercises

Start with the following working data. Questions 1–8 are independent: use practice as the starting point each time. Question 9 uses the renamed result from Question 7.

practice = penguins.rename(columns={
    "bill_length_mm": "bill_length",
    "bill_depth_mm": "bill_depth",
    "body_mass_g": "mass_g",
}).copy()
  1. Select species, island, bill_length, bill_depth, and mass_g.
  2. Select Adelie penguins with mass above 3500 g. Show species, island, and mass_g.
  3. Sort by mass_g descending and display the first five rows.
  4. Using .iloc, select 10 rows centred in the dataset. Let the starting position be (len(practice) - 10) // 2.
  5. Create bill_area = bill_length * bill_depth. Treat it as a derived numerical measure rather than a measured anatomical area.
  6. Create size_category: below 3500 is "small"; 3500 to below 4500 is "medium"; at least 4500 is "large"; missing is "missing".
  7. Rename mass_g to body_mass_g and bill_length to bill_len_mm. Save this result as renamed.
  8. Extract species as a Python list.
  9. Using renamed, calculate the mean and sample SD of body_mass_g by species. Report both total rows and the number of recorded masses.

17 Summary

  • Distinguish labels (loc) from positions (iloc), and a Series from a DataFrame.
  • Use consistent column names as a workflow progresses.
  • State bin boundaries and the treatment of missing data explicitly.
  • Distinguish all-row counts from nonmissing counts in summaries.
  • Remember that concatenation aligns labels on the other axis.

18 Further reading