Pandasを用いたExcel操作の基本から応用までを解説する。

ファイルの読み込みと書き出し

Excelファイルの読み込みには pd.read_excel() を使用する。ヘッダー行の指定やインデックス列の設定も同時に行える。

import pandas as pd

# 基本的な読み込み
df = pd.read_excel('data/sample.xlsx')

# ヘッダー行を3行目に指定
df = pd.read_excel('data/sample.xlsx', header=2)

# 特定の列をインデックスに設定して読み込み
df = pd.read_excel('data/sample.xlsx', index_col='uid')

データフレームの作成と保存も容易である。

import pandas as pd

record = {'uid': [101, 102, 103, 104, 105], 'user_name': ['Alice', 'Bob', 'Carol', 'Dave', 'Eve']}
frame = pd.DataFrame(record)
frame.to_excel('output/result.xlsx')

デフォルトでは自動採番のインデックスが付与される。特定の列をインデックスとして扱いたい場合は set_index を用いる。

frame = frame.set_index('uid')
frame.to_excel('output/result.xlsx')

データの確認と基本操作

読み込んだデータの構造や内容は各種メソッドで確認できる。

df = pd.read_excel('output/result.xlsx', index_col='uid')

print(df.shape)    # 行数と列数
print(df.columns)  # 列名一覧
print(df.head(2))  # 先頭2行
print(df.tail(2))  # 末尾2行

ヘッダー行が存在しないデータには header=None を指定し、後から列名を付与できる。

df = pd.read_excel('data/raw.xlsx', header=None)
df.columns = ['uid', 'user_name', 'points', 'age', 'gender']
df.set_index('uid', inplace=True)

SeriesとDataFrameの生成

辞書やリストからSeriesを生成し、さらにDataFrameへと変換できる。

import pandas as pd

# 辞書からのSeries生成
mapping = {'alpha': 10, 'beta': 20, 'gamma': 30}
ser1 = pd.Series(mapping)

# リストからのSeries生成
vals = [10, 20, 30]
keys = ['alpha', 'beta', 'gamma']
ser2 = pd.Series(vals, index=keys)

複数のSeriesを組み合わせてDataFrameを作成する。

s1 = pd.Series([1, 2, 3], index=[1, 2, 3], name='X')
s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name='Y')
s3 = pd.Series([100, 200, 300, 400], index=[1, 2, 3, 4], name='Z')

# 列方向の結合
df_col = pd.DataFrame({s1.name: s1, s2.name: s2, s3.name: s3})

# 行方向の結合
df_row = pd.DataFrame([s1, s2, s3])

データの自動填充と更新

セルへの値代入や、条件に応じた自動填充を行う。

import pandas as pd
from datetime import date, timedelta

def increment_months(origin, months):
    """指定した月数だけ日付を進める"""
    year_offset = months // 12
    new_month = origin.month + months % 12
    if new_month > 12:
        year_offset += 1
        new_month -= 12
    return date(origin.year + year_offset, new_month, origin.day)

catalog = pd.read_excel('data/catalog.xlsx', skiprows=3, usecols="C:F", dtype={'No': str, 'Availability': str, 'Date': str})
base_date = date(2023, 4, 1)

for idx in catalog.index:
    catalog['No'].at[idx] = idx + 1
    catalog['Availability'].at[idx] = 'InStock' if idx % 2 == 0 else 'OutOfStock'
    catalog['Date'].at[idx] = increment_months(base_date, idx)

catalog.set_index('No', inplace=True)
catalog.to_excel('output/catalog_updated.xlsx')

関数を用いた列計算

列全体の計算には演算子を用い、特定行のみの更新には at を用いる。

import pandas as pd

items = pd.read_excel('data/items.xlsx')

# 列全体の計算
items['FinalPrice'] = items['BasePrice'] * items['Rate']

# 特定行のみ計算
for i in range(3, 8):
    items['FinalPrice'].at[i] = items['BasePrice'].at[i] * items['Rate'].at[i]

apply を用いることで、関数やラムダ式を列に適用できる。

def markup(val):
    return val + 5

items['BasePrice'] = items['BasePrice'].apply(markup)
# ラムダ式でも記述可能
# items['BasePrice'] = items['BasePrice'].apply(lambda v: v + 5)

ソート処理

単一列および複数列でのソートが可能。ascending で昇順・降順を制御する。

import pandas as pd

products = pd.read_excel('data/products.xlsx', index_col='No')

# 単列ソート（価格降順）
products.sort_values(by='FinalPrice', inplace=True, ascending=False)

# 複数列ソート（優先度昇順、価格降順）
products.sort_values(by=['Priority', 'FinalPrice'], inplace=True, ascending=[True, False])

データのフィルタリング

関数やラムダ式を apply に渡し、条件に合致する行を抽出する。

import pandas as pd

devices = pd.read_excel('data/devices.xlsx', index_col='No')

filtered = devices.loc[devices.Cost.apply(lambda c: 500 <= c < 1500)]
filtered = filtered.loc[devices.Rate.apply(lambda r: 0.4 <= r < 0.8)]
filtered.sort_values(by='Category', inplace=True)

グラフ描画

Matplotlibと連携し、各種グラフを描画できる。

棒グラフ

import pandas as pd
import matplotlib.pyplot as plt

members = pd.read_excel('data/members.xlsx')
members.sort_values(by='Count', inplace=True, ascending=False)

plt.bar(members.Category, members.Count, color='steelblue')
plt.xticks(members.Category, rotation=45, ha='right')
plt.xlabel('Category')
plt.ylabel('Count')
plt.title('Member Distribution')
plt.tight_layout()
plt.show()

積み上げ横棒グラフ

import pandas as pd
import matplotlib.pyplot as plt

usage = pd.read_excel('data/usage.xlsx')
usage['Sum'] = usage['Q1'] + usage['Q2'] + usage['Q3']
usage.sort_values(by='Sum', inplace=True)

usage.plot.barh(x='Team', y=['Q1', 'Q2', 'Q3'], stacked=True, title='Quarterly Usage')
plt.tight_layout()
plt.show()

円グラフ

import pandas as pd
import matplotlib.pyplot as plt

regions = pd.read_excel('data/regions.xlsx', index_col='Area')
regions['2023'].plot.pie(fontsize=8, counterclock=False, startangle=-270)
plt.title('Region Distribution 2023')
plt.ylabel('2023')
plt.show()

面グラフ

import pandas as pd
import matplotlib.pyplot as plt

weekly = pd.read_excel('data/weekly.xlsx', index_col='Week')
weekly.plot.area(y=['Hardware', 'Software', 'Services'])
plt.title('Weekly Sales Trend')
plt.ylabel('Revenue')
plt.show()

散布図・ヒストグラム・KDE

import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

estate = pd.read_excel('data/estate.xlsx')

# 散布図
estate.plot.scatter(x='avg_income', y='property_value')
plt.title('収入と物件価格の散布図')
plt.show()

# ヒストグラム
estate.property_value.plot.hist(bins=50)
plt.title('物件価格の分布')
plt.show()

# KDE
estate.property_value.plot.kde()
plt.title('物件価格の密度分布')
plt.show()

# 相関行列
print(estate.corr())

線形回帰

import pandas as pd
import matplotlib.pyplot as plt
from scipy.stats import linregress

revenue = pd.read_excel('data/revenue.xlsx', dtype={'Period': str})

slope, intercept, r_value, p_value, std_err = linregress(revenue.index, revenue.Amount)
trend = revenue.index * slope + intercept

plt.scatter(revenue.index, revenue.Amount)
plt.plot(revenue.index, trend, color='tomato')
plt.title(f'y={slope:.2f}x+{intercept:.2f}')
plt.xticks(revenue.index, revenue.Period, rotation=90, fontsize=8)
plt.tight_layout()
plt.show()

テーブル結合

merge や join を用いて複数シートのデータを統合する。

import pandas as pd

emp = pd.read_excel('data/hr.xlsx', sheet_name='employees', index_col='EmpID')
eval_score = pd.read_excel('data/hr.xlsx', sheet_name='evaluations', index_col='EmpID')

# 左外部結合
merged = emp.join(eval_score, how='left').fillna(0)
merged.Rating = merged.Rating.astype(int)
print(merged)

データ検証

apply と axis=1 を組み合わせ、行ごとにバリデーション処理を実行する。

import pandas as pd

def check_score(row):
    if not 0 <= row.Score <= 100:
        print(f'Invalid: EmpID={row.name}, Score={row.Score}')

grades = pd.read_excel('data/grades.xlsx', index_col='EmpID')
grades.apply(check_score, axis=1)

文字列の分割

単一列の文字列を複数列に分割する。

import pandas as pd

contacts = pd.read_excel('data/contacts.xlsx', index_col='CID')
split_name = contacts['FullName'].str.split(expand=True)
contacts['FirstName'] = split_name[0].str.upper()
contacts['LastName'] = split_name[1].str.upper()
print(contacts)

統計量の算出

行方向・列方向の合計や平均を算出し、総合行を追加する。

import pandas as pd

exams = pd.read_excel('data/exams.xlsx', index_col='SID')

score_cols = ['Exam1', 'Exam2', 'Exam3']
exams['Total'] = exams[score_cols].sum(axis=1)
exams['Avg'] = exams[score_cols].mean(axis=1)

summary = exams[score_cols + ['Total', 'Avg']].mean()
summary['FullName'] = 'Aggregate'
exams = pd.concat([exams, summary.to_frame().T], ignore_index=True)
print(exams)

重複データの処理

drop_duplicates で重複を削除し、duplicated で重複箇所を特定する。

import pandas as pd

logs = pd.read_excel('data/logs.xlsx')

# 重複削除
logs.drop_duplicates(subset='Action', inplace=True, keep='first')

# 重複確認
dup_mask = logs.duplicated(subset='Action')
print(logs.iloc[dup_mask[dup_mask].index])

行と列の転置

transpose() を用いて行と列を入れ替える。

import pandas as pd

metrics = pd.read_excel('data/metrics.xlsx', index_col='Quarter')
swapped = metrics.transpose()
print(swapped)

CSV, TSV, テキストファイルの読み込み

read_csv と sep パラメータで区切り文字を指定する。

import pandas as pd

df_csv = pd.read_csv('data/records.csv', index_col='RID')
df_tsv = pd.read_csv('data/records.tsv', sep='\t', index_col='RID')
df_txt = pd.read_csv('data/records.txt', sep='|', index_col='RID')

ピボットテーブルとグループ集計

pivot_table または groupby を用いてデータを集計する。

import pandas as pd
import numpy as np

transactions = pd.read_excel('data/transactions.xlsx')
transactions['Year'] = pd.DatetimeIndex(transactions['Date']).year

# ピボットテーブル
pt = transactions.pivot_table(index='Segment', columns='Year', values='Amount', aggfunc=np.sum)
print(pt)

# グループ集計
grp = transactions.groupby(['Segment', 'Year'])
agg_df = pd.DataFrame({'Sum': grp['Amount'].sum(), 'Count': grp['TxID'].count()})
print(agg_df)

Jupyter Notebookでの条件付き書式

Jupyter環境上でスタイルを適用し、データを視覚的に強調表示できる。

import pandas as pd
import seaborn as sns

pupils = pd.read_excel('data/pupils.xlsx')

# 閾値未満の文字を赤色に
def color_low(val):
    return 'color: red' if val < 60 else 'color: black'

pupils.style.applymap(color_low, subset=['Midterm', 'Final', 'Makeup'])

# 最大値の背景を強調
def highlight_max(col):
    return ['background-color: lime' if v == col.max() else '' for v in col]

pupils.style.apply(highlight_max, subset=['Midterm', 'Final', 'Makeup'])

# 濃淡カラーマップ
cmap = sns.light_palette('green', as_cmap=True)
pupils.style.background_gradient(cmap=cmap, subset=['Midterm', 'Final', 'Makeup'])

# バーチャート表示
pupils.style.bar(color='orange', subset=['Midterm', 'Final', 'Makeup'])

行操作のまとめ

import pandas as pd
import numpy as np

batch1 = pd.read_excel('data/batch.xlsx', sheet_name='Batch1')
batch2 = pd.read_excel('data/batch.xlsx', sheet_name='Batch2')

# 縦方向の結合
combined = pd.concat([batch1, batch2], axis=0).reset_index(drop=True)

# 行の追加
new_row = pd.Series({'RID': 999, 'Label': 'NewEntry', 'Value': 42})
combined = pd.concat([combined, new_row.to_frame().T], ignore_index=True)

# セルの更新
combined.at[0, 'Label'] = 'Updated'

# 行の削除
combined.drop(index=[0, 1], inplace=True)

# 欠損値を含む行の削除
combined.dropna(inplace=True)
combined.reset_index(drop=True, inplace=True)

列操作のまとめ

import pandas as pd
import numpy as np

dataset = pd.read_excel('data/dataset.xlsx')

# 列の追加
dataset['Seq'] = np.arange(len(dataset))

# 特定位置への列挿入
dataset.insert(1, column='Flag', value=np.repeat('Active', len(dataset)))

# 列名の変更
dataset.rename(columns={'Flag': 'Status', 'Label': 'Tag'}, inplace=True)

# 列の削除
dataset.drop(columns=['Seq', 'Status'], inplace=True)

# 欠損値を含む行の削除
dataset.dropna(inplace=True)