Finding and storing dominant rows of pandas df into another df

user_12074357 0
Viewed 85

I have a dataframe df.shape (15,4) I want to pairwise compare 2 rows, extract the dominant row into another df and now compare following row with less dominant row. Repeat the loop till we get least dominant row at last in the new df. Here's what I'm trying:

for i in range(len(df)):
a = df.iloc[i]
b = df.iloc[i+1]
diff=a-b
if(diff >= 0):
    l.append(a) #List/df
    df = df.drop(df.iloc[i])  #Extract 1st row into another df if it's dominant
#Now compare 3rd row with not dominant row (either 1st or 3rd) and repeat the loop
print(l)

My method seems really time consuming. Are there any inbuilt pandas functions which Can help me achieve the task easily?

df.head

      C      W       L    D
A1  82.0  78.00  1100.0  3.0
A2  19.0  99.00  9520.0  3.0
A3  25.0  42.00  1700.0  7.0
A4  93.0  37.00  1700.0  7.0
A5   9.2   0.44   510.0  7.0

(15 rows, 4 cols)

output = in new df

Count of number of times each A[i] was dominant over the following A[i+1] row for each independent column (C,W,L and D) respectively.

For example:

First for Criteria C:
new_df should be = df.head()
A1  #Since A1.C is greater than A2.C
A3  #Next comparison between A2.C and A3.C, A3.C wins, hence stored here
A4  #A2 and A4, A4 win
A2  #A2 (still remaining) and A5, here A2 wins and is stored
A5  #Now A5 stored it it's greater than A6.. so on and so forth till A15

Then for rest 4 columns. Was I able to explain what I'm trying to do or did I make it more complicated? Waiting for your comment, I can add more info if required. Many Thanks.

pandas iteration python-3.x loops
1 Answers

Here's an example using a for loop for column C. But normally when using pandas you really shouldn't use a for loop. I just don't know how else to solve this part of the problem.

import pandas as pd
from io import StringIO

text = """
      C      W       L    D
A1  82.0  78.00  1100.0  3.0
A2  19.0  99.00  9520.0  3.0
A3  25.0  42.00  1700.0  7.0
A4  93.0  37.00  1700.0  7.0
A5   9.2   0.44   510.0  7.0
"""

df_subset = df['C'].copy().reset_index()

# loop over 2 lines each, see who is the winner, and sort on that
for i in range(len(df_subset)-1):
    df_subset.iloc[i:i+2, :] = df_subset.iloc[i:i+2,:].sort_values(
        ascending=False, 
        by='C',
    ).values
    
df_subset.set_index('index')

Resulting series:

index C 
A1    82.0
A3    25.0
A4    93.0
A2    19.0
A5     9.2
user_34891550
Related