Sort certain rows in Data Frames by columns
<p>I have a dataframe that is unsorted. I want to sort columns <code>A</code>,<code>B</code>,<code>C</code> and <code>D</code> in descending order (largest to smallest) however they must remain in the denomination group. For example, it should sort denomination 100 by columns <code>A</code>,<code>B</code>,<code>C</code> and <code>D</code> so hence the row  0,1,2 changes to 0,2,1. </p>

<pre><code>Index   Denomination    A   B   C   D
0        100            5   0   0   0
1        100            0   0   1   0
2        100            0   2   0   0
3        200            5   2   0   0
4        200            5   0   1   0
5        200            0   4   0   0
6        200            10  0   0   0
7        200            0   2   1   0
8        200            0   0   2   0
</code></pre>

<p>The order of sorting levels must be <code>A</code>,<code>B</code>,<code>C</code> and then <code>D</code>. Relabeling <code>Index</code> is not important. 
The resulting dataframe should be:</p>

<pre><code>Index   Denomination    A   B   C   D
0        100            5   0   0   0
2        100            0   2   0   0
1        100            0   0   1   0
6        200            10  0   0   0
3        200            5   2   0   0
4        200            5   0   1   0
5        200            0   4   0   0
7        200            0   2   1   0
8        200            0   0   2   0
</code></pre>

<p>This can be done in excel by selecting the rows and then applying a custom sort but I need it to be done in python using dataframes.</p>

<p>This should do it:</p>

<pre><code>df.sort_values(by=['Denomination', 'A', 'B', 'C', 'D'], 
               ascending=[True, False, False, False, False])
Out: 
   Denomination   A  B  C  D
0           100   5  0  0  0
2           100   0  2  0  0
1           100   0  0  1  0
6           200  10  0  0  0
3           200   5  2  0  0
4           200   5  0  1  0
5           200   0  4  0  0
7           200   0  2  1  0
8           200   0  0  2  0
</code></pre>

<p>Sorts by <code>Denomination</code> in ascending order; in case of ties, it sorts by <code>A</code> in descending order; in case of ties, it sorts by <code>B</code> in descending order and so on.</p>

<p>If the Denomination column shouldn't be sorted but should be left in the order of appearence for groups, you can do something like this:</p>

<pre><code>df.groupby('Denomination')['Denomination'].transform(pd.Series.first_valid_index)
Out: 
0    0
1    0
2    0
3    3
4    3
5    3
6    3
7    3
8    3
Name: Denomination, dtype: int64
</code></pre>

<p>This returns a new column to keep track of the groups. You can add this column to the DataFrame and it can have the highest priority.</p>

<pre><code>(df.assign(denomination_group = 
     df.groupby('Denomination')['Denomination'].transform(pd.Series.first_valid_index))
   .sort_values(by=['denomination_group', 'A', 'B', 'C', 'D'], 
                ascending=[True, False, False, False, False])
   .drop('denomination_group', axis=1))
</code></pre>

python sorting pandas dataframe