Sum values in dataframe depending on columns

Question

Let's say I have the following dataframe:

df = pd.DataFrame([[1, np.nan, 4, 5, 6, 7], [1, 3, 2, 1, np.nan, 8], [4, 6, 1, 1, 1, 4]], columns=['Student1', 'Student2', 'Student3', 'Exam1', 'Exam2', 'Exam3'])

>>> df
   Student1  Student2  Student3  Exam1  Exam2  Exam3
0         1       NaN         4      5    6.0      7
1         1       3.0         2      1    NaN      8
2         4       6.0         1      1    1.0      4

I want to sum the cells of Students and Exams only when a value in a specific column is not nan. For the first row, for the example, I could sum only Student1 & Student3 (result in column student_sum) and Exam1 and Exam3, since the Student2 is empty, Exam2 is not considered as well. I've tried to iterate using itertuples, but the problem is that it sums everything.

The results should be:


   Student1  Student2  Student3  Exam1  Exam2  Exam3  Res_stud Res_exam
0         1       NaN         4      5    6.0      7         5       12
1         1       3.0         2      1    NaN      8         3       9
2         4       6.0         1      1    1.0      4        11       6

Because Exam2 is Nan, so Student2 is not taken into account. — nivomo
– nivomo, Commented Jul 27, 2021 at 6:46

Anurag Dabas · Accepted Answer · 2021-07-26 16:46:04Z

2

Another way:

stu=df.filter(like='Student').columns
exam=df.filter(like='Exam').columns
#Grab Columns Name

Finally pass opposite mask of each other to each other:

df['Student_Sum']=df[stu].mask(df[exam].isna().values).sum(1)
df['Exam_Sum']=df[exam].mask(df[stu].isna().values).sum(1)

output of df:

   Student1     Student2    Student3    Exam1   Exam2   Exam3   Student_Sum     Exam_Sum
0   1               NaN         4       5       6.0     7       5.0             12.0
1   1               3.0         2       1       NaN     8       3.0              9.0
2   4               6.0         1       1       1.0     4       11.0             6.0

answered Jul 26, 2021 at 16:46

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum values in dataframe depending on columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related