If you call .reset_index() on the series that you have, it will get you a dataframe like you want (each level of the index will be converted into a column):

df.groupby(['name', 'id', 'dept'])['total_sale'].mean().reset_index()

EDIT: to respond to the OP's comment, adding this column back to your original dataframe is a little trickier. You don't have the same number of rows as in the original dataframe, so you can't assign it as a new column yet. However, if you set the index the same, pandas is smart and will fill in the values properly for you. Try this:

cols = ['date','name','id','dept','sale1','sale2','sale3','total_sale']
data = [
['1/1/17', 'John', 50, 'Sales', 50.0, 60.0, 70.0, 180.0],
['1/1/17', 'Mike', 21, 'Engg', 43.0, 55.0, 2.0, 100.0],
['1/1/17', 'Jane', 99, 'Tech', 90.0, 80.0, 70.0, 240.0],
['1/2/17', 'John', 50, 'Sales', 60.0, 70.0, 80.0, 210.0],
['1/2/17', 'Mike', 21, 'Engg', 53.0, 65.0, 12.0, 130.0],
['1/2/17', 'Jane', 99, 'Tech', 100.0, 90.0, 80.0, 270.0],
['1/3/17', 'John', 50, 'Sales', 40.0, 50.0, 60.0, 150.0],
['1/3/17', 'Mike', 21, 'Engg', 53.0, 55.0, 12.0, 120.0],
['1/3/17', 'Jane', 99, 'Tech', 80.0, 70.0, 60.0, 210.0]
]
df = pd.DataFrame(data, columns=cols)

mean_col = df.groupby(['name', 'id', 'dept'])['total_sale'].mean() # don't reset the index!
df = df.set_index(['name', 'id', 'dept']) # make the same index here
df['mean_col'] = mean_col
df = df.reset_index() # to take the hierarchical index off again
Answer from Nathan on Stack Overflow
Top answer
1 of 4
59

If you call .reset_index() on the series that you have, it will get you a dataframe like you want (each level of the index will be converted into a column):

df.groupby(['name', 'id', 'dept'])['total_sale'].mean().reset_index()

EDIT: to respond to the OP's comment, adding this column back to your original dataframe is a little trickier. You don't have the same number of rows as in the original dataframe, so you can't assign it as a new column yet. However, if you set the index the same, pandas is smart and will fill in the values properly for you. Try this:

cols = ['date','name','id','dept','sale1','sale2','sale3','total_sale']
data = [
['1/1/17', 'John', 50, 'Sales', 50.0, 60.0, 70.0, 180.0],
['1/1/17', 'Mike', 21, 'Engg', 43.0, 55.0, 2.0, 100.0],
['1/1/17', 'Jane', 99, 'Tech', 90.0, 80.0, 70.0, 240.0],
['1/2/17', 'John', 50, 'Sales', 60.0, 70.0, 80.0, 210.0],
['1/2/17', 'Mike', 21, 'Engg', 53.0, 65.0, 12.0, 130.0],
['1/2/17', 'Jane', 99, 'Tech', 100.0, 90.0, 80.0, 270.0],
['1/3/17', 'John', 50, 'Sales', 40.0, 50.0, 60.0, 150.0],
['1/3/17', 'Mike', 21, 'Engg', 53.0, 55.0, 12.0, 120.0],
['1/3/17', 'Jane', 99, 'Tech', 80.0, 70.0, 60.0, 210.0]
]
df = pd.DataFrame(data, columns=cols)

mean_col = df.groupby(['name', 'id', 'dept'])['total_sale'].mean() # don't reset the index!
df = df.set_index(['name', 'id', 'dept']) # make the same index here
df['mean_col'] = mean_col
df = df.reset_index() # to take the hierarchical index off again
2 of 4
7

You are very close. You simply need to add a set of brackets around [['total_sale']] to tell python to select as a dataframe and not a series:

df.groupby(['name', 'id', 'dept'])[['total_sale']].mean()

If you want all columns:

df.groupby(['name', 'id', 'dept'], as_index=False).mean()[['name', 'id', 'dept', 'total_sale']]
🌐
Statology
statology.org › home › how to calculate the mean by group in pandas (with examples)
How to Calculate the Mean by Group in Pandas (With Examples)
August 29, 2022 - The mean points value for players on team B and position G is 24. The following tutorials explain how to perform other common functions in pandas: How to Find the Max Value by Group in Pandas How to Find Sum by Group in Pandas How to Calculate Quantiles by Group in Pandas
Discussions

Pandas.groupby.mean() returns infinity although finite values used in the dataframe
I am aggregating over some data-frame with respect to entity IDs ('Pickup Census Tract') and averaging the corresponding values ('milePrice'), In the outcome df, some rows are inf, ... More on github.com
🌐 github.com
2
December 1, 2019
Grouping by week in Pandas
I'm not entirely sure what your df is like (can you share the result of df.head()?), but if you have a row column with type datetime (or can get one with pd.to_datetime()), then try df.groupby(df['date'].dt.week).count() where 'date' is the name of your dates column. You can also get other summary statistics by replacing .count() with e.g. .mean(). More on reddit.com
🌐 r/datascience
8
4
June 3, 2016
Pandas group by quintile
Are you sure you're not looking for qcut()? https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.qcut.html More on reddit.com
🌐 r/learnpython
5
1
March 30, 2019
pandas .groupby.mean() is for some reason messing up with column headers
That s not a column header. It's the name of the index. When you group on one or more columns, those columns essentially become the index. By default, df indices don't have names. But when the index does have a name, this is how pandas displays it (i.e., on its own line, below the column headers but above the first row of data). This is why when you group on 'Cocoa', you see that become the name of the index. Nothing funky going on here :) Edit: Here's the proof: >>> import numpy as np, pandas as pd >>> df = pd.DataFrame(np.random.rand(5, 2), columns=['A', 'B']) >>> df A B 0 0.846377 0.513847 1 0.726443 0.601799 2 0.878659 0.671620 3 0.966029 0.302133 4 0.739392 0.293212 >>> df.index.name = 'foo' >>> df A B foo 0 0.846377 0.513847 1 0.726443 0.601799 2 0.878659 0.671620 3 0.966029 0.302133 4 0.739392 0.293212 More on reddit.com
🌐 r/learnpython
2
4
November 8, 2021
🌐
GeeksforGeeks
geeksforgeeks.org › pandas › python-pandas-dataframe-groupby
Pandas dataframe.groupby() Method - GeeksforGeeks
Pandas groupby() function is a powerful tool used to split a DataFrame into groups based on one or more columns, allowing for efficient data analysis and aggregation. It follows a "split-apply-combine" strategy, where data is divided into groups, ...
Published   July 11, 2025
🌐
Pandas
pandas.pydata.org › docs › reference › api › pandas.DataFrame.groupby.html
pandas.DataFrame.groupby — pandas 3.0.1 documentation
The implementation of groupby is hash-based, meaning in particular that objects that compare as equal will be considered to be in the same group. An exception to this is that pandas has special handling of NA values: any NA values will be collapsed to a single group, regardless of how they compare.
🌐
Saturn Cloud
saturncloud.io › blog › how-to-get-the-average-of-a-groupby-with-pandas
How to Get the Average of a Groupby with Pandas | Saturn Cloud Blog
October 23, 2023 - To get the average of a groupby in pandas, you can use the mean() method on the GroupBy object. This method calculates the mean of each numeric column for each group. We can group this DataFrame by the product column and then calculate the average ...
🌐
Medium
medium.com › @amit25173 › understanding-groupby-and-how-to-calculate-the-average-in-pandas-1bea7538989c
Understanding groupby and How to Calculate the Average in Pandas | by Amit Yadav | Medium
March 6, 2025 - Think of it like sorting items into baskets and then performing operations (like averaging) on each basket separately. ... This groups your data based on a specific column and calculates the mean (average) for each group.
🌐
Towards Data Science
towardsdatascience.com › home › latest › how to group-by pandas dataframes to compute the mean
How to Group-By Pandas DataFrames to Compute the Mean | Towards Data Science
January 18, 2025 - In today’s short tutorial we will be showcasing how to perform Group-By operations over pandas DataFrames in order to compute the mean (aka average) and median values per group.
Find elsewhere
🌐
Pandas
pandas.pydata.org › docs › user_guide › groupby.html
Group by: split-apply-combine — pandas 3.0.1 documentation
SELECT Column1, Column2, mean(Column3), sum(Column4) FROM SomeTable GROUP BY Column1, Column2 · We aim to make operations like this natural and easy to express using pandas.
🌐
Pandas
pandas.pydata.org › docs › reference › groupby.html
GroupBy — pandas 3.0.1 documentation
pandas.api.typing.DataFrameGroupBy and pandas.api.typing.SeriesGroupBy instances are returned by groupby calls pandas.DataFrame.groupby() and pandas.Series.groupby() respectively.
🌐
Medium
medium.com › data-science › all-about-pandas-groupby-explained-with-25-examples-494e04a8ef56
All About Pandas Groupby Explained with 25 Examples | by Soner Yıldırım | TDS Archive | Medium
June 30, 2023 - The groupby is one of the most frequently used Pandas functions in data analysis. It is used for grouping the data points (i.e. rows) based on the distinct values in the given column or columns.
🌐
YouTube
youtube.com › data science parichay
Get Mean Value in Each Group in Pandas | Python Tutorial - YouTube
How do you get the mean value after a groupby operation in Pandas?In this tutorial, we explore how to get the mean value in each group in a Pandas dataframe....
Published   February 15, 2023
Views   165
🌐
YouTube
youtube.com › statistics globe
Calculate Mean by Group in Python (2 Examples) | Average of Subgroups | groupby() & mean() Functions - YouTube
How to get the mean by group in the Python programming language. More details: https://statisticsglobe.com/calculate-mean-group-pythonPython code of this vid...
Published   May 13, 2022
Views   792
🌐
GitHub
github.com › numpy › numpy › issues › 15371
Pandas.groupby.mean() returns infinity although finite values used in the dataframe · Issue #15371 · numpy/numpy
December 1, 2019 - I am aggregating over some data-frame with respect to entity IDs ('Pickup Census Tract') and averaging the corresponding values ('milePrice'), In the outcome df, some rows are inf, although I removed any inf values before performing my calculations, I expect this issue has to do with the datatype float64 and the average algorithm used in Pandas (numpy), and tried to round my data and limit the precision to 2 decimals, but it didn't help much, I also tried to get the sum and I would then divide by the entities count, but also didn't help, I am new to Python and there must be something I miss! Any advice on this issue? import numpy as np gb=totalTrips.groupby('Pickup Census Tract') gb.agg({ 'Pickup Census Tract':np.count_nonzero, 'milePrice':np.mean }) No one assigned ·
Author   BishoyKelleny
🌐
YouTube
youtube.com › luiz rodrigues
PANDAS #8 - GROUP BY (AGRUPAMENTOS), AGG, MAX, MIN, MEAN, SUM, COUNT, SIZE, ... - YouTube
AboutPressCopyrightContact usCreatorsAdvertiseDevelopersTermsPrivacyPolicy & SafetyHow YouTube worksTest new features · © 2024 Google LLC
Published   October 19, 2021
Views   2K
🌐
Spark By {Examples}
sparkbyexamples.com › home › pandas › pandas groupby() and sum() with examples
Pandas groupby() and sum() With Examples
July 3, 2025 - The groupby() function in Pandas is used to group rows of a DataFrame based on one or more columns. It creates a DataFrameGroupBy object which is useful for performing aggregate functions on grouped data.
🌐
AskPython
askpython.com › home › pandas: conditionally grouping values
Pandas: Conditionally Grouping Values - AskPython
August 6, 2022 - # We split the dataset by column 'Branch'. # Rows having the same Branch will be in the same group. groupby = df.groupby('Branch', axis=0) # We apply the accumulator function that we want. Here we use the mean function here but we can also other functions.
🌐
Auth
pytolearn.csd.auth.gr › b4-pandas › 40 › groupby.html
groupby
After grouping you can proceed and implement all available numpy/scipy/pandas-based statistics in the subgroups thus increasing the power of your analysis. Of course, you need to additionally enter in your data spreadsheet the information necessary for grouping (the grouping factor(s)). We are going now to input some sample data to explore the potential offered by 'groupby'