Python Dictionary transpose rows as column

Question

I have a CSV file that will be imported and converted into a dictionary.

with open(r"DictionaryQuestion.csv", encoding='utf-8-sig') as csvfile:
csvReader = csv.DictReader(csvfile)
for row in map(dict, csvReader):
    print(row)

Example Input

I want to be able to transpose the data so that the Discount & NonDiscount rows will be added as columns with their associated amount as well as getting rid of duplicates. Essentially, I want a new dictionary so that I can zip through it.

This is the desired output.

Desired Output as Dictionary

Answer 1

You can use itertools.groupby() to group records by productId and then update your data.

Below I've converted a list which has records same as yours and created new list with data as expected.

data = [
    {
        "ProductId": "1", "Brand": "Brand1", "rateamount": 1, "rate_type": "Discount" 
    },
    {
        "ProductId": "1", "Brand": "Brand1", "rateamount": 2, "rate_type": "NonDiscount" 
    },
    {
        "ProductId": "2", "Brand": "Brand2", "rateamount": 3, "rate_type": "Discount" 
    },
    {
        "ProductId": "2", "Brand": "Brand2", "rateamount": 4, "rate_type": "NonDiscount" 
    },
    {
        "ProductId": "3", "Brand": "Brand3", "rateamount": 5, "rate_type": "Discount" 
    },
    {
        "ProductId": "3", "Brand": "Brand3", "rateamount": 6, "rate_type": "NonDiscount" 
    },
    {
        "ProductId": "4", "Brand": "Brand4", "rateamount": 7, "rate_type": "Discount" 
    },
    {
        "ProductId": "4", "Brand": "Brand4", "rateamount": 2, "rate_type": "NonDiscount" 
    },
]

Solution

Assuming you data is ordered by productId, otherwise you'll need to order it before grouping.

import itertools

groups = itertools.groupby(data, lambda e: {"ProductId": e["ProductId"], "Brand": e["Brand"]})

output = []
for group, items in groups:
    el = dict(group)
    for item in items:
        if item["rate_type"] == "Discount":
            el["Discount"] = item["rateamount"]
        else:
            el["NonDiscount"] = item["rateamount"]
    output.append(el)

print(output)

Above for loop can be converted to a map

import itertools

groups = itertools.groupby(data, lambda e: {"ProductId": e["ProductId"], "Brand": e["Brand"]})

output = map(
    lambda group: dict(
    **group[0],
    **{
        item["rate_type"]: item["rateamount"] for item in group[1]
    }),
    groups
)

print(list(output))

Both prints

[
 {'ProductId': '1', 'Brand': 'Brand1', 'Discount': 1, 'NonDiscount': 2},
 {'ProductId': '2', 'Brand': 'Brand2', 'Discount': 3, 'NonDiscount': 4},
 {'ProductId': '3', 'Brand': 'Brand3', 'Discount': 5, 'NonDiscount': 6},
 {'ProductId': '4', 'Brand': 'Brand4', 'Discount': 7, 'NonDiscount': 2}
]