Add column to dataframe based on date column range
<p>I have a <code>df</code> containing <code>n</code> <code>columns</code>. One of these is a <code>column</code> named <code>date</code> which contains values formatted as <code>mm-dd-yy</code>. Now I want to add a <code>column</code> <code>interval</code> to my <code>df</code>. This <code>column</code> should return the <code>year</code> contained in <code>date</code> but also if it's <code>H1</code> or <code>H2</code>. <code>H1</code> is half year one and should be all <code>date</code> values between <code>01-dd-yy</code> <code>06-dd-yy</code> and thus <code>H2</code> should be all <code>date</code> values between <code>07-dd-yy</code> and <code>12-dd-yy</code>.</p>

<p>This is an example of the data in <code>df['date']</code>:</p>

<pre><code>0         01-27-16
1         02-27-16
2         03-27-16
3         04-27-16
4         05-27-16
5         06-27-16
6         07-27-16
7         08-24-16
8         09-24-16
9         10-16-15
...etc...
</code></pre>

<p>In <code>df</code> I want to add another column named <code>interval</code> containing:</p>

<pre><code>    0         16H1
    1         16H1
    2         16H1
    3         16H1
    4         16H1
    5         16H1
    6         16H2
    7         16H2
    8         16H2
    9         15H2
    ...etc...
</code></pre>

<p>So I thought I'd create a <code>function</code> and then use <code>map</code>.</p>

<pre><code>def is_in_range(x):
   if x['date'] &gt;= '01-01-16'  x['date'] &lt;= '06-31-16':
       print '16H1'
   elif x['date'] &gt;= '07-01-16' and x['date'] &lt;= '12-31-16':
       print '16H2'
   elif x['date'] &gt;= '01-01-15' and x['date'] &lt;= '06-31-15':
       print '15H1'
   elif x['date'] &gt;= '07-01-15' and x['date'] &lt;= '12-31-15':
       print '15H2'  
   ...etc...
</code></pre>

<p>I call the function like this:</p>

<pre><code>df.groupby(df['date'].map(is_in_range))
</code></pre>

<p>Now this gives me:</p>

<blockquote>
  <p>`TypeError: 'Timestamp' object has no attribute '<strong>getitem</strong>'</p>
</blockquote>

<p>to begin with. I'm not sure why, but either way there surely must be a better way?</p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.dt.quarter.html" rel="nofollow"><code>dt.quarter</code></a> or <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.dt.month.html" rel="nofollow"><code>dt.month</code></a>.</p>

<p>First convert <code>int</code> year value to <code>str</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.astype.html" rel="nofollow"><code>astype</code></a>, then select last <code>2</code> chars. Last use <a href="http://docs.scipy.org/doc/numpy-1.10.1/reference/generated/numpy.where.html" rel="nofollow"><code>numpy.where</code></a> with condition:</p>

<pre><code>#first convert to datetime if not datetime
df.date = pd.to_datetime(df.date)

df['interval'] = df.date.astype(str).str[2:4] + np.where(df.date.dt.month &lt; 7, 'H1','H2')
print (df)
        date interval
0 2016-01-27     16H1
1 2016-02-27     16H1
2 2016-03-27     16H1
3 2016-04-27     16H1
4 2016-05-27     16H1
5 2016-06-27     16H1
6 2016-07-27     16H2
7 2016-08-24     16H2
8 2016-09-24     16H2
9 2015-10-16     15H2
</code></pre>

<p>Or:</p>

<pre><code>df['interval'] = df.date.astype(str).str[2:4] + np.where(df.date.dt.quarter &lt; 3,'H1','H2')
print (df)
        date interval
0 2016-01-27     16H1
1 2016-02-27     16H1
2 2016-03-27     16H1
3 2016-04-27     16H1
4 2016-05-27     16H1
5 2016-06-27     16H1
6 2016-07-27     16H2
7 2016-08-24     16H2
8 2016-09-24     16H2
9 2015-10-16     15H2
</code></pre>

<p><code>string</code> solution:</p>

<pre><code>df['interval'] = df.date.str[6:] + np.where(df.date.str[:2].astype(int) &lt; 7, 'H1','H2')
print (df)
       date interval
0  01-27-16     16H1
1  02-27-16     16H1
2  03-27-16     16H1
3  04-27-16     16H1
4  05-27-16     16H1
5  06-27-16     16H1
6  07-27-16     16H2
7  08-24-16     16H2
8  09-24-16     16H2
9  10-16-15     15H2
</code></pre>

<p>List comprehension solutions work if not <strong>NaN</strong>:</p>

<p><code>string</code> column:</p>

<pre><code>df['interval'] = [x[6:] + 'H1' if int(x[:2])&lt; 7 else x[6:] + 'H2' for x in df['date']]
</code></pre>

<p><code>datetime</code> column:</p>

<pre><code>#first convert to datetime if not datetime
df.date = pd.to_datetime(df.date)

df['interval'] = [x[2:4] + 'H1' if int(x[5:7])&lt; 7 else x[2:4] + 'H2' for x in df['date'].astype(str)]

print (df)
       date interval
0  01-27-16     16H1
1  02-27-16     16H1
2  03-27-16     16H1
3  04-27-16     16H1
4  05-27-16     16H1
5  06-27-16     16H1
6  07-27-16     16H2
7  08-24-16     16H2
8  09-24-16     16H2
9  10-16-15     15H2
</code></pre>

python date datetime pandas time-series