align rows by date in pandas dataframe
<p>An excerpt of the Dateframe could look like this (it's certainly much larger):</p>

<pre><code>            Date1        Log1     Date2     Log2    Date3       Log3   
 Index
   0       01.01.2000    1000   02.01.2000  2000   01.01.2000   3000
   1       02.01.2000    1050   03.01.2000  1950   02.01.2000   3020
   2       03.01.2000    1100   04.01.2000  2000   03.01.2000   3000
</code></pre>

<p>Is there a quick way to align the rows so that the dates (here in Columns <code>Date3</code> and <code>Log3</code>) match with those in column <code>Date1</code>?</p>

<pre><code>            Date1        Log1     Date2     Log2    Date3       Log3   
 Index
   0       01.01.2000    1000   NaN                01.01.2000   3000
   1       02.01.2000    1050   02.01.2000  2000   02.01.2000   3020
   2       03.01.2000    1100   03.01.2000  1950   03.01.2000   3000
</code></pre>

<p>Many thanks in advance</p>

<p>A dictionary to represent your data, this is just a convenience to load the sample data to dataframe.</p>

<pre><code>d = {'Date1': {0: '01.01.2000', 1: '02.01.2000', 2: '03.01.2000'}, 'Date3': {0: '01.01.2000', 1: '02.01.2000', 2: '03.01.2000'}, 'Date2': {0: '02.01.2000', 1: '03.01.2000', 2: '04.01.2000'}, 'Log2': {0: 2000, 1: 1950, 2: 2000}, 'Log3': {0: 3000, 1: 3020, 2: 3000}, 'Log1': {0: 1000, 1: 1050, 2: 1100}}
df = pd.DataFrame(d)
df = df[['Date1','Log1','Date2','Log2','Date3','Log3']]
df.index.names = ['Index']

print df
</code></pre>

<p><strong>Starting Dataframe:</strong></p>

<pre><code>            Date1  Log1       Date2  Log2       Date3  Log3
Index                                                      
0      01.01.2000  1000  02.01.2000  2000  01.01.2000  3000
1      02.01.2000  1050  03.01.2000  1950  02.01.2000  3020
2      03.01.2000  1100  04.01.2000  2000  03.01.2000  3000
</code></pre>

<p>This is crude but does the job:</p>

<pre><code>list_dfs = []
for i in range(1,4):
    column_subset =  [col for col in df.columns if str(i) in col]
    df_subset_columns =  df[column_subset]
    df_subset_columns.columns = ['Date','Log']
    df_subset_columns['id'] = i
    list_dfs.append(df_subset_columns)

df =  pd.concat(list_dfs,axis=0,ignore_index=True)

df = df.set_index(['Date','id'])
df = df.unstack('id')
df.columns = df.columns.droplevel(0)
</code></pre>

<p>At this point I think this is what you are looking logically:</p>

<pre><code>id             1     2     3
Date                        
01.01.2000 1,000   nan 3,000
02.01.2000 1,050 2,000 3,020
03.01.2000 1,100 1,950 3,000
04.01.2000   nan 2,000   nan
</code></pre>

<p>But to revert back to the desired output</p>

<pre><code>list_dfs = []
for i in range(1,4):
    df_s = df[i].to_frame()
    df_s.columns = ['Log' + str(i)]
    print df_s
    list_dfs.append(df_s.reset_index())

print pd.concat(list_dfs,axis=1)
</code></pre>

python pandas dataframe