jsoncsvjqcsvtojson

How to convert object keys to arrays with jq


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"
  }
]

Complete working solution

cat <csv_data> | csvtojson | jq '. as $in | reduce (.[0] | keys_unsorted[]) as $k ( {}; .[$k] = ($in|map(.[$k])))'

jq play - Converting all numbers to strings

https://jqplay.org/s/HKjHLVp9KZ


Solution

  • 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])))
    

    Converting all number-valued strings to numbers

    . as $in
    | reduce (.[0] | keys_unsorted[]) as $k ( {}; 
        .[$k] = ($in|map(.[$k] | (tonumber? // .))))