0

Below is the data that I have:

There are three columns the class, date and marks

I need my output to be in the format below: Here column headers 1,2,3 are the classes which contain the total marks stored month and year wise

My approach to this was using the following logic: (Although I get the result, I am not satisfied with the code. I was hoping that someone could help me with a more efficient solution for the same?)

class=sorted(df.Class.unique())
dict_all = dict.fromkeys(class , 1)
for c in class:
    actuals=[]
    for i in range(2018,2019):
       for j in range(1,13):
          a = df['date'].map(lambda x : x.year == i)
          b = df['date'].map(lambda x : x.month == j)
          x= df[a & b & (df.class==c)].marks.sum()
          actuals.append(x)
     dict_all[c]=actuals
result = pd.DataFrame.from_dict(dict_all)
FObersteiner
  • 22,500
  • 8
  • 42
  • 72
Mat
  • 41
  • 5
  • Does this answer your question? [how-to-pivot-a-dataframe](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) – Anurag Dabas Jun 06 '21 at 11:30

1 Answers1

0

Input:

   Class        Date  Total
0      3  01-01-2018     32
1      2  01-01-2018     69
2      1  01-01-2018    129
3      3  01-01-2019     12

Using df.pivot

df1 = df.pivot(index="Date",columns="Class", values="Total", ).reset_index().fillna(0)
print(df1)

Using crosstab

df1 = pd.crosstab(index=df["Date"],columns=df["Class"], values=df["Total"],aggfunc="max").reset_index().fillna(0)
print(df1)

Using groupby & unstack()

df1 = df.groupby(['Date','Class'])['Total'].max().unstack(fill_value=0).reset_index()
print(df1)

All the code(s) gives the output:

Class        Date    1   2   3
0      01-01-2018  129  69  32
1      01-01-2019    0   0  12
sharathnatraj
  • 1,614
  • 5
  • 14