Convert a CSV into a JSON using a JSON Schema

I'm not entirely clear on why JSON schema would be needed for this, but if you wanted to, you could easily create a convenience function which can essentially "unflatten" the flat JSON that your CSV data would be mapped to, into a nested dictionary format as mentioned above.

The following example should demonstrate a simplified example of how this would work. Note the following two points:

• In the CSV header, I've corrected a typo and renamed one of the columns to address.city; previously, it was adress.city, which would result in it getting mapped to another JSON path under a separate adress key, which might not be desirable.

• I wasn't sure of the best way to handle this, but it looks like csv module only allows a single-character delimiter; in the CSV file, it looks like you have a comma and a space , as the separator, so I've just replaced all occurrences of this with a single comma , so that the split on the delimiter works as expected.

from csv import DictReader
from io import StringIO
from typing import Any


csv_data = StringIO("""\
user_id, address.city, address.street, address.number, name, aka, contacts.name, contacts.relationship
1, Seattle, Atomic Street, 6910, Rick Sanchez, Rick, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Grandpa, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Albert Ein-douche, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Richard, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Rick, Beth, Daughter
1, Seattle, Atomic Street, 6910, Rick Sanchez, Grandpa, Beth, Daughter
1, Seattle, Atomic Street, 6910, Rick Sanchez, Albert Ein-douche, Beth, Daughter
1, Seattle, Atomic Street, 6910, Rick Sanchez, Richard, Beth, Daughter
""".replace(', ', ',')
)


def unflatten_json(json_dict: dict):
    """Unflatten a JSON dictionary object, with keys like 'a.b.c'"""
    result_dict = {}

    for k, v in json_dict.items():
        *nested_parts, field_name = k.split('.')

        obj = result_dict
        for p in nested_parts:
            obj = obj.setdefault(p, {})

        obj[field_name] = v

    return result_dict


def main():
    reader = DictReader(csv_data)
    flat_json: list[dict[str, Any]] = list(reader)

    first_obj = flat_json[0]
    nested_dict = unflatten_json(first_obj)

    print('Flat JSON:   ', first_obj)
    print('Nested JSON: ', nested_dict)


if __name__ == '__main__':
    main()

The output is given below:

Flat JSON:    {'user_id': '1', 'address.city': 'Seattle', 'address.street': 'Atomic Street', 'address.number': '6910', 'name': 'Rick Sanchez', 'aka': 'Rick', 'contacts.name': 'Morty', 'contacts.relationship': 'Grandson'}
Nested JSON:  {'user_id': '1', 'address': {'city': 'Seattle', 'street': 'Atomic Street', 'number': '6910'}, 'name': 'Rick Sanchez', 'aka': 'Rick', 'contacts': {'name': 'Morty', 'relationship': 'Grandson'}}

Note, if you want to unflatten all JSON dictionary objects in the list, you could instead use a list comprehension as below:

result_list = [unflatten_json(d) for d in flat_json]

I would also point out that the above solution isn't perfect, as it will pass in everything as string values, for example in the case of 'user_id': '1'. To work around that, you can modify the unflatten_json function so it is like below:

...
for k, v in json_dict.items():
    ...

    try:
        v = int(v)
    except ValueError:
        pass

    obj[field_name] = v

Now the unflattened JSON object should be as below. Note that I'm pretty printing it with json.dumps(nested_dict, indent=2) so it's a little easier to see.

{
  "user_id": 1,
  "address": {
    "city": "Seattle",
    "street": "Atomic Street",
    "number": 6910
  },
  "name": "Rick Sanchez",
  "aka": "Rick",
  "contacts": {
    "name": "Morty",
    "relationship": "Grandson"
  }
}

Complete Solution

The full solution to achieve the desired output (data for all rows appended to aka and contacts) is provided below:

from csv import DictReader
from io import StringIO
from pprint import pprint


csv_data = StringIO("""\
user_id, address.city, address.street, address.number, name, aka, contacts.name, contacts.relationship
1, Seattle, Atomic Street, 6910, Rick Sanchez, Rick, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Grandpa, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Albert Ein-douche, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Richard, Morty, Grandson
1, Seattle, Atomic Street, 6910, Rick Sanchez, Rick, Beth, Daughter
1, Seattle, Atomic Street, 6910, Rick Sanchez, Grandpa, Beth, Daughter
1, Seattle, Atomic Street, 6910, Rick Sanchez, Albert Ein-douche, Beth, Daughter
1, Seattle, Atomic Street, 6910, Rick Sanchez, Richard, Beth, Daughter
""".replace(', ', ',')
)


def unflatten_json(json_dict: dict[str, str]):
    """Unflatten a JSON dictionary object, with keys like 'a.b.c'"""
    result_dict = {}

    for k, v in json_dict.items():
        *nested_parts, field_name = k.split('.')

        obj = result_dict
        for p in nested_parts:
            obj = obj.setdefault(p, {})

        obj[field_name] = int(v) if v.isnumeric() else v

    return result_dict


def main():
    reader = DictReader(csv_data)

    rows = list(map(unflatten_json, reader))

    # retrieve the first element in the (unflattened) sequence
    result_obj = rows[0]
    # define list fields that we want to merge data for
    list_fields = ('aka', 'contacts')
    # now loop through, and for all rows merge the data for these fields
    for field in list_fields:
        result_obj[field] = [row[field] for row in rows]

    print('Result object:')
    pprint(result_obj)


if __name__ == '__main__':
    main()

This should have the desired result as also noted in the question:

Result object:
{'address': {'city': 'Seattle', 'number': 6910, 'street': 'Atomic Street'},
 'aka': ['Rick',
         'Grandpa',
         'Albert Ein-douche',
         'Richard',
         'Rick',
         'Grandpa',
         'Albert Ein-douche',
         'Richard'],
 'contacts': [{'name': 'Morty', 'relationship': 'Grandson'},
              {'name': 'Morty', 'relationship': 'Grandson'},
              {'name': 'Morty', 'relationship': 'Grandson'},
              {'name': 'Morty', 'relationship': 'Grandson'},
              {'name': 'Beth', 'relationship': 'Daughter'},
              {'name': 'Beth', 'relationship': 'Daughter'},
              {'name': 'Beth', 'relationship': 'Daughter'},
              {'name': 'Beth', 'relationship': 'Daughter'}],
 'name': 'Rick Sanchez',
 'user_id': 1}

Like already mentionned, JSON schema can't help you to transform your data. It can help you validate the result though.
In order to get one entry per user I think you should group your DataFrame by ["user_id", "address.city", "address.street", "address.number", "name"]. Those values should be constant for a user.
Then aggregate the remaining columns to create lists.

I created generic functions to unflatten the dictionaries and merge the list in dictionaries. You could get rid of the recursion since in your case everything is done on top level:

import json
import pandas as pd

df = pd.read_csv("file.csv", sep=", ", engine="python")
df = df.groupby(["user_id", "address.city", "address.street", "address.number", "name"], as_index=False).agg(lambda x: list(x))

#print(df) # uncomment to see the transformation

json_data = df.to_dict(orient="records")

def unflatten_dic(dic):
    for k,v in list(dic.items()):
        subkeys = k.split('.')
        if len(subkeys) > 1:
            dic.setdefault(subkeys[0],dict())
            dic[subkeys[0]].update({"".join(subkeys[1:]): v})
            unflatten_dic(dic[subkeys[0]])
            del(dic[k])


def merge_lists(dic):
    for k,v in list(dic.items()):
        if isinstance(v, dict):
            keys = list(v.keys())
            vals = list(v.values())
            if all(isinstance(l, list) and len(l)==len(vals[0]) for l in vals):
                dic[k] = []
                val_tuple = set(zip(*vals)) # removing duplicates with set()
                for t in val_tuple:
                    dic[k].append({subkey: t[i] for i, subkey in enumerate(keys)})
            else:
                merge_lists(v)
        elif isinstance(v, list):
            dic[k] = list(set(v))   # removing list duplicates
                    

for user in json_data:
    unflatten_dic(user)
    merge_lists(user)

print(json.dumps(json_data, indent=4))

Output:

[
    {
        "user_id": 1,
        "name": "Rick Sanchez",
        "aka": [
            "Richard",
            "Grandpa",
            "Albert Ein-douche",
            "Rick"
        ],
        "address": {
            "city": "Seattle",
            "street": "Atomic Street",
            "number": 6910
        },
        "contacts": [
            {
                "name": "Morty",
                "relationship": "Grandson"
            },
            {
                "name": "Beth",
                "relationship": "Daughter"
            }
        ]
    }
]