for loop over dataframe rows pandas

Question

I have a dataframe having 54 columns and 100000 rows I need to run sql query using row values in where condition .Is there a faster way to acheive that .this is what I have done

My code:

    def GetTMIDCMID(ClientFileDf):
    try:
        CMCPCodedf_lst=[]
        NewColumnName = ['TCM_TM_ID','TCM_CM_ID']
        col_one_list = ClientFileDf['tradingmemberpan'].tolist()
        col_one_list = ['' if pd.isna(x) else x for x in col_one_list]
        for i in range(len(col_one_list)):
            Query="select OFF_TM_ID,OFF_CM_ID from UCIDBA.COR_OFF_VW  where  OFF_TM_PANNO ='" + col_one_list[i] +"'"
            df_CMCPCodeDF = pd.read_sql(Query, con=connection)
            df_CMCPCodeDF_lst=df_CMCPCodeDF.values.tolist()
            CMCPCodedf_lst.extend(df_CMCPCodeDF_lst)           
        CMCPCodedf=pd.DataFrame(CMCPCodedf_lst)
        CMCPCodedf.columns = NewColumnName
        return CMCPCodedf
    except Exception as ex:
        logging.exception("CollateralValidation:Utility-> GetTMIDCMID : |")   
        print(ex)  
        return False

This is just one of the functions .There are some cases where multiple where clause condition is implemented .dataframe is passed as an argumnent of the function. Thanks .

Perhaps this might help speed up performance via multi-processing? https://stackoverflow.com/questions/51164163/multiprocessing-pandas-sql — fibonachoceres, Dec 18 '21 at 11:16

score 0 · Answer 1 · answered Dec 18 '21 at 11:29

0

You might get a huge boost by grabbing all values from the sql table in one go and then merge them together, similar to this:

ids_ = ClientFileDf['tradingmemberpan'].dropna().tolist()

query_ = "select OFF_TM_ID,OFF_CM_ID from UCIDBA.COR_OFF_VW  where OFF_TM_PANNO IN " + "({})".fromat(str(ids_))

df_CMCPCodeDF = pd.read_sql(Query, con=connection)

ClientFileDf.merge(df_CMCPCodeDF , on=[<MatchingKey>], how='left')

answered Dec 18 '21 at 11:29

Andreas

8,694
3
14
38

For even more performance in the sql statement check 'Ed Guiness' answer: https://stackoverflow.com/questions/5803472/sql-where-id-in-id1-id2-idn – Andreas Dec 18 '21 at 11:30

for loop over dataframe rows pandas

1 Answers1