Efficiently read/shift out-of-range datetimes using pandas
<p>so I have a dataset (a bunch of csv files) which contains (anonymized) datetimes in the following form:</p>

<blockquote>
  <p>3202-11-11 14:51:00 EST</p>
</blockquote>

<p>The dates have been shifted by some random time for each entity. So differences in time for a given entity are still meaningful.</p>

<p>When trying to convert using e.g. 
<code>pd.to_datetime(['3202-11-11 14:51:00 EST'], format='%Y-%m-%d %H:%M:%S EST')</code>, this will result in 'OutOfBoundsDatetime' error.</p>

<p>For my use case it would be ideal to specify a number of years by which to shift all dates when reading the csv files, s.t. they are within the valid pandas datetime range.</p>

<p>Do you have an idea how this could be solved efficiently? I have to do this on ~40k entities/csv files, with 10 to 10k such dates per csv.  (my non-efficient idea: Go through python datetime which works for years till 9999, shift dates there and then convert to pandas datetime)</p>

<p><strong>EDIT</strong>: I also asked this question in IRC #pydata and got this answer (thanks jboy): </p>

<pre><code>&gt;&gt;&gt; from datetime import timedelta
&gt;&gt;&gt; offset = timedelta(days=10000)
&gt;&gt;&gt; df
                  time
0  3001-01-01 01:00:01
1  3001-01-01 01:00:02
2  3001-01-01 01:00:05
3  3001-01-01 01:00:09

&gt;&gt;&gt; df['time'].map(lambda t: t - offset)
0    2973-08-15 01:00:01
1    2973-08-15 01:00:02
2    2973-08-15 01:00:05
3    2973-08-15 01:00:09
Name: time, dtype: object
</code></pre>

<p>The only thing I have to do differently was:</p>

<pre><code> df['time'].map(lambda t: datetime.datetime.strptime(t, '%Y-%m-%d %H:%M:%S EST')-offset)
</code></pre>

<p>Because my time column was still str and not datetime.datetime.</p>

<p>One thing you could do is to work on this at the string level, deducting some number of years (in the following, 1200):</p>

<pre><code>s = '3202-11-11 14:51:00 EST'

&gt;&gt;&gt; In [21]: pd.to_datetime(str(int(s[: 4]) - 1200) + s[4: ])
Out[21]: Timestamp('2002-11-11 14:51:00')
</code></pre>

<p>You can also vectorize this. Say you start with</p>

<pre><code>dates = pd.Series([s, s])
</code></pre>

<p>Then you can use</p>

<pre><code>&gt;&gt;&gt; pd.to_datetime((dates.str[: 4].astype(int) - 1200).astype(str) + dates.str[4: ])
0   2002-11-11 14:51:00
1   2002-11-11 14:51:00
dtype: datetime64[ns]
</code></pre>

python datetime pandas