python - Multi Level Filtering and Frequency count of a series in a dataframe

Question

I currently have following:

   Department   Semester      Grade   
      D1         Fall-2019        A
      D1         Fall-2019        B
      D1         Spring-2020      A
      D1         Spring-2020      A
      D1         Spring-2019      C
      D1         Spring-2019      C
      D1         Fall-2019        A
      D1         Fall-2019        A

Department is constant, I want the each grade count in new column per semester level.

Essentially the output I am looking for is:

   Department   Semester      Grade A   Grade B Grade C
      D1         Fall-2019        3        1     0
      D1         Spring-2019      0        0     2 
      D1         Spring-2020      2        0     0

Any help would be greatly appreciated!

Edit: As an extension to this question: If I need to derive the below table from the original table how do I go about?

   Department   Semester      GradeType1   GradeType2 GradeType3
      D1         Fall-2019       GradeA      GradeB     NaN
      D1         Spring-2019     NaN         NaN       GradeC 
      D1         Spring-2020     GradeA      NaN       Nan

Thanks in advance!!!

question from:https://stackoverflow.com/questions/65887370/multi-level-filtering-and-frequency-count-of-a-series-in-a-dataframe

Answer 1

This is crosstab:

(pd.crosstab([df['Department'], df['Semester']], df['Grade'])
   .add_prefix('Grade ')
   .reset_index()
)

which is a short-hand for groupby().value_counts() then unstack:

(df.groupby(['Department','Semester'])['Grade']
   .value_counts()
   .unstack(fill_value=0)
   .reset_index()
)

Output:

Grade Department     Semester  Grade A  Grade B  Grade C
0             D1    Fall-2019        3        1        0
1             D1  Spring-2019        0        0        2
2             D1  Spring-2020        2        0        0