How do I efficiently map transformations over a pandas DataFrame

user_8325579 0
Viewed 64

a bit of a funny ask.

I have a (big) table that looks like:

transaction_date (index) store_id department_id gross_revenue
'2020-01-01' Store1 Fruit $7.50
'2020-01-01' Store2 Fruit $2.75
'2020-01-01' Store1 Veg $47.50
'2020-01-01' Store2 Veg $8.25
... ... ... ...

And I want to transform the gross_revenue column depending on the value of store_id and department_id.

For argument sake, let's say I want to increase all Store1 sales by 25%, increase Veg sales by 10%, and Fruit sales by 75% (let's not worry about the order just yet).

I'd like the user to be able to write:

modifiers = {
    'store_id': {
        'Store1': lambda x: x*1.25
     },
    'department_id: {
        'Veg' : lambda x: x*1.10, 
        'Fruit': lambda x: x*1.75
    }
}

Is there a performant way to execute this in Pandas?

As a baseline, this code works:

from functools import reduce

ans = (table
 .assign(gross_revenue = lambda x: x
     .apply(lambda row: reduce(lambda x, f: f(x), [row['gross_revenue'], 
                                                   modifiers.get(row['business_id'], lambda x: x),
                                                   modifiers.get(row['department_description'], lambda x: x)
                                                  ]), axis=1)
    )
)

However, it takes close to 2min (table is 5-10m rows).

Does anyone know a faster approach?

Thanks in advance.

pandas dataframe python performance
1 Answers

Use map:

store_adjust = {'Store1': 1.25, 'Store10':1.3}
dep_adjust = {'Veg': 1.10, 'Fruit':1.75}

df['gross_revenue'] *= ( df['store_id'].map(store_adjust).fillna(1) *
                         df['department_id'].map(dep_adjust).fillna(1) )
user_42384080
Related