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

user_13648306 0
Viewed 78

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!!!

pandas-groupby pandas python-3.x numpy python
2 Answers

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
user_42384080

Update to meet new requirement in question:

df = pd.read_clipboard()

df['type'] = (df['Grade'].factorize()[0]+1).astype(str)

df['type'] = 'GradeType'+df['type']
df['Grade'] =  'Grade'+df['Grade']

df.drop_duplicates().set_index(['Department','Semester','type'])['Grade']\
  .unstack('type').reset_index()

Output:

type Department     Semester GradeType1 GradeType2 GradeType3
0            D1    Fall-2019     GradeA     GradeB        NaN
1            D1  Spring-2019        NaN        NaN     GradeC
2            D1  Spring-2020     GradeA        NaN        NaN

Try this:

(pd.get_dummies(df, columns=['Grade'], prefix_sep=' ')
   .groupby(['Department', 'Semester'], as_index=False)
   .sum()
)

Output:

  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
user_63615310
Related