Multidimensional multiplication in Pandas pivot tables

Question

Consider a Pandas pivot table like so:

                  E
A   B   C     D    
bar one large 4   6
        small 5   8
    two large 7   9
        small 6   9
foo one large 2   9
        small 1   2
    two small 3  11

I would like to multiply each E entry that has A = bar by l and A = foo by m. For entries that have B = one I'd like to multiply them by n, for B = two by p. For every level of every dimension I have a different value that I would like to multiply E by. The resultant table would have each original value in E multiplied by [the number of dimensions in the table (four)] variables.

What is the fastest way to do this in Python? My actual table is high-dimensional and this operation will need to be done many times as part of an optimization process.

I created the pivot table using this code:

df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
                         "bar", "bar", "bar", "bar"],
                   "B": ["one", "one", "one", "two", "two",
                         "one", "one", "two", "two"],
                   "C": ["small", "large", "large", "small",
                         "small", "large", "small", "small",
                         "large"],
                   "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                   "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})

table = pd.pivot_table(df, values='D', index=['A', 'B', 'C', 'D'], aggfunc=np.sum)

The values to multiply by are stored in a dictionary.

For instance:

{'A': {'bar': 0.5, 'foo': 0.2}, 
'B': {'one': 0.1, 'two': 0.3},
'C': {'large': 2, 'small': 4},
'D': {1: 10, 2: 20, 3: 30, 4: 40, 5: 50, 6: 60, 7: 70}}

With this dictionary, the result for the first row would be 6 * 0.5 * 0.1 * 2 * 40 = 24.

user_9274732 · Answer

you could use map in each level of your index got with index.get_level_values.

table['Emult'] = table['E']*np.prod([table.index.get_level_values(lv).map(d[lv]) 
                                     for lv in table.index.names], 
                                    axis=0)
print (table)
                  E  Emult
A   B   C     D           
bar one large 4   6   24.0
        small 5   8   80.0
    two large 7   9  189.0
        small 6   9  324.0
foo one large 2   9    7.2
        small 1   2    1.6
    two small 3  11   79.2

where d is the dictionary you gave in the question

Multidimensional multiplication in Pandas pivot tables

1 Answers