pythonjsonpython-3.xautomationcsvtojson

Retrieve JSON from CSV using python in desired format


Many thanks in advance, I have been keen on collecting some insights on getting the below output from its corresponding input. Would like to see the table getting converted to a desirable format via using a python script as I have to work with a huge CSV at a later stage. Any inputs are highly appreciated.

Input CSV:

reference mcc value currency
10000 5300 134.09 USD
10001 5651 128.95 USD
10002 5912 104.71 USD

Used python code:

from csv import DictReader
from itertools import groupby
from pprint import pprint
import json

with open('Test_bulk_transactions_data.csv') as csvfile:
    r = DictReader(csvfile, skipinitialspace=True)
    data = [dict(d) for d in r]

    group = []
    uniquekeys = []

    for k, g in groupby(data, lambda r: (r['reference'], r['mcc'])):
        group.append({
            "reference": k[0],
            "mcc": k[1],
            "amount": [{k:v for k, v in d.items() if k not in ['reference','mcc']} for d in list(g)]})
        uniquekeys.append(k)

print(json.dumps(group, indent = 3) + '}')

Current Output:

  {
   "reference": "10000",
   "mcc": "5300",
   "amount": [
    {
     "value": "134.09",
     "currency": "USD"
    }
   ]
  },
  {
   "reference": "10001",
   "mcc": "5651",
   "amount": [
    {
     "value": "128.95",
     "currency": "USD"
    }
   ]
  },
  {
   "reference": "10002",
   "mcc": "5912",
   "amount": [ 
    {
     "value": "104.71",
     "currency": "USD"
    }
   ]
  }

Desired Output JSON:

  {
   "reference": "10000",
   "mcc": "5300",
   "amount": {
     "value": 134.09,
     "currency": "USD"
    }
  },
  {
   "reference": "10001",
   "mcc": "5651",
   "amount": {
     "value": 128.95,
     "currency": "USD"
    }
  },
  {
   "reference": "10002",
   "mcc": "5912",
   "amount": {
     "value": 104.71,
     "currency": "USD"
    }
  }

Solution

  • import csv
    
    csv_filepath =  "/home/mhs/test.csv"
     
    output = []
    
    
    with open(csv_filepath) as cd:
        csvReader = csv.DictReader(cd)
        for r in csvReader:
            r["amount"] = {"value": float(r.pop("value")), "currency": r.pop("currency")}
            output.append(r)