2025年09月06日玄貓（BlackCat）

命令列工具CSV資料清理與轉換

本文介紹如何使用命令列工具進行資料清洗與轉換，特別針對 CSV 檔案的處理技巧。文章涵蓋了資料格式轉換、篩選、提取、取代、分割、合併等操作，並搭配 `grep`、`awk`、`jq`、`pup` 等實用工具，以及 `body`、`header`、`cols` 和 `csvsql` 等 CSV

資料科學資料工程

資料清洗 CSV 命令列 awk grep 資料轉換

資料清洗是資料科學流程中不可或缺的環節，良好的資料品質是確保後續分析準確性的關鍵。本文將探討如何利用命令列工具，特別是針對 CSV 檔案，進行高效的資料清理和轉換作業。從基礎的資料格式轉換到進階的欄位操作，文章將逐步引導讀者掌握各種實用技巧。我們將會運用 grep、awk 等經典工具，以及 jq、pup 等新興工具，搭配 body、header、cols 和 csvsql 等專為 CSV 檔案設計的工具，示範如何篩選、提取、取代、分割、合併資料，並提供清晰的程式碼範例與解說，協助讀者在實際應用中提升資料處理效率，為後續的資料分析和建模奠定堅實基礎。

資料清洗

在第三章中，我們探討了OSEMN資料科學模型的取得資料步驟，本章將著重於第二步驟：資料清洗。實際上，很少有資料可以直接從取得階段進入探索或建模階段，資料通常需要先進行清洗或格式轉換。

為什麼需要資料清洗？

首先，資料可能不具備所需的格式。例如，從API取得的JSON資料需要轉換成CSV格式以便進行視覺化處理。常見的資料格式包括純文字、HTML和XML等多種形式。大多數命令列工具僅支援一到兩種格式，因此能夠在不同格式間轉換資料至關重要。

即使資料格式正確，仍可能存在缺失值、不一致性、特殊字元或不必要的部分。這些問題可以透過套用篩選器、取代值和合併多個檔案來解決。命令列非常適合進行這類別轉換，因為有許多專門的工具可用於處理大量資料。本章將介紹經典工具如grep和awk，以及較新的工具如jq和pup。

本章學習目標

將資料從一種格式轉換為另一種格式
直接對CSV檔案套用SQL查詢
篩選行
提取和取代值
分割、合併和提取列
合併多個檔案

本章將從以下檔案開始：

$ cd /data/ch05
$ ls -l
total 200K
-rw-r--r-- 1 dst dst 164K Jun 29 14:28 alice.txt
-rw-r--r-- 1 dst dst 4.5K Jun 29 14:28 iris.csv
-rw-r--r-- 1 dst dst 179 Jun 29 14:28 irismeta.csv
-rw-r--r-- 1 dst dst 160 Jun 29 14:28 names-comma.csv
-rw-r--r-- 1 dst dst 129 Jun 29 14:28 names.csv
-rw-r--r-- 1 dst dst 7.8K Jun 29 14:28 tips.csv
-rw-r--r-- 1 dst dst 5.1K Jun 29 14:28 users.json

資料轉換無所不在

在第一章中，我們提到OSEMN模型的步驟在實際操作中很少是線性執行的。雖然資料清洗是第二步，但本章所學的轉換技術可以在OSEMN模型的任何步驟中使用。通常，如果一個命令列工具的輸出可以直接被下一個工具使用，那麼這兩個工具就可以透過管道運算元（|）串聯起來。否則，需要先對資料進行轉換。

示例：Fizz Buzz序列分析

假設我們需要視覺化Fizz Buzz序列中「fizz」、「buzz」和「fizzbuzz」出現的頻率。首先，我們生成序列並將其寫入fb.seq：

$ seq 100 | /data/ch04/fizzbuzz.py | tee fb.seq | trim
1
2
fizz
4
buzz
fizz
7
8
fizz
buzz
... with 90 more lines

接下來，使用grep篩選出包含「fizz」或「buzz」的行，並使用sort和uniq統計每個詞出現的次數：

$ grep -E "fizz|buzz" fb.seq | sort | uniq -c | sort -nr > fb.cnt
$ bat -A fb.cnt
───────┬────────────────────────────────────────────────────────────────────────
│ File: fb.cnt
───────┼────────────────────────────────────────────────────────────────────────
1 │ ·····27·fizz␊
2 │ ·····14·buzz␊
3 │ ······6·fizzbuzz␊
───────┴────────────────────────────────────────────────────────────────────────

使用awk進行資料轉換

為了將fb.cnt轉換為CSV格式，我們可以使用awk新增標頭、交換欄位並插入逗號：

$ < fb.cnt awk 'BEGIN { print "value,count" } { print $2","$1 }' > fb.csv
$ bat fb.csv
───────┬────────────────────────────────────────────────────────────────────────
│ File: fb.csv
───────┼────────────────────────────────────────────────────────────────────────
1 │ value,count
2 │ fizz,27
3 │ buzz,14
4 │ fizzbuzz,6
───────┴────────────────────────────────────────────────────────────────────────

#### 內容解密：

此處的awk指令做了三件事：

BEGIN { print "value,count" }：在處理任何輸入之前，先輸出CSV檔案的標頭。
{ print $2","$1 }：對於每一行輸入，將第二個欄位（單詞）和第一個欄位（計數）交換，並用逗號分隔。
整個流程將fb.cnt的內容轉換為CSV格式，儲存在fb.csv中。

最後，我們可以使用rush根據CSV檔案建立柱狀圖：

$ rush plot -x value -y count --geom col --height 2 fb.csv > fb.png
$ display fb.png

這個範例展示了在命令列操作中常見的模式：關鍵工具（如取得資料、建立視覺化或訓練模型）通常需要中間轉換步驟，以便將資料轉換為適當的格式以供下一個工具使用。

資料清理與文書處理

在進行資料處理和分析時，資料清理是非常重要的一步。好的資料清理流程可以確保後續分析的準確性和效率。本篇文章將介紹如何使用 Unix 命令列工具來清理和處理文字資料。

純文字的優勢

純文字是指一系列人類可讀的字元，以及一些特定的控制字元，如製表符和換行符。純文字具有許多優點，包括：

可以使用任何文字編輯器開啟、編輯和儲存
自我描述且獨立於建立它的應用程式
不需要額外的知識或應用程式即可處理

Unix 哲學認為純文字是命令列工具之間的通用介面。這意味著大多數工具接受純文字作為輸入，並產生純文字作為輸出。

篩選行

篩選行是資料清理的第一步。這涉及評估輸入資料的每一行，以決定是否保留或丟棄它。

根據位置篩選

最直接的篩選方法是根據行的位置。例如，若要檢查檔案的前 10 行，可以使用 head 命令：

$ seq -f "Line %g" 10 | tee lines
$ < lines head -n 3
Line 1
Line 2
Line 3

根據模式篩選

有時需要根據行的內容來篩選。grep 是用於篩選行的典型命令列工具。例如，若要從《愛麗絲夢遊仙境》中提取所有章節標題：

$ < alice.txt grep -i chapter
CHAPTER I. Down the Rabbit-Hole
CHAPTER II. The Pool of Tears
...

根據隨機性篩選

在除錯資料管道時，生成較小的樣本資料可能很有用。這時可以使用 sample 工具來輸出輸入資料的一定百分比：

$ < data.txt sample -r 0.1

使用 Plantuml 圖表呈現流程

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 命令列工具CSV資料清理與轉換

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示說明瞭資料清理流程中的篩選步驟。

程式碼範例與解析

使用 `sed` 命令篩選行

$ < lines sed -n '1,3p'
Line 1
Line 2
Line 3

內容解密：

sed 是一個流編輯器，用於過濾和轉換文字。
-n 選項禁止自動列印模式空間。
1,3p 表示列印第 1 到第 3 行。

使用 `awk` 命令篩選行

$ < lines awk 'NR <= 3'
Line 1
Line 2
Line 3

內容解密：

awk 是一種程式語言，用於處理文字資料。
NR 是內建變數，表示目前已讀取的行號。
NR <= 3 表示列印行號小於或等於 3 的行。

命令列工具在資料清理中的應用

在處理大量文字資料時，命令列工具提供了強大的資料清理功能。本文將介紹如何使用 sample、cut、grep、tr 和 sed 等工具來清理和轉換資料。

抽樣資料

使用 sample 工具可以從大量資料中抽取樣本。例如，從 1000 行資料中抽取 1% 的樣本：

$ seq -f "Line %g" 1000 | sample -r 1%
Line 50
Line 159
Line 173
Line 682
Line 882
Line 921
Line 986

內容解密：

seq -f "Line %g" 1000：產生從 1 到 1000 的序列，並格式化為 “Line %g”。
sample -r 1%：從輸入資料中隨機抽取 1% 的行。

新增時間戳記

使用 ts 工具可以在每行輸出前新增時間戳記：

$ seq -f "Line %g" 1000 | sample -r 1% -d 1000 -s 5 | ts
Jun 29 14:28:50 Line 28
Jun 29 14:28:51 Line 262
Jun 29 14:28:52 Line 324
Jun 29 14:28:53 Line 546
Jun 29 14:28:54 Line 589
Jun 29 14:28:55 Line 613
Jun 29 14:28:56 Line 629

內容解密：

-d 1000：在每行輸出之間新增 1 秒的延遲。
-s 5：執行 sample 命令 5 秒後停止。
ts：在每行輸出前新增時間戳記。

提取特定欄位

使用 cut 工具可以從每行資料中提取特定的欄位。例如，從 alice.txt 中提取章節標題：

$ grep -i chapter alice.txt | cut -d ' ' -f 3-
Down the Rabbit-Hole
The Pool of Tears
A Caucus-Race and a Long Tale
...

內容解密：

grep -i chapter alice.txt：搜尋包含 “chapter” 的行，不區分大小寫。
cut -d ' ' -f 3-：以空格為分隔符號，提取第 3 個欄位及其後的欄位。

使用 `grep` 和 `tr` 處理文字

使用 grep 和 tr 可以進行更複雜的文書處理。例如，提取以 “a” 開頭並以 “e” 結尾的單字：

$ < alice.txt tr '[:upper:]' '[:lower:]' | grep -oE '\w{2,}' | grep -E '^a.*e$' | sort | uniq | sort -nr | trim
available
ate
assistance
askance
arise
argue
are
archive
applicable
apple
...

內容解密：

tr '[:upper:]' '[:lower:]'：將所有大寫字母轉換為小寫字母。
grep -oE '\w{2,}'：提取所有長度大於等於 2 的單字。
grep -E '^a.*e$'：提取以 “a” 開頭並以 “e” 結尾的單字。
sort | uniq | sort -nr：對單字進行排序、去重複，並按反向順序排序。

取代和刪除字元

使用 tr 和 sed 可以取代或刪除特定的字元。例如，將空格取代為底線：

$ echo 'hello world!' | tr ' ' '_'
hello_world!

內容解密：

tr ' ' '_'：將空格取代為底線。

使用 sed 可以進行更複雜的取代操作，例如，將 “hello” 取代為 “bye”：

$ echo ' hello world!' | sed -re 's/hello/bye/'
 bye world!

內容解密：

sed -re 's/hello/bye/'：將 “hello” 取代為 “bye”。

使用命令列工具處理CSV檔案

在處理CSV檔案時，常常需要使用一些特定的命令列工具來進行資料清理和操作。這些工具能夠幫助我們高效地處理CSV檔案中的資料。

body、header和cols工具介紹

為了能夠在CSV檔案上使用普通的命令列工具，Jeroen Janssens開發了三個非常有用的命令列工具：body、header和cols。

body工具

body工具允許我們將任何命令列工具應用於CSV檔案的主體部分，也就是除了標頭以外的所有資料。例如：

$ echo -e "value\n7\n2\n5\n3" | body sort -n
value
2
3
5
7

這個範例中，body工具將sort -n命令應用於輸入資料的主體部分，並保持標頭不變。

header工具允許我們操作CSV檔案的標頭。如果沒有提供任何引數，它會列印出CSV檔案的標頭：

$ < tips.csv header
bill,tip,sex,smoker,day,time,size

我們也可以使用-a選項來新增標頭，或使用-d選項來刪除標頭：

$ seq 5 | header -a count
count
1
2
3
4
5

$ < iris.csv header -d | trim
5.1,3.5,1.4,0.2,Iris-setosa
4.9,3.0,1.4,0.2,Iris-setosa
...

cols工具

cols工具允許我們將特定的命令應用於CSV檔案中的某個或某些欄位。例如，如果我們想要將Tips資料集中的day欄位的值轉換為大寫，可以使用以下命令：

$ < tips.csv cols -c day body "tr '[a-z]' '[A-Z]'" | head -n 5 | csvlook
│ day │ bill │ tip │ sex │ smoker │ time │ size │
├────────────┼───────┼──────┼────────┼────────┼────────┼──────┤
│ 0001-01-07 │ 16.99 │ 1.01 │ Female │ False │ Dinner │ 2 │
│ 0001-01-07 │ 10.34 │ 1.66 │ Male │ False │ Dinner │ 3 │
│ 0001-01-07 │ 21.01 │ 3.50 │ Male │ False │ Dinner │ 3 │
│ 0001-01-07 │ 23.68 │ 3.31 │ Male │ False │ Dinner │ 2 │

csvsql工具

除了上述三個工具以外，Christopher Groskopf開發的csvsql工具也非常有用。它允許我們直接在CSV檔案上執行SQL查詢：

$ seq 5 | header -a val | csvsql --query "SELECT SUM(val) AS sum FROM stdin"
sum
15.0

這個範例中，csvsql工具對輸入的CSV資料執行了一個SQL查詢，計算了val欄位的總和。

CSV 資料處理與操作

在處理 CSV 檔案時，經常需要進行資料的提取、重新排序、篩選和合併等操作。本文將介紹如何使用命令列工具如 csvcut、csvgrep、awk 和 csvsql 來完成這些任務。

提取和重新排序欄位

使用 csvcut 工具可以輕鬆地提取和重新排序 CSV 檔案中的欄位。例如，若要從 Iris 資料集中提取數值欄位並重新排序，可以使用以下命令：

$ < iris.csv csvcut -c sepal_length,petal_length,sepal_width,petal_width | csvlook

內容解密：

csvcut 是一個用於提取和重新排序 CSV 欄位的工具。
-c 選項後面跟著要提取的欄位名稱。
csvlook 用於將輸出格式化為表格。

或者，也可以使用欄位的索引來提取欄位：

$ echo 'a,b,c,d,e,f,g,h,i\n1,2,3,4,5,6,7,8,9' | csvcut -c $(seq 1 2 9 | paste -sd,)

內容解密：

seq 1 2 9 生成從 1 到 9 的奇數序列。
paste -sd, 將序列轉換為逗號分隔的字串。
csvcut 根據指定的索引提取對應的欄位。

篩選資料列

篩選 CSV 檔案中的資料列可以根據特定的欄位值進行。使用 csvgrep、awk 或 csvsql 都可以實作這一功能。例如，若要排除 tips.csv 中 size 小於 5 的資料列，可以使用：

$ csvgrep -c size -i -r "[1-4]" tips.csv

內容解密：

csvgrep 用於根據正規表示式篩選 CSV 資料列。
-c size 指定要篩選的欄位為 size。
-i 表示反向匹配，即排除符合條件的資料列。
-r "[1-4]" 指定正規表示式，匹配 size 為 1 到 4 的資料列。

或者，使用 awk 進行數值比較篩選：

$ < tips.csv awk -F, 'NR==1 || ($1 > 40.0) && ($5 ~ /^S/)'

內容解密：

awk 是一種強大的文書處理工具。
-F, 指定欄位分隔符為逗號。
NR==1 表示保留第一行（標題行）。
$1 > 40.0 和 $5 ~ /^S/ 分別表示篩選條件：第一欄大於 40 且第五欄以 S 開頭。

合併欄位

當資料分散在多個欄位中時，可能需要將它們合併。例如，將 names.csv 中的 first_name 和 last_name 合併為 full_name：

$ < names.csv sed -re '1s/.*/id,full_name,born/g;2,$s/(.*),(.*),(.*),(.*)/\1,\3 \2,\4/g' | csvlook -I

內容解密：

sed 用於字串替換和處理。
第一條 sed 命令替換標題行為新的欄位名稱。
第二條 sed 命令使用正規表示式和反向參照將 first_name 和 last_name 合併，並重新排序。

或者，使用 awk 或 csvsql 也能達到相同的效果：

$ < names.csv awk -F, 'BEGIN{OFS=","; print "id,full_name,born"} {if(NR > 1) {print $1,$3" "$2,$4}}' | csvlook -I

內容解密：

awk 的 BEGIN 區塊用於初始化輸出欄位分隔符（OFS）並列印新的標題行。
主區塊中的邏輯用於合併和重新排列資料欄位。

這些命令列工具提供了靈活且強大的方式來處理和操作 CSV 資料。根據具體需求選擇合適的工具和方法，可以大大提高資料處理的效率和準確性。

資料清洗

為什麼需要資料清洗？

本章學習目標

資料轉換無所不在

示例：Fizz Buzz序列分析

使用awk進行資料轉換

#### 內容解密：

資料清理與文書處理

純文字的優勢

篩選行

根據位置篩選

根據模式篩選

根據隨機性篩選

使用 Plantuml 圖表呈現流程

此圖示說明瞭資料清理流程中的篩選步驟。

程式碼範例與解析

使用 sed 命令篩選行

內容解密：

使用 awk 命令篩選行

內容解密：

命令列工具在資料清理中的應用

抽樣資料

內容解密：

新增時間戳記

內容解密：

提取特定欄位

內容解密：

使用 grep 和 tr 處理文字

內容解密：

取代和刪除字元

內容解密：

內容解密：

使用命令列工具處理CSV檔案

body、header和cols工具介紹

body工具

header工具

cols工具

csvsql工具

CSV 資料處理與操作

提取和重新排序欄位

內容解密：

內容解密：

篩選資料列

內容解密：

內容解密：

合併欄位

內容解密：

內容解密：

使用 `sed` 命令篩選行

使用 `awk` 命令篩選行

使用 `grep` 和 `tr` 處理文字