Pandas DatetimeIndex from MongoDB ISODate
<p>I am having some difficulty working with times/timezones. I have raw JSON data of the form</p>

<pre><code>{
  "Date": "28 Sep 2009 00:00:00",
  ....
}
</code></pre>

<p>This data is then loaded into MongoDB, and this string representation of the date is transformed into a <a href="https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Date" rel="nofollow noreferrer">JavaScript Date object</a>. This conversion to <a href="https://en.wikipedia.org/wiki/Coordinated_Universal_Time" rel="nofollow noreferrer">UTC</a> time results in the following date</p>

<pre><code>{
  "_id": ObjectId("577a788f4439e17afd4e21f7"),
  "Date": ISODate("2009-09-27T23:00:00Z")
}
</code></pre>

<p>It "looks" as though the date has actually been moved forward a day, I'm assuming (perhaps incorrectly) that this is because my machine is set to <a href="https://en.wikipedia.org/wiki/Time_in_Ireland" rel="nofollow noreferrer">Irish Standard Time</a>.</p>

<p>I then read this data from MongoDB and use it to create a pandas <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DatetimeIndex.html" rel="nofollow noreferrer">DatetimeIndex</a></p>

<pre><code>idx =  pd.DatetimeIndex([x['Date'] for x in test_docs], freq='D')
</code></pre>

<p>which gives me</p>

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

<p>which is incorrect since the time has not been converted back correctly from UTC to local time. So I followed the solution given in <a href="https://stackoverflow.com/questions/4770297/python-convert-utc-datetime-string-to-local-datetime">this answer</a></p>

<pre><code>idx =  pd.DatetimeIndex([x['Date'] for x in test_docs], freq='D')
idx = idx.tz_localize(tz=tz.tzutc())
idx = idx.tz_convert(tz=tz.tzlocal())
frame = DataFrame(test_docs, index=idx)
frame = frame.drop('Date', 1)
</code></pre>

<p>which gives me the right day back</p>

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

<p>I then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DatetimeIndex.normalize.html" rel="nofollow noreferrer">normalize</a> the DatetimeIndex so the hours are removed, allowing me to group all entries by day. </p>

<pre><code>frame.groupby(idx).sum()
</code></pre>

<p>At this point, however, something strange happens. The dates end up getting grouped as follows</p>

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

<p>but this doesn't reflect the dates in the frame</p>

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

<p>Can anyone shed some light on where I might be going wrong?</p>

<hr>

<h1>Response to @ptrj</h1>

<h2>Explicitly using my timezone as a string</h2>

<pre><code>idx =  pd.DatetimeIndex([x['Date'] for x in test_docs], freq='D')
idx = idx.tz_localize(tz=tz.tzutc())
idx = idx.tz_convert(tz='Europe/Dublin')
idx = idx.normalize()
frame = DataFrame(test_docs, index=idx)
...
...
aggregate = frame.groupby(idx).sum()
aggregate.plot()
</code></pre>

<p>this doesn't work for me, it results in the following plot</p>

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

<p>For some reason the groupby is not properly grouping for 2014, as shown below</p>

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

<p>If instead, I use</p>

<pre><code>idx = idx.tz_convert(tz.gettz('Europe/Dublin'))
</code></pre>

<p>I get the same problem</p>

<h2>Convert to an object</h2>

<pre><code>idx =  pd.DatetimeIndex([x['Date'] for x in test_docs], freq='D')
idx = idx.tz_localize(tz=tz.tzutc())
idx = idx.tz_convert(tz=tz.tzlocal())
idx = idx.normalize()
frame = DataFrame(test_docs, index=idx)
aggregate = frame.groupby(idx.astype(object)).sum()
</code></pre>

<p>This approach seems to work correctly for me</p>

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