groupby weighted average and sum in pandas dataframe

by

EDIT: update aggregation so it works with recent version of pandas

To pass multiple functions to a groupby object, you need to pass a tuples with the aggregation functions and the column to which the function applies:

# Define a lambda function to compute the weighted mean:
wm = lambda x: np.average(x, weights=df.loc[x.index, "adjusted_lots"])

# Define a dictionary with the functions to apply for a given column:
# the following is deprecated since pandas 0.20:
# f = {'adjusted_lots': ['sum'], 'price': {'weighted_mean' : wm} }
# df.groupby(["contract", "month", "year", "buys"]).agg(f)

# Groupby and aggregate with namedAgg [1]:
df.groupby(["contract", "month", "year", "buys"]).agg(adjusted_lots=("adjusted_lots", "sum"),  
                                                      price_weighted_mean=("price", wm))

                          adjusted_lots  price_weighted_mean
contract month year buys                                    
C        Z     5    Sell            -19           424.828947
CC       U     5    Buy               5          3328.000000
SB       V     5    Buy              12            11.637500
W        Z     5    Sell             -5           554.850000

You can see more here:

and in a similar question here:

Hope this helps

[1] : https://pandas.pydata.org/pandas-docs/stable/whatsnew/v0.25.0.html#groupby-aggregation-with-relabeling

More Related Contents:

  1. What are Python pandas equivalents for R functions like str(), summary(), and head()?
  2. How to convert R dataframe back to Pandas using rpy2?
  3. Calculate cumulative intraday measures that reset every day in pandas
  4. Convert a Pandas DataFrame to a dictionary
  5. Extracting just Month and Year separately from Pandas Datetime column
  6. Python matplotlib multiple bars
  7. Pandas column access w/column names containing spaces
  8. How to get value counts for multiple columns at once in Pandas DataFrame?
  9. Add x and y labels to a pandas plot
  10. Plot different DataFrames in the same figure
  11. How to apply a function on every row on a dataframe?
  12. How do I round datetime column to nearest quarter hour
  13. How to plot and annotate grouped bars in seaborn / matplotlib
  14. Divide multiple columns by another column in pandas
  15. Access index in pandas.Series.apply
  16. Python Pandas to_sql, how to create a table with a primary key?
  17. return max value from pandas dataframe as a whole, not based on column or rows
  18. Export Pandas DataFrame into a PDF file using Python
  19. Concat DataFrame Reindexing only valid with uniquely valued Index objects
  20. pandas – Merge nearly duplicate rows based on column value
  21. Pivot Tables or Group By for Pandas?
  22. pandas fillna not working
  23. Giving a column multiple indexes/headers
  24. How to select rows with NaN in particular column?
  25. datetime x-axis matplotlib labels causing uncontrolled overlap
  26. pandas convert from datetime to integer timestamp
  27. geopandas point in polygon
  28. mean from pandas and numpy differ
  29. Rename columns in Pandas based on a dictionary
  30. When is it appropriate to use df.value_counts() vs df.groupby(‘…’).count()?

Leave a Comment