Create Datetime from year and month hidden in multi-index
<p>I have a dataframe where year and month are hidden in the <code>multi-index</code>. I want to create a datetime index as additional column (or separate series with same index).</p>

<pre><code>                                           price            
                                            mean     mom_2  
foo          bar              year month                                      
997182819645 11               2010 1      1.1900  3.000000  
                                   2      2.2625  4.001769  
</code></pre>

<p>I thought of adding the two levels of indices together as strings, and then read in that sequence into <code>pd.to_datetime()</code>. However, adding the two indices, I faced problems. I can add them up as integers just fine, but if I want to add them up as strings, I face some error:</p>

<pre><code>In[193]: df.index.get_level_values('year').values.astype(str)
Out[193]: 

array(['2010', '2010', '2010', ..., '2014', '2014', '2014'], 
      dtype='&lt;U21')
In[194]: df.index.get_level_values('month').values.astype(str)
Out[194]: 

array(['1', '2', '3', ..., '10', '11', '12'], 
      dtype='&lt;U21')
In[195]: df.index.get_level_values('month').values.astype(str) + df.index.get_level_values('year').values.astype(str)

TypeError: ufunc 'add' did not contain a loop with signature matching types
 dtype('&lt;U21') dtype('&lt;U21') dtype('&lt;U21')
</code></pre>

<p>How can I add create the datetime index here?</p>

<p>I think you can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html" rel="nofollow"><code>to_datetime</code></a>, but first need multiple <code>year</code> and <code>month</code> values:</p>

<pre><code>y = df.index.get_level_values('year')
m = df.index.get_level_values('month')

df['Date'] = pd.to_datetime(y * 10000 + m * 100 + 1, format="%Y%m%d")
print (df)
                              price                 Date
                                foo       bar           
foo          bar year month                             
997182819645 11  2010 1      1.1900  3.000000 2010-01-01
                      2      2.2625  4.001769 2010-02-01
</code></pre>

<p>If need then append column to <code>index</code>:</p>

<pre><code>df['Date'] = pd.to_datetime(y * 10000 + m * 100 + 1, format="%Y%m%d")
df.set_index('Date', append=True, inplace=True)
print (df)
                                         price          
                                           foo       bar
foo          bar year month Date                        
997182819645 11  2010 1     2010-01-01  1.1900  3.000000
                      2     2010-02-01  2.2625  4.001769
</code></pre>

<hr>

<p>Another solution with creating new <code>DataFrame</code>, but need last <a href="http://pandas.pydata.org/pandas-docs/stable/whatsnew.html#whatsnew-0181-enhancements-assembling" rel="nofollow">0.18.1 version</a>:</p>

<pre><code>y = df.index.get_level_values('year')
m = df.index.get_level_values('month')
d = pd.Index(len(df.index) * [1], name='day')
df1 = pd.DataFrame({'year':y, 'month':m, 'day':d}, index=df.index)

df['Date']  = pd.to_datetime(df1)
print (df)
                              price                 Date
                                foo       bar           
foo          bar year month                             
997182819645 11  2010 1      1.1900  3.000000 2010-01-01
                      2      2.2625  4.001769 2010-02-01
</code></pre>

python datetime pandas multi-index datetimeindex