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>Since you're using Python 2.7, you can use the <code>cmp</code> keyword argument to <code>sorted</code>. To get the column names in the order that you're looking for, I would do something like:</p>

<pre><code># Returns -1 if first_column[i] &gt; second_column[i] more often.
# Returns 1 if vice versa.
# Returns 0 if equal.
# Assumes df[first_column] and df[second_column] are the same length.
def compare_two(first_column, second_column):
    c1_greater_count = 0
    c2_greater_count = 0
    # Iterate over the two columns in the dataframe. df must be in accessible scope.
    for i in range(len(df[first_column])):
        if df[first_column].iloc(i) &gt; df[second_column].iloc[i]:
            c1_greater_count += 1
        elif df[second_column].iloc[i] &gt; df[first_column].iloc[i]:
            c2_greater_count += 1

    if c1_greater_count &gt; c2_greater_count:
        return -1
    if c2_greater_count &gt; c1_greater_count:
        return 1
    return 0

df = get_dataframe_from_somewhere()
relevant_column_names = get_relevant_column_names(df) # e.g., get all the dates.
sorted_column_names = sorted(relevant_column_names, cmp=compare_two)

# sorted_column_names holds the names of the relevant columns,
# sorted according to the given ordering.
</code></pre>

<p>I'm sure there's a more Pythonic way to do it, but this should work. Note that for Python 3, you can use the <a href="https://wiki.python.org/moin/HowTo/Sorting#The_Old_Way_Using_the_cmp_Parameter" rel="nofollow"><code>cmp_to_key</code></a> utility.</p>

python python-2.7 sorting datetime pandas