From dataframe create dict with tuple of multiple columns as key and another column as value

user_14159987 0
Viewed 613

I have a dataframe in the following format:

Sr. |  From  | Tran type | Inv type |  Opposite   |    Comment   |
------------------------------------------------------------------
6   | Seller |    X, Y   |   P, Q   |    Buyer    | Rand comment |

Where 'Sr.' is the index of the df. There are several rows in the df, and varying number of values in 'Tran type' and 'Inv type' columns of each row.

I want to create a dict list this:

{k:v} = {(From, Tran type, Inv type, To) : Comment}

The keys are essentially cross products of the 4 columns, and each key will only contain one value of Tran type and Inv type. For the above example, it should look like this:

{
 (Seller, X, P, Buyer): Rand Comment,
 (Seller, X, Q, Buyer): Rand Comment,
 (Seller, Y, P, Buyer): Rand Comment,
 (Seller, Y, Q, Buyer): Rand Comment
}

This is the code I'm using right now:

def create_combos_dict(rule):
    tran_types = str(rule['Tran type'])
    tran_types = tran_types.split()
    inv_types = str(rule['Inv type'])
    inv_types = inv_types.split()
    from_side = str(rule['From'])
    opposite = str(rule['Opposite'])
    
    key_tuple = product([from_side], tran_types, inv_types, [opposite])
    combos_dict = {combo:str(rule['Comment']) for combo in key_tuple}
    return combos_dict

mapped_dict = {}
mapped_dict.update(df.apply(create_combos_dict, axis=1))
mapped_dict

However, this is the output I get:

{6: {
        (Seller, X, P, Buyer): Rand Comment,
        (Seller, X, Q, Buyer): Rand Comment,
        (Seller, Y, P, Buyer): Rand Comment,
        (Seller, Y, Q, Buyer): Rand Comment
    }
}

Why is the dict forming with 'Sr.' as key and the value is actual dict I'm making?

For the time being, I'm iterating through combos_dict.values() and adding those to another dict. But surely there has to be some simple fix to this?

pandas tuples dictionary python
1 Answers

What I was meaning was the following:

import functools as ft
import pandas as pd

df = pd.DataFrame([[6,"Seller","X, Y","P, Q","Buyer","Rand comment"],
                  [6,"Seller","Z, Y","L, Q","Buyer","Rand comment"]],
                  columns = ["Sr.","From","Tran type","Inv type","Opposite","Comment"])

df[["col1","col2"]] = df["Tran type"].str.split(",", expand = True,)
df[["col3","col4"]] = df["Inv type"].str.split(",", expand = True,)
df["col5"] = df.apply(lambda x: {(x["From"],x["col1"],x["col3"],x["Opposite"]):x["Comment"],
                                (x["From"],x["col1"],x["col4"],x["Opposite"]):x["Comment"],
                                (x["From"],x["col2"],x["col3"],x["Opposite"]):x["Comment"],
                                (x["From"],x["col2"],x["col4"],x["Opposite"]):x["Comment"]},axis = 1)

ft.reduce(lambda total,x: {**total,**x},df["col5"])

Output:

{('Seller', 'X', 'P', 'Buyer'): 'Rand comment',
 ('Seller', 'X', ' Q', 'Buyer'): 'Rand comment',
 ('Seller', ' Y', 'P', 'Buyer'): 'Rand comment',
 ('Seller', ' Y', ' Q', 'Buyer'): 'Rand comment',
 ('Seller', 'Z', 'L', 'Buyer'): 'Rand comment',
 ('Seller', 'Z', ' Q', 'Buyer'): 'Rand comment',
 ('Seller', ' Y', 'L', 'Buyer'): 'Rand comment'}
user_68134170
Related