<p dir="auto">Hello,</p>
<p dir="auto">I've stumbled onto a behavior that causes performance problems on large datasets: When creating a multiindex with second level being a DateTime64 col, and then doing a <code class="notranslate">.loc[('val1', TimeStamp('val2'),)]</code> to get at particular value, pandas coerces (via _box_func, called from <strong>iter</strong>) all values in that particular column to TimeStamp dtype. This is done only on first access, but affect performance quite heavily since the op is O(n).</p>
<p dir="auto">However, when chaining the same op (or calling it via multi-level <code class="notranslate">.xs</code>, this behavior is not exhibited. It is not a viable workaround however because doing so introduces an extra call to getitem per each level of multiindex.</p>
<p dir="auto">Is there a workaround for this or what am i doing wrong?<br>
In essence, given a df with unique multiindex, i want to get to a particular row given values for each level of the index.</p>
<p dir="auto">Repro:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="df = &lt;dataframe with: [colA:string, colB:datetime64, colC:numeric with 1,000,000 rows&gt;
gr = df.groupby(('colA', 'colB',))
ag = gr.aggregate({'colC':'sum'})

path = ('one', pd.TimeStamp('2015-01-01 01:02:03'),)
ag.loc[path] # &lt;&lt; first call iterates over entire dataset doing the conversion, and is slow
ag.loc[path] # &lt;&lt; second call is very quick
ag.xs(path, levels=('colA','colB',)) # &lt;&lt; this takes a different code path but does an extra call to getitem, making it slower."><pre class="notranslate"><code class="notranslate">df = &lt;dataframe with: [colA:string, colB:datetime64, colC:numeric with 1,000,000 rows&gt;
gr = df.groupby(('colA', 'colB',))
ag = gr.aggregate({'colC':'sum'})

path = ('one', pd.TimeStamp('2015-01-01 01:02:03'),)
ag.loc[path] # &lt;&lt; first call iterates over entire dataset doing the conversion, and is slow
ag.loc[path] # &lt;&lt; second call is very quick
ag.xs(path, levels=('colA','colB',)) # &lt;&lt; this takes a different code path but does an extra call to getitem, making it slower.
</code></pre></div>
<p dir="auto">Other things:</p>
<ul dir="auto">
<li>padding numpy.datetime64 into the path tuple instead of timestamp doesnt help</li>
<li>specifiying axis for loc doesn't help</li>
</ul>
<p dir="auto">The <code class="notranslate">drop_duplicates()</code> function in Python 3 is broken. Take the following example snippet:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import pandas as pd

raw_data = {'x': [7,6,3,3,4,8,0],'y': [0,6,5,5,9,1,2]}
df = pd.DataFrame(raw_data, columns = ['x', 'y'])

print(&quot;Before:&quot;, df)
df = df.drop_duplicates()
print(&quot;After:&quot;, df)"><pre class="notranslate"><code class="notranslate">import pandas as pd

raw_data = {'x': [7,6,3,3,4,8,0],'y': [0,6,5,5,9,1,2]}
df = pd.DataFrame(raw_data, columns = ['x', 'y'])

print("Before:", df)
df = df.drop_duplicates()
print("After:", df)
</code></pre></div>
<p dir="auto">When run under python 2, the results are correct, but when running under python 3, pandas removes <code class="notranslate">6,6</code> from the frame, which is a completely unique row. When using this function with large CSV files, it causes thousands of lines of unique data loss.</p>
<p dir="auto">See:<br>
<a href="http://stackoverflow.com/questions/33224356/why-is-pandas-dropping-unique-rows" rel="nofollow">http://stackoverflow.com/questions/33224356/why-is-pandas-dropping-unique-rows</a></p>