HiveBrain v1.2.0
Get Started
← Back to all entries
patternpythonMinor

Excel Laboratory Data Entry from Python 2.7

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
codereviewexcelpandasstackoverflowperformancepython
entrylaboratoryexcelpythonfromdata

Problem

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').Fin

Solution

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]

Code Snippets

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


    z_seven = xsheet1.Range('b1:b1000').FindNext(z_six)
except TheExceptioIExpect:
    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]
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]

Context

StackExchange Code Review Q#87301, answer score: 3

Revisions (0)

No revisions yet.