Does pandas iterrows have performance issues?

Generally, iterrows should only be used in very, very specific cases. This is the general order of precedence for performance of various operations:

1) vectorization2) using a custom cython routine3) apply    a) reductions that can be performed in cython    b) iteration in python space4) itertuples5) iterrows6) updating an empty frame (e.g. using loc one-row-at-a-time)

Using a custom Cython routine is usually too complicated, so let's skip that for now.

1) Vectorization is ALWAYS, ALWAYS the first and best choice. However, there is a small set of cases (usually involving a recurrence) which cannot be vectorized in obvious ways. Furthermore, on a smallish DataFrame, it may be faster to use other methods.

3) apply usually can be handled by an iterator in Cython space. This is handled internally by pandas, though it depends on what is going on inside the apply expression. For example, df.apply(lambda x: np.sum(x)) will be executed pretty swiftly, though of course, df.sum(1) is even better. However something like df.apply(lambda x: x['b'] + 1) will be executed in Python space, and consequently is much slower.

4) itertuples does not box the data into a Series. It just returns the data in the form of tuples.

5) iterrows DOES box the data into a Series. Unless you really need this, use another method.

6) Updating an empty frame a-single-row-at-a-time. I have seen this method used WAY too much. It is by far the slowest. It is probably common place (and reasonably fast for some python structures), but a DataFrame does a fair number of checks on indexing, so this will always be very slow to update a row at a time. Much better to create new structures and concat.

Vector operations in Numpy and pandas are much faster than scalar operations in vanilla Python for several reasons:

• Amortized type lookup: Python is a dynamically typed language, so there is runtime overhead for each element in an array. However, Numpy (and thus pandas) perform calculations in C (often via Cython). The type of the array is determined only at the start of the iteration; this savings alone is one of the biggest wins.

• Better caching: Iterating over a C array is cache-friendly and thus very fast. A pandas DataFrame is a "column-oriented table", which means that each column is really just an array. So the native actions you can perform on a DataFrame (like summing all the elements in a column) are going to have few cache misses.

• More opportunities for parallelism: A simple C array can be operated on via SIMD instructions. Some parts of Numpy enable SIMD, depending on your CPU and installation process. The benefits to parallelism won't be as dramatic as the static typing and better caching, but they're still a solid win.

Moral of the story: use the vector operations in Numpy and pandas. They are faster than scalar operations in Python for the simple reason that these operations are exactly what a C programmer would have written by hand anyway. (Except that the array notion is much easier to read than explicit loops with embedded SIMD instructions.)

Here's the way to do your problem. This is all vectorized.

In [58]: df = table1.merge(table2,on='letter')In [59]: df['calc'] = df['number1']*df['number2']In [60]: dfOut[60]:   letter  number1  number2  calc0      a       50      0.2    101      a       50      0.5    252      b      -10      0.1    -13      b      -10      0.4    -4In [61]: df.groupby('letter')['calc'].max()Out[61]: lettera         25b         -1Name: calc, dtype: float64In [62]: df.groupby('letter')['calc'].idxmax()Out[62]: lettera         1b         2Name: calc, dtype: int64In [63]: df.loc[df.groupby('letter')['calc'].idxmax()]Out[63]:   letter  number1  number2  calc1      a       50      0.5    252      b      -10      0.1    -1