Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #73743 > unrolled thread

Python While loop Takes too much time.

Back to article view | Back to comp.lang.python

Contents

  Python While loop Takes too much time. Jaydeep Patil <patil.jay2009@gmail.com> - 2014-06-30 04:32 -0700
    Re: Python While loop Takes too much time. Peter Otten <__peter__@web.de> - 2014-06-30 14:46 +0200
      Re: Python While loop Takes too much time. Jaydeep Patil <patil.jay2009@gmail.com> - 2014-07-01 05:04 -0700
        Re: Python While loop Takes too much time. Peter Otten <__peter__@web.de> - 2014-07-01 14:40 +0200
          Re: Python While loop Takes too much time. Denis McMahon <denismfmcmahon@gmail.com> - 2014-07-01 18:45 +0000
    Re: Python While loop Takes too much time. marco.nawijn@colosso.nl - 2014-06-30 11:34 -0700
      Re: Python While loop Takes too much time. Gregory Ewing <greg.ewing@canterbury.ac.nz> - 2014-07-01 11:50 +1200

#73743 — Python While loop Takes too much time.

I have did excel automation using python.
In my code I am creating python dictionaries for different three columns data at a time.There are are many rows above 4000. Lets have look in below function. Why it is taking too much time?

Code:

def transientTestDict(self,ws,startrow,startcol):
        
        self.hwaDict = OrderedDict()
        self.yawRateDict = OrderedDict()
        
        rng = ws.Cells(startrow,startcol)
        
        while not rng.Value is None:
            r = rng.Row
            c = rng.Column
            
            time = rng.Value
            
            rng1 = rng.GetOffset(0,1)
            hwa = rng1.Value
            
            rng2 = rng.GetOffset(0,2)
            yawrate = rng2.Value
            
            self.hwaDict[time] = hwa,rng.Row,rng.Column
            self.yawRateDict[time] = yawrate,rng.Row,rng.Column
            
            rng = ws.Cells(r+1,c)



Please have look in above code & suggest me to improve speed of my code.



Regards
Jaydeep Patil

[toc] | [next] | [standalone]

#73744

Jaydeep Patil wrote:

> I have did excel automation using python.
> In my code I am creating python dictionaries for different three columns
> data at a time.There are are many rows above 4000. Lets have look in below
> function. Why it is taking too much time?
> 
> Code:
> 
> def transientTestDict(self,ws,startrow,startcol):
>         
>         self.hwaDict = OrderedDict()
>         self.yawRateDict = OrderedDict()
>         
>         rng = ws.Cells(startrow,startcol)
>         
>         while not rng.Value is None:
>             r = rng.Row
>             c = rng.Column
>             
>             time = rng.Value
>             
>             rng1 = rng.GetOffset(0,1)
>             hwa = rng1.Value
>             
>             rng2 = rng.GetOffset(0,2)
>             yawrate = rng2.Value
>             
>             self.hwaDict[time] = hwa,rng.Row,rng.Column
>             self.yawRateDict[time] = yawrate,rng.Row,rng.Column
>             
>             rng = ws.Cells(r+1,c)
> 
> 
> 
> Please have look in above code & suggest me to improve speed of my code.

Assuming that what slows down things is neither Python nor Excel, but the 
communication between these I'd try to do as much as possible in Python. For 
example (untested):

    def transientTestDict(self, ws, startrow, startcol):
        self.hwaDict = OrderedDict()
        self.yawRateDict = OrderedDict()
        
        time_col, hwa_col, yawrate_col = range(startcol, startcol+3)

        for row in xrange(startrow, sys.maxint):
            time = ws.Cells(row, time_col).Value
            if time is None:
                break
            hwa = ws.Cells(row, hwa_col).Value
            yawrate = ws.Cells(row, yawrate_col).Value

            self.hwaDict[time] = hwa, row, time_col
            self.yawRateDict[time] = yawrate, row, time_col

While this avoids cell arithmetic in Excel it still fetches every value 
separately, so I have no idea if there is a significant effect.

Does Excel provide a means to get multiple cell values at once? That would 
likely help.

[toc] | [prev] | [next] | [standalone]

#73782

On Monday, 30 June 2014 18:16:21 UTC+5:30, Peter Otten  wrote:
> Jaydeep Patil wrote:
> 
> 
> 
> > I have did excel automation using python.
> 
> > In my code I am creating python dictionaries for different three columns
> 
> > data at a time.There are are many rows above 4000. Lets have look in below
> 
> > function. Why it is taking too much time?
> 
> > 
> 
> > Code:
> 
> > 
> 
> > def transientTestDict(self,ws,startrow,startcol):
> 
> >         
> 
> >         self.hwaDict = OrderedDict()
> 
> >         self.yawRateDict = OrderedDict()
> 
> >         
> 
> >         rng = ws.Cells(startrow,startcol)
> 
> >         
> 
> >         while not rng.Value is None:
> 
> >             r = rng.Row
> 
> >             c = rng.Column
> 
> >             
> 
> >             time = rng.Value
> 
> >             
> 
> >             rng1 = rng.GetOffset(0,1)
> 
> >             hwa = rng1.Value
> 
> >             
> 
> >             rng2 = rng.GetOffset(0,2)
> 
> >             yawrate = rng2.Value
> 
> >             
> 
> >             self.hwaDict[time] = hwa,rng.Row,rng.Column
> 
> >             self.yawRateDict[time] = yawrate,rng.Row,rng.Column
> 
> >             
> 
> >             rng = ws.Cells(r+1,c)
> 
> > 
> 
> > 
> 
> > 
> 
> > Please have look in above code & suggest me to improve speed of my code.
> 
> 
> 
> Assuming that what slows down things is neither Python nor Excel, but the 
> 
> communication between these I'd try to do as much as possible in Python. For 
> 
> example (untested):
> 
> 
> 
>     def transientTestDict(self, ws, startrow, startcol):
> 
>         self.hwaDict = OrderedDict()
> 
>         self.yawRateDict = OrderedDict()
> 
>         
> 
>         time_col, hwa_col, yawrate_col = range(startcol, startcol+3)
> 
> 
> 
>         for row in xrange(startrow, sys.maxint):
> 
>             time = ws.Cells(row, time_col).Value
> 
>             if time is None:
> 
>                 break
> 
>             hwa = ws.Cells(row, hwa_col).Value
> 
>             yawrate = ws.Cells(row, yawrate_col).Value
> 
> 
> 
>             self.hwaDict[time] = hwa, row, time_col
> 
>             self.yawRateDict[time] = yawrate, row, time_col
> 
> 
> 
> While this avoids cell arithmetic in Excel it still fetches every value 
> 
> separately, so I have no idea if there is a significant effect.
> 
> 
> 
> Does Excel provide a means to get multiple cell values at once? That would 
> 
> likely help.



Dear Peter,
I have tested code written by you. But still it is taking same time.



Regards
Jay

[toc] | [prev] | [next] | [standalone]

#73786

Jaydeep Patil wrote:

> Dear Peter,
> I have tested code written by you. But still it is taking same time.

Too bad ;(

If you run the equivalent loop written in Basic from within Excel -- is that 
faster?

If you run the loop in Python with some made-up data instead of that fetched 
from Excel -- is that faster?

What I'm trying to tell you: you need to put in some work to identify the 
culprit...

[toc] | [prev] | [next] | [standalone]

#73804

On Tue, 01 Jul 2014 14:40:18 +0200, Peter Otten wrote:

> What I'm trying to tell you: you need to put in some work to identify
> the culprit...

His next question was "how do I read a range from excel, please give me 
an example"

I gave him an example of using google to search for solutions to his 
problem. If he can't be bothered to try and solve it himslef, I'm nopt 
going to write his code for him.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#73754

On Monday, June 30, 2014 1:32:23 PM UTC+2, Jaydeep Patil wrote:
> I have did excel automation using python.
> 
> In my code I am creating python dictionaries for different three columns data at a time.There are are many rows above 4000. Lets have look in below function. Why it is taking too much time?
> 
> 
> 
> Code:
> 
> 
> 
> def transientTestDict(self,ws,startrow,startcol):
> 
>         
> 
>         self.hwaDict = OrderedDict()
> 
>         self.yawRateDict = OrderedDict()
> 
>         
> 
>         rng = ws.Cells(startrow,startcol)
> 
>         
> 
>         while not rng.Value is None:
> 
>             r = rng.Row
> 
>             c = rng.Column
> 
>             
> 
>             time = rng.Value
> 
>             
> 
>             rng1 = rng.GetOffset(0,1)
> 
>             hwa = rng1.Value
> 
>             
> 
>             rng2 = rng.GetOffset(0,2)
> 
>             yawrate = rng2.Value
> 
>             
> 
>             self.hwaDict[time] = hwa,rng.Row,rng.Column
> 
>             self.yawRateDict[time] = yawrate,rng.Row,rng.Column
> 
>             
> 
>             rng = ws.Cells(r+1,c)
> 
> 
> 
> 
> 
> 
> 
> Please have look in above code & suggest me to improve speed of my code.
> 
> 
> 
> 
> 
> 
> 
> Regards
> 
> Jaydeep Patil

Hi Jaydeep,

I agree with Peter. I would avoid moving from cell to
cell through the EXCEL interface if you can avoid.

If possible, I would try to read ranges from EXCEL into
a python list (or maybe numpy arrays) and do the processing
in Python. In the past I even dumped an EXCEL sheet as a
CSV file and then used the numpy recfromcsv function to 
process the data.

If you are really brave, dump EXCEL alltogether :) and do
all the work in Python (have you already tried IPython 
notebook?).

Regards,

Marco

[toc] | [prev] | [next] | [standalone]

#73768

marco.nawijn@colosso.nl wrote:
> In the past I even dumped an EXCEL sheet as a
> CSV file

That's probably the only way you'll speed things up
significantly. In my experience, accessing Excel via
COM is abysmally slow no matter how you go about it.

-- 
Greg

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web