Pandas groupby mean – into a dataframe?

by

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

More Related Contents:

  1. Get statistics for each group (such as count, mean, etc) using pandas GroupBy?
  2. Aggregation in Pandas
  3. Groupby value counts on the dataframe pandas
  4. Converting a Pandas GroupBy output from Series to DataFrame
  5. Multiple aggregations of the same column using pandas GroupBy.agg()
  6. Python Pandas max value in a group as a new column
  7. How to loop over grouped Pandas dataframe?
  8. How to access pandas groupby dataframe by key
  9. Pandas Groupby and Sum Only One Column
  10. How to drop duplicates based on two or more subsets criteria in Pandas data-frame
  11. Python Pandas Group by date using datetime data
  12. Pandas get frequency of item occurrences in a column as percentage [duplicate]
  13. Pandas – dataframe groupby – how to get sum of multiple columns
  14. Group dataframe and get sum AND count?
  15. How to summarize on different groupby combinations?
  16. get first and last values in a groupby
  17. Pandas groupby and aggregation output should include all the original columns (including the ones not aggregated on)
  18. Get the row corresponding to the max in pandas GroupBy [duplicate]
  19. How to get number of groups in a groupby object in pandas?
  20. When is it appropriate to use df.value_counts() vs df.groupby(‘…’).count()?
  21. How are iloc and loc different?
  22. Splitting dataframe into multiple dataframes
  23. Efficient way to unnest (explode) multiple list columns in a pandas DataFrame
  24. How to convert a dataframe to a dictionary
  25. Calculate average of every x rows in a table and create new table
  26. How to move pandas data from index to column after multiple groupby
  27. Removing prefix from column names in Pandas
  28. How to merge multiple dataframes
  29. Extract int from string in Pandas
  30. Returning multiple values from pandas apply on a DataFrame

Leave a Comment