I am trying to convert a csv where the headers are keys and the values in the column are a list.
For example I have the following csv
mpg cyl disp hp drat wt qsec vs am gear carb
Mazda RX4 21 6 160 110 3.9 2.62 16.46 0 1 4 4
Mazda RX4 Wag 21 6 160 110 3.9 2.875 17.02 0 1 4 4
Datsun 710 22.8 4 108 93 3.85 2.32 18.61 1 1 4 1
I would like the following format.
{
"field1" : [Mazda RX4 ,Mazda RX4 Wag,Datsun 710],
"mpg" : [21,21,22.8],
"cyl" : [6,6,6],
"disp" : [160,160,108],
...
}
Note that the numerical values are not quoted. I am assuming that the columns all have the same type.
I am using the following jq
command.
curl https://raw.githubusercontent.com/vincentarelbundock/Rdatasets/master/csv/datasets/mtcars.csv cars.csv | head -n4 | csvtojson | jq '.'
[
{
"field1": "Mazda RX4",
"mpg": "21",
"cyl": "6",
"disp": "160",
"hp": "110",
"drat": "3.9",
"wt": "2.62",
"qsec": "16.46",
"vs": "0",
"am": "1",
"gear": "4",
"carb": "4"
},
{
"field1": "Mazda RX4 Wag",
"mpg": "21",
"cyl": "6",
"disp": "160",
"hp": "110",
"drat": "3.9",
"wt": "2.875",
"qsec": "17.02",
"vs": "0",
"am": "1",
"gear": "4",
"carb": "4"
},
{
"field1": "Datsun 710",
"mpg": "22.8",
"cyl": "4",
"disp": "108",
"hp": "93",
"drat": "3.85",
"wt": "2.32",
"qsec": "18.61",
"vs": "1",
"am": "1",
"gear": "4",
"carb": "1"
}
]
cat <csv_data> | csvtojson | jq '. as $in | reduce (.[0] | keys_unsorted[]) as $k ( {}; .[$k] = ($in|map(.[$k])))'
Here's a concise, efficient, and conceptually simple solution based on just map
and reduce
:
. as $in
| reduce (.[0] | keys_unsorted[]) as $k ( {}; .[$k] = ($in|map(.[$k])))
. as $in
| reduce (.[0] | keys_unsorted[]) as $k ( {};
.[$k] = ($in|map(.[$k] | (tonumber? // .))))