I have a difficulty in calculating “total_sum.” If someone didn’t apply to subject, I expressed N/A. When total_sum is calculated, total_sum refer to Standard field and N/A is excluded. I’m not good at Python, So I don’t know how to calculate “total_sum”
ENG MATH ART COM subject_sum total_sum Standard 10 10 5 5 A 10 N/A N/A 1 11 15 B N/A 5 3 5 13 20 C 3 3 2 2 10 30
Advertisement
Answer
Suppose this dataframe is the same as yours (with index of strings)
dataframe = {'index' :['Standard', 'A', 'B', 'C'],
'ENG' : [10, 10, np.nan, 3],
'MATH' : [10, np.nan, 5, 3],
'ART' : [5, np.nan, 3, 2],
'COM' : [5, 1, 5, 2]}
df = pd.DataFrame(dataframe).set_index('index').rename_axis(None)
df['subject_sum'] = df.sum(axis=1)
df
ENG MATH ART COM subject_sum
Standard 10.0 10.0 5.0 5 30.0
A 10.0 NaN NaN 1 11.0
B NaN 5.0 3.0 5 13.0
C 3.0 3.0 2.0 2 10.0
then you can apply .dot() of every .notna() subject values to the values of Standard
standard = df.loc['Standard', ['ENG', 'MATH', 'ART', 'COM']] df['total_sum'] = df[['ENG', 'MATH', 'ART', 'COM']].notna().dot(standard) df
result :
ENG MATH ART COM subject_sum total_sum Standard 10.0 10.0 5.0 5 30.0 30.0 A 10.0 NaN NaN 1 11.0 15.0 B NaN 5.0 3.0 5 13.0 20.0 C 3.0 3.0 2.0 2 10.0 30.0