1

I'm trying to calculate the value of one column based on the entries of another column, I'm trying to achieve this using for loop, I don't know how to return the values for entire column, rather just the last iteration, below is an example of my code, please help me find where I'm doing wrong.

def marks_calculation(Master_Sheet):
    for a in Master_Sheet.Marks Condition:
        if a == "Yes":
            Master_Sheet['Total'] = Master_Sheet['Total Marks']*0.85
            Master_Sheet.loc[(Master_Sheet['Total Marks'] > Master_Sheet['Basic1']) & (Master_Sheet['Total Marks'] <= Master_Sheet['Basic3']),'Total'] = (Master_Sheet['Basic1'] * Master_Sheet['Slab1']) + ((Master_Sheet['Total Marks'] - Master_Sheet['Basic1']) * Master_Sheet['Slab2'])
            Master_Sheet.loc[(Master_Sheet['Total Marks'] > Master_Sheet['Basic2']),'Total'] = (Master_Sheet['Basic1'] * Master_Sheet['Slab1']) + ((Master_Sheet['Basic2'] - Master_Sheet['Basic1']) * Master_Sheet['Slab2']) + ((Master_Sheet['Total Marks'] - Master_Sheet['Basic2']) * Master_Sheet['Slab3'])
            return Master_Sheet['Total']
        else :     
            Master_Sheet['Total'] = Master_Sheet['Aggregated Marks']
            return Master_Sheet['Total']
Master_Sheet['Total'] = Master_Sheet['Total'].replace("",0, regex=True)  
Master_Sheet[['Students','Total Marks','Aggregated Marks','Marks Condition','Total']]

e.g. Data

Student Condition Aggregated TotalMarks Basic1 Basic2 Slab1 Slab2 Slab3 Total
A         Yes       65.34      54.29      45     55    49%   64%   82%  28.00
B         No        75.65      94.32      23     54    73%   81%   33%  75.65
C         No        87.9       82.9       67     78    85%   54%   46%  87.90
D         Yes       59.4       92.02      75     83    53%   71%   47%  47.59
E         No        83.05      62.45      23     35    70%   35%   23%  83.05

Here the Total has only to be calculated if the Condition is Yes, if it is NO, we need to use data in Aggregated. If the Condition is Yes, the calculation for Total goes as below:

If(TotalMarks<=Basic1, TotalMarksSlab1, If(AND(TotalMarks>Basic1, TotalMarks<=Basic2),(Basic1Slab1)+((TotalMarks-Basic1)Slab2),(Basic1Slab1)+((TotalMarks-Basic1)*Slab2)+((Basic2-TotalMarks)*Slab3)))

mamta_rao
  • 29
  • 4
  • Related answers: https://stackoverflow.com/questions/51787247/pandas-update-column-values-from-another-column-if-criteria and https://stackoverflow.com/questions/19913659/pandas-conditional-creation-of-a-series-dataframe-column – AverageHomosapien Nov 25 '20 at 09:13
  • I wanted to stick to this method, for the sake of readability, np.where gets a little complex, for my requirements, is there any way I can make this one work. – mamta_rao Nov 25 '20 at 12:37

1 Answers1

0

I couldn't understand the calculation example so I wasn't able to produce something for the yes column. The following line will update all your 'Total' No entries to the aggregated data column.

Master_Sheet['Total'].loc[Master_Sheet['Condition']=='No'] = Master_Sheet['Aggregated']
AverageHomosapien
  • 609
  • 1
  • 7
  • 15