Undoing corrections to a big dataframe

Question

I have 2 dataframes. The first one (900 lines) contains corrections that have been applied to a deal. The second dataframe (140,000 lines) contains the list of deals with corrected values. What I am trying to do is to put the old value back.

To link the corrected deals to the corrections I have to compare a number of attributes. In the correction dataframe (900 lines) I have the old and the new value for each corrected attribute. But each correction can be corrected on a different attribute, therefore I check every possible corrected attribute (in the correction dataframe) to compare the new value with the old one and check if this attribute was corrected. If it was I put the old value back. I'm precise that a correction can apply on several deals that share the same data in the fields used to identify.

To finish, I create a new column on the Deals dataframe (140,000 lines) where I put a boolean that true when a deals has been uncorrected, false otherwise.

My code right now is quite gross, I wanted to factorize a bit but the iteration process blocked me. It is running but it has to go through 900*140,000 lines. I launched it all night long (14h) on a Quad Core VM with 12GB RAM and it only went through 150*140,000 in this time.

How can I improve performance?

def Uncorrection(Correction,dataframe):
    dataframe['Modified']=np.nan
    #getting the link between the corrections and deals
    b=0

for index in Correction.index:
    b+=1 #just values to see progression of the program
    c=0
    for index1 in dataframe.index:
        c+=1
        a=0
        print('Handling correction '+str(b)+' and deal '+str(c)) # printing progress
        if (Correction.loc[index,'BO Branch Code']==dataframe.loc[index1,'Wings Branch'] and Correction.loc[index,'Profit Center']==dataframe.loc[index1,'Profit Center'] and Correction.loc[index,'Back Office']==dataframe.loc[index1,'Back Office']
            and Correction.loc[index,'BO System Code']==dataframe.loc[index1,'BO System Code']):

            if ((Correction.loc[index,'BO Trade Id']==dataframe.loc[index1,'BO Trade Id'] and Correction.loc[index,'BO Trade Id']!='#') or
                (Correction.loc[index,'Emetteur Trade Id']==dataframe.loc[index1,'Emetteur Trade Id']=='#' and Correction.loc[index,'BO Trade Id']==dataframe.loc[index1,'BO Trade Id'])):
                if (Correction.loc[index,'UE']==dataframe.loc[index1,'SGC Code'] and Correction.loc[index,'Id Ricos']==dataframe.loc[index1,'Siris Id']):
                    if Correction.loc[index,'Status']=='Modified X':
                        if Correction.loc[index,'Maturity Date']==dataframe.loc[index1,'Maturity Date'] and Correction.loc[index,'Start Date']==dataframe.loc[index1,'Start Date']:
                            # putting the dataframe to the old state, we need the data in the bad shape to make the computer learn what is a bad trade and what is normal
                            if Correction.loc[index,'Risk Category']!=Correction.loc[index,'Risk Categgory _M'] and Correction.loc[index,'Risk Category _M']!='':
                                dataframe.loc[index1,'Risk Category']=Correction.loc[index,'Risk Category']
                                a=1
                            if Correction.loc[index,'CEC Ricos']!=Correction.loc[index,'CEC Ricos _M'] and Correction.loc[index,'CEC Ricos _M']!='':
                                dataframe.loc[index1,'CEC Ricos']=Correction.loc[index,'CEC Ricos']
                                a=1
                            if Correction.loc[index,'Product Line']!= Correction.loc[index,'Product Line _M'] and Correction.loc[index,'Product Line _M']!='':
                                dataframe.loc[index1,'Product Line Code Ricos']=Correction.loc[index,'Product Line']
                                a=1
                            # if Correction.loc[index,'BS/OBS']!=Correction.loc[index,'BS/OBS _M'] and Correction.loc[index,'BS/OBS _M']!='' :    #Unused attributes
                            #     dataframe.loc[index1,'BS/OBS']=Correction.loc[index,'BS/OBS']
                            #     a=1
                            if Correction.loc[index,'Instrument']!= Correction.loc[index,'Instrument _M'] and Correction.loc[index,'Instrument _M']!='':
                                dataframe.loc[index1,'Instrument']=Correction.loc[index,'Instrument']
                                a=1
                            if Correction.loc[index,'DGCR Manual Flag']!=Correction.loc[index,'DGCR Manual Flag _M'] and Correction.loc[index,'DGCR Manual Flag _M']!='' :
                                dataframe.loc[index1,'DGCR Manual Flag']= Correction.loc[index,'DGCR Manual Flag']
                                a=1
                            if Correction.loc[index,'Back Office Seniority']!=Correction.loc[index,'Back Office Seniority _M']:
                                dataframe.loc[index1,'BO Seniority']=Correction.loc[index,'Back Office Seniority']
                                a=1
                            if Correction.loc[index,'Basel Portfolio']!=Correction.loc[index,'Basel Portfolio _M']:
                                dataframe.loc[index1,'Basel Ptf']=Correction.loc[index,'Basel Portfolio ']
                                a=1
                            if Correction.loc[index,'LGD (%)']!=Correction.loc[index,'LGD (%) _M']:
                                dataframe.loc[index1,'LGD (%)']=Correction.loc[index,'LGD (%)']
                                a=1
                            if Correction.loc[index,'RW (%)']!=Correction.loc[index,'RW (%) _M']:
                                dataframe.loc[index1,'RW (%)']=Correction.loc[index,'RW (%)']
                                a=1
                            if Correction.loc[index,'Risk Type']!=Correction.loc[index,'Risk Type _M']:
                                dataframe.loc[index1,'Risk Type']=Correction.loc[index,'Risk Type _M']
                                a=1
                            if Correction.loc[index,'Confirmed Credit']!=Correction.loc[index,'Confirmed Credit _M']:
                                dataframe.loc[index1,'Confirmed Credit']= Correction.loc[index,'Risk Type']
                                a=1
                            if Correction.loc[index,'Uncertain Belief'] != Correction.loc[index,'Uncertain Belief _M']:
                                dataframe.loc[index1,'Uncertain Belief']=Correction.loc[index,'Uncertain Belief']
                                a=1
                            if Correction.loc[index,'Played Flag']!= Correction.loc[index,'Played Flag _M']:
                                dataframe.loc[index1,'Played Flag']=Correction.loc[index,'Played Flag']
                                a=1
                            if Correction.loc[index,'Cap Interest Flag']!= Correction.loc[index,'Cap Interest Flag _M']:
                                dataframe.loc[index1,'Cap Int Flag']=Correction.loc[index,'Cap Interest Flag']
                                a=1
                            if Correction.loc[index,'Original Maturity Type']!=Correction.loc[index,'Original Maturity Type _M']:
                                dataframe.loc[index1,'Original Maturity Type']=Correction.loc[index,'Original Maturity Type']
                                a=1
                            if Correction.loc[index,'Maturity Type']!= Correction.loc[index,'Maturity Type _M']:
                                dataframe.loc[index1,'Maturity Date']=Correction.loc[index,'Maturity Date']
                                a=1
                            # if Correction.loc[index,'Amount']!= Correction.loc[index,'Amount _M']:    #Unused attributes
                            #     dataframe.loc[index1,'Amount']=Correction.loc[index,'Amount']
                            #     a=1
                            if Correction.loc[index,'Flag IntraGroup']!=Correction.loc[index,'Flag IntraGroup _M']:
                                dataframe.loc[index1,'Flag Intra Group']=Correction.loc[index,'Flag IntraGroup _M']
                                a=1
                            # if Correction.loc[index,'Correction effective date']!= Correction.loc[index,'Correction effective date _M']:    #Unused attributes
                            #     dataframe.loc[index1,'Correction effective date']=Correction.loc[index,'Correction effective date']
                            #     a=1
                            # if Correction.loc[index,'Correction maturity date']!=Correction.loc[index,'Correction maturity date _M']:    #Unused attributes
                            #     dataframe.loc[index1,'Correction maturity date']=dataframe[row]['Correction maturity date']
                            #     a=1
                            if Correction.loc[index,'Restructuration Flag']!= Correction.loc[index,'Restructuration Flag _M']:
                                dataframe.loc[index1,'Restructuration']=Correction.loc[index,'Restructuration Flag']
                                a=1
                            if Correction.loc[index,'Restructuration Date'] != Correction.loc[index,'Restructuration Date _M']:
                                dataframe.loc[index1,'Restructuration Date'] = Correction.loc[index,'Restructuration Date']
                                a=1
                            if Correction.loc[index,'Restructuration Exit Date'] != Correction.loc[index,'Restructuration Exit Date _M']:
                                dataframe.loc[index1,'Restructuration Exit Date'] = Correction.loc[index,'Restructuration Exit Date']
                                a=1

                    else:
                        if Correction.loc[index,'Risk Category'] != Correction.loc[index,'Risk Categgory _M']:
                            dataframe.loc[index1,'Risk Category'] = Correction.loc[index,'Risk Category']
                            a = 1
                        if Correction.loc[index,'CEC Ricos'] != Correction.loc[index,'CEC Ricos _M']:
                            dataframe.loc[index1,'CEC Ricos'] = Correction.loc[index,'CEC Ricos']
                            a = 1
                        if Correction.loc[index,'Product Line'] != Correction.loc[index,'Product Line _M']:
                            dataframe.loc[index1,'Product Line Code Ricos'] = Correction.loc[index,'Product Line']
                            a = 1
                        # if Correction.loc[index,'BS/OBS'] != Correction.loc[index,'BS/OBS _M']:    #Unused attributes
                        #     dataframe.loc[index1,'BS/OBS'] = Correction.loc[index,'BS/OBS']
                        #     a = 1
                        if Correction.loc[index,'Instrument'] != Correction.loc[index,'Instrument _M']:
                            dataframe.loc[index1,'Instrument'] = Correction.loc[index,'Instrument']
                            a = 1
                        if Correction.loc[index,'DGCR Manual Flag'] != Correction.loc[index,'DGCR Manual Flag _M']:
                            dataframe.loc[index1,'DGCR Manual Flag'] = Correction.loc[index,'DGCR Manual Flag']
                            a = 1
                        if Correction.loc[index,'Back Office Seniority'] != Correction.loc[index,'Back Office Seniority _M']:
                            dataframe.loc[index1,'BO Seniority'] = Correction.loc[index,'Back Office Seniority']
                            a = 1
                        if Correction.loc[index,'Basel Portfolio'] != Correction.loc[index,'Basel Portfolio _M']:
                            dataframe.loc[index1,'Basel Ptf'] = Correction.loc[index,'Basel Portfolio ']
                            a = 1
                        if Correction.loc[index,'LGD (%)'] != Correction.loc[index,'LGD (%) _M']:
                            dataframe.loc[index1,'LGD (%)'] = Correction.loc[index,'LGD (%)']
                            a = 1
                        if Correction.loc[index,'RW (%)'] != Correction.loc[index,'RW (%) _M']:
                            dataframe.loc[index1,'RW (%)'] = Correction.loc[index,'RW (%)']
                            a = 1
                        if Correction.loc[index,'Risk Type'] != Correction.loc[index,'Risk Type _M']:
                            dataframe.loc[index1,'Risk Type'] = Correction.loc[index,'Risk Type _M']
                            a = 1
                        if Correction.loc[index,'Confirmed Credit'] != Correction.loc[index,'Confirmed Credit _M']:
                            dataframe.loc[index1,'Confirmed Credit'] = Correction.loc[index,'Risk Type']
                            a = 1
                        if Correction.loc[index,'Uncertain Belief'] != Correction.loc[index,'Uncertain Belief _M']:
                            dataframe.loc[index1,'Uncertain Belief'] = Correction.loc[index,'Uncertain Belief']
                            a = 1
                        if Correction.loc[index,'Played Flag'] != Correction.loc[index,'Played Flag _M']:
                            dataframe.loc[index1,'Played Flag'] = Correction.loc[index,'Played Flag']
                            a = 1
                        if Correction.loc[index,'Cap Interest Flag'] != Correction.loc[index,'Cap Interest Flag _M']:
                            dataframe.loc[index1,'Cap Int Flag'] = Correction.loc[index,'Cap Interest Flag']
                            a = 1
                        if Correction.loc[index,'Original Maturity Type'] != Correction.loc[index,
                            'Original Maturity Type _M']:
                            dataframe.loc[index1,'Original Maturity Type'] = Correction.loc[index,'Original Maturity Type']
                            a = 1
                        if Correction.loc[index,'Maturity Type'] != Correction.loc[index,'Maturity Type _M']:
                            dataframe.loc[index1,'Maturity Date'] = Correction.loc[index,'Maturity Date']
                            a = 1
                        # if Correction.loc[index,'Amount'] != Correction.loc[index,'Amount _M']:    #Unused attributes
                        #     dataframe.loc[index1,'Amount'] = Correction.loc[index,'Amount']
                        #     a = 1
                        if Correction.loc[index,'Flag IntraGroup'] != Correction.loc[index,'Flag IntraGroup _M']:
                            dataframe.loc[index1,'Flag Intra Group'] = Correction.loc[index,'Flag IntraGroup _M']
                            a = 1
                        # if Correction.loc[index,'Correction effective date'] != Correction.loc[index,    #Unused attributes
                        #     'Correction effective date _M']:
                        #     dataframe.loc[index1,'Correction effective date'] = Correction.loc[index,
                        #         'Correction effective date']
                        #     a = 1
                        # if Correction.loc[index,'Correction maturity date'] != Correction.loc[index,    #Unused attributes
                        #     'Correction maturity date _M']:
                        #     dataframe.loc[index1,'Correction maturity date'] = dataframe[row]['Correction maturity date']
                        #     a = 1
                        if Correction.loc[index,'Restructuration Flag'] != Correction.loc[index,'Restructuration Flag _M']:
                            dataframe.loc[index1,'Restructuration'] = Correction.loc[index,'Restructuration Flag']
                            a = 1
                        if Correction.loc[index,'Restructuration Date'] != Correction.loc[index,'Restructuration Date _M']:
                            dataframe.loc[index1,'Restructuration Date'] = Correction.loc[index,'Restructuration Date']
                            a = 1
                        if Correction.loc[index,'Restructuration Exit Date'] != Correction.loc[index,
                            'Restructuration Exit Date _M']:
                            dataframe.loc[index1,'Restructuration Exit Date'] = Correction.loc[index,
                                'Restructuration Exit Date']
                            a = 1
        dataframe.loc[index1,'Modified']=a #here we assign the boolean to new column for each deal
return dataframe

def CreationUniqueid(dataframe,Correction):

    #creating new column to mark the rows we uncorrected
    dataframe['Modified']=0
    dataframe['index']='-1'
    #getting the link between the corrections and deals

    i=0

    while i <  Correction.shape[0]: #Correction.index
        j=0
         #just values to see progression of the program
        print('Handling correction ' + str(i))
        while j < dataframe.shape[0]:

            # print (Correction.get_value(index,'BO Branch Code').strip()+'=='+dataframe.get_value(j,'Wings Branch').strip() + ' AND ' +Correction.get_value(index,'Profit Center').strip()+'=='+dataframe.get_value(j,'Profit Center').strip()+ ' and '+ Correction.get_value(index,'Back Office').strip()+'=='+dataframe.get_value(j,'Back Office').strip()
            #        +' and '+ Correction.get_value(index,'BO System Code').strip()+ '==' + dataframe.get_value(j,'BO System').strip())
            # print('Handling correction '+str(index)+' and deal '+str(j)) # printing progress

            #
            if (Correction.get_value(i,'BO Branch Code').strip()==dataframe.get_value(j,'Wings Branch').strip() and  Correction.get_value(i,'Profit Center').strip()==dataframe.get_value(j,'Profit Center').strip() and Correction.get_value(i,'Back Office').strip()==dataframe.get_value(j,'Back Office').strip()
                and Correction.get_value(i,'BO System Code').strip()==dataframe.get_value(j,'BO System').strip()):
                #print('level 1 success')
                # dataframe.set_value(j,'Modified',1)
                if (((Correction.get_value(i,'Emetteur Trade Id').strip()==dataframe.get_value(j,'Emetteur Trade Id').strip()) and Correction.get_value(i,'Emetteur Trade Id').strip()!='#') or
                        (Correction.get_value(i,'Emetteur Trade Id').strip()=='#' and Correction.get_value(i,'BO Trade Id').strip()==dataframe.get_value(j,'Trade Id').strip())):
                    print ('level 2 success')
                    # dataframe.set_value(j, 'Modified', 2)
                    if (int(Correction.get_value(i,'UE'))==int(dataframe.get_value(j,'Entity')) and Correction.get_value(i,'Id Ricos').strip()==dataframe.get_value(j,'Siris Id').strip()):
                        print ('level 4 success')
                        # dataframe.set_value(j, 'Modified', 3)
                        if Correction.get_value(i,'Status').strip()=='Modified X':
                            # dataframe.set_value(j, 'Modified', 4)
                            print ('level 5 success')
                            if Correction.get_value(i,'Maturity Date').strip()==dataframe.get_value(j,'Maturity Date').strip() and Correction.get_value(i,'Start Date').strip()==dataframe.get_value(j,'Start Date').strip():
                                print('identification success')
                                print('Doing Corrections')
                                checkModif(Correction,dataframe,i,j)
                        else :
                            print('Level 5-B success')
                            checkModif(Correction, dataframe, i, j)
            j+=1
        i+=1
    return dataframe


def checkModif(Correction,dataframe,i,j):
    for col in Correction.columns:
        if col.strip()[-2:]=='_M' and col != 'Correction Maturity Date _M' and col != 'Correction Effective Date':
            if Correction.get_value(i,col)!='nan':
                column_modified=col[:-3]
                special_column=column_modified
                if column_modified=='Restructuration Flag':
                    special_column='Restructuration'
                if column_modified=='Flag IntraGroup':
                    special_column='Flag Intra Group'
                if column_modified=='Amount':
                    special_column='Amount Sell'
                if column_modified=='Cap Interest Flag':
                    special_column='Cap Int Flag'
                if column_modified=='Basel Portfolio':
                    special_column='Basel Ptf'
                if column_modified=='Back Office Seniority':
                    special_column='BO Seniority'
                if column_modified=='BS/OBS':
                    special_column='Bilan Hors Bilan'
                if column_modified=='Product Line':
                    special_column='Product Line Code Ricos'

                dataframe.set_value(j,special_column,Correction.get_value(i,column_modified))
                index=str(dataframe.get_value(j,'index'))
                index+='-'+str(i)
                dataframe.set_value(j,'index',index)
                dataframe.set_value(j,'Modified',1)

Answer 1

Would you please close out this question, and repost as a new, cleaned-up question? I get the sense that recently your code and your needs have been evolving. The code you posted is difficult to evaluate, it definitely conforms to your assessment of "gross", and your recent edits have likely cleaned it up. And posting time to execute a single iteration (out of 140k iterations) would be helpful.

DRY - don't repeat yourself

I'm looking at clauses like this:

if Correction.loc[index,'Back Office Seniority'] != Correction.loc[index,'Back Office Seniority _M']:
    dataframe.loc[index1,'BO Seniority'] = Correction.loc[index,'Back Office Seniority']
    a = 1

It's pretty clear you have a need for modeling synonyms. That is, you need a dictionary that maps e.g. 'Back Office Seniority' -> 'BO Seniority'.

With that in hand, you could turn lots of ifs into just one if in the middle of a loop. It might not affect performance, but it would have a very very strong effect on how reviewers interact with your code.

Also, there seems to be a

if Correction.loc[index, foo] != Correction.loc[index, foo + ' _M']:

interaction going on that your code should explicitly model, rather than using copy-n-paste string constants.

On a separate topic, I'm looking at this:

            if (((Correction.get_value(i,'Emetteur Trade Id').strip()==dataframe.get_value(j,'Emetteur Trade Id').strip()) and Correction.get_value(i,'Emetteur Trade Id').strip()!='#') or
                    (Correction.get_value(i,'Emetteur Trade Id').strip()=='#' and Correction.get_value(i,'BO Trade Id').strip()==dataframe.get_value(j,'Trade Id').strip())):
                print ('level 2 success')
                # dataframe.set_value(j, 'Modified', 2)
                if (int(Correction.get_value(i,'UE'))==int(dataframe.get_value(j,'Entity')) and Correction.get_value(i,'Id Ricos').strip()==dataframe.get_value(j,'Siris Id').strip()):
                    print ('level 4 success')

Is level 3 like Fight Club? We just don't talk about it?

The code you posted may "work" in the sense that it produces useful output, but it does not appear to be ready for a code review. You clearly have some ideas about how to usefully refactor it. I invite you to apply some of those ideas and to repost. We will still be here, ready to review!