[Python] 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'],
'A_NEW': [np.nan, np.nan, np.nan, np.nan],
'Priority_A_NEW': [np.nan, np.nan, np.nan, np.nan],
'B_OLD': ['B2', 'B1', 'B1', 'B2'],
'B_NEW': [np.nan, np.nan, np.nan, np.nan],
'Priority_B_NEW': [np.nan, np.nan, np.nan, np.nan],
'C_OLD': ['C4', 'C3', 'C1', 'C2'],
'C_NEW': [np.nan, np.nan, np.nan, np.nan],
'Priority_C_NEW': [np.nan, np.nan, np.nan, np.nan],
}
df = pd.DataFrame(data)




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.

Or in the table with priorities it looks like this:



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.

I gor pretty far, though the solution is not pretty (quiet extensive for loop..), and the comparison between priorities is something I'm stuck at at the moment as I get this solution from the code I have:



My code:

import pandas as pd
import numpy as np

data = {
'ID': [1, 2, 3, 4],
'A_OLD': ['A1', 'A4', 'A3', 'A4'],
'A_NEW': [np.nan, np.nan, np.nan, np.nan],
'Priority_A_NEW': [np.nan, np.nan, np.nan, np.nan],
'B_OLD': ['B2', 'B1', 'B1', 'B2'],
'B_NEW': [np.nan, np.nan, np.nan, np.nan],
'Priority_B_NEW': [np.nan, np.nan, np.nan, np.nan],
'C_OLD': ['C4', 'C3', 'C1', 'C2'],
'C_NEW': [np.nan, np.nan, np.nan, np.nan],
'Priority_C_NEW': [np.nan, np.nan, np.nan, np.nan],
}

conversionTable_data = {
'Dropdown': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C'],
'Value_OLD': ['A1', 'A2', 'A3', 'A4', 'B1', 'B2', 'B3', 'B4', 'C1', 'C2', 'C3', 'C4'],
'Value_NEW': ['A-1', 'A-2', 'A-3', 'A-4', 'B-1', 'B-2', 'B-3', 'B-4', 'C-1', 'C-2', 'C-3', 'C-4'],
'Priority_NEW': [2, 1, 2, 3, 1, 1, 2, 3, 1, 3, 1, 2],
'Other_Dropdown_Change': [np.nan, np.nan, np.nan, np.nan, 'A', np.nan, np.nan, 'A', 'A', 'B', np.nan, np.nan],
'Change_Value_Other_Dropdown': [np.nan, np.nan, np.nan, np.nan, 'A-1a', np.nan, np.nan, 'A-2a', 'A-3a', 'B-1a',
np.nan, np.nan],
'Priority_Other_Dropdown': [np.nan, np.nan, np.nan, np.nan, 2, np.nan, np.nan, 2, 1, 1, np.nan, np.nan]
}

# Convert string holding columns to 'object' type
df = pd.DataFrame(data)
df['A_NEW'] = df['A_NEW'].astype('object')
df['B_NEW'] = df['B_NEW'].astype('object')
df['C_NEW'] = df['C_NEW'].astype('object')

df_conversionTable = pd.DataFrame(conversionTable_data)

for index, row in df.iterrows():
# For A A
OldRowA = row['A_OLD']
filtered_conversionTable_A = df_conversionTable[(df_conversionTable['Dropdown'] == 'A') & (df_conversionTable['Value_OLD'] == OldRowA)]

if not filtered_conversionTable_A.empty:
df.at[index, 'A_NEW'] = filtered_conversionTable_A['Value_NEW'].iloc[0]
df.at[index, 'Priority_A_NEW'] = filtered_conversionTable_A['Priority_NEW'].iloc[0]

# Check for Other_Dropdown_Change
if pd.notna(filtered_conversionTable_A['Other_Dropdown_Change'].iloc[0]):
DropdownType = filtered_conversionTable_A['Other_Dropdown_Change'].iloc[0]
if DropdownType == 'B':
NEW = 'B_NEW'
PRIORITY = 'Priority_B_NEW'
OLD = 'B_OLD'
elif DropdownType == 'C':
NEW = 'C_NEW'
PRIORITY = 'Priority_C_NEW'
OLD = 'C_OLD'
else:
print("Mistake in Type")
continue # Skip to next iteration

# Check if NEW column is not NaN
if pd.notna(row[NEW]):
# Compare priority
if pd.notna(row[PRIORITY]) and row[PRIORITY] < filtered_conversionTable_A['Priority_Other_Dropdown'].iloc[0]:
df.at[index, NEW] = filtered_conversionTable_A['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, PRIORITY] = filtered_conversionTable_A['Priority_Other_Dropdown'].iloc[0]
elif row[PRIORITY] == filtered_conversionTable_A['Priority_Other_Dropdown'].iloc[0]:
# Concatenate values
combi_new_value = row[NEW] + '_' + row[OLD]
df.at[index, NEW] = combi_new_value
else:
# If NEW is NaN, update it
df.at[index, NEW] = filtered_conversionTable_A['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, PRIORITY] = filtered_conversionTable_A['Priority_Other_Dropdown'].iloc[0]

# For B

OldRowB = row['B_OLD']
filtered_conversionTable_B = df_conversionTable[(df_conversionTable['Dropdown'] == 'B') & (df_conversionTable['Value_OLD'] == OldRowB)]

if not filtered_conversionTable_B.empty:
if pd.notna(row['B_NEW']):
# Compare priority
if pd.notna(row['Priority_B_NEW']) and row['Priority_B_NEW'] < \
filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]:
df.at[index, 'B_NEW'] = filtered_conversionTable_B['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, 'Priority_B_NEW'] = filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]
elif row['Priority_B_NEW'] == filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]:
# Concatenate values
combi_new_value = row['B_NEW'] + '_' + filtered_conversionTable_B['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, 'B_NEW'] = combi_new_value
else:
df.at[index, 'B_NEW'] = filtered_conversionTable_B['Value_NEW'].iloc[0]
df.at[index, 'Priority_B_NEW'] = filtered_conversionTable_B['Priority_NEW'].iloc[0]

# Check for Other_Dropdown_Change
if pd.notna(filtered_conversionTable_B['Other_Dropdown_Change'].iloc[0]):
DropdownType = filtered_conversionTable_B['Other_Dropdown_Change'].iloc[0]
if DropdownType == 'A':
NEW = 'A_NEW'
PRIORITY = 'Priority_A_NEW'
OLD = 'A_OLD'
elif DropdownType == 'C':
NEW = 'C_NEW'
PRIORITY = 'Priority_C_NEW'
OLD = 'C_OLD'
else:
continue # Skip to next iteration

# Check if NEW column is not NaN
if pd.notna(row[NEW]):
# Compare priority
if pd.notna(row[PRIORITY]) and row[PRIORITY] < filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]:
df.at[index, NEW] = filtered_conversionTable_B['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, PRIORITY] = filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]
elif row[PRIORITY] == filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]:
# Concatenate values
combi_new_value = row[NEW] + '_' + filtered_conversionTable_B['Change_Value_Other_Dropdown'].iloc[0]
print(combi_new_value)
df.at[index, NEW] = combi_new_value
else:
# If NEW is NaN, update it
df.at[index, NEW] = filtered_conversionTable_B['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, PRIORITY] = filtered_conversionTable_B['Priority_Other_Dropdown'].iloc[0]

# For C
OldRowC = row['C_OLD']
filtered_conversionTable_C = df_conversionTable[(df_conversionTable['Dropdown'] == 'C') & (df_conversionTable['Value_OLD'] == OldRowC)]

if not filtered_conversionTable_C.empty:
if pd.notna(row['C_NEW']):
# Compare priority
if pd.notna(row['Priority_C_NEW']) and row['Priority_C_NEW'] < \
filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]:
df.at[index, 'C_NEW'] = filtered_conversionTable_C['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, 'Priority_C_NEW'] = filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]
elif row['Priority_C_NEW'] == filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]:
# Concatenate values
combi_new_value = row['C_NEW'] + '_' + filtered_conversionTable_C['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, 'C_NEW'] = combi_new_value
print("geplakte versie C:")
print(combi_new_value)
else:
df.at[index, 'C_NEW'] = filtered_conversionTable_C['Value_NEW'].iloc[0]
df.at[index, 'Priority_C_NEW'] = filtered_conversionTable_C['Priority_NEW'].iloc[0]

# Check for Other_Dropdown_Change
if pd.notna(filtered_conversionTable_C['Other_Dropdown_Change'].iloc[0]):
print("test")
DropdownType = filtered_conversionTable_C['Other_Dropdown_Change'].iloc[0]
if DropdownType == 'A':
NEW = 'A_NEW'
PRIORITY = 'Priority_A_NEW'
OLD = 'A_OLD'
elif DropdownType == 'B':
NEW = 'B_NEW'
PRIORITY = 'Priority_B_NEW'
OLD = 'B_OLD'
else:
print("Error in Type")
continue # Skip to next iteration

# Check if NEW column is not NaN
if pd.notna(row[NEW]):
# Compare priority
if pd.notna(row[PRIORITY]) and row[PRIORITY] < filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]:
df.at[index, NEW] = filtered_conversionTable_C['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, PRIORITY] = filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]
elif row[PRIORITY] == filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]:
# Concatenate values
combi_new_value = row[NEW] + '_' + filtered_conversionTable_C['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, NEW] = combi_new_value
else:
# If NEW is NaN, update it
df.at[index, NEW] = filtered_conversionTable_C['Change_Value_Other_Dropdown'].iloc[0]
df.at[index, PRIORITY] = filtered_conversionTable_C['Priority_Other_Dropdown'].iloc[0]


Additional Comment after edit

My code at the moment (apart from not being very pretty) seems to be able to find the correct new value for the dropdowns but it does not do the extra replacement of 'Other dropdown change' yet. Though I have not checked everything, it seems like this is the only problem left..

Continue reading...