Chapter 3: Pandas Basics - Machine Learning Introduction

Master Pandas, the essential library for data analysis

Introduction

Pandas is the most important library for performing data analysis in Python. It makes it easy to work with tabular data (such as Excel or CSV files) and is indispensable for the data preprocessing stage of machine learning.

In this chapter, you will learn the following:

The basics of Series and DataFrame
Reading and saving CSV files
Selecting and extracting data
Data cleaning (handling missing values)
Data transformation and aggregation
Data joining and visualization

The difference between Pandas and NumPy
NumPy specializes in numerical computation, whereas Pandas specializes in handling tabular data (data with rows and columns). Pandas is built on top of NumPy and uses NumPy arrays internally.

1. Series Basics

A Series is a one-dimensional data structure. Think of it as an array with an index (labels).

Example 1: Creating and manipulating a Series

import pandas as pd
import numpy as np

# Create a Series from a list
s1 = pd.Series([10, 20, 30, 40, 50])
print("Series 1:")
print(s1)
# Output:
# 0    10
# 1    20
# 2    30
# 3    40
# 4    50
# dtype: int64

# Specify the index
s2 = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
print("\nSeries 2:")
print(s2)
# Output:
# a    10
# b    20
# c    30
# dtype: int64

# Create a Series from a dictionary
data_dict = {'Tokyo': 1400, 'Osaka': 880, 'Nagoya': 230, 'Fukuoka': 155}
population = pd.Series(data_dict)
print("\nPopulation (10,000s):")
print(population)

# Accessing elements
print("\nPopulation of Osaka:", population['Osaka'])  # 880
print("First two:")
print(population[:2])

# Statistics
print("\nStatistics:")
print("Mean:", population.mean())
print("Max:", population.max())
print("Min:", population.min())
print("Sum:", population.sum())

# Boolean operations
print("\nCities with a population of 5 million or more:")
print(population[population >= 500])

2. DataFrame Basics

A DataFrame is a two-dimensional, tabular data structure. It resembles an Excel spreadsheet.

Example 2: Creating a DataFrame

import pandas as pd

# Create a DataFrame from a dictionary
data = {
    'Name': ['Taro', 'Hanako', 'Jiro', 'Momoko'],
    'Age': [25, 30, 22, 28],
    'City': ['Tokyo', 'Osaka', 'Nagoya', 'Fukuoka'],
    'Income': [450, 520, 380, 490]
}
df = pd.DataFrame(data)
print("DataFrame:")
print(df)
# Output:
#      Name  Age     City  Income
# 0    Taro   25    Tokyo     450
# 1  Hanako   30    Osaka     520
# 2    Jiro   22   Nagoya     380
# 3  Momoko   28  Fukuoka     490

# DataFrame information
print("\nShape:", df.shape)  # (4, 4) = 4 rows, 4 columns
print("Column names:", df.columns.tolist())
print("Index:", df.index.tolist())
print("Data types:")
print(df.dtypes)

# Basic statistics
print("\nStatistics:")
print(df.describe())

# Display the first and last few rows
print("\nFirst 2 rows:")
print(df.head(2))

print("\nLast 2 rows:")
print(df.tail(2))

# Information summary
print("\nDataFrame info:")
print(df.info())

graph LR A[Pandas] --> B[Series] A --> C[DataFrame] B --> D[1D data] C --> E[2D tabular] D --> F[Indexed array] E --> G[Table with rows and columns] style A fill:#e3f2fd style B fill:#fff3e0 style C fill:#f3e5f5

3. Reading and Saving CSV Files

Example 3: CSV operations

import pandas as pd

# Create sample data
data = {
    'Date': ['2025-01-01', '2025-01-02', '2025-01-03', '2025-01-04'],
    'Product': ['Apple', 'Banana', 'Orange', 'Apple'],
    'Quantity': [10, 15, 8, 12],
    'Price': [100, 80, 120, 100]
}
df = pd.DataFrame(data)

# Save to a CSV file
df.to_csv('sales.csv', index=False, encoding='utf-8')
print("Saved the CSV file.")

# Read the CSV file
df_loaded = pd.read_csv('sales.csv')
print("\nLoaded data:")
print(df_loaded)

# Convert to datetime type
df_loaded['Date'] = pd.to_datetime(df_loaded['Date'])
print("\nAfter datetime conversion:")
print(df_loaded.dtypes)

# Read only specific columns
df_partial = pd.read_csv('sales.csv', usecols=['Product', 'Quantity'])
print("\nSpecific columns only:")
print(df_partial)

# Reading a CSV without a header
# pd.read_csv('data.csv', header=None, names=['col1', 'col2'])

# When the delimiter is different
# pd.read_csv('data.tsv', sep='\t')  # TSV file

# Reading a large file in chunks
# for chunk in pd.read_csv('large_file.csv', chunksize=1000):
#     process(chunk)

4. Selecting and Extracting Data

Example 4: loc, iloc, boolean indexing

import pandas as pd

# Sample data
data = {
    'Name': ['Taro', 'Hanako', 'Jiro', 'Momoko', 'Goro'],
    'Age': [25, 30, 22, 28, 35],
    'City': ['Tokyo', 'Osaka', 'Nagoya', 'Fukuoka', 'Tokyo'],
    'Income': [450, 520, 380, 490, 600]
}
df = pd.DataFrame(data)
print("Original data:")
print(df)

# Selecting a column
print("\nAge column:")
print(df['Age'])

# Selecting multiple columns
print("\nName and Income:")
print(df[['Name', 'Income']])

# loc: label-based indexing
print("\nRow at index 0:")
print(df.loc[0])

print("\nName and Age for indices 0-2:")
print(df.loc[0:2, ['Name', 'Age']])

# iloc: position-based indexing
print("\nFirst 2 rows, first 2 columns:")
print(df.iloc[0:2, 0:2])

# Boolean indexing
print("\nAge 30 or older:")
print(df[df['Age'] >= 30])

print("\nResiding in Tokyo:")
print(df[df['City'] == 'Tokyo'])

# Multiple conditions (&: AND, |: OR)
print("\nResiding in Tokyo and income 5 million or more:")
print(df[(df['City'] == 'Tokyo') & (df['Income'] >= 500)])

# isin(): matches any of multiple values
cities = ['Tokyo', 'Osaka']
print("\nResiding in Tokyo or Osaka:")
print(df[df['City'].isin(cities)])

# String operations
print("\nNames containing 'ro':")
print(df[df['Name'].str.contains('ro')])

5. Data Cleaning (Handling Missing Values)

Example 5: Handling missing values

import pandas as pd
import numpy as np

# Data containing missing values
data = {
    'Name': ['Taro', 'Hanako', None, 'Momoko', 'Goro'],
    'Age': [25, 30, 22, np.nan, 35],
    'Income': [450, np.nan, 380, 490, 600]
}
df = pd.DataFrame(data)
print("Data containing missing values:")
print(df)

# Checking for missing values
print("\nNumber of missing values:")
print(df.isnull().sum())

# Whether there are missing values (per row):
print("\nWhether there are missing values (per row):")
print(df.isnull().any(axis=1))

# Drop rows containing missing values
df_dropped = df.dropna()
print("\nDrop rows containing missing values:")
print(df_dropped)

# Drop columns containing missing values
df_dropped_col = df.dropna(axis=1)
print("\nDrop columns containing missing values:")
print(df_dropped_col)

# Fill missing values with a specific value
df_filled = df.fillna(0)
print("\nFill missing values with 0:")
print(df_filled)

# Fill each column with a different value
df_filled2 = df.fillna({'Name': 'Unfilled', 'Age': df['Age'].mean(), 'Income': df['Income'].median()})
print("\nFill each column with a different value:")
print(df_filled2)

# Forward fill / backward fill
df_ffill = df.fillna(method='ffill')  # Fill with the previous value
df_bfill = df.fillna(method='bfill')  # Fill with the next value

# Handling duplicates
data_dup = {
    'Name': ['Taro', 'Hanako', 'Taro', 'Jiro'],
    'Age': [25, 30, 25, 22]
}
df_dup = pd.DataFrame(data_dup)
print("\nData containing duplicates:")
print(df_dup)

print("\nChecking for duplicates:")
print(df_dup.duplicated())

print("\nDrop duplicates:")
print(df_dup.drop_duplicates())

graph TD A[Missing value handling] --> B[Removal] A --> C[Imputation] B --> D[dropna row removal] B --> E[dropna column removal] C --> F[Fill with a fixed value] C --> G[Fill with a statistic] C --> H[Fill with adjacent values] style A fill:#e3f2fd style B fill:#fff3e0 style C fill:#f3e5f5

6. Data Transformation

Example 6: apply, map, replace

import pandas as pd

# Sample data
data = {
    'Name': ['Taro', 'Hanako', 'Jiro', 'Momoko'],
    'Age': [25, 30, 22, 28],
    'Income': [450, 520, 380, 490]
}
df = pd.DataFrame(data)
print("Original data:")
print(df)

# apply: apply a function
df['Income (thousand yen)'] = df['Income'].apply(lambda x: x * 1000)
print("\nConvert income to thousand-yen units:")
print(df)

# Add an age category
def age_category(age):
    if age < 25:
        return 'Junior'
    elif age < 30:
        return 'Mid-level'
    else:
        return 'Veteran'

df['Category'] = df['Age'].apply(age_category)
print("\nAdd age category:")
print(df)

# map: map using a dictionary
name_english = {'Taro': 'Taro', 'Hanako': 'Hanako', 'Jiro': 'Jiro', 'Momoko': 'Momoko'}
df['English name'] = df['Name'].map(name_english)
print("\nAdd English name:")
print(df)

# replace: replace values
df_replaced = df.replace({'Category': {'Junior': 'Junior', 'Mid-level': 'Mid', 'Veteran': 'Senior'}})
print("\nCategory in English:")
print(df_replaced)

# Add a new column (computation)
df['After-tax income'] = df['Income'] * 0.8
print("\nAdd after-tax income:")
print(df[['Name', 'Income', 'After-tax income']])

# Dropping columns
df_dropped = df.drop(['Income (thousand yen)', 'English name'], axis=1)
print("\nDrop unnecessary columns:")
print(df_dropped)

# Renaming columns
df_renamed = df.rename(columns={'Age': 'Age', 'Income': 'Salary'})
print("\nRename columns:")
print(df_renamed)

7. Grouping and Aggregation

Example 7: groupby and agg

import pandas as pd

# Sales data
data = {
    'Date': ['2025-01-01', '2025-01-01', '2025-01-02', '2025-01-02', '2025-01-03'],
    'Product': ['Apple', 'Banana', 'Apple', 'Orange', 'Banana'],
    'Store': ['Tokyo', 'Tokyo', 'Osaka', 'Tokyo', 'Osaka'],
    'Quantity': [10, 15, 12, 8, 20],
    'Sales': [1000, 1200, 1200, 960, 1600]
}
df = pd.DataFrame(data)
print("Sales data:")
print(df)

# Aggregation by product
print("\nTotal sales by product:")
print(df.groupby('Product')['Sales'].sum())

# Multiple statistics
print("\nStatistics by product:")
print(df.groupby('Product').agg({
    'Quantity': 'sum',
    'Sales': ['sum', 'mean', 'count']
}))

# Grouping by multiple columns
print("\nAggregation by product x store:")
grouped = df.groupby(['Product', 'Store'])['Sales'].sum()
print(grouped)

# Pivot table
print("\nPivot table:")
pivot = df.pivot_table(
    values='Sales',
    index='Product',
    columns='Store',
    aggfunc='sum',
    fill_value=0
)
print(pivot)

# Custom aggregation function
print("\nSales range by product:")
print(df.groupby('Product')['Sales'].agg(lambda x: x.max() - x.min()))

# Filtering after aggregation
print("\nProducts with total sales of 2000 or more:")
grouped_filtered = df.groupby('Product')['Sales'].sum()
print(grouped_filtered[grouped_filtered >= 2000])

8. Joining Data

Example 8: merge, concat

import pandas as pd

# Customer data
customers = pd.DataFrame({
    'CustomerID': [1, 2, 3, 4],
    'Name': ['Taro', 'Hanako', 'Jiro', 'Momoko']
})

# Order data
orders = pd.DataFrame({
    'OrderID': [101, 102, 103, 104],
    'CustomerID': [1, 2, 1, 3],
    'Amount': [5000, 3000, 7000, 4500]
})

print("Customer data:")
print(customers)
print("\nOrder data:")
print(orders)

# merge: join
merged = pd.merge(customers, orders, on='CustomerID', how='inner')
print("\nInner join:")
print(merged)

# Left outer join
merged_left = pd.merge(customers, orders, on='CustomerID', how='left')
print("\nLeft join:")
print(merged_left)

# Right outer join
merged_right = pd.merge(customers, orders, on='CustomerID', how='right')
print("\nRight join:")
print(merged_right)

# Full outer join
merged_outer = pd.merge(customers, orders, on='CustomerID', how='outer')
print("\nOuter join:")
print(merged_outer)

# concat: vertical concatenation
df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})

concatenated = pd.concat([df1, df2], ignore_index=True)
print("\nVertical concatenation:")
print(concatenated)

# Horizontal concatenation
concatenated_h = pd.concat([df1, df2], axis=1)
print("\nHorizontal concatenation:")
print(concatenated_h)

graph LR A[Data joining] --> B[merge] A --> C[concat] B --> D[inner join] B --> E[left join] B --> F[right join] B --> G[outer join] C --> H[Vertical axis=0] C --> I[Horizontal axis=1] style A fill:#e3f2fd style B fill:#fff3e0 style C fill:#f3e5f5

9. Basics of Data Visualization

Example 9: Integration with matplotlib

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

# Monthly sales data
data = {
    'Month': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'],
    'Sales': [120, 135, 150, 142, 168, 175],
    'Profit': [30, 35, 42, 38, 48, 52]
}
df = pd.DataFrame(data)

# Line chart
df.plot(x='Month', y='Sales', kind='line', marker='o', title='Monthly Sales Trend')
plt.xlabel('Month')
plt.ylabel('Sales (10,000 yen)')
# plt.savefig('sales_trend.png')
# plt.show()

# Multiple series
df.plot(x='Month', y=['Sales', 'Profit'], kind='line', marker='o', title='Monthly Sales and Profit')
# plt.show()

# Bar chart
df.plot(x='Month', y='Sales', kind='bar', title='Monthly Sales (Bar Chart)')
# plt.show()

# Histogram
np.random.seed(42)
scores = pd.DataFrame({'Score': np.random.normal(70, 10, 100)})
scores.plot(kind='hist', bins=20, title='Score Distribution', edgecolor='black')
plt.xlabel('Score')
plt.ylabel('Count')
# plt.show()

# Scatter plot
data_scatter = {
    'Study time': [1, 2, 3, 4, 5, 6, 7, 8],
    'Score': [50, 55, 65, 70, 75, 80, 85, 90]
}
df_scatter = pd.DataFrame(data_scatter)
df_scatter.plot(kind='scatter', x='Study time', y='Score', title='Relationship Between Study Time and Score')
# plt.show()

# Box plot
data_box = {
    'Class A': np.random.normal(75, 10, 30),
    'Class B': np.random.normal(70, 15, 30),
    'Class C': np.random.normal(80, 8, 30)
}
df_box = pd.DataFrame(data_box)
df_box.plot(kind='box', title='Score Distribution by Class')
# plt.show()

print("Generated the graphs.")

10. Practical Example: A Data Analysis Workflow

Example 10: Comprehensive data analysis

import pandas as pd
import numpy as np

# Create sample data (employee data)
np.random.seed(42)
n = 100

data = {
    'EmployeeID': range(1, n + 1),
    'Name': [f'Employee{i}' for i in range(1, n + 1)],
    'Department': np.random.choice(['Sales', 'Development', 'HR', 'Finance'], n),
    'Age': np.random.randint(22, 60, n),
    'Years of service': np.random.randint(1, 30, n),
    'Income': np.random.randint(300, 1000, n),
    'Rating': np.random.choice(['A', 'B', 'C', 'D'], n, p=[0.1, 0.3, 0.4, 0.2])
}
df = pd.DataFrame(data)

# Randomly add missing values
df.loc[np.random.choice(df.index, 5), 'Income'] = np.nan

print("=== 1. Data overview ===")
print(f"Data shape: {df.shape}")
print(f"\nFirst 5 rows:")
print(df.head())

print("\n=== 2. Data types and missing values ===")
print(df.info())

print("\n=== 3. Missing value handling ===")
print(f"Number of missing values: {df.isnull().sum().sum()}")
df['Income'] = df['Income'].fillna(df['Income'].median())
print(f"Missing values after imputation: {df.isnull().sum().sum()}")

print("\n=== 4. Basic statistics ===")
print(df.describe())

print("\n=== 5. Analysis by department ===")
dept_analysis = df.groupby('Department').agg({
    'Age': 'mean',
    'Years of service': 'mean',
    'Income': ['mean', 'median', 'count']
})
print(dept_analysis)

print("\n=== 6. Analysis by rating ===")
rating_analysis = df.groupby('Rating')['Income'].describe()
print(rating_analysis)

print("\n=== 7. Correlation analysis ===")
correlation = df[['Age', 'Years of service', 'Income']].corr()
print(correlation)

print("\n=== 8. Extracting high earners ===")
high_earners = df[df['Income'] >= 800]
print(f"Income of 8 million or more: {len(high_earners)} people")
print(high_earners[['Name', 'Department', 'Income', 'Rating']])

print("\n=== 9. Cross-tabulation of department x rating ===")
crosstab = pd.crosstab(df['Department'], df['Rating'])
print(crosstab)

print("\n=== 10. Exporting data ===")
# Save the analysis results to CSV
df.to_csv('employee_data.csv', index=False, encoding='utf-8')
dept_analysis.to_csv('dept_analysis.csv', encoding='utf-8')
print("Saved the analysis results.")

Summary

In this chapter, you learned the basics of Pandas:

✅ Series/DataFrame: fundamental data structures
✅ CSV operations: read_csv, to_csv
✅ Data selection: loc, iloc, boolean indexing
✅ Missing value handling: dropna, fillna
✅ Data transformation: apply, map, replace
✅ Aggregation: groupby, agg, pivot_table
✅ Joining: merge, concat
✅ Visualization: plot (matplotlib integration)

Next step: In Chapter 4, you will use the knowledge of Python, NumPy, and Pandas you have learned so far to study an overview of machine learning.

Exercises

Exercise 1: DataFrame operations

Problem: Create a DataFrame from the following data, then (1) extract students whose average score is 80 or higher, and (2) compute the average score for each subject.

# Data
data = {
    'Name': ['Taro', 'Hanako', 'Jiro', 'Momoko', 'Goro'],
    'Math': [85, 92, 78, 88, 95],
    'English': [78, 88, 82, 90, 85],
    'Japanese': [82, 85, 75, 92, 88]
}

# Solution example
import pandas as pd

df = pd.DataFrame(data)
print("Student data:")
print(df)

# (1) Compute the average score
df['Average'] = df[['Math', 'English', 'Japanese']].mean(axis=1)
print("\nAverage score added:")
print(df)

# Average of 80 or higher
high_scorers = df[df['Average'] >= 80]
print("\nStudents with an average of 80 or higher:")
print(high_scorers)

# (2) Average of each subject
subject_avg = df[['Math', 'English', 'Japanese']].mean()
print("\nAverage score of each subject:")
print(subject_avg)

Exercise 2: Handling missing values

Problem: With the following data, (1) impute the missing values with the mean of each column, and (2) compare the statistics before and after imputation.

import pandas as pd
import numpy as np

# Data containing missing values
data = {
    'A': [1, 2, np.nan, 4, 5],
    'B': [10, np.nan, 30, 40, 50],
    'C': [100, 200, 300, np.nan, 500]
}
df = pd.DataFrame(data)

# Solution example
print("Original data:")
print(df)

print("\nStatistics before imputation:")
print(df.describe())

# Impute with the mean
df_filled = df.fillna(df.mean())
print("\nData after imputation:")
print(df_filled)

print("\nStatistics after imputation:")
print(df_filled.describe())

print("\nChanges:")
for col in df.columns:
    before = df[col].mean()
    after = df_filled[col].mean()
    print(f"Mean of column {col}: {before:.2f} → {after:.2f}")

Exercise 3: Grouping and aggregation

Problem: With the following sales data, (1) aggregate by store, and (2) extract the top 3 stores by sales for each product category.

import pandas as pd

data = {
    'Store': ['A', 'B', 'A', 'C', 'B', 'C', 'A', 'B'],
    'Category': ['Food', 'Food', 'Clothing', 'Food', 'Clothing', 'Clothing', 'Food', 'Food'],
    'Sales': [100, 150, 200, 120, 180, 220, 110, 160]
}
df = pd.DataFrame(data)

# Solution example
print("Sales data:")
print(df)

# (1) Aggregation by store
store_summary = df.groupby('Store')['Sales'].agg(['sum', 'mean', 'count'])
print("\nAggregation by store:")
print(store_summary)

# (2) Category x store pivot
pivot = df.pivot_table(values='Sales', index='Category', columns='Store', aggfunc='sum', fill_value=0)
print("\nCategory x store pivot:")
print(pivot)

# Top 3 stores for each category
print("\nTop stores by category:")
for category in df['Category'].unique():
    cat_data = df[df['Category'] == category]
    top3 = cat_data.groupby('Store')['Sales'].sum().nlargest(3)
    print(f"\n{category}:")
    print(top3)

Exercise 4: Joining data

Problem: Join the customer master and order data, then aggregate the number of orders and total amount for each customer.

import pandas as pd

# Customer master
customers = pd.DataFrame({
    'CustomerID': [1, 2, 3, 4, 5],
    'Name': ['Taro', 'Hanako', 'Jiro', 'Momoko', 'Goro'],
    'Membership rank': ['Gold', 'Silver', 'Gold', 'Bronze', 'Silver']
})

# Order data
orders = pd.DataFrame({
    'OrderID': [101, 102, 103, 104, 105, 106],
    'CustomerID': [1, 2, 1, 3, 2, 1],
    'Amount': [5000, 3000, 7000, 4500, 2000, 6000]
})

# Solution example
print("Customer master:")
print(customers)
print("\nOrder data:")
print(orders)

# Join
merged = pd.merge(customers, orders, on='CustomerID', how='left')
print("\nAfter joining:")
print(merged)

# Aggregation by customer
customer_summary = merged.groupby(['CustomerID', 'Name', 'Membership rank'])['Amount'].agg(['count', 'sum'])
customer_summary.columns = ['Number of orders', 'Total amount']
customer_summary = customer_summary.reset_index()
print("\nAggregation by customer:")
print(customer_summary)

# Aggregation by membership rank
rank_summary = customer_summary.groupby('Membership rank')['Total amount'].mean()
print("\nAverage purchase amount by membership rank:")
print(rank_summary)

Exercise 5: Comprehensive problem

Problem: Analyze monthly sales data by (1) computing the month-over-month growth rate, (2) computing the 3-month moving average, and (3) visualizing it with a graph.

import pandas as pd
import matplotlib.pyplot as plt

data = {
    'Month': ['2024-01', '2024-02', '2024-03', '2024-04', '2024-05', '2024-06'],
    'Sales': [120, 135, 150, 142, 168, 175]
}
df = pd.DataFrame(data)

# Solution example
print("Monthly sales:")
print(df)

# (1) Month-over-month growth rate
df['MoM change'] = df['Sales'].pct_change() * 100
print("\nMonth-over-month growth rate:")
print(df)

# (2) 3-month moving average
df['3-month moving average'] = df['Sales'].rolling(window=3).mean()
print("\nMoving average added:")
print(df)

# (3) Visualization
plt.figure(figsize=(10, 6))
plt.plot(df['Month'], df['Sales'], marker='o', label='Sales')
plt.plot(df['Month'], df['3-month moving average'], marker='s', linestyle='--', label='3-month moving average')
plt.xlabel('Month')
plt.ylabel('Sales (10,000 yen)')
plt.title('Monthly Sales Trend')
plt.legend()
plt.xticks(rotation=45)
plt.grid(True, alpha=0.3)
plt.tight_layout()
# plt.savefig('sales_analysis.png')
# plt.show()

print("\nCreated the graph.")

← Chapter 2: NumPy Basics Chapter 4: Overview of Machine Learning →