Csv to json by the same key-python

Question

I have a big csv file (aprx. 1GB) that I want to convert to a json file in the following way:

the csv file has the following structure:

header: tid;inkey;outkey;value

values:

tid1;inkey1;outkey1;value1
tid1;inkey2;outkey2;value2
tid2;inkey2;outkey3;value2
tid2;inkey4;outkey3;value2

etc.

The idea is to convert this csv to a json with the following structure, basically to group everything by "tid":

{
"tid1":  {
    "inkeys":["inkey1", "inkey2"],
    "outkeys":["outkey1", "outkey2"]
         }
}

I can imagine how to do it normal python dicts and lists, but my problem is also the huge amount of data that i have to process. I suppose pandas can help here, but I am still very confused with this tool.

Answer 1

I think this should be straight-forward to do with standard Python data structures such as defaultdict. Unless you have very limited memory, I see no reason why a 1gb file will be problematic using a straight-forward approach.

Something like (did not test):

from collections import defaultdict
import csv 
import json

out_data = defaultdict(lambda: {"inkeys": [], "outkeys": [], "values": []})
with file("your-file.csv") as f:
    reader = csv.reader(f):
    for line in reader:
        tid, inkey, outkey, value = line
        out_data[tid]["inkeys"].append(inkey)
        out_data[tid]["outkeys"].append(outkey)
        out_data[tid]["values"].append(value)

print(json.dumps(out_data))

There might be a faster or more memory efficient way to do it with Pandas or others, but simplicity and zero dependencies go a long way.