Pandas slow on data frame replace
<p>I have an Excel file (.xlsx) with about 800 rows and 128 columns with pretty dense data in the grid. There are about 9500 cells that I am trying to replace the cell values of using Pandas data frame:</p>

<pre><code>xlsx = pandas.ExcelFile(filename)
frame = xlsx.parse(xlsx.sheet_names[0])
media_frame = frame[media_headers] # just get the cols that need replacing

from_filenames = get_from_filenames() # returns ~9500 filenames to replace in DF
to_filenames = get_to_filenames()

media_frame = media_frame.replace(from_filenames, to_filenames)
frame.update(media_frame)
frame.to_excel(filename)
</code></pre>

<p>The <code>replace()</code> takes 60 seconds. Any way to speed this up? This is not huge data or task, I was expecting pandas to move much faster. FYI I tried doing the same processing with same file in CSV, but the time savings was minimal (about 50 seconds on the <code>replace()</code>)</p>

<p><strong><em>strategy</em></strong><br>
create <code>pd.Series</code> representing a <code>map</code> from filenames to filenames.<br>
<code>stack</code> our dataframe, <code>map</code>, then <code>unstack</code></p>

<p><strong><em>setup</em></strong>  </p>

<pre><code>import pandas as pd
import numpy as np
from string import letters

media_frame = pd.DataFrame(
    pd.DataFrame(
        np.random.choice(list(letters), 9500 * 800 * 3) \
          .reshape(3, -1)).sum().values.reshape(9500, -1))

u = np.unique(media_frame.values)
from_filenames = pd.Series(u)
to_filenames = from_filenames.str[1:] + from_filenames.str[0]

m = pd.Series(to_filenames.values, from_filenames.values)
</code></pre>

<p><strong><em>solution</em></strong>  </p>

<pre><code>media_frame.stack().map(m).unstack()
</code></pre>

<hr>

<h1>timing</h1>

<p><strong><em>5 x 5 dataframe</em></strong></p>

<p><a href="http://i.stack.imgur.com/jZbck.png" rel="nofollow"><img src="http://i.stack.imgur.com/jZbck.png" alt="enter image description here"></a></p>

<p><strong><em>100 x 100</em></strong></p>

<p><a href="http://i.stack.imgur.com/199TE.png" rel="nofollow"><img src="http://i.stack.imgur.com/199TE.png" alt="enter image description here"></a></p>

<p><strong><em>9500 x 800</em></strong></p>

<p><a href="http://i.stack.imgur.com/117t8.png" rel="nofollow"><img src="http://i.stack.imgur.com/117t8.png" alt="enter image description here"></a></p>

<p><strong><em>9500 x 800</em></strong><br>
<code>map</code> using <code>series</code> vs <code>dict</code><br>
<code>d = dict(zip(from_filenames, to_filenames))</code></p>

<p><a href="http://i.stack.imgur.com/Sw2V3.png" rel="nofollow"><img src="http://i.stack.imgur.com/Sw2V3.png" alt="enter image description here"></a></p>

python excel pandas dataframe