CODEWITHSUNDEEP
pythonpandasdictionary
Santiago Veintimilla
Santiago Veintimilla

Reputation: 51

How do I map a multi-level dictionary into a DataFrame series

I want to map a multi level dictionary according to two columns in a DataFrame. What I have so far is this:

df = pd.DataFrame({
     'level_1':['A','B','C','D','A','B','C','D'],
     'level_2':[1,2,3,1,2,1,2,3]
})

dict = {
    'A':{1:0.5, 2:0.8, 3:0.4},
    'B':{1:0.4, 2:0.3, 3:0.7},
    'C':{1:0.3, 2:0.6, 3:0.6},
    'D':{1:0.5, 2:0.4, 3:0.4}
}

df['mapped'] = np.where(
    df.level_1 == 'A',
    df.level_2.map(dict['A']),
    np.where(
        df.level_1 == 'B',
        df.level_2.map(dict['B']),
        np.where(
            df.level_1 == 'C',
            df.level_2.map(dict['C']),
            np.where(
                df.level_1 == 'D',
                df.level_2.map(dict['D']),
                np.nan
            )
        )
    )
)

There must be a better way but I can't seem to find it. It gets really tedious as my real dictionary has a lot more options on level_2.

Thanks!

Upvotes: 1

Views: 922

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

We can try MultiIndex.map

df['mapped'] = df.set_index(['level_1', 'level_2']).index.map(pd.DataFrame(d).unstack())

  level_1  level_2  mapped
0       A        1     0.5
1       B        2     0.3
2       C        3     0.6
3       D        1     0.5
4       A        2     0.8
5       B        1     0.4
6       C        2     0.6
7       D        3     0.4

Note: dict is a builtin in python, so using dict as a variable name must be avoided. Here I have used d to represent your mapping dictionary

Upvotes: 4

Related Questions