Multiply Column of One Dataframe by a Value from Another Dataframe, Determined by a Key

Question

I want to take a large dataframe with around 26,000 rows, foodList, and multiply the column foodList['food_quant'] by a certain value from the dataframe foodConversions. To determine this value from foodConversions, another column foodList['food_name'] has a string which corresponds to the index of foodConversions. I am doing this to convert grams of different foods to calories, and each food type has a different number of calories.

I've tried doing nested loops to go through every value in foodConversions and see if it is equal to foodList['food_name'], but that's super slow and never actually finishes running for some reason; hence, I would prefer to move away from this method. I have also tried using applymap and a lambda function, but I don't think I've done this right. Lastly, I've tried to use the methods outlined in another stackoverflow problem, but I wasn't sure how to apply it to my situation or if it even works for my situation. Here's the link to it: Multiply dataframe with values from other dataframe

Here are the two dataframes:

foodConversions = pd.Dataframe([2,3], index=['meat','vegetables'], columns=['cal/gram'])

            cal/gram
meat        2
vegetables  3

foodList = pd.Dataframe([['meat',40]['meat',30]['vegetables',20]['meat',10]], columns=['food_name','food_quant'])

    food_name    food_quant
0   meat         40
1   meat         30
2   vegetables   20
3   meat         10

And the output should look like:

    food_name    food_quant
0   meat         80
1   meat         60
2   vegetables   60
3   meat         20

Hopefully that made sense, I tried to be as thorough as possible so I'm sorry for the lengthy explanation. Thanks everyone for you help!

score 2 · Accepted Answer · answered Nov 04 '19 at 03:12

We can do reindex or loc or map or merge

reindex|loc

df2.assign(food_quant=df2.food_quant*(df1['cal/gram'].reindex(df2.food_name).values))# change reindex to loc
Out[121]: 
    food_name  food_quant
0        meat          80
1        meat          60
2  vegetables          60
3        meat          20

map|replace

df2.assign(food_quant=df2.food_quant*df2.food_name.map(df1['cal/gram']))
df2.assign(food_quant=df2.food_quant*df2.food_name.replace(df1['cal/gram']))

score 0 · Answer 2 · answered Nov 04 '19 at 03:18

Try using:

print(foodList.set_index('food_name').mul(foodConversions.reindex(foodList['food_name'])['cal/gram'], axis=0).reset_index())

Output:

    food_name  food_quant
0        meat          80
1        meat          60
2  vegetables          60
3        meat          20

Multiply Column of One Dataframe by a Value from Another Dataframe, Determined by a Key

2 Answers2