Pandas Basics: Series and DataFrames, Selecting, groupby and merge on Real Data

By 0xmote · Jun 30, 2026 · 13 min read · Updated Sep 27, 2026 Intermediate Tutorial

Key takeaways

Pandas basics for Python: Series and DataFrame, reading CSV and Excel, filtering with loc/iloc, groupby, merge and concat, with the pitfalls that silently produce wrong results: object dtypes, index alignment, join types that drop rows, duplicate merge keys, and chained assignment.

Introduction

Pandas is Python’s core library for data analysis and manipulation. What makes it different from working with raw Python lists or dicts is that every operation is vectorized and label-aware: instead of writing a loop to filter rows or a manual join, you express what you want (df[df['age'] >= 30]) and Pandas handles the how using optimized C code under the hood — which is both why it’s fast on real datasets and why idiomatic Pandas code looks so different from idiomatic pure-Python code.

Pandas basics

Installation

pip install pandas

Series and DataFrame

A Series is a single labeled column — think of it as a NumPy array with an index attached, which is what lets Pandas align values by label instead of by raw position when you combine two Series. A DataFrame is a dict of Series that share the same index, which is why selecting one column (df['age']) hands you back a Series, while selecting several (df[['name', 'age']]) hands you back a smaller DataFrame — same underlying data, different container depending on how many columns you ask for.

import pandas as pd
# Series (1D)
s = pd.Series([1, 2, 3, 4, 5])
print(s)
# 0    1
# 1    2
# 2    3
# 3    4
# 4    5
# DataFrame (2D)
df = pd.DataFrame({
    'name': ['Alice', 'Beth', 'Carl'],
    'age': [25, 30, 28],
    'city': ['Seoul', 'Busan', 'Daegu']
})
print(df)
#     name  age   city
# 0  Alice   25  Seoul
# 1   Beth   30  Busan
# 2   Carl   28  Daegu

Reading and writing data

CSV files

encoding='utf-8-sig' on the write side is a small but common gotcha for anyone who touches this code on Windows: plain utf-8 output opens correctly in Python but shows garbled headers in Excel, because Excel expects a byte-order-mark (BOM) to recognize UTF-8 rather than assuming it. usecols and reading only the columns you actually need matters more than it looks — on a CSV with dozens of columns but you only need three, it noticeably cuts both memory use and load time, since Pandas can skip parsing the unused columns entirely rather than loading everything and dropping it after.

# Read CSV
df = pd.read_csv('data.csv')
# Write CSV
df.to_csv('output.csv', index=False, encoding='utf-8-sig')
# Read selected columns only
df = pd.read_csv('data.csv', usecols=['name', 'age'])
# Custom delimiter
df = pd.read_csv('data.tsv', sep='\t')

Excel files

# Read Excel
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# Write Excel
df.to_excel('output.xlsx', index=False)

Excel support is not built into pandas itself. Reading or writing .xlsx needs the openpyxl package, and without it the call fails with ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl. Excel reading is also much slower than CSV, because the file is a zipped XML document that has to be parsed cell by cell; for a large sheet that you read repeatedly, converting it once to CSV or Parquet saves time on every later run.

On the CSV side, the default encoding for read_csv is UTF-8. A CSV exported from Excel on a Korean or Japanese Windows machine is usually in the legacy code page instead, and reading it fails with UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb1 in position 0: invalid start byte. Pass encoding='cp949' (or the right code page) in that case, rather than switching to errors='ignore', which drops the characters.

Exploring data

Basic information

These five calls are worth running, in this order, on literally every new dataset before writing any analysis code — df.info() catches dtype surprises (a numeric column that got read as object because of one stray non-numeric value hiding somewhere in it), and df.describe() catches scale surprises (a column you expected to range 0–100 that actually has a max of 999999, hinting at a sentinel “missing” value someone used instead of a real null). Skipping this step is the single most common reason later groupby/merge code produces subtly wrong results instead of an obvious error — bad dtypes and disguised sentinel values usually don’t crash anything, they just quietly poison the aggregation.

# First 5 rows
print(df.head())
# Last 5 rows
print(df.tail())
# Summary info
print(df.info())
# Descriptive statistics
print(df.describe())
# Shape
print(df.shape)  # (rows, columns)
# Column names
print(df.columns)

Selecting data

Column selection

The parentheses around each condition in a compound boolean filter ((df['age'] >= 25) & (df['city'] == 'Seoul')) aren’t optional style — they’re required, because Python’s operator precedence binds & tighter than >=/==, so without them the expression parses incorrectly and either raises an error or silently filters on the wrong thing. This is also why Pandas boolean filtering uses &/|/~ instead of Python’s and/or/not: the built-in boolean operators expect a single True/False, but a filter condition here produces a whole Series of booleans, one per row, and only the bitwise operators are defined to work element-wise across a Series.

# Single column
ages = df['age']
# Multiple columns
subset = df[['name', 'age']]
# Boolean filtering
adults = df[df['age'] >= 30]
seoul_users = df[df['city'] == 'Seoul']
# Compound conditions
result = df[(df['age'] >= 25) & (df['city'] == 'Seoul')]

Row selection

# By position (iloc)
first_row = df.iloc[0]
first_three = df.iloc[:3]
# By label (loc)
df_indexed = df.set_index('name')
alice = df_indexed.loc['Alice']
# Conditional rows
young = df[df['age'] < 30]

iloc and loc look interchangeable for simple cases but mean genuinely different things: iloc always indexes by integer position (0, 1, 2…) regardless of what the actual row labels are, while loc indexes by the label itself — which matters as soon as a DataFrame’s index isn’t the default 0..n sequence, such as after set_index('name') here, or after filtering rows (filtering keeps the original labels, so after young = df[df['age'] < 30] the first row may have label 2; young.iloc[0] returns it, while young.loc[0] raises KeyError: 0 or returns a different row if label 0 survived the filter). Mixing them up on a filtered or re-indexed DataFrame is a common source of “off by one row” bugs that don’t throw an error, just quietly return the wrong data.

Transforming data

Add and drop columns

# Add columns
df['country'] = 'Korea'
df['birth_year'] = 2026 - df['age']
# Drop one column
df = df.drop('country', axis=1)
# Drop multiple columns
df = df.drop(['col1', 'col2'], axis=1)

Changing values

# Update specific values
df.loc[df['name'] == 'Alice', 'age'] = 26
# Apply a function
df['age_group'] = df['age'].apply(
    lambda x: 'young' if x < 30 else 'middle-aged'
)
# Apply to multiple columns
df[['age', 'birth_year']] = df[['age', 'birth_year']].applymap(int)

.apply() with a Python lambda is convenient but runs row-by-row in the Python interpreter, which loses the C-level vectorization that makes Pandas fast — on a small DataFrame it’s invisible, but on a few million rows it can be dozens of times slower than an equivalent vectorized expression like pd.cut() for binning or np.where() for a conditional assignment. Note also that .applymap() (element-wise over an entire DataFrame) is deprecated in current Pandas in favor of .map(); it’s shown here because a lot of code in production still uses it, but new code should reach for .map() instead.

The first line of “Changing values”, df.loc[df['name'] == 'Alice', 'age'] = 26, is written that way on purpose. The tempting version, df[df['name'] == 'Alice']['age'] = 26, is a chained assignment: the first [...] produces a new object (a filtered copy), and the second assignment modifies that temporary instead of df. Pandas 1.x and 2.x warn with SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame, and the original DataFrame is unchanged. With copy-on-write, which is the default in pandas 3.0, chained assignment never updates the original at all. A single loc[row_selector, column] call is unambiguous in every version. The same warning appears later when you filter into a new variable (young = df[df['age'] < 30]) and then add a column to it; if young is meant to be an independent table, create it with .copy().

Grouping and aggregation

groupby

groupby follows a split-apply-combine pattern: Pandas splits the DataFrame into one chunk per group (one chunk per distinct city value here), applies the aggregation function independently to each chunk, then combines the per-group results back into a single Series or DataFrame. Understanding this model explains why .agg({'age': ['mean', 'min', 'max']}) produces a multi-level column index in the result — each combination of column and aggregation function becomes its own column, and the two-level naming is how Pandas keeps ('age', 'mean') distinguishable from ('age', 'max').

# Average age by city
city_avg = df.groupby('city')['age'].mean()
print(city_avg)
# Multiple aggregations
result = df.groupby('city').agg({
    'age': ['mean', 'min', 'max'],
    'name': 'count'
})
print(result)

Combining data

merge (joins)

merge behaves like a SQL join, and the how parameter changes what happens to rows that don’t have a match on the other side — this is the detail that trips people up in practice. id=3 exists only in df1, and id=4 exists only in df2; an inner join drops both (only fully-matched rows survive), while a left join keeps every row from df1 and fills score with NaN for the unmatched id=3. Picking the wrong join type silently drops data rather than erroring, so it’s worth being deliberate about which side you want “everything” from before running a merge on real datasets.

# Two DataFrames
df1 = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Beth', 'Carl']
})
df2 = pd.DataFrame({
    'id': [1, 2, 4],
    'score': [85, 90, 88]
})
# Inner join
merged = pd.merge(df1, df2, on='id', how='inner')
print(merged)
#    id   name  score
# 0   1  Alice     85
# 1   2   Beth     90
# Left join
merged = pd.merge(df1, df2, on='id', how='left')

The other way a merge silently goes wrong is duplicate keys. If df2 contained two rows with id=1 (two scores for Alice), the inner join would return two Alice rows, and every sum or count computed afterwards would double-count her. On real data this shows up as a joined table with more rows than either input, or revenue totals that are too high after enriching orders with a customer table that has duplicate customer ids. Passing validate='one_to_one' or validate='many_to_one' makes pandas check the assumption and raise MergeError: Merge keys are not unique in right dataset; not a many-to-one merge instead. Adding indicator=True adds a _merge column that says whether each row came from left_only, right_only or both, which is the quickest way to see which rows a join dropped or failed to match.

Key dtypes must also agree. An id column read as integers in one file and as strings in another (1 vs '1') raises ValueError: You are trying to merge on int64 and object columns in current versions; convert one side explicitly before merging.

concat (stacking)

# Concatenate vertically
df_concat = pd.concat([df1, df2], ignore_index=True)
# Concatenate horizontally
df_concat = pd.concat([df1, df2], axis=1)

concat is for stacking DataFrames that already share the same shape — it doesn’t match rows by key the way merge does, it just glues them together by position (vertically) or by index (horizontally). Concatenating horizontally two DataFrames whose indexes don’t actually correspond to the same real-world entity is a common mistake: Pandas will align by index value without complaint, silently producing rows that pair up the wrong records if the indexes don’t mean the same thing in both frames.

Practical example

Sales analysis

This mirrors the shape of a lot of real reporting work: load raw transactional data, derive a time-based grouping key (month, extracted from a proper datetime column via .dt.month — which only works because pd.to_datetime() was called first to convert the raw string dates), then aggregate. The .dt accessor is Pandas’ namespace for datetime-specific operations on a Series, and it only becomes available once the column’s dtype is actually datetime64, not a string that merely looks like a date — a frequent source of AttributeError: Can only use .dt accessor with datetimelike values when someone forgets the conversion step.

import pandas as pd
# Load data
sales = pd.read_csv('sales.csv')
# Basic info
print(f"Total sales: {len(sales)} transactions")
print(f"Total revenue: ${sales['amount'].sum():,}")
# Monthly revenue
sales['date'] = pd.to_datetime(sales['date'])
sales['month'] = sales['date'].dt.month
monthly_sales = sales.groupby('month')['amount'].sum()
print(monthly_sales)
# Top 10 products by revenue
top_products = sales.groupby('product')['amount'].sum().sort_values(ascending=False).head(10)
print(top_products)
# Save results
monthly_sales.to_csv('monthly_report.csv')

One trap in this example: .dt.month is just the month number, so January 2025 and January 2026 land in the same group. That is correct for a seasonal “which month sells best” question and wrong for a monthly revenue report spanning more than a year. For the latter, group by sales['date'].dt.to_period('M'), which keeps the year, or use sales.resample('MS', on='date')['amount'].sum(), which also includes months with no sales as zero instead of leaving them out.

The amount column deserves a check too. If one row in the CSV contains "1,200" or "N/A", the whole column is read as object (strings), and sum() then concatenates strings or raises TypeError: unsupported operand type(s) for +: 'int' and 'str'. pd.to_numeric(sales['amount'], errors='coerce') converts what it can and turns the rest into NaN, which you can then count and inspect rather than lose silently; for thousands separators, read_csv(..., thousands=',') handles them at load time.

Pandas memory, cleanup, and chaining tips

Pandas tips

# ✅ Memory optimization
df = pd.read_csv('large.csv', dtype={'id': 'int32'})
# ✅ Missing values (pick one strategy per column, not both)
df = df.dropna()   # drop rows with NaN
df = df.fillna(0)  # fill with 0
# ✅ Duplicates
df = df.drop_duplicates()
# ✅ Method chaining
result = (df
    .query('age >= 25')
    .groupby('city')['age']
    .mean()
    .sort_values(ascending=False)
)

The two missing-value lines are alternatives, not a sequence. dropna() with no arguments removes a row if any column is missing, which on a wide table with one sparse optional column can discard most of the data; dropna(subset=['amount']) limits it to the columns that matter. fillna(0) is only correct where zero really means “none” (units sold on a day with no sales), and wrong where it changes a statistic (filling a missing age with 0 drags the average down). Deciding per column, and counting what you drop with df.isna().sum() first, is the habit that keeps analyses honest.

For memory, the biggest win on real datasets is usually not int32 but repeated strings. A city column with a handful of distinct values stored as Python strings costs tens of bytes per row; astype('category') stores each distinct value once plus a small integer code, and df.memory_usage(deep=True) shows the difference. Categories do change behavior slightly (a groupby on a categorical column includes unused categories unless you pass observed=True), so convert columns you group or filter on, after the data has been cleaned.