[Python] Perform a binary op on values in a pandas dataframe column by a value in that same...

Sorry for the mouthful title. I think this is best illustrated by an example. Let's say we have an item that has different rarity levels, all of which have dfferent prices in different shops. I want to know how much more expensive a given rarity is than the base "normal" rarity in each shop separately.

How could I add a new "premium" column that would give me the result of dividing the price of the item with a given rarity at a given shop, by the price of that item in "normal" quality at that shop in particular? The result can be seen in the table below.

item quality price shop premium (todo) base price
bread normal 2.0 Lumbridge 1.0 2.0
bread rare 3.0 Lumbridge 1.5 2.0
bread legendary 5.0 Lumbridge 2.5 2.0
bread normal 1.5 Varrock 1.0 1.5
bread rare 4.5 Varrock 3.0 1.5
bread legendary 6.0 Varrock 4.0 1.5
bread normal 3.0 Yanille 1.0 3.0
bread rare 2.0 Yanille 0.66 3.0
bread legendary 4.0 Yanille 1.33 3.0

I thought about repeating the rows with normal quality as a new column (given as the "base price" column), but I don't see any mechanism that could allow for that. If this is possible could it be done if instead of just one "item" we had many (i.e. extending the filtering to multiple columns)?

Continue reading...