Pandas DataFrames: Indexing, Joining, and Missing Data

This lesson finished the introduction to Matplotlib and moved into Pandas. Matplotlib gave me a way to inspect data visually. Pandas gave that data a table structure and a set of tools for selecting, combining, and cleaning it.

NumPy had already made me pay attention to shape and axis. Pandas keeps those dimensions, but adds row labels, column names, and indexes. The shift is from manipulating numeric arrays directly to preparing structured data for analysis.

Why use a DataFrame?

A Pandas DataFrame is a two-dimensional data structure with rows and columns. A database table or spreadsheet is a useful mental model: columns have names, and rows have index labels.

The important advantage is repeatability. Pandas can read and write CSV or Excel files, then express filtering, sorting, missing-value handling, and column creation as code. A spreadsheet can perform many of the same operations, but code is easier to repeat and connect to the rest of a system.

Pandas can also handle datasets that would be awkward in a spreadsheet, although that does not mean it can load any 100 GB file without trouble. The practical limit still depends on available memory, data types, and the operations being performed. For now, I think of Pandas as a flexible, code-based layer for preparing data before analysis.

import pandas as pd

df = pd.read_csv("data.csv")

df.head()
df.info()
df.describe()

Data analysis often requires looking at the same dataset repeatedly under the same conditions. Pandas makes those conditions reproducible.

DataFrames and Series

A Series is a one-dimensional data structure. Selecting one column or one row from a table often produces a Series.

A DataFrame is two-dimensional. In the lesson, I built one from a dictionary of subject scores and assigned student names as the index.

exam_data = {
    "math": [90, 80, 70],
    "english": [98, 89, 95],
    "music": [85, 95, 100],
    "physical_education": [100, 90, 90],
}

students = ["Alex", "Blake", "Casey"]
df = pd.DataFrame(exam_data, index=students)

df

Selecting a single column returns a Series.

math_scores = df["math"]

print(type(df))
print(type(math_scores))

This distinction matters because the result type determines its shape and which operations are available next. A DataFrame is a table; a Series is one labeled dimension taken from that table.

Avoid making `inplace` the default

Some Pandas methods accept an inplace option. Passing inplace=True asks the method to modify the original DataFrame instead of returning the changed result.

df5 = df.copy()

df5.drop(["english", "music"], axis=1, inplace=True)

df5

The code is short, but mutation happens inside the method call. Once originals and copies are mixed together, it becomes harder to track which object changed.

I prefer assigning the returned DataFrame explicitly while learning the API.

df5 = df.copy()

df5 = df5.drop(["english", "music"], axis=1)

df5

Here, axis=1 refers to columns. The labels english and music are column names, so they are removed along that axis. Removing a row uses axis=0.

df_without_blake = df.drop(["Blake"], axis=0)

The option itself is less important than making mutation explicit. If I need to preserve the original, I can copy it first and assign each result to a variable.

Choosing between `loc`, `iloc`, and square brackets

Selecting rows and columns was the most confusing part at first. The syntax changes depending on whether the selection uses labels or integer positions, and whether it targets rows or columns.

loc selects by label. It uses row index labels and column names.

# The complete row labeled Blake
df.loc["Blake"]

# Blake's English score
df.loc["Blake", "english"]

# Multiple rows and columns
df.loc[["Alex", "Casey"], ["math", "music"]]

iloc selects by zero-based integer position.

# The second row
df.iloc[1]

# The second row and second column
df.iloc[1, 1]

# The first two rows and first three columns
df.iloc[0:2, 0:3]

Plain square brackets are mainly used for selecting columns.

df["math"]
df[["math", "english"]]

df["Blake"] does not select the row labeled Blake. It asks for a column named Blake and raises an error when that column does not exist. Label-based row selection requires df.loc["Blake"].

row = df.loc["Blake"]
column = df["english"]

Square-bracket slicing adds another wrinkle because df[1:3] behaves like positional row slicing.

df[1:3]

When both dimensions need to be explicit, loc and iloc make the selection rule easier to read.

List selection with `loc` and filtering with `isin`

Passing a list to loc selects a known set of index labels.

years = ["2013", "2014", "2018"]

selected = df_california.loc[years]

This works when all requested labels exist. If the list includes an index label that is missing from the DataFrame, the selection can raise an error.

isin builds a boolean condition instead. Values that do not exist simply do not match.

years = ["2013", "2014", "2018", "2099"]

selected = df_california[df_california.index.isin(years)]

The same method works on regular columns.

cities = ["Seoul", "Busan"]

selected = df[df["city"].isin(cities)]

I use a list with loc when I know the exact index labels I want. I use isin when the list is a filter condition and some candidates may not appear in the data.

Target both dimensions at once

Pandas allows a value to be reached through multiple chained selections.

df_california["total_rooms"]["2012"]
df_california["total_rooms"].loc["2012"]
df_california.loc["2012"]["total_rooms"]

These expressions may work for reading, but each one creates an intermediate Series and then selects from it again. During assignment, that makes it harder to know whether the original DataFrame or an intermediate copy is being modified.

Specifying the row and column in one operation avoids that ambiguity.

# Label-based selection
df_california.loc["2012", "total_rooms"]

# Position-based selection
df_california.iloc[0, 3]

The label "2012" works with loc only because that dataset uses strings for its index. If the index labels were integers, loc would require the integer label. Access by row position belongs to iloc.

This is the rule I want to keep: when both the row and column are known, select them together. It states the intent directly and avoids chained-indexing problems during updates.

`concat`, `join`, and `merge`

Pandas provides several ways to combine DataFrames, but they align data by different rules.

concat stacks DataFrames vertically or horizontally. It is useful when similarly shaped data arrives in separate chunks.

first = pd.DataFrame({"name": ["A", "B"], "score": [90, 80]})
second = pd.DataFrame({"name": ["C", "D"], "score": [70, 60]})

pd.concat([first, second], axis=0)

With axis=0, rows are stacked. With axis=1, columns are placed side by side.

left = pd.DataFrame({"name": ["A", "B"]})
right = pd.DataFrame({"score": [90, 80]})

pd.concat([left, right], axis=1)

join combines DataFrames by their indexes.

left = pd.DataFrame({"score": [90, 80]}, index=["Alex", "Blake"])
right = pd.DataFrame({"club": ["football", "music"]}, index=["Alex", "Blake"])

left.join(right)

merge combines rows by matching values in one or more columns, much like a SQL join.

students = pd.DataFrame({
    "student_id": [1, 2, 3],
    "name": ["Alex", "Blake", "Casey"],
})

scores = pd.DataFrame({
    "student_id": [1, 2, 4],
    "score": [90, 80, 70],
})

pd.merge(students, scores, on="student_id", how="inner")

The how option determines which keys remain in the result.

pd.merge(students, scores, on="student_id", how="left")
pd.merge(students, scores, on="student_id", how="right")
pd.merge(students, scores, on="student_id", how="outer")
pd.merge(students, scores, on="student_id", how="inner")

Method	Rows retained
left merge	Every row from the left DataFrame
right merge	Every row from the right DataFrame
outer merge	Every key found in either DataFrame
inner merge	Only keys found in both DataFrames

The first distinction to remember is the matching key. join aligns indexes, while merge matches values in the columns named by on.

Finding and handling missing data

Pandas provides several ways to inspect missing values.

df.isnull()

pd.isnull(df)

# Number of missing values in each column
df.isnull().sum()

dropna removes rows containing missing values.

# Remove rows with any missing value
cleaned = df.dropna()

# Remove rows missing a value in a specific column
cleaned = df.dropna(subset=["score"])

fillna replaces missing values with a specified value.

filled = df.fillna(0)

Forward fill and backward fill use a neighboring value.

forward_filled = df.ffill()
backward_filled = df.bfill()

Those methods can make sense for ordered data such as a time series. On unordered data, copying the previous or next value can introduce a false value. The replacement strategy has to follow the meaning of the data rather than the convenience of the method.

Sorting and saving

sort_values sorts a DataFrame by one or more columns.

# Ascending by score
sorted_df = df.sort_values(by="score")

# Descending by score
sorted_df = df.sort_values(by="score", ascending=False)

The result can be written to a CSV file.

df.to_csv("result.csv", index=False)

Passing index=False prevents the DataFrame index from becoming an extra column in the file. A meaningful index may belong in the export, but a default row number usually does not.

What I am keeping from the lesson

This was the first lesson that covered DataFrames, Series, indexing, combining tables, and missing-value handling together. The API contains many method names, but the choices became more manageable once I separated the underlying criteria.

For selection, I first decide whether the reference is a label or a position. For combining data, I decide whether rows should align by index or by column values. For missing data, the first question is what the absence means, because that determines whether removing or replacing it is valid.

Pandas is not just a library for displaying tables. It is the layer where raw data is reshaped into something an analysis can use. This lesson established the basic workflow: load a DataFrame, select the relevant parts, combine related data, and handle missing values deliberately.