3
\$\begingroup\$

I've written a script to automate the entry of laboratory instrument data into an Excel spreadsheet using pandas and win32com.

I've got the script functioning correctly, but it is painfully slow. In an attempt to profile the code, my acfmp_ToExcel function seems to be the culprit. I've pasted the profiling data for this function at the bottom. Is there any way to get this code running faster? It takes anywhere from 20-30 seconds each time I run it.

What the function does is take a list of queries (strings within a column of a dataframe, df_acfmp), then using those queries pull data from the other dataframe columns and put those values into an Excel spreadsheet at specific locations.

The function is essentially one bundle of code (if any():) repeated 3 times within the main for loop.

def acfmp_ToExcel(queries):

order_list = {'one':['_410-', '_510-'], 'two':['_420-', '_530-'], 'three': ['_430-', '_590-']}

queerz = Series(queries)

fronts = queerz[queerz.str.endswith("_F")]
fronts_plus = queerz[queerz.str.endswith("F+7")]
backs = queerz[queerz.str.endswith("_B")]

for each_queer in queerz:

    if any(q in each_queer for q in order_list['one']):
        locale_front = np.where(df_acfmp['Name'].str.contains(fronts.iloc[0]+'$'))
        positions_front = locale_front[0]

        fnd_f = 'F + 0 mm'
        x = xsheet1.Range('b1:b1000').Find(fnd_f)
        x_two = xsheet1.Range('b1:b1000').FindNext(x)
        x_three = xsheet1.Range('b1:b1000').FindNext(x_two)
        x_four = xsheet1.Range('b1:b1000').FindNext(x_three)
        x_five = xsheet1.Range('b1:b1000').FindNext(x_four)
        x_six = xsheet1.Range('b1:b1000').FindNext(x_five)
        x_seven = xsheet1.Range('b1:b1000').FindNext(x_six)

        front_queer = fronts_plus.iloc(0)
        locale_fronts_plus = np.where(df_acfmp['Name'].str.contains(front_queer, regex = False))
        positions_fronts_plus = locale_fronts_plus[0]

        fnd_p = 'F + 7 mm'
        y_ = xsheet1.Range('b1:b1000').Find(fnd_p)
        y_two = xsheet1.Range('b1:b1000').FindNext(y_)
        y_three = xsheet1.Range('b1:b1000').FindNext(y_two)
        y_four = xsheet1.Range('b1:b1000').FindNext(y_three)
        y_five = xsheet1.Range('b1:b1000').FindNext(y_four)
        y_six = xsheet1.Range('b1:b1000').FindNext(y_five)
        try:
            y_seven = xsheet1.Range('b1:b1000').FindNext(y_six)
        except: pass

        locale_backs = np.where(df_acfmp['Name'].str.contains(backs.iloc[0]))
        positions_backs = locale_backs[0]

        fnd_b = 'Back' 
        z_ = xsheet1.Range('b1:b1000').find(fnd_b)
        z_two = xsheet1.Range('b1:b1000').FindNext(z_)
        z_three = xsheet1.Range('b1:b1000').FindNext(z_two)
        z_four = xsheet1.Range('b1:b1000').FindNext(z_three)
        z_five = xsheet1.Range('b1:b1000').FindNext(z_four)
        z_six = xsheet1.Range('b1:b1000').FindNext(z_five)
        try:
            z_seven = xsheet1.Range('b1:b1000').FindNext(z_six)
        except: pass

        if 1 in df_acfmp['Stage_Number']:
            for nums in range(5):
                x_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 2 in df_acfmp['Stage_Number']:                        
            for nums in range(5):
                x_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 3 in df_acfmp['Stage_Number']:                        
            for nums in range(5):
                x_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 4 in df_acfmp['Stage_Number']:                        
            for nums in range(5):
                x_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]



    if any(r in each_queer for r in order_list['two']):
        locale_front = np.where(df_acfmp['Name'].str.contains(fronts.iloc[1] + '$'))
        positions_front = locale_front[0]

        fnd_f = 'F + 0 mm'
        x = xsheet2.Range('b1:b1000').Find(fnd_f)
        x_two = xsheet2.Range('b1:b1000').FindNext(x)
        x_three = xsheet2.Range('b1:b1000').FindNext(x_two)
        x_four = xsheet2.Range('b1:b1000').FindNext(x_three)
        x_five = xsheet2.Range('b1:b1000').FindNext(x_four)
        x_six = xsheet2.Range('b1:b1000').FindNext(x_five)
        x_seven = xsheet2.Range('b1:b1000').FindNext(x_six)           

        front_queer = fronts_plus.iloc(1)
        locale_fronts_plus = np.where(df_acfmp['Name'].str.contains(front_queer, regex = False))
        positions_fronts_plus = locale_fronts_plus[0]

        fnd_p = 'F + 7 mm'
        y_ = xsheet2.Range('b1:b1000').Find(fnd_p)
        y_two = xsheet2.Range('b1:b1000').FindNext(y_)
        y_three = xsheet2.Range('b1:b1000').FindNext(y_two)
        y_four = xsheet2.Range('b1:b1000').FindNext(y_three)
        y_five = xsheet2.Range('b1:b1000').FindNext(y_four)
        y_six = xsheet2.Range('b1:b1000').FindNext(y_five)
        try:
            y_seven = xsheet2.Range('b1:b1000').FindNext(y_six)
        except: pass

        locale_backs = np.where(df_acfmp['Name'].str.contains(backs.iloc[1]))
        positions_backs = locale_backs[0]

        fnd_b = 'Back' 
        z_ = xsheet2.Range('b1:b1000').find(fnd_b)
        z_two = xsheet2.Range('b1:b1000').FindNext(z_)
        z_three = xsheet2.Range('b1:b1000').FindNext(z_two)
        z_four = xsheet2.Range('b1:b1000').FindNext(z_three)
        z_five = xsheet2.Range('b1:b1000').FindNext(z_four)
        z_six = xsheet2.Range('b1:b1000').FindNext(z_five)
        try:
            z_seven = xsheet2.Range('b1:b1000').FindNext(z_six)
        except: pass


        if 1 in df_acfmp['Stage_Number'].values:
            for nums in range(5):
                x_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 2 in df_acfmp['Stage_Number'].values:                        
            for nums in range(5):
                x_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 3 in df_acfmp['Stage_Number'].values:                        
            for nums in range(5):
                x_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 4 in df_acfmp['Stage_Number'].values:                        
            for nums in range(5):
                x_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]



    if any(s in each_queer for s in order_list['three']):
        #query_front = fronts.ix[1, 'filter'] + '$'
        locale_front = np.where(df_acfmp['Name'].str.contains(fronts.iloc[2] + '$'))
        positions_front = locale_front[0]

        fnd_f = 'F + 0 mm'
        x = xsheet3.Range('b1:b1000').Find(fnd_f)
        x_two = xsheet3.Range('b1:b1000').FindNext(x)
        x_three = xsheet3.Range('b1:b1000').FindNext(x_two)
        x_four = xsheet3.Range('b1:b1000').FindNext(x_three)
        x_five = xsheet3.Range('b1:b1000').FindNext(x_four)
        x_six = xsheet3.Range('b1:b1000').FindNext(x_five)
        x_seven = xsheet3.Range('b1:b1000').FindNext(x_six)

        front_queer = fronts_plus.iloc(2)
        locale_fronts_plus = np.where(df_acfmp['Name'].str.contains(front_queer, regex = False))
        positions_fronts_plus = locale_fronts_plus[0]

        fnd_p = 'F + 7 mm'
        y_ = xsheet3.Range('b1:b1000').Find(fnd_p)
        y_two = xsheet3.Range('b1:b1000').FindNext(y_)
        y_three = xsheet3.Range('b1:b1000').FindNext(y_two)
        y_four = xsheet3.Range('b1:b1000').FindNext(y_three)
        y_five = xsheet3.Range('b1:b1000').FindNext(y_four)
        y_six = xsheet3.Range('b1:b1000').FindNext(y_five)
        try:
            y_seven = xsheet1.Range('b1:b1000').FindNext(y_six)
        except: pass                

        locale_backs = np.where(df_acfmp['Name'].str.contains(backs.iloc[2]))
        positions_backs = locale_backs[0]

        fnd_b = 'Back' 
        z_ = xsheet3.Range('b1:b1000').find(fnd_b)
        z_two = xsheet3.Range('b1:b1000').FindNext(z_)
        z_three = xsheet3.Range('b1:b1000').FindNext(z_two)
        z_four = xsheet3.Range('b1:b1000').FindNext(z_three)
        z_five = xsheet3.Range('b1:b1000').FindNext(z_four)
        z_six = xsheet3.Range('b1:b1000').FindNext(z_five)
        try:
            z_seven = xsheet3.Range('b1:b1000').FindNext(z_six)
        except: pass

        if 1 in df_acfmp['Stage_Number'].values:
            for nums in range(5):
                x_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 2 in df_acfmp['Stage_Number'].values:                        
            for nums in range(5):
                x_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 3 in df_acfmp['Stage_Number'].values:                        
            for nums in range(5):
                x_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

        if 4 in df_acfmp['Stage_Number'].values:
            for nums in range(5):
                x_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
                y_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
                z_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

Some profiling data that has led me to believe this is the culprit function. I am very new to profiling code so I'm not entirely sure what this is telling me.

 ncalls  tottime  percall  cumtime  percall filename:lineno(function)
    3    2.138    0.713   24.834    8.278 grab_enter.py:688(acfmp_ToExcel)

Function                          called...
                                  ncalls  tottime  cumtime
grab_enter.py:688(acfmp_ToExcel)  ->     180    0.002    0.626  <COMObject <unknown>>:1(Range)
                                       1    0.000    0.004  <COMObject Range>:1(FindNext)
                                     189    0.003    0.007  C:\Python27\lib\site-packages\pandas\core\frame.py:1757(__getitem__)
                                      36    0.000    0.001  C:\Python27\lib\site-packages\pandas\core\generic.py:686(__contains__)
                                     891    0.003    0.005  C:\Python27\lib\site-packages\pandas\core\generic.py:1030(_indexer)
                                       6    0.000    0.000  C:\Python27\lib\site-packages\pandas\core\generic.py:1932(__getattr__)
                                       6    0.000    0.000  C:\Python27\lib\site-packages\pandas\core\generic.py:1949(__setattr__)
                                      27    0.000    0.001  C:\Python27\lib\site-packages\pandas\core\indexing.py:49(__call__)
                                     864    0.004    0.528  C:\Python27\lib\site-packages\pandas\core\indexing.py:1198(__getitem__)
                                       3    0.000    0.001  C:\Python27\lib\site-packages\pandas\core\series.py:114(__init__)
                                      72    0.000    0.001  C:\Python27\lib\site-packages\pandas\core\series.py:296(values)
                                       9    0.000    0.003  C:\Python27\lib\site-packages\pandas\core\series.py:507(__getitem__)
                                       3    0.000    0.000  C:\Python27\lib\site-packages\pandas\core\series.py:1011(__iter__)
                                       6    0.000    0.000  C:\Python27\lib\site-packages\pandas\core\series.py:2454(str)
                                       9    0.000    0.002  C:\Python27\lib\site-packages\pandas\core\strings.py:879(wrapper3)
                                      81    0.001    0.025  C:\Python27\lib\site-packages\pandas\core\strings.py:963(contains)
                                     810    0.010    3.217  C:\Python27\lib\site-packages\win32com\client\dynamic.py:184(__call__)
                                    1944    0.053    9.291  C:\Python27\lib\site-packages\win32com\client\dynamic.py:444(__getattr__)
                                     810    0.029    5.190  C:\Python27\lib\site-packages\win32com\client\dynamic.py:524(__setattr__)
                                       9    0.000    0.000  grab_enter.py:697(<genexpr>)
                                       9    0.000    0.000  grab_enter.py:765(<genexpr>)
                                       9    0.000    0.000  grab_enter.py:834(<genexpr>)
                                      81    0.000    0.000  {any}
                                      81    0.001    0.003  {numpy.core.multiarray.where}
                                      54    0.000    0.000  {range}
|improve this question|||||
\$\endgroup\$
3
\$\begingroup\$

Do not bare except

try:
    z_seven = xsheet1.Range('b1:b1000').FindNext(z_six)
except: pass

Should be avoided as any kind of error will be expected, instead use:

try:


    z_seven = xsheet1.Range('b1:b1000').FindNext(z_six)
except TheExceptioIExpect:
    pass

Remove the massive code duplication

    if 1 in df_acfmp['Stage_Number']:
        for nums in range(5):
            x_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
            y_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
            z_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

    if 2 in df_acfmp['Stage_Number']:                        
        for nums in range(5):
            x_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
            y_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
            z_five.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

    if 3 in df_acfmp['Stage_Number']:                        
        for nums in range(5):
            x_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
            y_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
            z_six.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

    if 4 in df_acfmp['Stage_Number']:                        
        for nums in range(5):
            x_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
            y_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
            z_seven.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]

becomes:

def contains_any(items, lst):
    return any(i in lst for i in items)

if contains_any([1,2,3,4], df_acfmp['Stage_Number']):
        for nums in range(5):
            x_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_front[0], nums]
            y_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_fronts_plus[0], nums]
            z_four.Offset(1, nums+2).Value = df_acfmp.iloc[positions_backs[0], nums]
|improve this answer|||||
\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you very much for the response. Reducing the large code duplication in the manner you mentioned helped reduce the run time significantly. I will look out for more repetitive portions of code as red flags for optimization. \$\endgroup\$ – big_ligands Apr 18 '15 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.