return df with lowest performers per col or row

user_14183407 0
Viewed 42

I'm trying to generate a df with the lowest n scores per unit by element. A simplified version would look like this:

data = pd.DataFrame({'Unit':[''A', 'B', C'], 'leader':['John', 'Jane', 'Joe'],element 1':[1.0, 0.97, 0.65], 'element 2':[0.55, 0.67, 1.0], 'element 3':[0.32, 0.87, 0.66 }, index=['Unit')

This results in:

       leader  element 1 element 2 element 3
A        John       1.0       0.55     0.32
B        Jane      0.97       0.67     0.87
C         Joe      0.65        1.0     0.66

I'm trying to return the top two worst performing elements by score and element tile per unit. The output should look like this:

       leader Worst performing element Worst performing score Second worst element Second worse score
Unit 1   John                element 3                  0.32             element 2               0.55
Unit 2   Jane                element 2                  0.67             element 3               0.87
Unit 3    Joe                element 1                  0.65             element 3               0.67

I've tried a pivot_table and then looping for the .min() value by row (ex 1), but I can't get the second worse value then. .nsmallest isn't cooperating by row either.

ex1:

scorecard = pd.DataFrame()
elements = ['element 1', 'element 2', 'element 3']

for row in data:
    scorecard['Unit'] = data['Unit']
    scorecard['leader'] = data['leader']
    scorecard['Lowest Element Compliance'] = april[elements].min(axis=1)
    scorecard['Lowest Performing Element'] = april[elements].idxmin(axis=1)
    unit_sorted = april[elements]

scorecard

Any help is greatly appreciated!!

Chris

pandas pivot-table python
1 Answers

You're almost there, but you're missing some important concepts in pandas.

Keep in mind that looping over a pandas.DataFrame() directly will iterate over the dataframe's columns. For example, for col in pandas.DataFrame() is the same as for col in pandas.DataFrame().columns.

In order to solve your problem, you should've used pandas.DataFrame().iterrows() which iterates over the dataframe's column followed by a .sort_values() on the rows containing your elements. The solution to your problem will be like this:

scorecard = pd.DataFrame()
elements = ['element 1', 'element 2', 'element 3']
columns = ['leader', 'Worst performing element', 'Worst performing score', 'Second worst element', 'Second worst score']

for idx, row in data.iterrows():
    # scores sorted in ascending order
    scores = row[elements].sort_values()
    # grab leader value
    leader = row['leader']
    # add values to scorecard
    scorecard.loc[idx, columns] = [leader, scores.index[0], scores.values[0], scores.index[1], scores.values[1]]
    
scorecard

Output:

        leader  Worst performing element    Worst performing score  Second worst element    Second worst score
Unit 1    John                 element 3                      0.32             element 2                  0.55
Unit 2    Jane                 element 2                      0.67             element 3                  0.87
Unit 3     Joe                 element 1                      0.65             element 3                  0.66
user_157327550
Related