I have some students data that I need to get average. What I've realized is that when I work it out in pandas, I get the average as according to the number of subjects done by a student, it it doesn't include those subjects that the student didn't sit for. I however, need it to work out the average using the total number of subjects in that class(including those the student didn't sit for). Is this even possible??
df["AVE"] = df[column_list].mean(axis=1).round(decimals=0)
Replace missing values by 0 by DataFrame.fillna:
df = pd.DataFrame({
'A':list('abcdef'),
'B':[np.nan,5,np.nan,5,5,np.nan],
'C':[7,8,9,np.nan,2,3],
'D':[1,3,5,7,1,0],
'E':[5,3,6,9,2,np.nan],
'F':list('aaabbb')
})
column_list = ['B','C','D']
df["AVE1"] = df[column_list].mean(axis=1).round(decimals=0)
df["AVE2"] = df[column_list].fillna(0).mean(axis=1).round(decimals=0)
print (df)
A B C D E F AVE1 AVE2
0 a NaN 7.0 1 5.0 a 4.0 3.0
1 b 5.0 8.0 3 3.0 a 5.0 5.0
2 c NaN 9.0 5 6.0 a 7.0 5.0
3 d 5.0 NaN 7 9.0 b 6.0 4.0
4 e 5.0 2.0 1 2.0 b 3.0 3.0
5 f NaN 3.0 0 NaN b 2.0 1.0
Thanks for this has done it well.
df["AVE"] = df[column_list].fillna(0).mean(axis=1).round(decimals=0)
If you want to return NaN when there is a NaN, as in Numpy mean(), you can use skipna argument for skeeping/not skeeping, NA/null values. By default this argument is set to True:
df["AVE"] = df[column_list].mean(axis=1, skipna=False).round(decimals=0)
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.mean.html