Database Reference
In-Depth Information
$
csvlook irismeta.csv
|------------------+----------------------------------------------+----------|
| species | wikipedia_url | usda_id |
|------------------+----------------------------------------------+----------|
| Iris-versicolor | http://en.wikipedia.org/wiki/Iris_versicolor | IRVE2 |
| Iris-virginica | http://en.wikipedia.org/wiki/Iris_virginica | IRVI |
| Iris-setosa | | IRSE |
|------------------+----------------------------------------------+----------|
What this data set and the Iris data set have in common is the
species
column. We
can use
csvjoin
(Groskopf, 2014) to join the two data sets:
$
csvjoin -c species iris.csv irismeta.csv | csvcut -c sepal_length,
\
>
sepal_width,species,usda_id | sed -n
'1p;49,54p'
| csvlook
|---------------+-------------+-----------------+----------|
| sepal_length | sepal_width | species | usda_id |
|---------------+-------------+-----------------+----------|
| 4.6 | 3.2 | Iris-setosa | IRSE |
| 5.3 | 3.7 | Iris-setosa | IRSE |
| 5.0 | 3.3 | Iris-setosa | IRSE |
| 7.0 | 3.2 | Iris-versicolor | IRVE2 |
| 6.4 | 3.2 | Iris-versicolor | IRVE2 |
| 6.9 | 3.1 | Iris-versicolor | IRVE2 |
|---------------+-------------+-----------------+----------|
Of course we can also use the SQL approach using
csvsql
, which is, as usual, a bit
longer (but potentially much more flexible):
$
csvsql --query
'SELECT i.sepal_length, i.sepal_width, i.species, m.usda_id '
\
>
'FROM iris i JOIN irismeta m ON (i.species = m.species)'
\
>
iris.csv irismeta.csv | sed -n
'1p;49,54p'
| csvlook
|---------------+-------------+-----------------+----------|
| sepal_length | sepal_width | species | usda_id |
|---------------+-------------+-----------------+----------|
| 4.6 | 3.2 | Iris-setosa | IRSE |
| 5.3 | 3.7 | Iris-setosa | IRSE |
| 5.0 | 3.3 | Iris-setosa | IRSE |
| 7.0 | 3.2 | Iris-versicolor | IRVE2 |
| 6.4 | 3.2 | Iris-versicolor | IRVE2 |
| 6.9 | 3.1 | Iris-versicolor | IRVE2 |
|---------------+-------------+-----------------+----------|
Further Reading
• Molinaro, A. (2005).
SQL Cookbook
. O'Reilly Media.
• Goyvaerts, J., & Levithan, S. (2012).
Regular Expressions Cookbook
(2nd Ed.)
.
O'Reilly Media.
