1

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

Alex
  • 2,603
  • 4
  • 40
  • 73
  • This is not the same transformation but it might be helpful: https://stackoverflow.com/questions/32357240/transposing-objects-in-jq – jarmod Nov 10 '20 at 23:34

1 Answers1

2

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? // .))))
peak
  • 105,803
  • 17
  • 152
  • 177
  • that is really cool. one problem. it quotes the numerics. "field1": [ "Mazda RX4", "Mazda RX4 Wag" ], "mpg": [ "21", "21" ], "cyl": [ "6", "6" ], "disp": [ "160", "160" ],` – Alex Nov 10 '20 at 23:46
  • It is fine if the characters are quoted, but the numbers can't be quoted. – Alex Nov 10 '20 at 23:49
  • If you want the integer-valued strings to be converted, you could use `tonumber`, but you didn't exactly specify the requirements in this regard (e.g., what if the original JSON has both integer-valued and non-integer-valued values for one of the keys?) – peak Nov 10 '20 at 23:49
  • "Simple"? I'd love to see what you consider complex ;-) JQ - so useful, yet so utterly inscrutable. – jarmod Nov 10 '20 at 23:52
  • Maybe direct would capture what I mean more accurately, but since the two-liner is really only based on two well-known concepts (reduce and map), I'd count it as pretty simple, even if the syntax might be unfamiliar. – peak Nov 10 '20 at 23:56
  • @jarmod - Once you realize jq’s syntax is based on the idea of pipes and filters, as in bash et al., things quickly fall into place. Many people find reading the jq tutorial first to be helpful. – peak Nov 11 '20 at 13:59
  • @peak yes, sage advice. When I find some time, I'll revisit the tutorial. The challenge has been that I don't use jq sufficiently frequently for its syntax to not be a burden. – jarmod Nov 11 '20 at 14:41