CODEWITHSUNDEEP
pythonpandasnumpydataframe
rbelliv
rbelliv

Reputation: 127

Calculating top 10 average per year in python (groupby, nlargest)

I would like to get the average of the top 10 "Net Profits" per year in the data set and have it stored in new df. Here is what I have so far.

#loading the csv file
df = pd.read_csv('tmdb-movies.csv')

#Narrows down data to only include the last 25 years
df = df[df['release_year'] > 1992]

#Create column for calculated profit (revenue - budget)
df['Net Profit'] = (df['revenue_adj'] - df['budget_adj']) / 1000000

df = df['Net Profit'].groupby(df['release_year']).nlargest(10)

df.head(20)

This is the output I have so far:

release_year       
1993          10223    1293.766704
              10225     628.371507
              10226     490.390201
              10222     451.732687
              10312     345.086055
              10260     344.534054
              10240     312.158213
              10234     279.252261
              10228     272.728678
              10250     226.825117
1994          4180     1093.391570
              4179      916.422179
              4187      483.382849
              4181      471.414971
              4203      434.906488
              4186      388.200466
              4197      365.866669
              4182      338.760765
              4177      302.944183
              4194      246.901592
Name: Net Profit, dtype: float64

My desired output would be something like:

release_year      Average Net Profit
1993              400
1994              459
1995              480

Upvotes: 1

Views: 299

Answers (2)

BENY
BENY

Reputation: 323366

You can do with mean pass with level

df.mean(level=0)

Upvotes: 2

rafaelc
rafaelc

Reputation: 59284

Group by the level=0 for you index and use .mean()

df.groupby(df.index.get_level_values(level=0)).mean()

release_year
1993    464.484548
1994    504.219173
1995    511.392629
1996    424.185431
1997    543.448686
Name: Net Profit, dtype: float64

Upvotes: 2

Related Questions