Changing columns to row and its values to column and show the count values

Question

I have a dataframe like this

|Class ID| Student ID||English| Maths|Science|
|:------:|:---------:|:------:|:----:|:-----:|
|    1   |     101   |  Pass  | Fail |  Pass |
|    1   |     102   |  Fail  | Pass |  Pass |
|    1   |     103   |  Pass  | Fail |  Fail |
|    1   |     104   |  Pass  | Pass |  Fail |
|    2   |     101   |  Fail  | Fail |  Pass |
|    2   |     102   |  Fail  | Pass |  Pass |
|    2   |     103   |  Fail  | Fail |  Fail |
|    2   |     104   |  Pass  | Pass |  Fail |

I want a output like this

|Class ID| Subjects|Pass|Fail|
|:------:|:-------:|:--:|:--:|
|    1   | English |  3 |  1 |
|    1   | Maths   |  2 |  2 |
|    1   | Science |  2 |  2 |
|    2   | English |  1 |  3 |
|    2   | Maths   |  2 |  2 |
|    2   | Science |  2 |  2 |

Is this possible,

How do I write in python to get this kind of data frame?

score 0 · Answer 1 · answered Sep 24 '22 at 10:48

use .melt then .groupby

df1 = pd.melt(df,id_vars=['Class ID', 'Student ID'],var_name='Subjects')

df1.groupby(['Class ID','Subjects','value']).size().unstack(-1).reset_index()


value Class ID Subjects  Fail  Pass
0            1  English     1     3
1            1     Math     2     2
2            1  Science     2     2
3            2  English     3     1
4            2     Math     2     2
5            2  Science     2     2

pivot_table also works after you use .melt

df1.pivot_table(index=['Class ID','Subjects'],columns='value',aggfunc='count')

                  Student ID
value                   Fail Pass
Class ID Subjects
1        English           1    3
         Math              2    2
         Science           2    2
2        English           3    1
         Math              2    2
         Science           2    2

Changing columns to row and its values to column and show the count values

1 Answers1