Is there a way to assign a rank to three values and then create a new column that takes the minimum-CodePudding

In my one sheet excel file that i created thru my SQL, I have 3 columns that represent letter ratings. The rating values may differ between ratings 1, 2, and 3, but they can still be ranked with the same value.

I am trying to create a new column in my excel file that can take these 3 letter ratings and pull the minimum rating. I am struggling with how to execute this...I'm not sure where to start.

I need to assign a number ranking to each possible rating value under rating_1, rating_2, and rating_3 (rating_1_rank = if rating_1 like 'A ' then 3 elseif...)

I need to create a new column in my SQL file that can calculate the 3 number rankings of rating_1, rating_2, rating_3 and then output the minimum rating. I'm not sure where to store the number rankings of each rating once I've ranked them so i could use a function to output the minimum rating (the output could be either the letter ranking or the number ranking -- whichever is simplest).

Next...after the new column pulls the minimum rating, i also need to convert it back to the format of Rating_1

    ranking    | Rating_1 | Rating_2 | Rating_3 | NEW_COLUMN |
   (1 lowest)  | -------- | -------- | -------- | --------   |
        3      |    A     |   AA     |    Aa    | min(rating)|
        2      |    B     |   BB     |    Bb    | min(rating)|
        1      |    Fa    |   Fb     |    Fc    | min(rating)|
               | -------- | -------- | -------- | ---------  |


               | Rating_1 | Rating_2 | Rating_3 | NEW_COLUMN |

so for example: | A | BB | Fc | Fa |

Could i please get some suggestions for how to start this? Or if im on the right track of thinking

I tried doing a 2-d list to create the number rankings and associated letter ratings, but I wasn't sure how to assign number ranking to my SQL results in rating_1, rating_2, and rating_3

rating_1rank = [['A ', 3], ['B ', 2], ['Fa', 1] rating_2rank = [['AA', 3], ['BB', 2], ['Fb', 1] ` rating_3rank = [['Aa', 3], ['Bb', 2], ['Fc', 1]

I'm stumped on how to store the number rankings of each rating unless I create 3 completely new columns. Would like to avoid that if possible

CodePudding user response：

Given your dataframe as:

df = pd.DataFrame(
    {"Rating_1": ["A ", "AA", "Aa"],
     "Rating_2": ["B ", "BB", "Bb"],
     "Rating_3": ["Fa", "Fb", "Fc"]}
)

You can create a mapping dictionary (instead of lists) like this:

mapping = {
    "A ": 3, "AA": 3, "Aa": 3,
    "B ": 2, "BB": 2, "Bb": 2,
    "Fa": 1, "Fb": 1, "Fc": 1,
}

Mapping each rating to a numerical value.

To get the minimum for each row, use min with axis=1

df.replace(mapping).min(axis=1)  # For the given example this returns 1 for every row

Assign the value to a new column like this:

df["NEW_COLUMN"] = df.replace(mapping).min(axis=1)

you end up with:

df
  Rating_1 Rating_2 Rating_3  NEW_COLUMN
0       A        B        Fa           1
1       AA       BB       Fb           1
2       Aa       Bb       Fc           1