Python – Priority-Based Conditional Data Transformation

I have a use case of a form with 3 dropdowns ( A, B and C for this example). They each have 4 options:

Which gives me data per record/ID like this:

data = {
    'ID': [1, 2, 3, 4],
    'A_OLD': ['A1', 'A4', 'A3', 'A4'],
    'B_OLD': ['B2', 'B1', 'B1', 'B2'],
    'C_OLD': ['C4', 'C3', 'C1', 'C2']
}

ID     A_OLD  B_OLD  C_OLD
 1     A1     B2     C4
 2     A4     B1     C3
 3     A3     B1     C1
 4     A4     B2     C2

I want to change the naming of the options. I need to change all the old data to the new values. The complexity comes in due to the following:

• Some of the values will not only change the value of 1 dropdown value, but also desides the change in another dropdown
• This might lead to conflicts between 2 (or more) new values.
• To resolve most of the conflicts I added priotities

If I apply the conversion, taking into account the priorities, I want my result to be like this:

ID   A_OLD  B_OLD  C_OLD  A_NEW    B_NEW       C_NEW
1    A1     B2     C4     A-1      B-2         C-4
2    A4     B1     C3     A-1<     B-1         C-3
3    A3     B1     C1     A-3<     B-1         C-1
4    A4     B2     C2     A-4      B-1, B-2<   C-2

The ‘<‘ gives an indication of which values were affected by the cross-over and priorities.

To make it more clear I also created an extra image:

I know that this is possible, with the loop, if/else and the comparison of the priorities it should do it, but I can’t get to the right answer.

You need to sign in to view this answers