How to group two columns and multiply other two into a new column in Pandas?

user_19425006 0
Viewed 40

I'm importing the following .xlsx file into a dataframe.

dfMenu = pd.read_excel("/Users/FoodTrucks.xlsx")
Price Quantity FoodTruck FoodTruck_ID
3.00 10 Burgers 1
1.20 50 Tacos 2
0.60 30 Tacos 2
1.12 40 Drinks 4
2.00 20 Burgers 1

My goal is to show the total revenue for each food truck with its ID and name in a new column, called "Revenue".

I am currently trying to use the code below, but I'm struggling with the multiplication of the columns "Price" and "Quantity" into a new one and grouping "FoodTruck" and "FoodTruck_ID" in an elegant way.

df = df.groupby((['FoodTruck', 'FoodTruck_ID'])(df['Revenue'] = df['Price'] * q9['Quantity']))

I am getting a syntax error "SyntaxError: cannot assign to subscript here. Maybe you meant '==' instead of '='?"

What would be the most elegant way to solve it?

pandas python
1 Answers

It will be easier to first calculate Price*Quantity before doing the groupby:

import pandas as pd

df = pd.DataFrame({
    'Price': [3.0, 1.2, 0.6, 1.12, 2.0],
    'Quantity': [10, 50, 30, 40, 20],
    'FoodTruck': ['Burgers', 'Tacos', 'Tacos', 'Drinks', 'Burgers'],
    'FoodTruck_ID': [1, 2, 2, 4, 1]
})

df['Revenue'] = df['Price']*df['Quantity']

df.groupby(['FoodTruck','FoodTruck_ID'])['Revenue'].sum()

Output

FoodTruck  FoodTruck_ID
Burgers    1               70.0
Drinks     4               44.8
Tacos      2               78.0
Name: Revenue, dtype: float64
user_65805430
Related