Faster For Loop to Manipulate Data in Pandas
<p>I am working with pandas dataframes that have shapes of ~<code>(100000, 50)</code> and although I can achieve the desired data formatting and manipulations, I find my code takes longer than desired to run (3-10mins) depending on the specific task, including:</p>

<ol>
<li>Combining strings in different columns</li>
<li>Applying a function to each instance within a data frame series</li>
<li>Checking if a value is contained within a separate list or numpy array</li>
</ol>

<p>I will have larger data frames in the future and want to ensure I'm using the appropriate coding methods to avoid very long processing times.  I find my <code>for</code> loops take the longest.  I try to avoid <code>for</code> loops with list comprehensions and series operators (e.g. <code>df.loc[:,'C'] = df.A + df.B</code>) but in some cases, I need to perform more complicated/involved manipulations with nested <code>for</code> loops.  For example, the below iterates through a dataframe's series <code>history</code> (a series of lists), and subsequently iterates through each item within each <code>list</code>:</p>

<pre><code>for row in DF.iterrows():

    removelist = []

    for i in xrange(0, len(row[1]['history'])-1):
        if ((row[1]['history'][i]['title'] == row[1]['history'][i+1]['title']) &amp; 
            (row[1]['history'][i]['dept'] == row[1]['history'][i+1]['dept']) &amp; 
            (row[1]['history'][i]['office'] == row[1]['history'][i+1]['office']) &amp; 
            (row[1]['history'][i]['employment'] == row[1]['history'][i+1]['employment'])):
                removelist.append(i)

    newlist = [v for i, v in enumerate(row[1]['history']) if i not in removelist]
</code></pre>

<p>I know that list comprehensions can accommodate nested <code>for</code> loops but the above would seem really cumbersome within a list comprehension.</p>

<p><strong>My questions:</strong> what other techniques can I use to achieve the same functionality as a <code>for</code> loop with shorter processing time?  And when iterating through a series containing lists, should I use a different technique other than a nested <code>for</code> loop?</p>

<p>So what you seem to have here is a dataframe where the history entry of each row contains a list of dictionaries? Like:</p>

<pre><code>import pandas as pd
john_history = [{'title': 'a', 'dept': 'cs'}, {'title': 'cj', 'dept': 'sales'}]
john_history
jill_history = [{'title': 'boss', 'dept': 'cs'}, {'title': 'boss', 'dept': 'cs'}, {'title': 'junior', 'dept': 'cs'}]
jill_history
df = pd.DataFrame({'history': [john_history, jill_history], 
    'firstname': ['john', 'jill']})
</code></pre>

<p>I would restructure your data so that you use pandas structures at the bottom level of your structure, e.g. a dict of DataFrames where each DataFrame is the history (I don't think Panel works here as the DataFrames may have different lengths):</p>

<pre><code>john_history = pd.DataFrame({'title': ['a', 'cj'], 'dept': ['cs', 'sales']})
john_history['name'] = 'john'
jill_history = pd.DataFrame({'title': ['boss', 'boss', 'junior'], 'dept': ['cs', 'cs', 'cs']})
jill_history['name'] = 'jill'
people = pd.concat([john_history, jill_history])
</code></pre>

<p>You can then process them using groupby like:</p>

<pre><code>people.groupby('name').apply(pd.DataFrame.drop_duplicates)
</code></pre>

<p>In general, if you cannot find the functionality you want within pandas/numpy, you should find that using the pandas primitives to create it rather than iterating over a dataframe will be faster. For example to recreate your logic above, first create a new dataframe that is the first one shifted:</p>

<pre><code>df2 = df.shift()
</code></pre>

<p>Now you can create a selection by comparing the contents of the dataframes and only keeping the ones that are different and use that to filter the dataframe:</p>

<pre><code>selection_array = (df.history == df2.history) &amp; (df.title == df2.title)
unduplicated_consecutive = df[~selection_array]
print(unduplicated_consecutive)
  history  id title
0       a   1     x
1       b   2     y
# or in one line:
df[~((df.history == df2.history) &amp; (df.title == df2.title))]
# or:
df[(df.history != df2.history) | (df.title != df2.title)]
</code></pre>

<p>So putting this into the groupby:</p>

<pre><code>def drop_consecutive_duplicates(df):
    df2 = df.shift()
    return df.drop(df[(df.dept == df2.dept) &amp; (df.title == df2.title)].index)

people.groupby('name').apply(drop_consecutive_duplicates)
</code></pre>

python pandas numpy