Pandas Pivot table and Rank

  dataframe, pandas, pivot-table, python

I have below dataframe

df = pd.DataFrame()
df['SubjectArea'] = ["a","b","a","c","a","s","d","b","s","a","s","c","s","z","a"]
df['Articles'] = [10, 20,5,58,98,15,35,89,47,15,25,145,89,689,25]
df['NoOfReading'] = [30, 40,45,25,35,88,68,98,45,125,255,np.nan,75,125,265]
df
    SubjectArea Articles    NoOfReading
0       a         10            30.0
1       b         20            40.0
2       a         5             45.0
3       c         58            25.0
4       a         98            35.0
5       s         15            88.0
6       d         35            68.0
7       b         89            98.0
8       s         47            45.0
9       a         15            125.0
10      s         25            255.0
11      c         145           NaN
12      s         89            75.0
13      z         689           125.0
14      a         25            265.0

I wanted to create a dataframe like the below for each subject area and rank going to give based on the weighted average.

df.fillna(0, inplace=True)
df["weightedAverage"] = df["Articles"]*0.35 + df["NoOfReading"]*0.65
df2 = df[df["SubjectArea"]=="a"]
##df2["weightedAverage"] = df2["Articles"]*0.35 + df2["NoOfReading"]*0.65
df2 = df2.sort_values(by="weightedAverage",ascending=[False])
df2['Rank'] = df2['weightedAverage'].rank(method='dense', ascending=False)
df2.index = range(len(df2))
df2
    SubjectArea Articles    NoOfReading weightedAverage Rank
0       a       25          265.0           181.00      1.0
1       a       15          125.0           86.50       2.0
2       a       98          35.0            57.05       3.0
3       a       5           45.0            31.00       4.0
4       a       10          30.0            23.00       5.0

So, I wanted to create one dataframe content for all the "subjectArea" with their rank and weighted average like below one.

   SubjectArea Articles    NoOfReading weightedAverage Rank
0       a       25          265.0           181.00      1.0
1       a       15          125.0           86.50       2.0
2       a       98          35.0            57.05       3.0
3       a       5           45.0            31.00       4.0
4       a       10          30.0            23.00       5.0
  SubjectArea  Articles    NoOfReading weightedAverage Rank
0       b       89          98.0            94.85       1.0
1       b       20          40.0            33.00       2.0
.
.
.
.
.

Is it possible to create a similar one using pandas pivot table with Rank? or any other method?

Any help would be appreciated. Thanks in advance.!!

Source: Python Questions

LEAVE A COMMENT