How can I sort datetime columns by row value in a Pandas dataframe?
<p>I'm new to Python and Pandas, and I've pulled in a database table that contains 15+ different datetime columns. My task is to sort these columns generally by earliest to latest value in the rows. However, the data is not clean; sometimes, where Column A's date would come before Column B's date in Row 0, A would come after B in Row 1. </p>

<p>I wrote a few functions (redacted here for simplicity) that compare two columns by calculating the percentage of times dates in A come before and after B, and then sorting the columns based on that percentage:</p>

<pre><code>def get_percentage(df, df_subset):
    return len(df_subset)/float(len(df))    

def duration_report(df, earlier_column, later_column):   
    results = {}
    td = df[later_column] - df[earlier_column]
    results["Before"] = get_percentage(df, df.loc[td &gt;= pd.Timedelta(0)])
    results["After"] = get_percentage(df, df.loc[td &lt;= pd.Timedelta(0)])
    ind = "%s vs %s" % (earlier_column, later_column)
    return pd.DataFrame(data=results, index=[ind])

def order_date_columns(df, col1, col2):
    before = duration_report(df, col1, col2).Before.values[0]
    after = duration_report(df, col1, col2).After.values[0]
    if before &gt;= after:
        return [col1, col2]
    else:
        return [col2, col1]
</code></pre>

<p>My goal with the above code is to programmatically implement the following: </p>

<blockquote>
  <p>If Col A dates come before Col B dates 50+% of the time, Col A should come before Col B in the list of earliest to latest datetime columns.</p>
</blockquote>

<p>The <code>order_date_columns()</code> function successfully sorts two columns into the correct order, but how do I apply this sorting to the 15+ columns at once? I've looked into <code>df.apply()</code>, <code>lambda</code>, and <code>map()</code>, but haven't been able to crack this problem. </p>

<p>Any help (with code clarity/efficiency, too) would be appreciated!</p>

<p>If you don't mind taking a bit of a shortcut and using the median of each date column, this should work:</p>

<pre><code>def order_date_columns(df, date_columns_to_sort):
    x = [(col, df[col].astype(np.int64).median()) for col in date_columns_to_sort]
    return [x[0] for x in sorted(x, key=lambda x: x[1])]
</code></pre>

python python-2.7 sorting datetime pandas