Scrubbing Data - Data Science at the Command Line - page 76

| 2 | Danny Elfman | 1953 |
| 3 | James Horner | 1953 |
| 4 | Howard Shore | 1946 |
| 5 | Hans Zimmer | 1957 |
|-----+---------------+-------|
The cols approach in combination with tr :
$ < names.csv cols -c first_name,last_name tr \" , \" \" \" |
> header -r full_name,id,born | csvcut -c id,full_name,born | csvlook
|-----+---------------+-------|
| id | full_name | born |
|-----+---------------+-------|
| 1 | John Williams | 1932 |
| 2 | Danny Elfman | 1953 |
| 3 | James Horner | 1953 |
| 4 | Howard Shore | 1946 |
| 5 | Hans Zimmer | 1957 |
|-----+---------------+-------|
Note that csvsql employs SQLite as the database to execute the query and that ||
stands for concatenation:
$ < names.csv csvsql --query "SELECT id, first_name || ' ' || last_name " \
> "AS full_name, born FROM stdin" | csvlook
|-----+-----------------------+-------|
| id | full_name | born |
|-----+-----------------------+-------|
| 1 | John Williams | 1932 |
| 2 | Danny Elfman | 1953 |
| 3 | James Horner | 1953 |
| 4 | Howard Shore | 1946 |
| 5 | Hans Zimmer | 1957 |
|-----+-----------------------+-------|
What if last_name contained a comma? Let's have a look at the raw input CSV for
clarity's sake:
$ cat names-comma.csv
id,last_name,first_name,born
1,Williams,John,1932
2,Elfman,Danny,1953
3,Horner,James,1953
4,Shore,Howard,1946
5,Zimmer,Hans,1957
6,"Beethoven, van",Ludwig,1770
Well, it appears that the first three approaches fail; all in different ways. Only csvsql
is able to combine first_name and full_name properly:
$ < names-comma.csv sed -re '1s/.*/id,full_name,born/g;' \
> '2,$s/(.*),(.*),(.*),(.*)/\1,\3 \2,\4/g' | tail -n 1
6,"Beethoven,Ludwig van",1770