CSV to JSON and add title

Question

I have a csv document:

{
    "epsilon_id": 194029423,
    "weather": "cloudy",
    "temperature": 27
},
{
    "epsilon_id": 932856192,
    "weather": "sunny",
    "temperature": 31
}

I was wondering if there was a tool to make it into valid json where the field epsilon_id is the title for the data.

ex:

{
    194029423: {
        "weather": "cloudy",
        "temperature": 27
    },
    932856192: {
        "weather": "sunny",
        "temperature": 31
    }
}

I would prefer it to be a program (in whatever language) that I can run because I have 1,000 entries in my test sample and I will have tens of thousands in my final copy.

Any help would be much appreciated!

@corn3lius I wasn't sure how to format the csv, so I just threw it into the first csv to json converter I found online to make it easier to display — 182319283, Jul 13 '18 at 20:48

Amith Kumar · Accepted Answer · 2018-07-13T20:49:43.453

You are looking at JSON transformation, and ofcourse can be achieved with a custom programming. I can explain you how you can achieve this in Java, but functionally its gonna be the same for any programming of your choice.

Your input json will look like this:

[{
    "epsilon_id": 194029423,
    "weather": "cloudy",
    "temperature": 27
},
{
    "epsilon_id": 932856192,
    "weather": "sunny",
    "temperature": 31
}]

When you parse in java using popular Jackson library, you will get list of object for below class:

class Input
{
    @JsonProperty(access = Access.WRITE_ONLY)
    String epsilon_id,
    String weather,
    int temperature
}

Then you create a map object Map<Integer, Input>, populate data like below:

Map<Integer, Input> map = new HashMap<>();
for(Input obj : listOfInputs){
 map.put(obj.epsilon_id, obj)
};

Serialize your result map using Jackson again to get your desired output format:

{
    194029423: {
        "weather": "cloudy",
        "temperature": 27
    },
    932856192: {
        "weather": "sunny",
        "temperature": 31
    }
}

If you are not very familiar with Java & Jackson JSON parsing, I found this tutorial with code sample, which will give you headstart.

Okay thanks! Would it also be possible for me to reformat dates after pulling them out of a field? — 182319283, Jul 13 '18 at 20:51
I don't see date field in your json, but yes once your string date is read in Java object, you can change its format using `SimpleDateFormat` class. [Reference1](https://stackoverflow.com/questions/9872419/how-to-convert-a-string-to-a-date-using-simpledateformat) & [Reference2](http://www.java67.com/2013/01/how-to-format-date-in-java-simpledateformat-example.html) — Amith Kumar, Jul 13 '18 at 21:00

corn3lius · Answer 2 · 2018-07-13T21:22:46.827

import csv, json, os
# rename this file or pass it in as process.argv[2] 
#  then pipe output into another file. or 
with open("./foo.csv") as f: 
  output = {} 
  for line in csv.DictReader(f):
    key = line.pop("epsilon_id")
    if output.has_key(key):
      print("Duplicate Id -> {} ".format(key))
    output[key] = line

#  then pipe this output into another file.
print(json.dumps(output, indent=2))
# or write a file
with open("/tmp/foo.json",'w') as f:
    json.dump(output, f)

Python makes it pretty easy : this will detect all types from a csv file.

demo : https://repl.it/@markboyle/UsableIcyFeed

CSV to JSON and add title

2 Answers2