I have a sample csv like this :
keys key_regex datatype detailed_datatype precedence val_regex val_regex_2 val_regex_3 max_words alpha_char_check
0 billingAddress original_billing_key_regex alphabetic address primary NaN NaN NaN NaN NaN
1 deliveryAddress original_delivery_key_regex alphabetic address primary NaN NaN NaN NaN NaN
2 notifyParty original_notify_party_regex alphabetic alphabetic primary NaN NaN NaN NaN NaN
3 originAddress original_seller_address_regex alphabetic address primary NaN NaN NaN NaN NaN
4 billingAddressAlt alternative_billing_key_regex alphabetic address tertiary NaN NaN NaN NaN NaN
5 deliveryAddressAlt alternative_delivery_key_regex alphabetic address tertiary NaN NaN NaN 5.0 1.0
6 originAddressAlt alternative_seller_key_regex alphabetic address tertiary NaN sample_val_re1 NaN NaN 0.0
I'm trying to replace the rows for which keys column have value as the key in the tertiary_row_replacement_dict with the rows with the keys column value as the corresponding value and then rename the precendence column value from 'tertiary' to 'primary' - while keeping the index position same as before.
The expected output is like this :
keys key_regex datatype detailed_datatype precedence val_regex val_regex_2 val_regex_3 max_words alpha_char_check
0 billingAddress alternative_billing_key_regex alphabetic address primary NaN NaN NaN NaN NaN
1 deliveryAddress alternative_delivery_key_regex alphabetic address primary NaN NaN NaN 5.0 1.0
2 notifyParty original_notify_party_regex alphabetic alphabetic primary NaN NaN NaN NaN NaN
3 originAddress alternative_seller_key_regex alphabetic address primary NaN sample_val_re1 NaN NaN 0.0
There are 3 original csvs - each of them are huge with lots of similar cases i.e. keys with primary precedence and their alternative keys with tertiary precedence. I've dictionary with the keys' alter like this :
tertiary_row_replacement_dict = {
"originAddress": "originAddressAlt",
"deliveryAddress": "deliveryAddressAlt",
# "totalAmount": "totalAmountAlt",
"billingAddress": "billingAddressAlt"
....
}
Provided that the keys and the corresponding values of this dictionary would be always present in the csv, I have this code :
for k, new_k in row_replacement_dict.items():
t2 = df.loc[df['keys']==new_k].index[0]
df.loc[df.loc[df['keys']==k].index[0]] = [i if i!='tertiary' else 'primary' for i in df.loc[t2]]
df = df.replace([new_k, 'tertiary'], [k, 'primary']).drop([t2])
It accomplishes what I am trying to do. It takes around 0.034 seconds to do this just on the test csv and might not be best or optimized way to handle this case of replacing just the rows and replace the cell values. Is there any faster alternative way with the prerequisite knowledge which rows to be replaced with which one (i.e. it's not mandatory to use that dictionary, we can use it as list of tuples of list of lists for speed trade off).