<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import pandas as pd
dates = [  20140101, 20140102, 20140103]
states = [  &quot;CA&quot;,  &quot;NY&quot;, &quot;CA&quot;]
x = pd.DataFrame({ 'dates' : dates, 'states' : states })

#y = pandas.DataFrame({ 'state' : [ 'CA', 'OR' ], 'value' : [ 1, 2]})
y = pd.DataFrame({ 'states' : [ &quot;CA&quot;, &quot;NY&quot; ], 'stateid' : [ 1, 2]})
z = pd.merge(x, y, how='left', on='states',)


x=
    dates   states
0   20140101    CA
1   20140102    NY
2   20140103    CA

y=
        stateid     states
0   1   CA
1   2   NY

z=   dates  states  stateid
0   20140101    CA  1
1   20140103    CA  1
2   20140102    NY  2"><pre class="notranslate"><code class="notranslate">import pandas as pd
dates = [  20140101, 20140102, 20140103]
states = [  "CA",  "NY", "CA"]
x = pd.DataFrame({ 'dates' : dates, 'states' : states })

#y = pandas.DataFrame({ 'state' : [ 'CA', 'OR' ], 'value' : [ 1, 2]})
y = pd.DataFrame({ 'states' : [ "CA", "NY" ], 'stateid' : [ 1, 2]})
z = pd.merge(x, y, how='left', on='states',)


x=
    dates   states
0   20140101    CA
1   20140102    NY
2   20140103    CA

y=
        stateid     states
0   1   CA
1   2   NY

z=   dates  states  stateid
0   20140101    CA  1
1   20140103    CA  1
2   20140102    NY  2
</code></pre></div>
<hr>
<p dir="auto">Note z is always sorted by "states" column whether argument sort=True or False.<br>
This only happens when the x's states column is not unique. If x.states is unique(such as NY, CA, CT), sort=True and False behaves as expected.</p>
<p dir="auto">This causes inconvenience when x is a time series and merge does not<br>
preserve the time sequence.</p>
<p dir="auto">Using floor on a DatetimeIndex that has a timezone with DST fails when converting the result back to the tz as there may be ambiguous time (see example below).</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="# create 15' date range with Brussels tz
idx = pandas.date_range(&quot;2016-01-01&quot;,&quot;2017-01-01&quot;,freq=&quot;15T&quot;, tz=&quot;Europe/Brussels&quot;)
# transform the index to get rid of minutes
idx_h = idx.floor(&quot;H&quot;)"><pre class="notranslate"><span class="pl-c"># create 15' date range with Brussels tz</span>
<span class="pl-s1">idx</span> <span class="pl-c1">=</span> <span class="pl-s1">pandas</span>.<span class="pl-en">date_range</span>(<span class="pl-s">"2016-01-01"</span>,<span class="pl-s">"2017-01-01"</span>,<span class="pl-s1">freq</span><span class="pl-c1">=</span><span class="pl-s">"15T"</span>, <span class="pl-s1">tz</span><span class="pl-c1">=</span><span class="pl-s">"Europe/Brussels"</span>)
<span class="pl-c"># transform the index to get rid of minutes</span>
<span class="pl-s1">idx_h</span> <span class="pl-c1">=</span> <span class="pl-s1">idx</span>.<span class="pl-en">floor</span>(<span class="pl-s">"H"</span>)</pre></div>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">idx_h rounded to the hour</p>
<h4 dir="auto">Actual Output</h4>
<p dir="auto">excepection raised when relocalizing the index after applying the rouding</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;...&quot;, line 118, in &lt;module&gt;
    idx_h = idx.floor(&quot;H&quot;)
  File &quot;...\lib\site-packages\pandas\tseries\base.py&quot;, line 98, in floor
    return self._round(freq, np.floor)
  File &quot;...\lib\site-packages\pandas\tseries\base.py&quot;, line 89, in _round
    result = result.tz_localize(self.tz)#, ambiguous=[d.dst() for d in self])
  File &quot;...\lib\site-packages\pandas\util\decorators.py&quot;, line 91, in wrapper
    return func(*args, **kwargs)
  File &quot;...\lib\site-packages\pandas\tseries\index.py&quot;, line 1857, in tz_localize
    ambiguous=ambiguous)
  File &quot;pandas\tslib.pyx&quot;, line 4087, in pandas.tslib.tz_localize_to_utc (pandas\tslib.c:69556)
pytz.exceptions.AmbiguousTimeError: Cannot infer dst time from Timestamp('2016-10-30 02:00:00'), try using the 'ambiguous' argument"><pre class="notranslate"><span class="pl-v">Traceback</span> (<span class="pl-s1">most</span> <span class="pl-s1">recent</span> <span class="pl-s1">call</span> <span class="pl-s1">last</span>):
  <span class="pl-v">File</span> <span class="pl-s">"..."</span>, <span class="pl-s1">line</span> <span class="pl-c1">118</span>, <span class="pl-s1">in</span> <span class="pl-c1">&lt;</span><span class="pl-s1">module</span><span class="pl-c1">&gt;</span>
    <span class="pl-s1">idx_h</span> <span class="pl-c1">=</span> <span class="pl-s1">idx</span>.<span class="pl-en">floor</span>(<span class="pl-s">"H"</span>)
  <span class="pl-v">File</span> <span class="pl-s">"...\lib\site-packages\pandas<span class="pl-cce">\t</span>series<span class="pl-cce">\b</span>ase.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">98</span>, <span class="pl-s1">in</span> <span class="pl-s1">floor</span>
    <span class="pl-k">return</span> <span class="pl-s1">self</span>.<span class="pl-en">_round</span>(<span class="pl-s1">freq</span>, <span class="pl-s1">np</span>.<span class="pl-s1">floor</span>)
  <span class="pl-v">File</span> <span class="pl-s">"...\lib\site-packages\pandas<span class="pl-cce">\t</span>series<span class="pl-cce">\b</span>ase.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">89</span>, <span class="pl-s1">in</span> <span class="pl-s1">_round</span>
    <span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">result</span>.<span class="pl-en">tz_localize</span>(<span class="pl-s1">self</span>.<span class="pl-s1">tz</span>)<span class="pl-c">#, ambiguous=[d.dst() for d in self])</span>
  <span class="pl-v">File</span> <span class="pl-s">"...\lib\site-packages\pandas\util\decorators.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">91</span>, <span class="pl-s1">in</span> <span class="pl-s1">wrapper</span>
    <span class="pl-k">return</span> <span class="pl-en">func</span>(<span class="pl-c1">*</span><span class="pl-s1">args</span>, <span class="pl-c1">**</span><span class="pl-s1">kwargs</span>)
  <span class="pl-v">File</span> <span class="pl-s">"...\lib\site-packages\pandas<span class="pl-cce">\t</span>series\index.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">1857</span>, <span class="pl-s1">in</span> <span class="pl-s1">tz_localize</span>
    <span class="pl-s1">ambiguous</span><span class="pl-c1">=</span><span class="pl-s1">ambiguous</span>)
  <span class="pl-v">File</span> <span class="pl-s">"pandas<span class="pl-cce">\t</span>slib.pyx"</span>, <span class="pl-s1">line</span> <span class="pl-c1">4087</span>, <span class="pl-s1">in</span> <span class="pl-s1">pandas</span>.<span class="pl-s1">tslib</span>.<span class="pl-en">tz_localize_to_utc</span> (<span class="pl-s1">pandas</span>\t<span class="pl-s1">slib</span>.<span class="pl-s1">c</span>:<span class="pl-c1">69556</span>)
<span class="pl-s1">pytz</span>.<span class="pl-s1">exceptions</span>.<span class="pl-v">AmbiguousTimeError</span>: <span class="pl-v">Cannot</span> <span class="pl-s1">infer</span> <span class="pl-s1">dst</span> <span class="pl-s1">time</span> <span class="pl-k">from</span> <span class="pl-v">Timestamp</span>(<span class="pl-s">'2016-10-30 02:00:00'</span>), <span class="pl-k">try</span> <span class="pl-s1">using</span> <span class="pl-s1">the</span> <span class="pl-s">'ambiguous'</span> <span class="pl-s1">argument</span></pre></div>
<h4 dir="auto">Possible solution</h4>
<p dir="auto">The code to reconvert to local tz need to have 'ambiguous' defined and can use the original array of dst flags.<br>
Lines 87-89 in ...\Lib\site-packages\pandas\tseries\base.py</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="        # reconvert to local tz
        if getattr(self, 'tz', None) is not None:
            result = result.tz_localize(self.tz)"><pre class="notranslate">        <span class="pl-c"># reconvert to local tz</span>
        <span class="pl-k">if</span> <span class="pl-en">getattr</span>(<span class="pl-s1">self</span>, <span class="pl-s">'tz'</span>, <span class="pl-c1">None</span>) <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>:
            <span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">result</span>.<span class="pl-en">tz_localize</span>(<span class="pl-s1">self</span>.<span class="pl-s1">tz</span>)</pre></div>
<p dir="auto">to be replaced by<br>
Lines 87-89 in ...\Lib\site-packages\pandas\tseries\base.py</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="        # reconvert to local tz
        if getattr(self, 'tz', None) is not None:
            result = result.tz_localize(self.tz, ambiguous=[d.dst() for d in self])"><pre class="notranslate">        <span class="pl-c"># reconvert to local tz</span>
        <span class="pl-k">if</span> <span class="pl-en">getattr</span>(<span class="pl-s1">self</span>, <span class="pl-s">'tz'</span>, <span class="pl-c1">None</span>) <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>:
            <span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">result</span>.<span class="pl-en">tz_localize</span>(<span class="pl-s1">self</span>.<span class="pl-s1">tz</span>, <span class="pl-s1">ambiguous</span><span class="pl-c1">=</span>[<span class="pl-s1">d</span>.<span class="pl-en">dst</span>() <span class="pl-k">for</span> <span class="pl-s1">d</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>])</pre></div>
<p dir="auto">Would it be also useful to have a DatetimeIndex.dst function to return a boolean array reuseable in tz_localize ?</p>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
# Paste the output here
## INSTALLED VERSIONS
<p dir="auto">commit: None<br>
python: 2.7.11.final.0<br>
python-bits: 32<br>
OS: Windows<br>
OS-release: 7<br>
machine: AMD64<br>
processor: Intel64 Family 6 Model 61 Stepping 4, GenuineIntel<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: None</p>
<p dir="auto">pandas: 0.18.0<br>
nose: 1.3.7<br>
pip: 8.1.1<br>
setuptools: 20.3<br>
Cython: 0.23.4<br>
numpy: 1.10.4<br>
scipy: 0.17.0<br>
statsmodels: 0.6.1<br>
xarray: None<br>
IPython: 4.1.2<br>
sphinx: 1.3.5<br>
patsy: 0.4.0<br>
dateutil: 2.5.1<br>
pytz: 2016.2<br>
blosc: None<br>
bottleneck: 1.0.0<br>
tables: 3.2.2<br>
numexpr: 2.5<br>
matplotlib: 1.5.1<br>
openpyxl: 2.3.2<br>
xlrd: 0.9.4<br>
xlwt: 1.0.0<br>
xlsxwriter: 0.8.4<br>
lxml: 3.6.0<br>
bs4: 4.4.1<br>
html5lib: None<br>
httplib2: None<br>
apiclient: None<br>
sqlalchemy: 1.0.12<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.8<br>
boto: 2.39.0</p>
</details>