pandas Data Manipulation: Series, DataFrame, I/O, Combining & Renaming

1. Recap: Lambda functions & Packages (Week 3)

Exam 本講開頭重複咗 Week 3 嘅 lambda (Q6/Q7) 同 packages 部分 (今次叫 Q1) — 老師講兩次嘅內容，考試機會較高。詳細解釋見 Week 3，以下係精簡版：

# lambda arguments: expression  —— anonymous，一個 expression
(lambda x, y: x - y)(3, 5)            # -2
id_string = ['a3', 'b1', 'c5', 'd2']
id_string.sort(key=lambda x: x[1])    # ['b1', 'd2', 'a3', 'c5']

def my_func(adder):
    return lambda x: x + adder        # lambda 作為 return value
my_func(5)(3)                         # 8

# Q1：檢查 / 安裝 packages (用 %pip，保證裝喺 notebook 當前 environment)
%pip show pandas
%pip install openpyxl                 # read_excel 需要
import pandas as pd                   # Install 一次 / 每個 session 都要 import

2. The pandas package: Series & DataFrame

pandas 係 fast, powerful, flexible, easy to use 嘅 open-source data analysis and manipulation tool，建基於 Python。用之前要 import pandas as pd。

兩個主要 data structures (亦即 pandas library 嘅兩個 classes)：

Class 係建立 objects 嘅 blueprint，定義 objects 會有嘅 attributes 同 methods。

2.1 Series

1D labeled array，可以裝任何 data type (integers, strings, floats, Python objects)。由 index (axis labels 嘅集合) 同 data 組成。

Constructor

pd.Series() 係 Series class 嘅 constructor (建立新 object 嘅 method)；Python 中 constructor 同 class 同名。可以用 pd.Series.__init__ 睇 constructor 參數，或者睇官方文檔：

class pandas.Series(data=None, index=None, dtype=None, name=None, copy=None)

• pd.Series(list) → index 係由 0 開始嘅自然數。
• pd.Series(data_list, index=index_list) → index 用你提供嘅 list。

import pandas as pd
index_day = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun', 'Mon']
data = [1, 2, 3, 4, 5, 6, 7, 8]
s = pd.Series(data)                    # index 0–7, dtype: int64
s = pd.Series(data, index=index_day)   # index = Mon … Sun, Mon (可以重複！)

Basic operations

s['new'] = 100     # insertion：用新 index label 直接 assign
s['Mon']           # retrieval by label → 返回兩行 (Mon 1 同 Mon 8)，因為 label 重複
s[2]               # FutureWarning：用整數當 position 已 deprecated → 應用 s.iloc[2]  (= 3)

s.mean()           # 15.11  = (1+2+…+8+100)/9 = 136/9
s.max()            # 100
s + s              # element-wise addition
s * 2              # element-wise multiplication
s ** 3             # 每個元素立方 (new → 1000000)

import numpy as np
s['Tue'] = np.nan  # dtype 由 int64 變 float64 (NaN 係 float)
s.mean()           # 16.75 = (136 − 2)/8  ← NaN 自動被跳過

2.2 DataFrame construction

Constructor pd.DataFrame() 可以接 dictionaries、lists、其他 DataFrames 等；可以指定 index、columns、dtype。所有 args 都有 default None，所以 pd.DataFrame() 會得到空 DataFrame。

class pandas.DataFrame(data=None, index=None, columns=None, dtype=None, copy=None)

pd.DataFrame([[1, 2], [3, 4]])                    # list of lists：每個內層 list 係一個 ROW
#    0  1
# 0  1  2
# 1  3  4
pd.DataFrame([[1, 2], [3, 4]], columns=["A", "B"])
#    A  B
# 0  1  2
# 1  3  4
pd.DataFrame({'A': [1, 2], 'B': [3, 4]})          # dict of lists：每個 key 係一個 COLUMN
#    A  B
# 0  1  3
# 1  2  4
pd.DataFrame(data={'A': [1, 2], 'B': [3, 4]}, index=['one', 'two'])
#      A  B
# one  1  3
# two  2  4
pd.DataFrame([[1, 2], ["Robin", "Aileen"]])       # mixed data types 都得 (column dtype 變 object)

2.3 Using Series as columns — index alignment

Exam Key point: Series are objects that have their own index! 用 Series 建 DataFrame 時，pandas 會按 index label 對齊，唔係按位置。

s = pd.Series(range(1, 8))                  # index 係 0–6
index_day = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun']
df = pd.DataFrame({'first': s, 'second': s**2}, index=index_day)
df     # 全部 NaN！因為 s 嘅 index (0–6) 同 'Mon'…'Sun' 完全對唔上
#       first  second
# Mon     NaN     NaN
# ...     ...     ...

# 正確做法：一開始就畀 Series 相同 index
s = pd.Series(range(1, 8), index=index_day)
df = pd.DataFrame({'first': s, 'second': s**2, 'third': s**3})
#       first  second  third
# Mon       1       1      1
# Tue       2       4      8
# ...
# Sun       7      49    343

2.4 Attributes and methods of DataFrame

Object 係名詞 (「我架車」)，attribute 係形容詞 (「我架車嘅顏色係藍色」)，method (特殊 attribute) 係動詞 (「我架車可以 drive()」)。Attributes 冇括號，methods 要括號。

print("DataFrame shape:", df.shape)            # (7, 3)
print("Column names:", df.columns.tolist())    # ['first', 'second', 'third']
print("Index:", df.index.tolist())             # ['Mon', 'Tue', ..., 'Sun']

df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
print(df.info())       # 印 summary 之後再印 None —— info() 自己 print，return None！
print(df.describe())
#          A    B
# count  3.0  3.0
# mean   2.0  5.0
# std    1.0  1.0
# min    1.0  4.0
# 25%    1.5  4.5
# 50%    2.0  5.0
# 75%    2.5  5.5
# max    3.0  6.0

Lab Q2: DataFrame construction

city_list = ['Tokyo', 'Delhi', 'Shanghai', 'Sao Paulo', 'Mexico City']
pop_list = [37.4, 28.5, 25.6, 21.7, 21.6]
# Step 1
data_dict = {'city': city_list, 'population': pop_list}   # 避免用 dict 做名
# Step 2
country_index = ['Japan', 'India', 'China', 'Brazil', 'Mexico']
# Step 3
df = pd.DataFrame(data_dict, index=country_index)
# Step 4
print(type(df['city']))     # <class 'pandas.core.series.Series'>
print(type(df[['city']]))   # <class 'pandas.core.frame.DataFrame'>

3. Importing data

常見外部檔案：csv (structured)、spreadsheets (structured)、json (semi-structured)、xml (semi-structured)…

3.1 pd.read_csv()

Signature 好長 (sep, header, names, index_col, usecols, dtype, skiprows, nrows, na_values, parse_dates, encoding…)。要識讀嘅參數：

File paths

• Current path："./"
• 當前路徑下 sub-folder："./sub_folder/" (slide 亦列 "/sub_folder/")
• 另一個 drive："D:/another_folder/" (absolute path)

df = pd.read_csv('your_file_with_its_path.csv')                 # 同一 folder
df = pd.read_csv('sub_folder/your_file_with_its_path.csv')      # relative path
df = pd.read_csv('D:/absolute_path_to_your_file/your_file.csv') # absolute path

dir_path = './Datasets/week_4/'          # 只放 folder，唔包括檔名
hero_power = pd.read_csv(dir_path + 'superhero_powers.csv')     # string 相加砌完整路徑
hero_power          # 667 rows × 168 columns (hero_names + 167 個 True/False power columns)
hero_power.head()   # 頭 5 rows

3.2 pd.read_excel()

同 read_csv 差唔多，但要指定 sheet：

hero_dc = pd.read_excel(dir_path + 'superhero_info.xlsx', sheet_name='DC Comics')
hero_marvel = pd.read_excel(dir_path + 'superhero_info.xlsx', sheet_name='Marvel Comics')
# 如果出現 missing "openpyxl" error → %pip install openpyxl

3.3 Previewing DataFrames

hero_power.info()     # RangeIndex: 667 entries；Columns: 168；dtypes: bool(167), object(1)
hero_dc.head()        # 頭 5 rows
hero_marvel.tail(10)  # 尾 10 rows
hero_dc.columns       # Index(['name', 'Gender', 'Eye color', 'Race', 'Hair color', 'Height',
                      #        'Publisher', 'Alignment', 'Weight'], dtype='object')
hero_marvel.index     # RangeIndex(start=0, stop=388, step=1)

留意：dtype 係 pandas 自動推斷 (inferred) 嘅，有時會出乎意料。例如 Marvel data 入面 Height / Weight 有 -99 呢類「假」數值、Race / Hair color 有 -，其實代表 missing — 呢啲係 Week 5–7 data cleaning 要處理嘅問題。

3.4 pandas dtypes

用 df.dtypes 睇。pandas dtypes 唔同 Python types：整數 column 係 int64 而唔係 int，浮點係 float64 而唔係 float。

3.5 指定 dtype 讀入

hero_dtype = {'name': 'string', 'Gender': 'category', 'Eye color': 'string',
              'Race': 'string', 'Hair color': 'string', 'Publisher': 'string',
              'Alignment': 'category'}
hero_dc = pd.read_excel(dir_path + 'superhero_info.xlsx', sheet_name='DC Comics', dtype=hero_dtype)
hero_dc.info()
#  8   Weight      144 non-null     float64     ← DC: 215 rows 只有 144 個 non-null → float64
hero_marvel.info()
#  8   Weight      388 non-null     int64       ← Marvel: 冇 missing → int64
hero_dc.shape, hero_marvel.shape          # (215, 9), (388, 9)
hero_dc.columns == hero_marvel.columns    # array([True, True, ..., True])  兩邊 columns 一樣

3.6 Exporting data: CSV vs Pickle

用 DataFrame.to_csv() 同 DataFrame.to_pickle() 儲存：

hero_dc.to_pickle(dir_path + 'hero_dc.pkl')
hero_marvel.to_pickle(dir_path + 'hero_marvel.pkl')

Lab Q3: Data import and export (Titanic)

dir_path = './Datasets/week_4/'
Titanic_dtype = {'PassengerId': 'string', 'Pclass': 'category', 'Name': 'string',
                 'Sex': 'string', 'Age': 'string', 'Fare': 'category'}
Titanic = pd.read_csv(dir_path + 'titanic.csv', dtype=Titanic_dtype)   # Step 1
print(Titanic.dtypes)                                                  # Step 2
Titanic.to_pickle(dir_path + 'Titanic.pkl')                            # Step 3
print(pd.read_pickle(dir_path + 'Titanic.pkl').dtypes)                 # Step 4：同 Step 2 一樣
Titanic.to_csv(dir_path + 'myTitanic.csv', index=False)                # Step 5
print(pd.read_csv(dir_path + 'myTitanic.csv').dtypes)                  # Step 6：dtypes 變返推斷值

Step 7 observation：Pickle 讀返嚟 dtypes 完全一樣 (string / category 保留)；CSV 讀返嚟 pandas 重新推斷 — 例如 string 變返 object，category 嘅 Pclass 變 int64，Fare / Age 變 float64 (具體結果視乎數據)。原因：CSV 只係純文字，冇記錄 dtype 資訊。

4. Combining DataFrames: concat vs merge

兩種方法，視乎 DataFrames 係咪有完全或部分相同嘅 columns / indices。

4.1 pd.concat()

pandas.concat(objs, axis=0, join='outer', ignore_index=False, keys=None,
              levels=None, names=None, verify_integrity=False, sort=False, copy=True)

df1 = pd.DataFrame({'account_id': [1, 2, 3, 4],
                    'gender': ['female', 'male', 'female', 'male'],
                    'age': [55, 25, 29, 39]})
df2 = pd.DataFrame({'account_id': [4, 5, 6, 7],
                    'gender': ['female', 'male', 'female', 'male'],
                    'age': [19, 28, 14, 15]})

pd.concat([df1, df2], axis=0)            # 上下疊 rows，保留原 index → 0,1,2,3,0,1,2,3
pd.concat([df1, df2], ignore_index=True) # 上下疊，重新編 index → 0…7
pd.concat([df1, df2], axis=1)            # 左右並排 columns (按 index 對齊) → 4 rows × 6 columns
#    account_id  gender  age  account_id  gender  age
# 0           1  female   55           4  female   19
# ...

join 參數 (default 'outer') 決定 另一條 axis 上 labels 唔一致時點處理：

a = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
b = pd.DataFrame({'B': [5, 6], 'C': [7, 8]})
pd.concat([a, b])                 # outer：columns A, B, C；冇嘅位填 NaN
pd.concat([a, b], join='inner')   # inner：只保留共同 column B

4.2 pd.merge()

pandas.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
             left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'),
             copy=True, indicator=False, validate=None)

left = pd.DataFrame({'col1': [1, 2, 3, 4, 5], 'col2': ['a', 'b', 'c', 'd', 'e']})
right = pd.DataFrame({'col1': [3, 4, 5, 6, 7], 'col3': ['f', 'g', 'h', 'i', 'j']})
right2 = pd.DataFrame({'col4': [3, 4, 5, 6, 7], 'col3': ['f', 'g', 'h', 'i', 'j']})

pd.merge(left, right)        # 冇 on → 自動用共同 column col1；default inner
#    col1 col2 col3
# 0     3    c    f
# 1     4    d    g
# 2     5    e    h
pd.merge(left, right2, left_on='col1', right_on='col4')   # key 名唔同
#    col1 col2  col4 col3
# 0     3    c     3    f
# 1     4    d     4    g
# 2     5    e     5    h

4.3 how: inner / outer / left / right / cross

pd.merge(left, right, on='col1', how='inner')   # 同 default 一樣
pd.merge(left, right, on='col1', how='outer')
#    col1 col2 col3
# 0     1    a  NaN
# 1     2    b  NaN
# 2     3    c    f
# 3     4    d    g
# 4     5    e    h
# 5     6  NaN    i
# 6     7  NaN    j
pd.merge(left, right, on='col1', how='left')    # col1 = 1,2,3,4,5
pd.merge(left, right, on='col1', how='right')   # col1 = 3,4,5,6,7
pd.merge(left, right, how='cross')              # 25 rows；兩邊都有 col1 → 變 col1_x / col1_y

Lab Q4: Data combining (superheroes)

# Q4.1 載入 pickle，沿 rows concat 並重新編 index
hero_dc = pd.read_pickle(dir_path + 'hero_dc.pkl')
hero_marvel = pd.read_pickle(dir_path + 'hero_marvel.pkl')
hero_info = pd.concat([hero_dc, hero_marvel], ignore_index=True)
# Q4.2
hero_info.shape           # (603, 9)  = 215 + 388 rows
# Q4.3
hero_power = pd.read_csv(dir_path + 'superhero_powers.csv', dtype={'hero_names': 'string'})
print(hero_info.columns)  # name, Gender, ..., Weight
print(hero_power.columns) # hero_names, Agility, ..., Omniscient (168 個)
# Q4.4 名稱 column 唔同名 → left_on / right_on；inner 只保留兩邊都有嘅 heroes
hero_merged = pd.merge(hero_info, hero_power, left_on='name', right_on='hero_names', how='inner')
hero_merged.head(2)       # 2 rows × 177 columns (9 + 168)
# Q4.6
hero_concat = pd.concat([hero_info, hero_power], axis=1, join='inner')
print(f'there are {hero_merged.shape[0]} rows in hero_merged and {hero_concat.shape[0]} rows in hero_concat')
# there are 537 rows in hero_merged and 603 rows in hero_concat

5. Renaming columns

5.1 String accessor .str

hero.columns 返回一個 Index object；.str 係 string accessor，令你可以對 Series / Index 嘅每個元素 (element-wise) 用 string methods。

hero = pd.read_pickle(dir_path + 'hero.pkl')
hero.columns.str.upper()        # 'NAME', 'GENDER', 'EYE COLOR', ... 全大寫
hero.columns.str.lower()        # 全小寫
hero.columns.str.title()        # 每個字首字母大寫：'Eye Color'
hero.columns.str.capitalize()   # 只係第一個字首字母大寫：'Eye color', 'Hero_names'
hero.columns                    # ← 仍然係原本！上面全部只係返回新 Index，冇改原本
hero.columns = hero.columns.str.lower()   # 要 assign 返先會更新

5.2 DataFrame.rename()

DataFrame.rename(mapper=None, *, index=None, columns=None, axis=None, copy=True, inplace=False, ...)

mapper / index / columns 可以係 dict (舊名 → 新名) 或者一個 function (會 apply 落每個 label)。

hero = hero.rename(columns={'hero_names': 'hero names'})   # dict：只改指定 column
hero = hero.rename(columns=str.upper)                      # function：所有 column 名大寫
hero = hero.rename(mapper=str.lower, axis='columns')       # 等價：mapper + axis
hero.columns = hero.columns.str.lower()
hero.to_pickle(dir_path + 'hero.pkl')

5.2.1 Lambda as mapper

hero.rename(mapper=lambda x: x.lower(), axis='columns')   # 全小寫
hero.rename(mapper=lambda x: x[:-1], axis='columns')      # 刪每個 column 名最後一個字元 → 'nam', 'gende', ...
hero.rename(mapper=lambda x: x.upper(), axis='columns')   # 全大寫 ✓
hero.rename(mapper=lambda x: x.upper, axis='columns')     # ✗ column 名變成 "<built-in method upper of str object at 0x...>"

注意：以上冇 assign 返 hero = ...，所以只係顯示結果，冇改到 hero。

hero.rename(mapper=lambda x: x.upper,   axis='columns')  # ✗ wrong
hero.rename(mapper=lambda x: x.upper(), axis='columns')  # ✓ correct
hero.rename(mapper=str.upper,           axis='columns')  # ✓ correct
hero.rename(mapper=str.upper(),         axis='columns')  # ✗ wrong

6. Main take-aways

• Lambda functions
• pandas package
• DataFrame and Series structures
• Reading and writing data (CSV, Excel, Pickle)
• Data combining: difference between concat and merge
• Columns renaming

How to submit：將題目 copy 去新 notebook 作答，交 question-answer-only notebook 嘅 PDF 版本去 Moodle，確保 code 同 output cells 清楚印出。

自我測驗 Self-test

一頁總結 Cheat sheet