CSV Units Conversion

Question

I've got a CSV file that looks like the following:

miami,20,in
lansing,2,cm
austin,3,mm
chicago,5,miles
phoenix,2,feet

The first field is the name, the next field is the numeric value and the third field indicates the units. I've listed all the units that my dataset currently has however it is not limited to these as I expect the dataset to change and require different units of measurement over time.

I want to be able to process this CSV file in a script so that all my values are converted to one common unit, inches, and are listed alongside the name. So it should look something like this:

miami,20
lansing,0.78740157
austin,0.11811024
chicago,316800
phoenix,24

I've figured out that there's a unit conversion tool units which is precisely what I'm looking for to use to convert my units as my data contains a mixture of metric and imperial units.

For example, if I wanted to convert my last line of data into inches I would do this

units 2cm in -t

Which would give me

0.78740157

What I'm currently seeking help on is how to go about using this command in a script and outputting it in the format I described (2nd code block). The CSV data is currently stored in a variable called $citydata in a bash script. It is currently a string.

What units do you have in your input file? Is it only centimeters and inches or does the script need to deal with more units? What type of variable is `$citydata`? An array or a string? — terdon, Mar 26 '15 at 11:08
I have a variety of units including centimeters, millimeters, miles, feet, inches. It's also not exclusive to that list as I do expect for this dataset to grow over time. `citydata` is currently stored as a strong. — Varludea, Mar 26 '15 at 11:13

score 1 · Answer 1 · answered Mar 26 '15 at 11:13

You could do something like this:

#!/usr/bin/env bash

## The data is in the $citydata array
citydata=( $(cat file) )

## For each element of the array
for i in "${citydata[@]}"
do
    ## Read the fields, splitting on commas
    IFS="," read -r city val unit <<<"$i"
    ## Print out the city and the value converted to inches
    echo "$city,$(units $val$unit in -t)"
done

Given the data in your example, the above produces

miami,20
lansing,0.78740157

NOTES

The script expects every line to consist of three comma-separated fields.
Nested commas will break it, for example "Washington, DC",20,cm.
It can deal with any unit that units can convert.

αғsнιη · Accepted Answer · 2015-03-26T11:57:42.743

Here is one linear awk command:

$ awk -F, '{system("printf " $1 ",;units " $2$NF " in -t ")}'  file
miami,20
lansing,0.78740157
austin,0.11811024
chicago,316800
phoenix,24

system command calls the another shell commands within awk command. So in first that prints the first field of SCV file by "printf " $1 ", followed by a single coma ,; then calls the units command with second $2 and last $NF fields as its input.

Note that when you want to use system command within awk, you have to enclose those commands and their options with double quotes around, except awk options in it.

Costas · Answer 3 · 2015-03-26T22:16:01.570

0

If you'd like to use units function it easy done with awk

awk -F, -v OFS=, '{"units "$(NF-1)$NF" in -t"|getline $(NF-1);NF=NF-1}1'

edited Mar 26 '15 at 22:16

answered Mar 26 '15 at 11:12

Costas

14,806
20
36

CSV Units Conversion

3 Answers3

NOTES