Xử lý CSV data với csvkit trên giao diện dòng lệnh

Linux

111

csv

3

Quăng viết ngày 05/06/2016

Mình là fan của Linux, nên mình dùng Linux cho tất cả các máy tính của mình từ laptop cá nhân, desktop trên cty hoặc server. Nhưng có một điều mình chưa bao giờ hài lòng là bộ Office trên Linux như OpenOffice hoặc LibreOffice.

Mình luôn lựa chọn các giải pháp như cài M$ Office trên máy ảo vbox hoặc dùng Google Drive. Nhưng dù sao thì với việc xử lý các file CSV cũng là khá thường xuyên (chủ yếu là view, cut, grep ...) thì không nhất thiết phải sử dụng tới các công cụ như trên.

Hôm nay mình có đọc 1 bài gới thiệu về csvkit thấy nó khá hay và tiện dụng nhất là việc xử lý các dữ liệu trên giao diện dòng lệnh của server tại .

Bài này là một bài lược dịch của bài đó, hi vọng nó hữu ích cho các bạn nếu gặp vấn đề xử lý CSV như mình.

Trước khi biết tới csvkit thì mình vẫn dùng sed, grep, cut, tr hoặc awk để xử lý các dữ liệu CSV.

Ví dụ với file CSV thì các thao tác mình thường hay phải xử lý là lấy một hoặc nhiều cột dữ liệu hoặc lọc lấy một vài dòng dữ liệu theo một điều kiện nào đó

1. Vấn đề cũ

Ví dụ mình cần lấy tên của một nghệ sĩ và quốc tịch của họ, thử xem dữ liệu có cấu trúc như thế nào bằng cách xem vài dòng dữ liệu

~$ head -n 5 Artists.csv

ConstituentID,DisplayName,ArtistBio,Nationality,Gender,BeginDate,EndDate,Wiki QID,ULAN
1,Robert Arneson,"American, 1930–1992",American,Male,1930,1992,,
2,Doroteo Arnaiz,"Spanish, born 1936",Spanish,Male,1936,0,,
3,Bill Arnold,"American, born 1941",American,Male,1941,0,,
4,Charles Arnoldi,"American, born 1946",American,Male,1946,0,Q1063584,500027998

Như ta thấy phần header gồm các cột

ConstituentID
DisplayName
ArtistBio
Nationality
Gender
BeginDate
EndDate 
Wiki QID
ULAN

Giờ để lấy tên và quốc tịch thì cần lấy cột số 2 và số 4. Có thể sử dụng các cách sau

~$ awk -F',' '{print $2 "," $4} ' Artists.csv | head 
DisplayName,Nationality
Robert Arneson, 1930–1992"
Doroteo Arnaiz, born 1936"
Bill Arnold, born 1941"
Charles Arnoldi, born 1946"
Per Arnoldi, born 1941"
Danilo Aroldi, born 1925"
Bill Aron, born 1941"
David Aronson, born Lithuania 1923"
Irene Aronson, born Germany 1918"

Hmm, nhìn kết quả có gì đó không đúng. Format của file csv là mỗi cột dữ liệu phân cách nhau bằng dấu phẩy , nhưng thật xui là trong cột số 3 - ArtistBio, nội dung lại có chứa dấu ,. Tất nhiên ta có thể chuyển sang lấy cột số 5 thay vì 4, nhưng xui mà nội dung trong cột số 3 mà không chứa dấu , thì lại coi như lại sai dữ liệu.

Tương tự với cut

~$ cut -d',' -f2,4 Artists.csv | head
DisplayName,Nationality
Robert Arneson, 1930–1992"
Doroteo Arnaiz, born 1936"
Bill Arnold, born 1941"
Charles Arnoldi, born 1946"
Per Arnoldi, born 1941"
Danilo Aroldi, born 1925"
Bill Aron, born 1941"
David Aronson, born Lithuania 1923"
Irene Aronson, born Germany 1918"

Nói chung việc xử lý các tệp CSV với awk, sed, grep rất cực khổ và nếu có quá nhiều cột thì nhìn cũng rất loạn. Giờ thử với csvkit xem làm được gì.

2. csvkit

Ta có thể dễ dàng cài đặt thông qua pip bằng cách pip install csvkit

2.1 csvlook

Thử xem vài dòng dữ liệu xem nào

~$  head -n 5 Artists.csv | csvlook
|-----------------+-----------------+---------------------+-------------+--------+-----------+---------+----------+------------|
|   ConstituentID | DisplayName     | ArtistBio           | Nationality | Gender | BeginDate | EndDate | Wiki QID | ULAN       |
|-----------------+-----------------+---------------------+-------------+--------+-----------+---------+----------+------------|
|  1              | Robert Arneson  | American, 1930–1992 | American    | Male   | 1930      | 1992    |          |            |
|  2              | Doroteo Arnaiz  | Spanish, born 1936  | Spanish     | Male   | 1936      | 0       |          |            |
|  3              | Bill Arnold     | American, born 1941 | American    | Male   | 1941      | 0       |          |            |
|  4              | Charles Arnoldi | American, born 1946 | American    | Male   | 1946      | 0       | Q1063584 | 500027998  |
|-----------------+-----------------+---------------------+-------------+--------+-----------+---------+----------+------------|

Nhìn thấy kết quả hiện ra là đời thấy vui rồi

2.2 csvcut

Liệt kê xem có các column nào với csvcut option -n chỉ lấy tên các column

~$ csvcut -n Artists.csv
  1: ConstituentID
  2: DisplayName
  3: ArtistBio
  4: Nationality
  5: Gender
  6: BeginDate
  7: EndDate
  8: Wiki QID
  9: ULAN

Giờ ví dụ ta cần lấy cột 2 và cột 4 như ở phần đầu, ta có thể dùng với option -c như sau

~$ head -n 6 Artists.csv | csvcut -c 2,4 | csvlook
|------------------+--------------|
|  DisplayName     | Nationality  |
|------------------+--------------|
|  Robert Arneson  | American     |
|  Doroteo Arnaiz  | Spanish      |
|  Bill Arnold     | American     |
|  Charles Arnoldi | American     |
|  Per Arnoldi     | Danish       |
|------------------+--------------|

hoặc lấy tất cả các cột trừ 2 cột 2 và 4

~$ head -n 6 Artists.csv | csvcut -C 2,4 | csvlook
|-----------------+---------------------+--------+-----------+---------+----------+------------|
|   ConstituentID | ArtistBio           | Gender | BeginDate | EndDate | Wiki QID | ULAN       |
|-----------------+---------------------+--------+-----------+---------+----------+------------|
|  1              | American, 1930–1992 | Male   | 1930      | 1992    |          |            |
|  2              | Spanish, born 1936  | Male   | 1936      | 0       |          |            |
|  3              | American, born 1941 | Male   | 1941      | 0       |          |            |
|  4              | American, born 1946 | Male   | 1946      | 0       | Q1063584 | 500027998  |
|  5              | Danish, born 1941   | Male   | 1941      | 0       |          |            |
|-----------------+---------------------+--------+-----------+---------+----------+------------|

2.3 csvgrep

Giờ nếu ta muốn lọc dữ liệu với một điều kiện nào đó, ví dụ ta muốn lọc DisplayName với điều kiện là BeginDate là năm 1932

~$ head -n 800 Artists.csv | csvcut -c 2,6 | csvgrep -c 2 -m "1932" | csvlook
|------------------+------------|
|  DisplayName     | BeginDate  |
|------------------+------------|
|  Eero Aarnio     | 1932       |
|  Stephen Ancona  | 1932       |
|  Wall Batterton  | 1932       |
|  Robert Bechtle  | 1932       |
|  Peter Blake     | 1932       |
|  Fernando Botero | 1932       |
|  Lynn Bowers     | 1932       |
|  Jürgen Brodwolf | 1932       |
|------------------+------------|

Hoặc với BeginData là những năm 193x, có thể sử dụng regex như sau

~$  head -n 80 Artists.csv | csvcut -c 2,6 | csvgrep -c 2 -r "193." | csvlook
|------------------------+------------|
|  DisplayName           | BeginDate  |
|------------------------+------------|
|  Robert Arneson        | 1930       |
|  Doroteo Arnaiz        | 1936       |
|  Jüri Arrak            | 1936       |
|  Eero Aarnio           | 1932       |
|  Magdalena Abakanowicz | 1930       |
|  Robert Abel           | 1937       |
|  Sigmund Abeles        | 1934       |
|  Raimund Abraham       | 1933       |
|  Ivor Abrahams         | 1935       |
|  Rodolfo Abularach     | 1933       |
|  Valerio Adami         | 1935       |
|  Alice Adams           | 1930       |
|  Robert Adams          | 1937       |
|  Dongkuk S. Ahn        | 1937       |
|------------------------+------------|

2.4 csvstat

Ta có thể đếm số lượng kết quả trả về tương tự như wc -l

~$ head -n 80 Artists.csv | csvcut -c 2,6 | csvgrep -c 2 -r "193." | csvstat --count
Row count: 14

Hoặc lấy các giá trị min, max, sum ...

~$  head -n 30 Artists.csv | csvcut -c 2,6 | csvstat -c 2 --min
0

~$ head -n 30 Artists.csv | csvcut -c 2,6 | csvstat -c 2 --max
1946

~$ head -n 30 Artists.csv | csvcut -c 2,6 | csvstat -c 2 --sum
46060

2.5 Others

Ngoài ra còn rất nhiều các lệnh khác ví dụ convert dữ liệu qua JSON

~$ head -n 4 Artists.csv | csvjson | jq "."
[
  {
    "ConstituentID": "1",
    "DisplayName": "Robert Arneson",
    "ArtistBio": "American, 1930–1992",
    "Nationality": "American",
    "Gender": "Male",
    "BeginDate": "1930",
    "EndDate": "1992",
    "Wiki QID": "",
    "ULAN": ""
  },
  {
    "ConstituentID": "2",
    "DisplayName": "Doroteo Arnaiz",
    "ArtistBio": "Spanish, born 1936",
    "Nationality": "Spanish",
    "Gender": "Male",
    "BeginDate": "1936",
    "EndDate": "0",
    "Wiki QID": "",
    "ULAN": ""
  },
  {
    "ConstituentID": "3",
    "DisplayName": "Bill Arnold",
    "ArtistBio": "American, born 1941",
    "Nationality": "American",
    "Gender": "Male",
    "BeginDate": "1941",
    "EndDate": "0",
    "Wiki QID": "",
    "ULAN": ""
  }
]

Hoặc convert từ file Excel với in2csv hoặc query dữ liệu với cú pháp SQL với csvsql ...

Hi vọng nó sẽ giúp ích phần nào cho mọi người khi có nhu cầu xử lý các file CSV từ giao diện dòng lệnh.