title: Pandas: Calculate the difference between two Datetime columns from different timezones
body: <p>I have two different time series. One is a series of timestamps in ms-format from the CET timezone delivered as strings. The other are unix-timestamps in s-format in the UTC timezone.</p>
  <p>Each of them is in a column in a larger dataframe, none of them is a DatetimeIndex and should not be one.</p>
  <p>I need to convert the CET time to UTC and then calculate the difference between both columns and I'm lost between the Datetime functionalities of Python and Pandas, and the variety of different datatypes.</p>
  <p>Here's an example:</p>
  <pre><code>import pandas as pd
  import pytz
  
  germany = pytz.timezone('Europe/Berlin')
  
  D1 = [&quot;2016-08-22 00:23:58.254&quot;,&quot;2016-08-22 00:23:58.254&quot;,
        &quot;2016-08-22 00:23:58.254&quot;,&quot;2016-08-22 00:40:33.260&quot;,
        &quot;2016-08-22 00:40:33.260&quot;,&quot;2016-08-22 00:40:33.260&quot;]
  
  D2 = [1470031195, 1470031195, 1470031195, 1471772027, 1471765890, 1471765890]
  
  S1 = pd.to_datetime(pd.Series(D1))
  S2 = pd.to_datetime(pd.Series(D2),unit='s')
  </code></pre>
  <h2>First problem</h2>
  <p>is with the use of <code>tz_localize</code>. I need the program to understand, that the data in <code>S1</code> is not in UTC, but in CET. However using <code>tz_localize</code> like this seems to interpret the given datetime as CET assuming it's UTC to begin with:</p>
  <pre><code>F1 = S1.apply(lambda x: x.tz_localize(germany)).to_frame()
  </code></pre>
  <p>Trying <code>tz_convert</code> always throws something like:</p>
  <blockquote>
  <p>TypeError: index is not a valid DatetimeIndex or PeriodIndex</p>
  </blockquote>
  <h2>Second problem</h2>
  <p>is that even with both of them having the same format I'm stuck because I can't calculate the difference between the two columns now:</p>
  <pre><code>F1 = S1.apply(lambda x: x.tz_localize(germany)).to_frame()
  F1.columns = [&quot;CET&quot;]
  F2 = S2.apply(lambda x: x.tz_localize('UTC')).to_frame()
  F2.columns = [&quot;UTC&quot;]
  FF = pd.merge(F1,F2,left_index=True,right_index=True)
  FF.CET-FF.UTC
  </code></pre>
  <blockquote>
  <p>ValueError: Incompatbile tz's on datetime subtraction ops</p>
  </blockquote>
  <p>I need a way to do these calculation with tz-aware datetime objects that are no DatetimeIndex objects.</p>
  <p><strong>Alternatively</strong> I need a way to make my CET-column to just look like this:</p>
  <pre><code>2016-08-21 22:23:58.254
  2016-08-21 22:23:58.254
  2016-08-21 22:23:58.254
  2016-08-21 22:40:33.260
  2016-08-21 22:40:33.260
  2016-08-21 22:40:33.260
  </code></pre>
  <p>That is, I don't need my datetime to be tz-aware, I just want to convert it automatically by adding/subtracting the necessary amount of time with an awareness for daylight saving times.</p>
  <p>If it weren't for DST I could just do a simple subtraction on two integers.</p>